Content

Shrink Sql Server Database and Transaction Log

8 Jun

+ 0

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.

Comment

#

I reserve the right to delete and / or modify the comments that contain inappropriate language, spam or other behavior not appropriate in a civilized community. If your comment does not appear, it may be that akismet caught it.