Shrinking transaction log

I am trying to create a job where a complete backup of a database is created and the transaction log file is shrunk.  Here are the t-sql statements:


DECLARE @LogicalName AS VARCHAR(100),  
      @PhysicalName VARCHAR(100),
      @Name VARCHAR(100),
      @Description VARCHAR(100)

SET @LogicalName = 'TAG_Backup_Week_of_' + DATENAME(MM, GETDATE()) + '_' + CAST(DATEPART(DD, GetDate()) AS VARCHAR(2)) + '_' + CAST(DatePart(YYYY, GetDate()) AS VARCHAR(4))
SET @PhysicalName = 'E:\Backups\TAG\TAG_Backup_Week_of_' + DATENAME(MM, GETDATE()) + '_' + CAST(DATEPART(DD, GetDate()) AS VARCHAR(2)) + '_' + CAST(DatePart(YYYY, GetDate()) AS VARCHAR(4)) + '.bak'
SET @Name = 'TAG Complete'
SET @Description = 'Complete weekly backup of TAG database'

EXEC sp_addumpdevice 'disk', @LogicalName, @PhysicalName

BACKUP DATABASE TAG
   TO @LogicalName
   WITH
      NAME = @Name,
      DESCRIPTION = @Description,
      NOINIT, --Do not overwrite previous backup sets
      RETAINDAYS = 30 --Number days must elapse before backup can be overwritten

DBCC SHRINKFILE (TAG_Log, 25)  -- Shrink the transaction log file to 25 mb if possible

GO

A complete backup of the database will automatically truncate the log file, right or do I need to do that manually with:

BACKUP LOG TAG
   TO @LogicalName  WITH TRUNCATEONLY


Currently the t-log size is 50mb.
So why is the transaction log not releasing the extra space back to the OS?

km1039Asked:
Who is Participating?
 
arbertConnect With a Mentor Commented:
No, they are marked as inactive, but you still have to shrink (dbcc shrinkfile) to recover "free" space...
0
 
boblahCommented:
Hi km1039,

Shrinking the transaction log is complicated. Here's a script that normally does the job for me (this does none of the other stuff you're doing, you'll need to add that in)

   SET NOCOUNT ON
   DECLARE @LogicalFileName sysname,
           @MaxMinutes INT,
           @NewSize INT

   -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
   USE     [goran_V2_CCDB]              -- This is the name of the database
                                  -- for which the log will be shrunk.
   SELECT  @LogicalFileName = 'goran_aroma_V2_xfm_Log',  -- Use sp_helpfile to
      -- identify the logical file
      -- name that you want to shrink.
           @MaxMinutes = 10,      -- Limit on time allowed to wrap log.
           @NewSize    = 30       -- 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

Cheers!
0
 
km1039Author Commented:
Thanks.  I'll give it a try.
0
 
arbertCommented:
"Shrinking the transaction log is complicated."

Not really, a lot of it depends on your recovery model, but you can simply truncate the log and then shrink it:

backup log yourdatabasename with truncate_only
go
dbcc shrinkifile(yourdatabasename_log,0)
go
0
 
km1039Author Commented:
so executing a complete backup will NOT remove inactive entries from the transaction log and I need to do it manually?

by the way this is a production database and the recovery model is full

thanks arbert
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.