December 16, 2010 at 1:50 pm
No idea. I've seen it on everything from 2000-2008. Sometimes caused by restoring a published database to server without replication configured. Rest of time I haven't been able to figure it out nor duplicate it.
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
December 16, 2010 at 2:15 pm
If you are absolutely sure that the database is not for replication, why not disable the replication?
(Use the following script:)
sp_replicationdboption 'database_name', 'publish', 'false', 1
December 16, 2010 at 2:21 pm
Because sometimes when the DB is in the half-replicated state, that fails with an error saying that the DB is not replicated.
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
December 17, 2010 at 9:10 am
Gila:
I went through your steps successfully, but I have a question about #5. How do i know if the results from DBCC OPENTRAN are related to 'distributed and non-distributed LSNs in the output'? I ran DBCC OPENTRAN and see one SPID 61, which has information it activity monitor stating 'IF @@TRANCOUNT > 0 COMMIT TRAN'.
Thank you, Jason
December 17, 2010 at 10:08 am
See the output of OPENTRAN that you posted earlier.
Transaction information for database 'CareCast'.
Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : ggs_repl
LSN : (33114:57895:4)
Start time : Dec 16 2010 9:14:48:693AM
SID : 0x01
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (32133:172909:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
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
December 17, 2010 at 12:27 pm
Ahh! Thanks for your help! You have been great!
Jason Nance, MHA
December 17, 2010 at 4:09 pm
why can I not get the log file drive space reallocated back down to a more reasonable amount?
There should be open transaction running for long which does not commit when you do backup if it is accumulating the log file then it won't shrink too.
Follow these Steps:
1. In sp_configure Truncate log on check point -enable it
2. Have log back every 30 mins --depends on your business needs
3. If you have lot of bulk-inserts --Change recovery model to Bulk-Logged
4. If you can not shrink and no other options, check which transaction is accumulating it and kill it if possible.
5. Check whether your log backup job is running success fully.
Regards,
Pavan Srirangam.
December 18, 2010 at 1:02 am
pavan_srirangam (12/17/2010)
1. In sp_configure Truncate log on check point -enable it
Truncate log on checkpoint has been deprecated since SQL 7. It should not be used any longer. On SQL 2005 and above, enabling it switches the database into simple recovery model. Hence no log backups and no point in time recovery
4. If you can not shrink and no other options, check which transaction is accumulating it and kill it if possible.
Assuming it is a transaction holding the log, which in this case it was not. There are other things than transactions that prevent log space from being 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
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply