Hi,
Can anyone help on this, how to reduce the size of the log file ?
I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
17Gbytes. The database has been in operation for more than 2 years and why
has the log file grown to such a size ? It was set to auto growth at 10%. Can
I reduce it size and how? I need to claim back some disk space.
Thank you for your time
KC ThongKCThong wrote:
> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf
> file is 17Gbytes. The database has been in operation for more than 2
> years and why has the log file grown to such a size ? It was set to
> auto growth at 10%. Can I reduce it size and how? I need to claim
> back some disk space.
> Thank you for your time
> KC Thong
See DBCC SHRINKFILE in BOL.
--
David Gugick
Imceda Software
www.imceda.com|||Here are a few things to keep in mind:
â?¢ Always perform system database and user database backups before and after
you make changes that affect the system. DBCC SHRINKFILE and DBCC
SHRINKDATABASE are not logged operations, and running them invalidates
further transaction log backups. You must make a full database backup after
you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
â?¢ Make sure that there are no backups scheduled to occur during the time the
shrink is supposed to occur.
â?¢ Make sure that there are no old, long-running, or unreplicated
transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
â?¢ Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
members of the sysadmin fixed server role or the db_owner fixed database
role, and are not transferable. For information about the differences between
these commands, refer to the following topics in SQL Books Online (note the
different parameters):
DBCC SHRINKFILE (file_name, target_size)
DBCC SHRINKDATABASE (database_name, target_percent)
â?¢ Create some dummy transactions to make the log wrap around and then issue
a BACKUP command to truncate the log. The BACKUP statement is what actually
attempts to shrink the log to the marked target size.
Here is a sample of how to create a dummy transactions that wraps the log
for a single logical log file and causes it to truncate, allowing for
shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
DECLARE @.LogicalFileName sysname,
@.MaxMinutes INT,
@.NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
USE [Test DB] -- This is the name of the database
-- for which the log will be shrunk.
SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
-- identify the logical file
-- name that you want to shrink.
@.MaxMinutes = 10, -- Limit on time allowed to wrap log.
@.NewSize = 10 -- 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
Check to see if the log has shrunk from its original size.Repeat the
preceding steps if necessary. If the log is not shrinking, re-check the
summary at the top of the article to see if you are encountering any of the
common issues with shrinking the log.
After the log shrinks:
1. Perform a full database backup of the master database.
2. Perform a full database backup of the user database. This is necessary
because the SHRINK command is not logged and invalidates future transaction
log backups unless a full database backup is completed.
To determine why the log is growing so big in the first place, you can check
for open transactions, long running transactions, unreplicated transactions,
or transactions that touch a lot of data.
"KCThong" wrote:
> Hi,
> Can anyone help on this, how to reduce the size of the log file ?
> I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
> 17Gbytes. The database has been in operation for more than 2 years and why
> has the log file grown to such a size ? It was set to auto growth at 10%. Can
> I reduce it size and how? I need to claim back some disk space.
> Thank you for your time
> KC Thong
>
>|||At this stage, if I set " trunc. log on chkpt" in the database option to
TRUE, does it help to clear the log or shrink ?
Pls advise.
"surajits" wrote:
> Here are a few things to keep in mind:
> â?¢ Always perform system database and user database backups before and after
> you make changes that affect the system. DBCC SHRINKFILE and DBCC
> SHRINKDATABASE are not logged operations, and running them invalidates
> further transaction log backups. You must make a full database backup after
> you run either the DBCC SHRINKFILE or the DBCC SHRINKDATABASE commands.
> â?¢ Make sure that there are no backups scheduled to occur during the time the
> shrink is supposed to occur.
> â?¢ Make sure that there are no old, long-running, or unreplicated
> transactions. To do so, use code similar to:DBCC OPENTRAN (database_name)
> â?¢ Run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to mark a
> shrinkpoint. DBCC SHRINKFILE and DBCC SHRINKDATABASE permissions default to
> members of the sysadmin fixed server role or the db_owner fixed database
> role, and are not transferable. For information about the differences between
> these commands, refer to the following topics in SQL Books Online (note the
> different parameters):
> DBCC SHRINKFILE (file_name, target_size)
> DBCC SHRINKDATABASE (database_name, target_percent)
> â?¢ Create some dummy transactions to make the log wrap around and then issue
> a BACKUP command to truncate the log. The BACKUP statement is what actually
> attempts to shrink the log to the marked target size.
> Here is a sample of how to create a dummy transactions that wraps the log
> for a single logical log file and causes it to truncate, allowing for
> shrinkage. Modify the sample as needed for your environment. SET NOCOUNT ON
> DECLARE @.LogicalFileName sysname,
> @.MaxMinutes INT,
> @.NewSize INT
> -- *** MAKE SURE TO CHANGE THE NEXT 4 LINES WITH YOUR CRITERIA. ***
> USE [Test DB] -- This is the name of the database
> -- for which the log will be shrunk.
> SELECT @.LogicalFileName = 'Test DB Log', -- Use sp_helpfile to
> -- identify the logical file
> -- name that you want to shrink.
> @.MaxMinutes = 10, -- Limit on time allowed to wrap log.
> @.NewSize = 10 -- 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
> Check to see if the log has shrunk from its original size.Repeat the
> preceding steps if necessary. If the log is not shrinking, re-check the
> summary at the top of the article to see if you are encountering any of the
> common issues with shrinking the log.
> After the log shrinks:
> 1. Perform a full database backup of the master database.
> 2. Perform a full database backup of the user database. This is necessary
> because the SHRINK command is not logged and invalidates future transaction
> log backups unless a full database backup is completed.
> To determine why the log is growing so big in the first place, you can check
> for open transactions, long running transactions, unreplicated transactions,
> or transactions that touch a lot of data.
> "KCThong" wrote:
> > Hi,
> > Can anyone help on this, how to reduce the size of the log file ?
> > I have a database (SQL7.0) the mdf file is 1.6 Gbytes, but the ldf file is
> > 17Gbytes. The database has been in operation for more than 2 years and why
> > has the log file grown to such a size ? It was set to auto growth at 10%. Can
> > I reduce it size and how? I need to claim back some disk space.
> >
> > Thank you for your time
> > KC Thong
> >
> >
> >
> >
sql
s the log file has grown to