June 20, 2009 at 3:37 am
I've just run a dbcc checkdb command across the command and all seems ok there:
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Databasenameremoved'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
June 20, 2009 at 3:41 am
GilaMonster (6/20/2009)
JamesNZ (6/20/2009)
The value is 6 for log_reuse_wait. What does that mean?Check the column log_reuse_wait_descr. It give the text description. log_reuse_wait just gives the numeric code.
It says REPLICATION.
It is a replicated database yes.
Where could I go from here with that?
June 20, 2009 at 3:46 am
Check that the log reader's running. Either the log reader's failed or it's slow in getting the transactions out of the log. Either way, the log cannot be truncated by a log backup while there are unreplicated commands.
Basically, there's some problem with your replication in getting transactions from publisher to distributor.
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
June 20, 2009 at 3:55 am
GilaMonster (6/20/2009)
Check that the log reader's running. Either the log reader's failed or it's slow in getting the transactions out of the log. Either way, the log cannot be truncated by a log backup while there are unreplicated commands.Basically, there's some problem with your replication in getting transactions from publisher to distributor.
Thanks Gail, when you say "log reader", what are you referring to? Where can I check that?
The thing is the distributor and the subscriber are in fact on the same server and the same SQL instance.
June 20, 2009 at 4:19 am
JamesNZ (6/20/2009)
GilaMonster (6/20/2009)
Check that the log reader's running. Either the log reader's failed or it's slow in getting the transactions out of the log. Either way, the log cannot be truncated by a log backup while there are unreplicated commands.Basically, there's some problem with your replication in getting transactions from publisher to distributor.
Thanks Gail, when you say "log reader", what are you referring to? Where can I check that?
The thing is the distributor and the subscriber are in fact on the same server and the same SQL instance.
Everything you ever wanted to know about replication agents: http://msdn.microsoft.com/en-us/library/ms152501(SQL.90).aspx
That article is also available in your local copy of Books Online, if you have that installed. It is indexed under "Log Reader Agent".
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 20, 2009 at 4:24 am
Paul White (6/20/2009)
JamesNZ (6/20/2009)
GilaMonster (6/20/2009)
Check that the log reader's running. Either the log reader's failed or it's slow in getting the transactions out of the log. Either way, the log cannot be truncated by a log backup while there are unreplicated commands.Basically, there's some problem with your replication in getting transactions from publisher to distributor.
Thanks Gail, when you say "log reader", what are you referring to? Where can I check that?
The thing is the distributor and the subscriber are in fact on the same server and the same SQL instance.
Everything you ever wanted to know about replication agents: http://msdn.microsoft.com/en-us/library/ms152501(SQL.90).aspx
That article is also available in your local copy of Books Online, if you have that installed. It is indexed under "Log Reader Agent".
Thanks Paul, no I'm using Snapshot replication so I don't have a Log Reader Agent.
What else could I check to try and find out how to clear/process this huge log file?
June 20, 2009 at 6:36 am
JamesNZ (6/20/2009)
Thanks Paul, no I'm using Snapshot replication so I don't have a Log Reader Agent.
What else could I check to try and find out how to clear/process this huge log file?
Log reader agent is used by transactional replication. It might be possible that log reader is not reading because of which replicated transactions are not moved to distribution database. Unless this is done u cannot truncate the log.
This URL tells you how to start replication agents:
http://msdn.microsoft.com/en-us/library/ms151783.aspx%5B/url%5D
once log reader agent moves all transactions that are supposed to be moved to distribution, you can take log backup which will free up the log size. after that u can proceed to shrink the log file.
June 20, 2009 at 6:51 am
ps (6/20/2009)
JamesNZ (6/20/2009)
Thanks Paul, no I'm using Snapshot replication so I don't have a Log Reader Agent.
What else could I check to try and find out how to clear/process this huge log file?
Log reader agent is used by transactional replication. It might be possible that log reader is not reading because of which replicated transactions are not moved to distribution database. Unless this is done u cannot truncate the log.
This URL tells you how to start replication agents:
http://msdn.microsoft.com/en-us/library/ms151783.aspx%5B/url%5D
once log reader agent moves all transactions that are supposed to be moved to distribution, you can take log backup which will free up the log size. after that u can proceed to shrink the log file.
No luck unfortnately, that didn't work. I started the replication agents and it transferred the latest data across and the log reduced by 2GB from 33Gb to 31GB.
Interestingly enough though, there's only 9 rows in DBCC LogInfo now where as there was 651 before:
FileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
2253952819275601280
22621442621447550640
2262144524288763264374000000024000002
22621447864327570128677000000024000001
226214410485767600128678000000024800001
226214413107207610128679000000025600001
226214415728647622128680000000024800001
226214418350087580128748000000025600001
226214420971527590128749000000024800001
Anyone have anymore suggestions?
June 20, 2009 at 6:59 am
take backup of the log now and then check the used/unused space by firing dbcc loginfo...
June 20, 2009 at 8:47 am
This was what I ran (did a copy of the SQL through Management Studio):
BACKUP LOG [DatabaseName] TO DISK = N'D:\tranbackup.bak' WITH NOFORMAT, INIT, NAME = N'DatabaseName-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'DatabaseName' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DatabaseName' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''DatabaseName'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\tranbackup.bak' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
and this is the result of the DBCC LOGINFO command afterwards:
There are 627 rows, with the bulk of them with a Status of 2. Sorry, I ran the command against the wrong database before.
and attached is the shrink screen showing it still remains at 30GB.
Some more help in trying to find a solution here would be greatly appreciated.
June 20, 2009 at 9:42 am
you need to figure out what's preventing the log file to be truncated. that you will come to know by seeing log_reuse_wait_descr as suggested by Gail earlier in the thread. once you know what's preventing the log file to be truncated, you can proceed to resolve that. Like earlier case, it was replication which was preventing the log to be truncated. Once you resolve that error, you should be able to truncate the log by taking its backup and then physically reduce its size by running dbcc shrinkfile once.
you really need to figure out whats preventing the log to be truncated.
June 20, 2009 at 1:52 pm
SO, if I'm understanding correctly, you have no transactional replication configured? Please can you run the following?
select * from syspublications -- run in the database in question
Is log_reuse_wait_descr still saying replication? If so, what does the following show?
DBCC OPENTRAN
Has anyone perhaps been messing with replication recently?
When the log_reuse_wait_descr says replication, it means transactional replication. Snapshot replication does not use the transaction log and hence cannot be a reason for the log space not been reused.
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
June 20, 2009 at 6:48 pm
Thanks for the response guys.
This is what I see with select * from syspublications . Please see attached text file.
No one I know of has been messing with the replication or at least I hope not.
Yes select * from sys.databases still shows REPLICATION for that database.
DBCCOpenTran:
Transaction information for database 'DatabaseName'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (51424:1852:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
June 20, 2009 at 8:13 pm
JamesNZ (6/20/2009)
Thanks for the response guys.This is what I see with select * from syspublications . Please see attached text file.
No one I know of has been messing with the replication or at least I hope not.
Yes select * from sys.databases still shows REPLICATION for that database.
DBCCOpenTran:
Transaction information for database 'DatabaseName'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (51424:1852:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
As said earlier there is some issue with the transaction replication. The value of Oldest non-distributed LSN should be (0:0:0) if your replication is smooth.
What you can do here is fire sp_repldone command on the database which will reset the log and you can then take the backup of the log.
Imp - this will invalidate the publishing. You will have to remove ur publication and set it up again.
It's replication that's preventing the log from shrinking.
If above method fails, try removing publication and then setup replication again.
June 20, 2009 at 8:18 pm
ps (6/20/2009)
JamesNZ (6/20/2009)
Thanks for the response guys.This is what I see with select * from syspublications . Please see attached text file.
No one I know of has been messing with the replication or at least I hope not.
Yes select * from sys.databases still shows REPLICATION for that database.
DBCCOpenTran:
Transaction information for database 'DatabaseName'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (51424:1852:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
As said earlier there is some issue with the transaction replication. The value of Oldest non-distributed LSN should be (0:0:0) if your replication is smooth.
What you can do here is fire sp_repldone command on the database which will reset the log and you can then take the backup of the log.
Imp - this will invalidate the publishing. You will have to remove ur publication and set it up again.
It's replication that's preventing the log from shrinking.
If above method fails, try removing publication and then setup replication again.
Thanks for responding.
I take it it's this command you're referring to:
http://msdn.microsoft.com/en-us/library/ms173775.aspx
Is it this command I should run:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
I'm running SQL Server 2005 SP3.
Viewing 15 posts - 31 through 45 (of 66 total)
You must be logged in to reply to this topic. Login to reply