Database backup job failed! Is there a way to identify the process ..

[Dilyias]

Hello all,

Our nightly database backup job has failed, and I get this info from the maintenence backup history:

"[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3023: [Microsoft][ODBC SQL Server Driver][SQL Server]Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally."

Is there a way to obtain a more detailed log and identify what process(s) is holding up the backup? I'll probably just restart services after business hours to remove all the processes but I was wondering if there was any more info I could gather from this error. There doesn't appear to be any other maintenence jobs running at this time either. Thanks for your time!

-Eric

 

[BobLoblaws]

Is this the information stored in the report file for the Maintenance Plan?

 

[Dilyias]

Right now it writes the history to the database (default) which I retrieve by right clicking the maintence plan, and selecting 'Maintenence plan history.'

Hmmm, perhaps if I have the plan also write a report to a text file (In the reports tab of the maintenence 'properties') it will include more information?

BTW, I'm using SQL 7 on win2k servers. I checked and there is no extra info in the server logs either (like there would be if I ran out of disk space, etc)

I'm just wondering if there is a way to pinpoint what process is running that is atopping the database from being backed up -- by looking at logs/reports.

 

[dangerman2k]

you could have a look at Current Activity to see which processes are using that database.

 

[Bernadette]

Hi There

I have seen this with some of my backups and most times it was a scheduled update of information that was causing it as it changed the SQL Server "Select Into" settings to run the update.

Do you have anything like that running? Can you change the time of the backup slightly to get around the process that is trying to run at that time ?



Bernadette

 

[Dilyias]

Thanks for your ideas, yes I was looking at the current processes as I wrote the first msg, none really popped up save one with veritas, which I suppose may have run a little late, accessing the file (another server backs up the raid on this one, perhaps jobs got delayed, or someone messed with how the database files got backed up through veritas, and "forgot" to mention it..etc...) In either case, the next day (after two nights of not working) it is working again..and the veritas process is still there..(and veritas backed up fine each night) Oh well, I'll keep my eyes open.

Perhaps some day SQL Server's msgs will say something along the lines of, "Sorry, I was blocked by process xxxxx. Which was invoked by the stored procedure, 'sp_querywith50joins_loopforever.' Which, by the way, was started by user xxx. Who, incidently, will be coming to work next Thursday at 7am."