September 29, 2008 at 5:30 am
Hi
We have a transaction log about 69Gb, the database itself is about 11Gb.
We have a maintenance plan but this often goes wrong. Now are transaction log is that big.
There is not enough storage place on ths sql server to backup.
This database is published for replication.
How can I truncate the log without deleting the replication ?
We tried already :
DBCC Shrinkfile (logfile,1) after having changed the recovery to simple.
Is replication a reason that this log gets that big ?
I have only 13Gb left 🙂
Kind regards.
JV
September 29, 2008 at 6:50 am
Replication shouldn't make any difference. When was your last FULL backup ?
September 29, 2008 at 6:52 am
today
but it won't shrink.
JV
September 29, 2008 at 1:42 pm
We've dropped the publication once and then we could truncate.
But then we of course have to setup replication again.
Anyone got a clue ?
It's running fast.
thx
JV
September 29, 2008 at 2:07 pm
I haven't performed it on a replicated database.
Keep in mind, you can only shrink a log file up to the 'last' active chunk it uses.
Maybe you'll have redo the shrink operation a couple of times so the "active chunk is rolled over to the beginning of the logfile.
--INF: How to Shrink the SQL Server 7.0 Transaction Log
-- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- SQL7 http://www.support.microsoft.com/kb/256650
-- SQL2000 http://support.microsoft.com/kb/272318/en-us
-- SQL2005 http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sysfiles
-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
SELECT @LogicalFileName = 'logicalname', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- 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
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 29, 2008 at 2:16 pm
Sorry but it doesn't work !
Message is this :
Cannot shrink log file 2 (NAV_SEPIA_Log) because all logical log files are in use.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
And this is because this database is published. When i disable I can shrink it without any problem. But whem I drop the publication I'm losing all my replications.
So ? Anybody has similar problem ?
Thx
JV
September 29, 2008 at 2:22 pm
homebrew01 (9/29/2008)
Replication shouldn't make any difference. When was your last FULL backup ?
Replication makes a big difference to logs. On a replicated DB, the log record can't be discarded until it is inactive (not required for recovery) and has been replicated.
If the log reader isn't running, or is running slow, the log will grow. You won't be able to shrink it until the log reader restarts and catches up to date.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2008 at 2:25 pm
jvElecenter (9/29/2008)
se this database is published. When i disable I can shrink it without any problem. But whem I drop the publication I'm losing all my replications.
Your log reader is either not running, or is runinng too slow to keep up with the rate of incoming transactions.. Make sure the log reader's running and watch (via replication monitor) how it's performing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2008 at 2:38 pm
Hi, log reader is new to me.
How can i find out it's running ?
thx in advance
JV
September 29, 2008 at 2:50 pm
GilaMonster (9/29/2008)
homebrew01 (9/29/2008)
Replication shouldn't make any difference. When was your last FULL backup ?Replication makes a big difference to logs. On a replicated DB, the log record can't be discarded until it is inactive (not required for recovery) and has been replicated.
If the log reader isn't running, or is running slow, the log will grow. You won't be able to shrink it until the log reader restarts and catches up to date.
Good point. My replication was always running & in sync, so I hadn't encountered this issue, but that might be the problem.
September 29, 2008 at 2:57 pm
ok, and how can i check this ?
I've launched the replication monitor and nothing is running.
JV
September 29, 2008 at 3:37 pm
ok, unfortunately replication is now running and there is no way of backing up the transaction log now.
But when I look into the activitity monitor I see that the following processes are suspended :
MAster database : Checkpoint
TempDB : Logwriter
Distribution : Waitfor
CAn these be the cause of my problem ? Is yes how can I solve this one ? Can I change order to process ?
thx in advance
JV
September 30, 2008 at 2:47 am
Suspended indicates they're waiting for a resource. What are they waiting for? If they're blocked, by what process and what is that doing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2008 at 12:25 pm
Im about to turn this on myself for 15 minutes log backups and also have replication on.
The log read is a job that runs in SQL under the jobs you can check this here.
How do you know what transactions in the LOG have not been replicated - is there a way to determine this.
That be very useful.
February 16, 2009 at 3:00 pm
I'm having the same issue in a SQL Server 2000 database that is published for snapshot replication. If replication is messing the active portion of the transaction log, I don't completely understand why, since it's a snapshot replication.
At this point I'm very tempted to substitute snapshot replication with a dts or dtsx package.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply