February 22, 2011 at 1:22 pm
Abi Chapagai (2/22/2011)
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,
What I mean by 'database specific' is that opentran applies only to the database it is run in. You cannot run opentran in DB1 and get output related to DB2. You can do that with sys.databases
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 4:41 pm
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.
Thanks
I was concerned because I read in BOL that the status 'NOTHING' means that the log is not waiting on anything including open transactions, so I wanted to see 'NOTHING'. I ran
CHECKPOINT;
And now it is saying 'NOTHING', although I take it from your reply that my concerns were misguided.
February 22, 2011 at 5:08 pm
Thanks for this article! Funny how something so important and fairly simple can be surrounded by so much obscurity.
-------------------------------------------------------------------------------------------------
My SQL Server Blog
February 22, 2011 at 7:30 pm
Thanks for the clarification
February 22, 2011 at 7:31 pm
Thanks you
February 23, 2011 at 5:59 am
Thanks to everyone involved in writing this one. Great article!
-Ki
February 23, 2011 at 8:46 am
Very informative article. In the section entitled 'Checkpoint' you say 'If the checkpoint log reuse wait appears frequently...'. Where would I look to check if this is appearing frequently?
Thanks.
February 23, 2011 at 9:27 am
K Foster (2/23/2011)
Very informative article. In the section entitled 'Checkpoint' you say 'If the checkpoint log reuse wait appears frequently...'. Where would I look to check if this is appearing frequently?Thanks.
From the article:
Causes of delayed log truncation
These are the log reuse wait reasons from sys.databases
February 23, 2011 at 9:28 am
Excellent article Gail. You are pro, and pro's lean on other pros. Nice to see such a practical treatment of log reuse while still giving significant background information in such a concise manner.
February 23, 2011 at 9:33 am
Please ignore my question in the previous post - reread the article and found the answer... (insert embarrassed icon here)
February 23, 2011 at 9:34 am
Thank you Mr. Davis.
February 23, 2011 at 10:51 am
Can you elaborate on why long-running backups can result in significant transaction log growth?
Or at least point to an explanation and example.
February 23, 2011 at 11:51 am
GilaMonster (2/22/2011)
Abi Chapagai (2/22/2011)
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,
What I mean by 'database specific' is that opentran applies only to the database it is run in. You cannot run opentran in DB1 and get output related to DB2. You can do that with sys.databases
Thanks for the clarification, and you are right that we cannot run the opentran in one database and get the related result to another database.
February 23, 2011 at 9:40 pm
Chet Xemoka (2/23/2011)
Can you elaborate on why long-running backups can result in significant transaction log growth?Or at least point to an explanation and example.
From the article:
The active portions of the log are necessary for database backups, as the backup has to include at least that much of the log to ensure a consistent restore. As such, long-running database backups may result in log growth as the portions of the log necessary for the backup must be retained for the duration of the backup.
Do you want more than that?
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 24, 2011 at 10:01 am
Gail - Great Article! Appreciated.
Viewing 15 posts - 31 through 45 (of 76 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy