CODEWITHSUNDEEP
sql-server
Rob L
Rob L

Reputation: 3303

In SQL Server, what reason is there to use DBCC SHRINKDATABASE with the 'NOTRUNCATE' parameter?

According to Microsoft's documentation here it says that if you invoke the following command

DBCC SHRINKDATABASE(N'MyDb', NOTRUNCATE)

that because the NOTRUNCATE parameter is passed that the freed up space will not be returned to the OS and therefore it will appear the database was not shrunk/modified. Moreover, the docs states that the physical file size will not change. So why would you ever do anything but

DBCC SHRINKDATABASE(N'MyDb', 20) --20 is the percentage of free space that you want left in the database file after the database has been shrunk

or

DBCC SHRINKDATABASE(N'MyDb')

Isn't the entire point of shrinking the database to free up memory? What am I missing here?

Upvotes: 0

Views: 384

Answers (1)

Philip Kelley
Philip Kelley

Reputation: 40319

I started writing a reply, but convinced myself my argument didn’t make sense. I then got another idea, started writing that, and again talked myself out of it. Did it a third time, and once again couldn’t rationalize NOTRUNCATE.

The only benefit to not giving up the file space is if you know you’re just going to grab it right back – in which case, why bother with SHRINKDATABASE? Stick with rebuilding the indexes to defrag tables without shrinking the database files (which, notoriously, can and will re-fragment the tables) and you’ve got better results than shrinking would produce.

So, nope, I’ve got no idea why you’d ever do this.

Upvotes: 1

Related Questions