Post reply

Query to Shrink the Database [MDF and NDF]

  • August 31, 2010 at 11:55 am

    #227059

    Hi All,

    Can anyone tell me the query, How to Shrink the Full Database [Remove Un-used space] and also how to lock the Databases from users..

    Please let me know these 2 answers as soon as possible..

    Thanks in advance,

    Vamshi.

  • August 31, 2010 at 11:56 am

    #1214517

    Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 31, 2010 at 12:00 pm

    #1214518

    Hi,

    Thanks for your reply.I have a change order to shrink all the databases in one server.Before doing that i need to lock the databases from Users. All these for Audit Trail Clean utility (prune database) . Sp, I need the information about the query.

  • August 31, 2010 at 8:28 pm

    #1214706

    Hi, If you really want to shrink your db, you can do it in the following ways:

    1) DBCC SHRINKDATABASE (DBNAME, 10) ---> Here you dont have the option of specifying which file you want to shrink

    2) DBCC SHRINKFILE (DataFileName, 10)----> Here you are more specific - Filename mentioned

    To prevent other users from connecting, you can put the db in single user mode with dbo use only

    sp_dboption 'dbname','dbo use only','true'

    sp_dboption 'dbname','single user','true'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply