Thursday, November 19, 2009

Shrinking SQL Server Database and Transaction Log

I was spending time on shrinking the transaction log and DB, some of our developer boxes didnt have enough space.After a while i just figured so i thought of sharing with others


backup log [DataBase Name] with truncate_only --Not supported in 2008
go
DBCC SHRINKDATABASE ([Data Base Name], 10, TRUNCATEONLY)
go

SQL Server 2008 use different approach because its no longer supports backup log with truncate_only

In SQL Server 2008 , Transaction can be shrinked in two step.

Set Recovery Model to Simple

Shrink the file using DBCC ShrinkFile

GO

-- database recovery model

ALTER DATABASE [Database Name]

SET RECOVERY SIMPLE;

GO

-- Shrink log file to 1 MB.

DBCC SHRINKFILE (2, 1)

-- 2 means log file ID or you can specify logfile like DBCC SHRINKFILE (db_log' , 1))

GO

ALTER DATABASE [Database Name]

SET RECOVERY FULL

GO

No comments:

Post a Comment