Backup job can''t start because previous backup is still running
-
I have a strange problem here.
I got an existing database full backup job (created through Database Maintenance Plan, DBMP) running fine all this while.
Last Monday, I realised that the same backup job process continue to run non-stop (run sp_who2 show RUNNABLE, it is still there but the phy database backup is already completed) and thus causing the subsequent days backup not able to fire. The process doesn’t seem to stop/complete.
When manually start the job in EM, the error appear.
Error 22022: SQLServerAgent Error: Request to run job ‘[jobname]’ refused because the job has been running from a request by user ‘ABC’
I have no choice to create a new backup job (again through Database Maintenance Plan, DBMP). The backup process again continue to run non-stop (again show RUNNABLE in sp_who2) although phy backup completed. I did a kill command and it is still there showing ROLLBACK in command column. It has been more than 1 day since the KILL command is issued. I can’t seem to stop the process.
The whole issue here is the non-stopping backup process is causing my subsequent days backup job won’t even starts because the existing backup process is yet to complete.
I desperate need someone to advice, please.
-
This isn't a solution, but can you not stop the backup job from SQLAgent? Does it show as running?
Also, is this only backing up one database? I'd also dig into the files and see if there's a lock being held on the backup physical file by the backup process. This way you can tell if something is getting messed up in SQL after the backup is complete or if the backup is still running.
Not sure what's happening here, might need to call PSS. Haven't see backup jobs keep running. Maybe it's the verify?
-
It is backing up 50 databases in that instance. sp_who2 show disk and cpu column figure is static. will check the sp_lock.
The backup job log specified under reporting tab in the database maintenance plan get cut off halfway. The complete log usually end with success (0) or failure (1) but the log entries ended at backing up the 40+th database. However, all the 50 physical database backup dump files are created.
Did DBCC CHECKDB on all database and found no problem with any database.
Google the error 22022 and it seems that error 22022 is very generic (not specific) can apply to SQLMail error or other things.
-
sp_lock showed no lock held by the non-stop process spid. But the backup process just won't go away even with kill command issued.
somehow, i guess it has to do with either SQLServerAgent or DB Maintenance Plan because when i do a manual backup using BACKUP DATABASE .... tsql has no such problem.
I couldn't stop the SQL Server service because it is a 24x7 system.
-
I have had this issue before. I have a 24X7 environment too but I just scheduled a maintenance window. You can just explain SQL Server is not meant to be run with out maintenance.
A Few things. 1) break up the maintenance plan so you do not hit this again. 2) The only good way to get rid of the process is to stop and restart SQL server
But you could try to run a backup cmd with RESTART from Query analyzer
Kind of like:
BACKUP DATABASE pubs
TO PubsBackupDevice1
WITH RESTART
the WITH RESTART cmd is for interrupted backups
you could also run the below in a batch file to keep it up for as long as possible and minimize down time or just at the cmd line --> copy and paste the below in to notepad then name it restartSqlServer.bat and save and double click on it.
cd \
NET STOP SQLSERVERAGENT
NET STOP MSSQLSERVER
NET START MSSQLSERVER
NET START SQLSERVERAGENT
You could also try just taking the effected database offline then back online this would allow the rest of the databases to stay online, but I am guessing it will say you can't do this while it is in use.
Hope this helps - sorry I could not help more
-
just try to restart your sql server agent may be your agent is hanged state......
[font="Verdana"]- Deepak[/font]
-
It is a good idea to try to just restart the agent but because SQL server is still running andyour agent is really not running the job only starting it. The SPID will most likely still be there.
-
You should not try and stop / restart the services until you've tried to kill that particular SPID.
"Kill <spid>" is the command. It may take a while, so be patient. In the meantime, disable other jobs that might be ready to start or trying to run. This will free up processor power to finish killing the job.
Another thing to verify is your hard drive space and that you still have a network connection open. Oddly enough, I've seen it happen once or twice when the server "burps" and loses it's connectivity to its SAN/NAS storage or when the drive is not quite full, but so close it might as well be. 95% of the time, a full hard drive space will cause the job to fail, but there's a 5% "odd" factor in which it doesn't if the HD space gets full in the middle of the job.
Let us know if any of our advice helps.
-
Trying to kill the process yes you should try that first but it will most likely complain because it is a system process.
-
Maybe not. After all, it isn't an "essential to keep the server running" system process. It's just a backup process. You should be able to kill that.
-
This happens occasionally on one of our older SQL servers. I will find one or more SPIDs associated with the Backup / Maint Plan that are still active. Usually if I kill them off, the next backup will resume as it should. I think there have been times where I have also had to restart the SQLAgent service to clear the issue.
Ed
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply