April 18, 2007 at 9:45 am
Hi all,
the system I am working on was upgraded recently from SQL2000 to SQL2005, and it's generating strange issues after then.
The maintenace plan stopped working (the job was going to run forever if I didn't stop them), and I was able after some tests to understand the cause: It's impossible to backup two of the databases I have on the server.
If I try to di it manually I got this:
ERROR 3026
"Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized (...)"
You could think it's because someone is using the DBs at the same time, but I tried to Execute the Plan at night and the morning before it was still there running since 12 hours. If I cut from the maintenance plan these two databases the job runs just fine.
The Log Backup is fine, differential and full backups are throwing the error above.
I have drive partitions C: 35 GB - 2 GB free, F: 110 GB - 27 GB free.
SQL2005 installed on C but I am backing up to F.
Ring any bells?
Thanks in advance,
Cheers,
Giovanni
April 18, 2007 at 12:10 pm
Giovanni,
I think two different operations on the same database are happening at the same time. For eg: you are executing an Alter Database statement to add a file or trying to shrink the database file along with database backup.
As you said the backup takes for ever to finish, it means that a file is being added/shrinked and a backup statement is being kicked on at the same time. When this happens, Backup job waits for a timeout and after a while results in a failure.
The solution for this problem is to reissue the statement for executing either backup or file management only after the other statement completes execution.
Thanks.
April 18, 2007 at 12:23 pm
Thanks for your answer.
I constantly check the job Activity monitor and nothing was executing any statement on those dbs.
I checked as well the auto-shrink option on the DBs and it'set to false.
I just stopped and restarted the SQL SERVER (and the agent as well) and it worked.
Probably something was going on behind the scenes but it's very hard for to understand what, as I am fighting with this backup issues since yesterday and I checked everything I am aware of.
And I am very concerned abuot finding it out as I don't want it to happen again.
Any deep advice?
Thanks again,
Giovanni
April 18, 2007 at 12:52 pm
From your response I think we can eliminate any possible executing of create index or reindex commands on the database.
Can you let me know which SP do you have on your SQL Server 2005?
April 18, 2007 at 4:50 pm
SP2 recently installed (after upgrading SQL Server 2000 ---> 2005).
Thanks for helping!
Cheers,
Giovanni
April 19, 2007 at 8:22 am
Its a long shot, but you could check open transactions on the database in question. Perhaps something has been left open...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply