Saturday, June 26, 2010

Is that a transaction log file in your pocket, or are you just happy to see me?

So after much research and muddling around I’ve found that the following works consistently for truncating *and* shrinking the transaction log file in SQL 2008:

Do a full backup and then a transaction log only back-up, then run this (where “MyDb” is the name of your DB and “MyDb_Log” is the name of your DB transaction log file):

USE MyDb
GO
ALTER DATABASE MyDb SET RECOVERY SIMPLE
GO
ALTER DATABASE MyDb SET RECOVERY FULL
GO
DBCC SHRINKFILE(MyDb_log, 1)
GO


The DBCC SHRINKFILE should display results indicating a 1m log file at this point.

I know this seems a bit weird but I was just able to shrink the a transaction log from 1.6 gig (!) down to the default minimum size of 1 meg.

Add to del.icio.usDiggIt!RedditStumble ThisAdd to Google BookmarksAdd to Yahoo MyWebAdd to Technorati FavesSlashdot it