USE Master

--You can replace with the database name but if it doesnt
--works then replace with master databas)

GO DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)

5 Comments »

1. Comment by joe — November 12, 2007 @ 4:31 pm

   Horrible. All you did is copy something from BOL, fowl it up with typos, and post it. I feel sorry for your employer.

2. Comment by codeproject — November 13, 2007 @ 9:05 am

   Thanks for your comments , a + b will always add the things.

   Same way we have some expression BACKUP LOG WITH TRUNCATE_ONLY
   which are builtin, so we are using them , I did the same.

   Rana

3. Comment by Ron Barone — September 8, 2008 @ 2:10 am

   I use this, and it works great on 2005. I cant remember where I got this code snippet from but whomever wrote it, thanks!

   SET NOCOUNT ON
   DECLARE @LogicalFileName sysname,
   @MaxMinutes INT,
   @NewSize INT
   — *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
   USE [YourDatabase] — This is the name of the database
   — for which the log will be shrunk.
   SELECT @LogicalFileName = ‘YourDatabase_Log’, — Use sp_helpfile to
   — identify the logical file
   — name that you want to shrink.
   @MaxMinutes = 1, — Limit on time allowed to wrap log.
   @NewSize = 50 — in MB

   — Setup / initialize
   DECLARE @OriginalSize int
   SELECT @OriginalSize = size — in 8K pages
   FROM sysfiles
   WHERE name = @LogicalFileName
   SELECT ‘Original Size of ‘ + db_name() + ‘ LOG is ‘ +
   CONVERT(VARCHAR(30),@OriginalSize) + ‘ 8K pages or ‘ +
   CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + ‘MB’
   FROM sysfiles
   WHERE name = @LogicalFileName

   CREATE TABLE DummyTrans
   (DummyColumn char (8000) not null)

   — Wrap log and truncate it.
   DECLARE @Counter INT,
   @StartTime DATETIME,
   @TruncLog VARCHAR(255)
   SELECT @StartTime = GETDATE(),
   @TruncLog = ‘BACKUP LOG ['+ db_name() + '] WITH TRUNCATE_ONLY’
   — Try an initial shrink.
   DBCC SHRINKFILE (@LogicalFileName, @NewSize)

   EXEC (@TruncLog)

   — Wrap the log if necessary.
   WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) — time has not expired
   AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) — the log has not shrunk
   AND (@OriginalSize * 8 /1024) > @NewSize — The value passed in for new size is smaller than the current size.
   BEGIN — Outer loop.
   SELECT @Counter = 0
   WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
   BEGIN — update
   INSERT DummyTrans VALUES (’Fill Log’) — Because it is a char field it inserts 8000 bytes.
   DELETE DummyTrans
   SELECT @Counter = @Counter + 1
   END — update
   EXEC (@TruncLog) — See if a trunc of the log shrinks it.
   END — outer loop
   SELECT ‘Final Size of ‘ + db_name() + ‘ LOG is ‘ +
   CONVERT(VARCHAR(30),size) + ‘ 8K pages or ‘ +
   CONVERT(VARCHAR(30),(size*8/1024)) + ‘MB’
   FROM sysfiles
   WHERE name = @LogicalFileName
   DROP TABLE DummyTrans
   PRINT ‘*** Perform a full database backup ***’
   SET NOCOUNT OFF

4. Comment by James — September 19, 2008 @ 2:40 am

   Hi, I found your blog on this new directory of WordPress Blogs at blackhatbootcamp.com/listofwordpressblogs. I dont know how your blog came up, must have been a typo, i duno. Anyways, I just clicked it and here I am. Your blog looks good. Have a nice day. James.

5. Comment by sandra407 — September 9, 2009 @ 5:06 pm

   Hi! I was surfing and found your blog post… nice! I love your blog. Cheers! Sandra. R.

RSS feed for comments on this post. TrackBack URI

Leave a comment