Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
petek
Posting Yak Master
192 Posts |
 Posted - 2009-01-02 : 06:59:22
|
| Hi all, Firstly thank you for reading this message and happy new year.....I had a problem with a backup which started on 26/12/08 and was still running today, so I stopped the job from Job activity monitor. NOTE there is a disk space issue with the server so the folder is compressed (I think this may have caused the problem).....Anyhow I navigated to the backup location and tried to delete the backup from on the 26th but it says the file is being used by another person or program. I checked the file sharing from admin tools and there are no open shares.I then tried the backup again (manually), and the backup failed with the following:System.Data.SqlClient.SqlError: Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. (Microsoft.SqlServer.Smo)After a bit of digging all options pointed to kill the process. ok so I tried to kill the process and noticed Process ID's had the same SPID's twice in Activity Monitor I tried to kill the and the command now says KILLED/ROLLBACK and status suspended.I ran KILL SPID from QUERY ANALYSER and it returns:SPID 74: transaction rollback in progress. Estimated rollback completion: 96%. Estimated time remaining: 20423 seconds.But the Estimated time remaining is increasing, is this right??I read somewhere that if I stopped and started DTC the process will be released, but I didn’t work.That’s where I am now......Any ideas??Kind RegardsPete. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-01-02 : 07:57:40
|
quote:
Originally posted by petek
I ran KILL SPID from QUERY ANALYSER and it returns:SPID 74: transaction rollback in progress. Estimated rollback completion: 96%. Estimated time remaining: 20423 seconds.But the Estimated time remaining is increasing, is this right??
It happens. It's an estimate and hence if things are going slower than SQL thought, the initial estimate will be very low and will increasequote:
Any ideas??
Wait. SQL has to complete that rollback and there's no way to abort it. Even if you restart the service, SQL will finish doing the rollback after the restart and the DB will be completely inaccessible while it's doing that.--Gail ShawSQL Server MVP |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-02 : 08:00:22
|
| How often is folder compressed? |
|
|
|
petek
Posting Yak Master
192 Posts |
Posted - 2009-01-02 : 08:14:23
|
| Hi Sodeep,File/Folder commpression has been enabled i am not sure how often...Kind RegardsPete. |
|
|
|
petek
Posting Yak Master
192 Posts |
Posted - 2009-01-02 : 08:16:33
|
| Sorry just to add....if I enable File/Folder compression does it have any performance issues for the backups, I mean if SQL is writing backups to these compressed folders does it slow the process down at all?Kind RegardsPete. |
|
|
|
petek
Posting Yak Master
192 Posts |
Posted - 2009-01-02 : 11:07:09
|
| hi i am still haveing probelms with this job. If i delete the job will all attached processes be deleted aswell??Kind RegardsPete. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-01-03 : 09:22:16
|
quote:
I mean if SQL is writing backups to these compressed folders does it slow the process down at all?
Absolutely. That's the tradeoff with compression. Less space, more CPU time.quote:
If i delete the job will all attached processes be deleted aswell??
You mean if you delete the job, will the connections executing that job be dropped? No, they will not. If you want to kill the running connections, use the kill command and wait for them to roll back.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Backup Failing.....