Shrink Sql Server Database and Transaction Log

Shrinking the transaction log was so very easy once I figured it out.  All you need to do:

1. Backup your database just in case.
2. Run the following. If your database is called “Vampires”, then Database name will be “Vampires” and most likely your log file will be “Vampires_Log”:

USE <DATABASE NAME>
BACKUP LOG <DATABASE NAME> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<LOG FILE NAME >, 2)

3. To shrink my database, I first reindexed all the tables, then ran the following. If your database is called “Vampires”, then your database file name will most likely be “Vampires_Data” :

USE <DATABASE NAME>
dbcc shrinkfile(FILE_ID('<DATABASE FILE NAME>'),5000)

To get the names of your file and log names, just right click on the database in Enterprise Manager and click Properties. Data Files and Transaction Log tabs will tell you what you need to know.

Leave a Reply

Your email address will not be published. Required fields are marked *