473,890 Members | 1,345 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Cannot shrink the transaction log

Hi Friends,
I have tried almost everything but I cant seem to shrink the
transaction log.
Executing DBCC SQLPERF(LOGSPAC E)
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 18348
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.
"BashiraInTroub le" <ab********@hot mail.com> wrote in message
news:aa******** *************** ***@posting.goo gle.com...
Hi Friends,
I have tried almost everything but I cant seem to shrink the
transaction log.
Executing DBCC SQLPERF(LOGSPAC E)
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
ab********@hotm ail.com (BashiraInTroub le) wrote in message news:<aa******* *************** ****@posting.go ogle.com>...
Hi Friends,
I have tried almost everything but I cant seem to shrink the
transaction log.
Executing DBCC SQLPERF(LOGSPAC E)
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********@hotm ail.com (BashiraInTroub le) wrote in message news:<aa******* *************** ****@posting.go ogle.com>...
Hi Friends,
I have tried almost everything but I cant seem to shrink the
transaction log.
Executing DBCC SQLPERF(LOGSPAC E)
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
@LogicalFileNam e manually.
*/
DECLARE @LogicalFileNam e SYSNAME,
@MaxSize INT,
@OriginalSize INT,
@StringData VARCHAR(500);

SELECT TOP 1
@LogicalFileNam e = 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 = @LogicalFileNam e;

SELECT @StringData = 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR (30),@OriginalS ize) + ' 8K pages or ' +
CONVERT(VARCHAR (30),(@Original Size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileNam e;

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 (@LogicalFileNa me, @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 =
@LogicalFileNam e)) 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 =
@LogicalFileNam e)) 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 = @LogicalFileNam e;

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
23119
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 strange because we backup the transaction log every hour, and that should have truncated the transaction log, and should have limited the size of the transaction log; somehow, the entire transaction log is still marked as being used. I was under...
1
5715
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 not shrink, (however it says it has completed). I've done a complete backup, tried shrink again, no dice. I then backed up the database (which the backup was 1.9GB), deleted the database and made a new database with 2,048MB for the data and same...
2
15862
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. The web application uses ASP pages and COM+ components written in VB6. After the installation of SP1 for Windows 2003 the application does not work anymore (before the installation of SP1 it worked without problems). The following error occurs:...
5
12302
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 reduce in size? Is there anything else that I should do to aide with the reduction? Thanks, Peter
4
6736
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 executed DBCC Shrinkdatabase(databasename),it reduced size of datafile but the LOG file had gone up to 5 Gb from 156mb.
4
1773
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 got a couple of products nearly ready for sale, and have already come up with some routines to protect them, (in the style of the old TurboPower OnGuard), but really wanted to shrink and protect the exe's, so as to make reverse engineering a bit...
13
7392
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 updating and writing in database, was MUCH more slower, than if I've this option turned off.
0
5259
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 to install using the setup.exe. Title is "Cannot Start Application" and it says: ==================== Cannot download the application. The application is missing required files. Contact application vendor for assistance.
6
8802
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 checkdb -OK. dbcc reindex all tables -OK Shrink database (with reorganize and release unused pace) -no any affets,
0
9980
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10830
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10468
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9641
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5855
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6061
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4682
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.