May 8, 2016 at 4:31 am
Hi Professionals
I encountered with an unknown situation.
I backup from main database every morning . It usually takes me in average 16 minutes. (It has totally 160 Giga bytes)
But this morning when I was checking backups, I saw that it took about 3 hours and 40 minutes !!!!
In addition it's size had about doubled. !!!!
I tried to restore it , But faced with another thing :
It reached to 100 percent about 14 minutes that was expected but after that It became busy to about 40 minutes and was not finished. Finally I canceled It.
...
90 percent processed.
95 percent processed.
100 percent processed.
Processed 10814464 pages for database '..._20160508', file '...' on file 1.
Processed 62160 pages for database '..._20160508', file 'CDCData_...' on file 1.
Processed 251473 pages for database '..._20160508', file '..._log' on file 1.
Processed 6842139 pages for database '..._20160508', file '...' on file 1.
.... (It was busy without finishing)
Please help me that What was happened?
Thank you
May 8, 2016 at 5:23 am
Looks like there's a long-running transaction that's keeping the transaction log active (and making it grow).
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
May 8, 2016 at 5:29 am
Thank you
But
Can I diagnose which transaction had been run?
OR in other word , Can I read the log part from this full backup and find the "Long open Transaction"s from That ?
Because it is very unusual and I should ready for repetition of it ?
May 8, 2016 at 5:49 am
You could look at DBCC OPENTRAN and the transaction-related DMVs, you know the normal way you diagnose long-running transaction. There's nothing special about the fact that it caused the backup to be slow.
It the transaction has finished or already been killed, then it'll be very hard to tell what happened.
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply