1

The SQL backups have been failing for a few days... I tried to just do a quick manual full backup and got an error: enter image description here

sp_who2'active' (screenshot below): Is it ok to kill SPID 186 and/or what's the general course of action here needing to be taken? I also feel perhaps a restart of the server wouldn't hurt. (Our VSS backups have been failing on this server too).

I am on SQL server 2014 SP1 / Windows server 2012 R2

enter image description here

  • Can you provide the error you're getting? – Kris Gruttemeyer Oct 24 '16 at 14:04
  • oops, sorry. has been added. i think it's basically telling me about SPID 186 if I had to guess.... – JzInqXc9Dg Oct 24 '16 at 14:19
1

The SQL backups have been failing for a few days... I tried to just do a quick manual full backup and got an error:

It seems like operation which conflict with backup are running at the time which backup was started so the backup failed. And as per your comment SPDI 186 is running which is showing backup which means that database backup is already running when new backup was being started. Concurrent full backup is not allowed when full backup is already running.

Also make sure no database shrink operation is running or Auto Shrink is not enabled. This can also interfere in backup operation.

select is_auto_shrink_on, name from sys.databases 

let us see how many databases have auto shrink enabled

Yes you an kill backup operation (SPID 186 here) but you would loose all the time spent in taking backup and you would have to start all over again. Before doing that let us see how much backup has finished, what is output

select percent_complete,session_id,status,total_elapsed_time
from sys.dm_exec_requests where session_id=186

EDIT: There is NO restart required

Edit:

After discussing on chat it was identified that Veeam backup was running for past 2-3 days and was somehow hung. OP killed the process and it went into rollback state where it was again stuck OP has to ultimately restart the VM and the issue seems to have resolved.

Also advised OP to log a ticket with Veeam because that was definitely a third party Veeam backup issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.