February 21, 2011 at 1:31 pm
It was just a question. I've seen that error before, I'll typically check the open trans and if/when the log was backed up.
It's most annoying when you see that error on a 'simple recovery' mode database. Specially common in QA/DEV DB's running on virtual machines.
--
:hehe:
February 21, 2011 at 1:48 pm
Slick84 (2/21/2011)
It was just a question. I've seen that error before, I'll typically check the open trans and if/when the log was backed up.
Which will only help you if the reason is log backup, active transaction or replication
It's most annoying when you see that error on a 'simple recovery' mode database. Specially common in QA/DEV DB's running on virtual machines.
Hence one of the reasons for writing the article and mentioning the possible fixes for the causes. Log backups (and hence recovery model) is just one of many reasons for a full log.
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
February 21, 2011 at 6:20 pm
Thanks for the very informative article.
Running the first query I see Simple Recovery model and 'ACTIVE_TRANSACTION'
However, running DBCC OPENTRAN report "No active open transactions."
This doesn't seem to add up. My limited understanding is that the log still has an open transaction preventing reuse, but there aren't any transactions running.
Any pointers on what to do from here?
February 21, 2011 at 7:02 pm
Hi Gail, Great article. However i have 1 question; Are you saying that when one issue the command backup log db_name with truncate_only it backs up the active portion and releases the space for re-use ? so the backup chain is not broken ?
February 21, 2011 at 7:29 pm
david.howell (2/21/2011)
Thanks for the very informative article.Running the first query I see Simple Recovery model and 'ACTIVE_TRANSACTION'
However, running DBCC OPENTRAN report "No active open transactions."
This doesn't seem to add up. My limited understanding is that the log still has an open transaction preventing reuse, but there aren't any transactions running.
Any pointers on what to do from here?
Sys.databases isn't going to be updated as soon as the transaction completes. Try running a checkpoint in the database.
February 21, 2011 at 7:34 pm
ceso (2/21/2011)
Hi Gail, Great article. However i have 1 question; Are you saying that when one issue the command backup log db_name with truncate_only it backs up the active portion and releases the space for re-use ? so the backup chain is not broken ?
When you run BACKUP LOG ... WITH TRUNCATE_ONLY, it does break the log chain. It doesn't release any space, it only marks space as inactive so it can be reused.
February 22, 2011 at 12:54 am
ceso (2/21/2011)
Are you saying that when one issue the command backup log db_name with truncate_only it backs up the active portion and releases the space for re-use ? so the backup chain is not broken ?
No, I never said that. I never even mentioned the truncate only option. Backup log with truncate only does exactly what the command says. It truncated ONLY. There is no backup file written, log records are simply marked inactive hence there is no way for the log chain to remain intact.
Backup log to disk backs up the active portion to a file on disk then truncates it and does not break the log chain.
Maybe you should read my other article on transaction logs Managing Transaction Logs[/url]
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
February 22, 2011 at 12:57 am
Robert Davis (2/21/2011)
david.howell (2/21/2011)
Thanks for the very informative article.Running the first query I see Simple Recovery model and 'ACTIVE_TRANSACTION'
However, running DBCC OPENTRAN report "No active open transactions."
This doesn't seem to add up. My limited understanding is that the log still has an open transaction preventing reuse, but there aren't any transactions running.
Any pointers on what to do from here?
Sys.databases isn't going to be updated as soon as the transaction completes. Try running a checkpoint in the database.
Also make sure you're running opentran from the right database. It's database-specific where sys.databases isn't
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
February 22, 2011 at 8:19 am
Thanks much. This will be required reading for my Jr. DBAs.
February 22, 2011 at 8:45 am
GilaMonster (2/22/2011)
Maybe you should read my other article on transaction logs Managing Transaction Logs[/url]
Just read everything Gail has written, and you'll be a better DBA for it.
February 22, 2011 at 10:46 am
Great article, thanks!
February 22, 2011 at 1:01 pm
Great Article Gail.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 22, 2011 at 1:07 pm
Robert Davis (2/22/2011)
GilaMonster (2/22/2011)
Maybe you should read my other article on transaction logs Managing Transaction Logs[/url]Just read everything Gail has written, and you'll be a better DBA for it.
😀 Thanks. High praise coming from you.
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
February 22, 2011 at 1:16 pm
We can also get the specific database with sys.databases with where condition.
select * from sys.databases
where name = 'DBName'
I always use with this condition so that i can be specific on the particular database.
Thanks,
February 22, 2011 at 1:17 pm
I have not read this Article, i am going to read it now. Thanks for the reminder!!!
Viewing 15 posts - 16 through 30 (of 76 total)
You must be logged in to reply to this topic. Login to reply