Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
DBCC ShrinkDatabase()
DBCC ShrinkFile()
  1. Do I need to run both DBCC commands in order to shrink the database?
  2. What is the difference between these two above?
share|improve this question

migrated from stackoverflow.com Jan 22 '12 at 14:38

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 7 down vote accepted

Simply...

  • ShrinkDatabase: shrink all files
  • ShrinkFile: just one file

For example, you may have a log backup issue and it's grown out of control so you run DBCC SHRINKFILE.

You almost never use ShrinkDatabase

Before you consider using either command, please read Paul Randall's blog on shrinking.

share|improve this answer
    
Should you suggest i should only shrink ldf (log file) not mdf? or which one? –  Jango Aug 23 '10 at 18:37
1  
I'd shrink neither one unless there was a clear reason. The files are the size they are because they need to be. –  gbn Aug 23 '10 at 18:38
    
Then why various blogs are suggesting to shrink file/database in the regular maintenance plan. (not offending you, just wanna clear my doubts) –  Jango Aug 23 '10 at 18:46
2  
Frankly, because they are clueless idiots who don't understand SQL Server –  gbn Aug 23 '10 at 18:47
    
Thanks man. you are right. I just read Brent Ozar blog.. he is literally swearing in his blog "Stop Shrinking Your Database Files. Seriously. Now.". –  Jango Aug 23 '10 at 19:03

A default database has two files

MyDb.MDF and MyDb.LDF

The MDF file is the data file where the primary partition resides. Depending on your needs, you can partition a database into multiple files. This is done so that data (single or multiple tables) can span multiple files that usually are put on separate hard drives to achieve a higher performance.

If you shrink a database, all files associated with that database will be shrunk.

If you shrink a file, then only the chosen file will be shrunk.

You only need to use the Shrink Database command. But it is not usually a good practice to do this and is not a recommended practice either.

If you tell us what the issue is that you are experiencing, we can give you more information on how to resolve it.

share|improve this answer
    
Oh..issue is our production server is running out of the disk space. Adding more disk is ideal but i was wondering what else we can do? –  Jango Aug 23 '10 at 18:42

Most likely we use only dbcc shrinkfile . sp_helpdb 'databasename' provides the list of data&log files in a database.

right click on the mnagement studio, tasks->shrink->file provides how much % you can shrink.

what ever we shrink , it is avilable back on the disk as free space.

eg: use 'databasename' dbcc shrinkfile(fileid,100)

here 100 is 100 MB

fileid can get from sp_helpdb 'databasename'

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.