Cannot shrink the transaction log

Hi Friends,
I have tried almost everything but I cant seem to shrink the
transaction log.
Executing DBCC SQLPERF(LOGSPACE)
gives me this info:
Database Log Size (MB) Log Space Used (%) Status
MY_eems 368.49219 16.034182 0
I made a complete backup of the database and transaction log and then
executed this statement:
DBCC SHRINKFILE (MYeems_log, 1)
and this is the message I got
Cannot shrink log file 2 (ghgeems_Log) because all logical log files
are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- ---- ---- ---- -----
17 2 42880 34561 42880 34560

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your
system administrator.

How can I get the transaction log to its minimum size?
Any help will be appreciated,
Thanks
A.BLooks like you need to truncate the log first before shrinking it. To
truncate the log, make sure there is no inflight transaction in the
database, then either perform a log backup or issue a checkpoint command
with the database recovery mode set to SIMPLE. DBCC ShrinkFile afterwards
should reduce the log file size.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"BashiraInTrouble" <abashir_ii@.hotmail.com> wrote in message
news:aa9a64b8.0411091315.2b0761f4@.posting.google.c om...
> Hi Friends,
> I have tried almost everything but I cant seem to shrink the
> transaction log.
> Executing DBCC SQLPERF(LOGSPACE)
> gives me this info:
> Database Log Size (MB) Log Space Used (%) Status
> MY_eems 368.49219 16.034182 0
> I made a complete backup of the database and transaction log and then
> executed this statement:
> DBCC SHRINKFILE (MYeems_log, 1)
> and this is the message I got
> Cannot shrink log file 2 (ghgeems_Log) because all logical log files
> are in use.
> DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
> -- -- ---- ---- ---- -----
> 17 2 42880 34561 42880 34560
> (1 row(s) affected)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> How can I get the transaction log to its minimum size?
> Any help will be appreciated,
> Thanks
> A.B|||Hi faculties
Should one mantain a single-user connection to the server to
truncate the log file?

Thanks in advance
Debashish|||debashish (debashish_majumdar@.rediffmail.com) writes:
> Should one mantain a single-user connection to the server to
> truncate the log file?

There is no reason to set the database in single-user mode to truncate
the log file.

What you may want to check, though, is for open transactions with
DBCC OPENTRAN. If there is a stray transaction which has been left
open, you cannot truncate the log file past the beginning of that
transaction.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||abashir_ii@.hotmail.com (BashiraInTrouble) wrote in message news:<aa9a64b8.0411091315.2b0761f4@.posting.google.com>...
> Hi Friends,
> I have tried almost everything but I cant seem to shrink the
> transaction log.
> Executing DBCC SQLPERF(LOGSPACE)
> gives me this info:
> Database Log Size (MB) Log Space Used (%) Status
> MY_eems 368.49219 16.034182 0
> I made a complete backup of the database and transaction log and then
> executed this statement:
> DBCC SHRINKFILE (MYeems_log, 1)
> and this is the message I got
> Cannot shrink log file 2 (ghgeems_Log) because all logical log files
> are in use.
> DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
> -- -- ---- ---- ---- -----
> 17 2 42880 34561 42880 34560
> (1 row(s) affected)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> How can I get the transaction log to its minimum size?
> Any help will be appreciated,
> Thanks
> A.B

The error messasge show you the answer already. There are still some
other active transactions and the log that SQL server have to keep is
bigger than 1M.
Hope that help|||Use this script. Just change the value from "10" to "1". It will shrink
the file as much as possible. Run it a second time for better results.

Always remember to perform a full database backup after truncating the
transaction log.

Tho Nguyen wrote:
> abashir_ii@.hotmail.com (BashiraInTrouble) wrote in message news:<aa9a64b8.0411091315.2b0761f4@.posting.google.com>...
>>Hi Friends,
>>I have tried almost everything but I cant seem to shrink the
>>transaction log.
>>Executing DBCC SQLPERF(LOGSPACE)
>>gives me this info:
>>Database Log Size (MB) Log Space Used (%) Status
>>MY_eems 368.49219 16.034182 0
>>I made a complete backup of the database and transaction log and then
>>executed this statement:
>>DBCC SHRINKFILE (MYeems_log, 1)
>>and this is the message I got
>>Cannot shrink log file 2 (ghgeems_Log) because all logical log files
>>are in use.
>>DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
>>-- -- ---- ---- ---- -----
>>17 2 42880 34561 42880 34560
>>
>>(1 row(s) affected)
>>
>>DBCC execution completed. If DBCC printed error messages, contact your
>>system administrator.
>>
>>How can I get the transaction log to its minimum size?
>>Any help will be appreciated,
>>Thanks
>>A.B
>
> The error messasge show you the answer already. There are still some
> other active transactions and the log that SQL server have to keep is
> bigger than 1M.
> Hope that help

/*
Shrink a named transaction log file belonging to a database
*/
SET NOCOUNT ON;

DECLARE @.MaxMinutes INT, @.NewSize INT, @.Factor FLOAT;
/*
The process has several control parameters, most of the time you only need to
worry about the first few as these are the big controls whereas the fifth is
simply a fine tuning control.

Switch to the database we are attempting to shrink the logs for. Uncomment this to
automatically switch to a database, otherwise it uses the current db. */ --USE [MyDatabase];

-- Define the ideal size of logfile in MB and also how much time may be used to shrink the log
SET @.NewSize = 10; SET @.MaxMinutes = 1;

/*
Factor determining maximum number of pages to pad out based on the original
number of pages in use (single page = 8K). Values in the range 1.0 - 0.8 seems to work
well for many databases.

Increasing the number will increase the maximum number of pages allowed to be padded,
which should force larger amounts of data to be dropped before the process finishes.
Often speeds up shrinking very large databases which are going through the process
before the timer runs out.

Decreasing the number will decrease the maximum number of pages allowed to be padded,
which should force less work to be done. Often aids with forcing smaller databases
to shrink to minimum size when larger values were actually expanding them.

*/
SET @.Factor = 0.95;

/*
Automatically retrieve the logical filename of the largest logfile currently
assigned to the database. If you want to manually select the file to shrink
then you can retrieve a list of the files using sp_helpfile and then setting
@.LogicalFileName manually.
*/
DECLARE @.LogicalFileName SYSNAME,
@.MaxSize INT,
@.OriginalSize INT,
@.StringData VARCHAR(500);

SELECT TOP 1
@.LogicalFileName = RTRIM( [name] ),
@.MaxSize = CASE [maxsize]
WHEN -1 THEN -1
ELSE ([maxsize] * 8)/1024
END
FROM sysfiles
WHERE [groupid] = 0
ORDER BY [size] DESC;

/*
All code after this point is driven by these parameters and will not require
editing unless you need to fix a bug in the padding/shrinking process itself. */

--Check user size is not larger than maximum file size
IF @.MaxSize > 0 AND @.NewSize > @.MaxSize
BEGIN
PRINT 'Note: '+CAST( @.NewSize AS VARCHAR )+'MB exceeds the limit of '+CAST( @.MaxSize AS VARCHAR )+'MB on this file, correcting...'
PRINT ''
SET @.NewSize = @.MaxSize;
END

SELECT @.OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @.LogicalFileName;

SELECT @.StringData = '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;

PRINT @.StringData;
PRINT '' --Drop the temporary table if it already exists
IF ( OBJECT_ID('[dbo].[DummyTrans]') IS NOT NULL )
DROP TABLE [DummyTrans]

CREATE TABLE [DummyTrans]( [DummyColumn] CHAR(8000) NOT NULL );

-- Wrap log and truncate it.
DECLARE @.Counter INT,
@.MaxCount INT,
@.StartTime DATETIME,
@.TruncLog VARCHAR(500)

-- Try an initial shrink. (this is what causes data to be returned)
DBCC SHRINKFILE (@.LogicalFileName, @.NewSize)

SET @.TruncLog = 'BACKUP LOG [' + db_name() + '] WITH TRUNCATE_ONLY'; EXEC (@.TruncLog)

-- Configure limiter
IF @.OriginalSize / @.Factor > 50000
SET @.MaxCount = 50000;
ELSE
SET @.MaxCount = @.OriginalSize * @.Factor;

-- Attempt to shrink down the log file
PRINT 'Minimum Quantity : '+CAST( @.MaxCount AS VARCHAR(10) ); IF @.MaxMinutes = 1
PRINT 'Maximum Time : '+CAST( @.MaxMinutes AS VARCHAR(10) )+' minute ('+CAST( @.MaxMinutes*60 AS VARCHAR(10) )+' seconds)'; ELSE
PRINT 'Maximum Time : '+CAST( @.MaxMinutes AS VARCHAR(10) )+' minutes ('+CAST( @.MaxMinutes*60 AS VARCHAR(10) )+' seconds)'; PRINT '';

SET @.Counter = 0;
SET @.StartTime = GETDATE();

--loop the padding code to reduce the log while
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@.MaxMinutes*60 > DATEDIFF(ss, @.StartTime, GETDATE())) AND
(@.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName)) AND
((@.OriginalSize * 8 / 1024) > @.NewSize)
)
BEGIN --Outer loop.

--pad out the logfile a page at a time while
-- number of pages padded does not exceed our maximum page padding limit
-- within time limit and
-- log has not been shrunk enough
WHILE (
(@.Counter < @.MaxCount) AND
(@.MaxMinutes*60 > DATEDIFF(ss, @.StartTime, GETDATE())) AND
(@.OriginalSize = (SELECT size FROM sysfiles WHERE name =
@.LogicalFileName)) AND
((@.OriginalSize * 8 / 1024) > @.NewSize)
)
BEGIN --Inner loop

INSERT INTO DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE FROM DummyTrans

SET @.Counter = @.Counter + 1

--Every 1,000 cycles tell the user what is going on
IF ROUND( @.Counter , -3 ) = @.Counter
BEGIN
PRINT 'Padded '+LTRIM( CAST( @.Counter*8 AS VARCHAR(10) ) )+'K @. '+LTRIM( CAST( DATEDIFF( ss, @.StartTime, GETDATE() ) AS VARCHAR(10) ) )+' seconds';
END
END

--See if a trunc of the log shrinks it.
EXEC( @.TruncLog )

END
PRINT ''

SELECT @.StringData = '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;

PRINT @.StringData
PRINT ''

DROP TABLE DummyTrans;
PRINT '*** Perform a full database backup ***'

SET NOCOUNT OFF

/*
Based on:

http://support.microsoft.com/suppor...s/q256/6/50.asp

Changes:
28.08.2001
Modified the inner loop so it tested the dx time so long overruns did not happen Modified the inner loop so it had a fixed minimum quantity so there was no skip in skip out

29.08.2001
Modified the inner loop so it had a dynamic minimum quantity to allow faster shrinkage

24.01.2002
Modified the USE statement so it uses brackets around the dbname Modified the @.TruncLog variable so it uses brackets around the dbname

31.05.2002
Modified the code to use PRINT instead of SELECT in several cases Modified the code to use @.MaxCount instead of two unclear rules
Modified the code to use @.Factor instead of several hard-coded values
Commented the use of @.Factor
Moved the configuration and @.Counter init code to before the start of the first loop to avoid repetition Modified the code to display the process runtime in seconds rather than minutes

17.01.2003
Modified timing display code to handle 1 minute correctly

08.05.2003
Modified the instructions to make them easier to read & understand

*/