Transaction log will not shrink 2

[mikelawrence]

I'm worried about the size of a transaction log filling up my whole server so have read up on truncating the log but it appears to remain the same size after all the things i read are recommended.

My .mdf file is about 2gb and my logfile is about 14gb so what i've tried is:

1) I do daily full database backups through sql server agent and my understanding is that these should shrink the log file but they appear to make no difference to the size of the log file.
2) I go into EM and backup the whole db from there where the button on options is set to remove inactive entries from the transaction log.
3) I go into EM and right click on the db - All Tasks - Shrink (where it says i have 83% free space) and click on OK
4) As 3 but go onto Files and then click the log and then ok
5) I've also tried to do a backup just on the log by right clicking the db - all tasks - backup and clicking the transaction log button. If i do this it just backs up to the daily name of my backup file with a .BAK extension and if i put in a different name then i get a message saying the file is not part of a multiple family media set and to create one with BACKUP FORMAT.

I would really appreciate help in understanding what is going on and in particular what to do

Thanks

mike

 

[onpnt]

You have to do a DBCC SHRINKFILE still on the transaction file

Here is a brief article that will help in understanding this

http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html

also check the FAQ's in this forum. One that is good and may help your maintenance setup

http://www.tek-tips.com/faqs.cfm?fid=5742

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[ptheriault]

Mike,
A full backup of the database will not shrink your log. If you want to shrink the log you must run...

DBCC SHRINKFILE('log_name', truncateonly)

This will return the free space at the end of the log back to the OS.

Also, If you set up incremental backups to your TLog throughout the day it will keep the size of you TLog down.

Also you can only clear commited transaction out of the log.
This link will help you understand a bit more about what you can and can not clear from a TLOG.

http://msdn2.microsoft.com/en-us/library/ms189573.aspx

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mikelawrence]

Thanks for these replies. Unfortunately I had read and thought I understood the FAQ one but i clearly am being quite slow on this one.

My understanding was that a full backup would reduce the physical size of the transaction log assumming that records had been committed. Does it do this or does it only truncate the log file but still leave it occupying whatever physical space it had before?

Also i tried shrinking the log by the following command dbcc shrinkfile (2, truncateonly) where 2 is the fileid and
dbcc shrinkfile ('DB_rtm_Log', truncateonly) but again it makes no difference to the physical size.

Any help is much appreciated

thanks

mike

 

[ptheriault]

try this.

--select * from sysfiles
--drop table DummyTrans
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
        @MaxMinutes INT,
        @NewSize INT

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

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[onpnt]

backup the log file
e.g.
BACKUP LOG [db] WITH TRUNCATE_ONLY

Then run the SHRINKFILE

Note: this means you are NOT backing up the trans log and are relying on the full backup for restoring to that point in time. Not recommended!

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[ptheriault]

My understanding was that a full backup would reduce the physical size of the transaction log assumming that records had been committed. Does it do this or does it only truncate the log file but still leave it occupying whatever physical space it had before?

That is incorrect. once a log file has hit an auto grow only a shrinkfile command will reduce it in size again.
Also, only a backup of the transaction log will clear space in the transaction log.

- Paul
- If at first you don't succeed, find out if the loser gets anything.