Tag: shrink

How to shrink a SQL Transaction Log

For 2005:
–* you can get the logical log file name usingthe following command in Query Analizer:

exec "databaseName".dbo.sp_helpfile

Now execute the following command to shrink the database log to 200MB:

DBCC SHRINKFILE ("logicalLogFileName", 200)
BACKUP LOG "databaseName" WITH TRUNCATE_ONLY
DBCC SHRINKFILE ("logicalLogFileName", 200)

–if it doesn’t work, run the two commands again.

–When done with that, do a full backup of your db as you will have broken your tlog backup chain.

For 2008+

USE databasename;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE databasename
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (2, 1);  -- here 2 is the file ID for trasaction log file,you can also mention the log file name (dbname_log)
GO
-- Reset the database recovery model.
ALTER DATABASE databasename
SET RECOVERY FULL;
GO