Solved

Shrinking transaction log

Posted on 2004-10-09
5
765 Views
Last Modified: 2008-03-17
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?

0
Comment
Question by:km1039
  • 2
  • 2
5 Comments
 
LVL 6

Expert Comment

by:boblah
ID: 12267569
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
 

Author Comment

by:km1039
ID: 12267638
Thanks.  I'll give it a try.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12268870
"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
 

Author Comment

by:km1039
ID: 12269129
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
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 12269138
No, they are marked as inactive, but you still have to shrink (dbcc shrinkfile) to recover "free" space...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now