BIG PROBLEM transaction log does not shrink after full database backup

[Former Member]

Hello

I have a BIG PROBLEM i.e.MS sql transaction log does not shrink after full database backup.

I am only few days before going live . the server is running half year.

I would like to switch off transaction log on development and shrink with full database copy or transaction log backup(or anyhow)

I am performing backup with Enterprise manager.

I also restarted server few days ago. but still the transaction log does not shrink after database backup. I tried also only transaction log backup.

Please help me

[Former Member · Accepted solution]

Hi,

Run Enterprise Manager, right-click on database \ All tasks \ Shrink Database

Click on button Files and on the dropdown list choose logfile.

Check values on "Current size" and "Space used"

If Your log is empty You may click on radio button "Shrink file to" and then OK.

Regards,

Marcin Gajewski

[former_member193399]

Use the following script if the log file still does not shrink using dbcc shrinkfile or Enterprise Manager's shrink option. Make sure you change the database name and log file name.

SET NOCOUNT ON

DECLARE @LogicalFileName sysname,

@MaxMinutes INT,

@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***

USE <<db name>> -- This is the name of the database

-- for which the log will be shrunk.

SELECT @LogicalFileName = '<<logical log file name>>', -- Use sp_helpfile to

-- identify the logical file

-- name that you want to shrink.

@MaxMinutes = 2, -- Limit on time allowed to wrap log.

@NewSize = 2 -- 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

[Former Member]

OK, if this doesnt work, you have problems..

1. FULL database Backup - MSSQL does not automatically truncate the log file...the little tick on the second TAB is quite ineffective...MSSQL2000, especially if the logs have grown considerably.

2,Create backup area as follows:

USE master

GO

EXEC sp_addumpdevice 'disk', '<DBNAME>LOGS',

'<Drive>:\<DBNAME>LOGS.bak'

GO

3.Configure the transactionnlog backups.Create backup job and shedule it for regular intervals during operations - every two hours is good measure.

USE <DBNAME>;

GO

BACKUP LOG <DBNAME>

TO <DBNAME>LOGS

GO

DBCC SHRINKFILE (<LOG_FILE_NAME>);

GO

Note: note putting a size behind the <LOG_FILE_NAME> with cause it to shrink it as much as possible. Let this run a few times and your logs with be small and manageable.

Hope this helps, please award points if it does.

[Former Member]

Thank you Marcin (I assigned you 6 points).

I shrinked the log file as you said (it seems only 644M are used). However on OS level the size of log remains 31.496M in size.

Is it possible to shrink file also?

Thank you in advance

[Former Member]

Tina,

If Your database files have more than ~30MB free space, leave that as it is. If they have more - shrink it.

Default database has autogrowth at 10%, so it grows anyway...

Marcin

[former_member185954]

Hi Tina,

You can use DBCC SHRINKFILE

Please check the following link:

http://support.microsoft.com/kb/272318/

Regards,

Siddhesh

[Former Member]

Run the following Transact-SQL statements in Query analyzer:

1. Clear log file :

BACKUP LOG DatabaseName WITH NO_LOG

2. Shrink the transaction log file.

DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS

3. If the DBCC SHRINKFILE statement does not shrink the transaction log file to the target size, run the BACKUP LOG statement that is mentioned in step 1 to make more of the virtual log files inactive

4. Run the DBCC SHRINKFILE statement that is mentioned in step 2. After this operation, the transaction log file should be similar to the target size.

Regards,

wojtek

[Former Member]

If there is still an uncommitted transaction on the SQL side, the space will not release after a log backup.

Make sure all transactions are committed first. Sometimes a hung process will cause this, in which case a stop/restart of SQL Server is necessary before the log backup and subsequent shrink command will free up the space.