How do I shrink my SQL Server Database?

Question

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE

GO

DBCC SHRINKFILE (MyDatabase_Log, 5)

GO

ALTER DATABASE MyDatabase SET RECOVERY FULL

GO

This may seem bizarre, but it's worked for me and I have written a C# program to automate this.

Step 1: Truncate the transaction log (Back up only the transaction log, turning on the option to remove inactive transactions)

Step 2: Run a database shrink, moving all the pages to the start of the files

Step 3: Truncate the transaction log again, as step 2 adds log entries

Step 4: Run a database shrink again.

My stripped down code, which uses the SQL DMO library, is as follows:

SQLDatabase.TransactionLog.Truncate();
SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_NoTruncate);
SQLDatabase.TransactionLog.Truncate();
SQLDatabase.Shrink(5, SQLDMO.SQLDMO_SHRINK_TYPE.SQLDMOShrink_Default);

This is an old question but I just happened upon it.

The really short and a correct answer is already given and has the most votes. That is how you shrink a transaction log, and that was probably the OPs problem. And when the transaction log has grown out of control, it often needs to be shrunk back, but care should be taken to prevent future situations of a log growing out of control. This question on dba.se explains that. Basically - Don't let it get that large in the first place through proper recovery model, transaction log maintenance, transaction management, etc.

But the bigger question in my mind when reading this question about shrinking the data file (or even the log file) is why? and what bad things happen when you try? It appears as though shrink operations were done. Now in this case it makes sense in a sense - because MSDE/Express editions are capped at max DB size. But the right answer may be to look at the right version for your needs. And if you stumble upon this question looking to shrink your production database and this isn't the reason why, you should ask yourself the why? question.

I don't want someone searching the web for "how to shrink a database" coming across this and thinking it is a cool or acceptable thing to do.

Shrinking Data Files is a special task that should be reserved for special occasions. Consider that when you shrink a database, you are effectively fragmenting your indexes. Consider that when you shrink a database you are taking away the free space that a database may someday grow right back into - effectively wasting your time and incurring the performance hit of a shrink operation only to see the DB grow again.

I wrote about this concept in several blog posts about shrinking databases. This one called "Don't touch that shrink button" comes to mind first. I talk about these concepts outlined here - but also the concept of "Right-Sizing" your database. It is far better to decide what your database size needs to be, plan for future growth and allocate it to that amount. With Instant File Initialization available in SQL Server 2005 and beyond for data files, the cost of growths is lower - but I still prefer to have a proper initial application - and I'm far less scared of white space in a database than I am of shrinking in general with no thought first. :)

DBCC SHRINKDATABASE works for me, but this is its full syntax:

DBCC SHRINKDATABASE ( database_name, [target_percent], [truncate] )

where target_percent is the desired percentage of free space left in the database file after the database has been shrunk.

And truncate parameter can be:

NOTRUNCATE

Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.

TRUNCATEONLY

Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

...and yes no_one is right, shrinking datbase is not very good practice becasue for example :

shrink on data files are excellent ways to introduce significant logical fragmentation, becasue it moves pages from the end of the allocated range of a database file to somewhere at the front of the file...

shrink database can have a lot of consequence on database, server.... think a lot about it before you do it!

on the web there are a lot of blogs and articles about it.

Late answer but might be useful useful for someone else

If neither DBCC ShrinkDatabase/ShrinkFile or SSMS (Tasks/Shrink/Database) doesn’t help, there are tools from Quest and ApexSQL that can get the job done, and even schedule periodic shrinking if you need it.

I’ve used the latter one in free trial to do this some time ago, by following short description at the end of this article:

https://solutioncenter.apexsql.com/sql-server-database-shrink-how-and-when-to-schedule-and-perform-shrinking-of-database-files/

All you need to do is install ApexSQL Backup, click "Shrink database" button in the main ribbon, select database in the window that will pop-up, and click "Finish".

You will also need to shrink the individual data files.

It is however not a good idea to shrink the databases. For example see here

You should use:

dbcc shrinkdatabase (MyDB)

It will shrink the log file (keep a windows explorer open and see it happening).