Query to Shrink the Database [MDF and NDF]

Question

Query to Shrink the Database [MDF and NDF]

vamshi.sql

SSCommitted

Points: 1765
More actions
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.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

vamshi.sql SSCommitted Points: 1765 More actions · Answer 2

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.

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 3

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'