May 23, 2016 at 1:09 am
Hi Experts,
can you please advise my log backup is taking long to finish.It normaly takes less than 12minutes but its running upto 2 hrs.
I am checking the logs i am seeing these messages and i am not if this has to do with the job taking long.
[db_MShare] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible:
May 23, 2016 at 7:24 am
How big is the transaction log? How often do you perform transaction log backups? did you check if you see anything in error logs or event viewer ? check whether the disk is in good shape with no errors
May 23, 2016 at 8:15 am
1) Simple question: what changed?!?
2) use dbcc sqlperf(logspace) to see how big and how much space is used by the tlog for this database
3) File IO stalls on the server?
4) run sp_whoisactive to see work being and having been done and wait information
5) wait stats on the server?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 24, 2016 at 12:03 am
tmmutsetse (5/23/2016)
Hi Experts,can you please advise my log backup is taking long to finish.It normaly takes less than 12minutes but its running upto 2 hrs.
I am checking the logs i am seeing these messages and i am not if this has to do with the job taking long.
[db_MShare] Status: ONLINE Standby: No Updateability: READ_WRITE User access: MULTI_USER Is accessible:
Check the percent_complete using select percent_complete,* from sys.dm_exec_requests where command like '%Backup%'
May 24, 2016 at 12:17 am
I will do so,to my suprise the backup ran normal today.we take log backup everyday which takes less 15 min but yesterday it took more than 2 hours.
May 24, 2016 at 6:11 am
Has your log backup job been failing? At least 50% of the clients I visit have jobs failing and they don't know about it because they didn't enable Database Mail (and/or didn't create failure notifications).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 24, 2016 at 6:26 am
The job is not failing,but it takes long than normal.instaed of taking less 20min it took 2 hours
May 24, 2016 at 6:29 am
tmmutsetse (5/24/2016)
I will do so,to my suprise the backup ran normal today.we take log backup everyday which takes less 15 min but yesterday it took more than 2 hours.
You take a log backup on a daily basis, one backup per day?
May 24, 2016 at 7:32 am
Yes once a day.
May 24, 2016 at 7:36 am
Hie Do you know Powershell very well?How can i build a credential object to connect to a remote server located on different domain.
May 24, 2016 at 7:44 am
tmmutsetse (5/24/2016)
Yes once a day.
Well, that can contribute to the log backups taking a long time. Please consider implementing a new backup strategy, something like a full database backup once per week, differentials once per day, log backups every 15 minutes. Base the log backup frequency on the amount of data your business user can afford to lose.
As part of this new strategy you should run a recovery test. Attempt to restore a database to a certain point in time.
This is a good read: MS TechNet Backup Article
May 24, 2016 at 10:00 am
There could be an open transaction that is blocking things, like the log getting backed up and truncated.
Have you checked for this with a DBCC OPENTRAN or something similar?
May 25, 2016 at 8:53 am
Grumpy DBA (5/24/2016)
Base the log backup frequency on the amount of data your business user can afford to lose.
This. If the database just does one ETL job daily and is not transactionally active after that, then perhaps one log backup daily is OK. This is not a common mode of operation, but maybe it is what you're doing. If the database is active, then having a once a day log backup is an invitation to disaster and potentially an RGE (resume-generating event, i.e., you're out looking for a new job).
How big is the database and the log? And is it active? Brent Ozar[/url] has recommended doing log backups EVERY MINUTE. I consider this slightly excessive, but it does have some good reasoning behind it.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply