June 29, 2012 at 3:35 pm
Hello there,
We have restarted our SQL Server last night.
Q1. We are taking a differential backup via a job which has been taking longer ( almost 10 hours now). Is it safe to stop the job now during business hours ?
Q2. Do I have to take a full back-up before taking differential backup ? Why is the job taking so long ?
Thank you in advance!
June 29, 2012 at 3:41 pm
Hard to say, we can't see from here what you can see there.
What does running sp_who2 show?
June 29, 2012 at 3:49 pm
The status of the job is 'RUNNABLE'.
CPUTime = 16
DiskIO = 16
May I know, what in specific are you looking for ?
June 29, 2012 at 3:52 pm
Looking to see if it may be blocked some how. There may be other processes associated with the backup as well.
June 29, 2012 at 3:55 pm
Should it be ok, to stop the job now during business hours ?
June 29, 2012 at 4:03 pm
Probably, just realize you don't have a good backup and need to be sure you get one soon.
This advice is my own and there are no guarantees implied or explicit. Your decision to follow it is your own.
June 29, 2012 at 4:09 pm
Thanks Lynn :). I totally get it!
I read on the link below that even if I restart server, I can take up the differential backup without taking full back up. However, if this is true, then the next Q is that the job is taking too long.
http://go4answers.webhost4life.com/Example/does-run-full-backup-sql-service-51510.aspx
June 29, 2012 at 4:13 pm
Here's the problem we have, we can't see from here what you see there. Running sp_who2 is just one thing to do. The other would be to look at the SQL Server error logs to see if something is going on there.
July 8, 2012 at 4:21 am
The differential backup is only good since your last full backup. When did you last take a full backup? And yes, you can restart the server and the differential backup will still work with a full backup. Remember, restoring these, you'll need to first restore the full, then the differential. If you've lost the full backup, the differential backup is useless.
As to why it's running long with extremely low CPU & disk readings... I'm with Lynn. I can't see what you can see.
Instead of sp_who2 though, I'd use the dynamic management objects to understand where you're at. sys.dm_exec_requests will show you the active sessions. You can join from there to sys.dm_exec_sql_text to see which statement is currently being executed. You say it's part of a job, maybe the job is stuck somewhere other than the backup. But again, I can't tell you for sure what's going on without tons more information.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply