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.

Sql Server Reindex Tables

During my attempt to shrink my database so that it would fit on my laptop (it was 20 gigs), I figured out that I needed to reindex all my tables.  Usually the format of your data and log files will follow this pattern – just replace the {DATABASE_NAME} with the name of your database.

DBCC SHRINKFILE ({DATABASE_NAME}_data,5)

DBCC SHRINKFILE (
{DATABASE_NAME}_log, 1);
BACKUP LOG
{DATABASE_NAME} WITH TRUNCATE_ONLY;
DBCC SHRINKFILE (
{DATABASE_NAME}_log, 1);