473,241 Members | 1,599 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

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.B
Jul 20 '05 #1
5 18255
Looks 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" <ab********@hotmail.com> wrote in message
news:aa**************************@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

Jul 20 '05 #2
Hi faculties
Should one mantain a single-user connection to the server to
truncate the log file?

Thanks in advance
Debashish
Jul 20 '05 #3
debashish (de****************@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
ab********@hotmail.com (BashiraInTrouble) wrote in message news:<aa**************************@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
Jul 20 '05 #5
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:
ab********@hotmail.com (BashiraInTrouble) wrote in message news:<aa**************************@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/support.../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

*/
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jay Chan | last post by:
The transaction log in a database in our SQLSERVER-2000 server has grown to 16GB. I cannot shrink the transaction log manually because it says that the entire 16GB log size is not free. This is...
1
by: R Camarda | last post by:
Help, I have a database that has a data file of 2GB and a log file of 31GB. In enterprise manager, when I choose shrink it says there is 30GB of unused space. When I shrink the database, it does...
2
by: Vencz Istv?n | last post by:
I have a web application installed on Windows 2003 Standard Edition with SP1, it accesses a SQL Servet 2000 database installed on Windows 2000 Server, the two computers are not in the same domain....
5
by: war_wheelan | last post by:
My trancaction log is 25GB and my database file is 39GB. I just switched to the 'Simple' recovery model from the 'Full' recovery model. When if ever can I expect the size of the transaction log to...
4
by: Tommy.Vincent | last post by:
hi all, This will be a easy question for all out here. I have a database of 28GB. having 3 Data Files 22 GB, 3.58 Gb and 2.70 GB respectively. and a Transaction Log file of 156 mb. When i...
4
by: Gary Bond | last post by:
Hi All, Can anybody point me to some 'how-to' documentation, tutorials, etc as to how to write a shrink/protect wrapper for .Net exes/dlls, (like the Shrinkwrap product for instance). I have...
13
by: Matik | last post by:
Hello everybody, First: SQL Server 2000 sp3a, HP cluster server, MS 2003 server, database recovery model simple Torn page detection: When I have this option turned on, processes conected with...
0
by: Tifer | last post by:
Hello, I am building my first .Net Application. The first couple of Publish and Installs I did went fine. But after a couple of builds, I get a modal dialogue box error every time upon trying...
6
by: Major Drake | last post by:
Windows 2003 64 bit sp2 + SQL Server std 32 bit sp2 compability mode 90, recovery model simple. I have about 40 Gb database where is about 98% free space (I deleted data from tables). dbcc...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.