September 23, 2011 at 8:48 am
I'm trying to restore an HP Quality Center database with full text catalog and the restore works initially however, when i try to restore it again my "ALTER DATABASE" gets blocked by spid -2. I've read that it has something to do with an orphaned distributed transaction but i'm not able to find where this may be. I've noticed that by restarting the sql services I then can ALTER the database to turn it offline in preperation for the restore. Does anyone have any ideas on another way around this other than restarting sql services?
I haven't been able to find anything on orphaned distributed transactions so if anyone has any ideas where i can look that would be great!
Thank you!
September 23, 2011 at 8:58 am
You can kill an orphaned transaction (lookup the KILL command in BOL) which will prevent you having to restart the service.
Leyton
September 23, 2011 at 9:07 am
I have the alter database command running right now and it is blocked by -2.
I've executed:
select req_transactionUOW
from master..syslockinfo
and it gives me 00000000-0000-0000-0000-000000000000
but when i try and kill it. I get:
Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.
September 23, 2011 at 9:14 am
ggoble (9/23/2011)
I'm trying to restore an HP Quality Center database with full text catalog and the restore works initially however, when i try to restore it again my "ALTER DATABASE" gets blocked by spid -2. I've read that it has something to do with an orphaned distributed transaction but i'm not able to find where this may be. I've noticed that by restarting the sql services I then can ALTER the database to turn it offline in preperation for the restore. Does anyone have any ideas on another way around this other than restarting sql services?I haven't been able to find anything on orphaned distributed transactions so if anyone has any ideas where i can look that would be great!
Thank you!
Question? Is the database you are trying to alter setup as the default database for the user that is trying to alter it? I have had some issues in the past trying to restore databases etc when the default database for a user is set to the database I am trying to alter instead of the master db.
September 23, 2011 at 9:19 am
Unfortunatly no. default is set to master.
Another note... I've went onto both the production server where the database is being backed up from and the server i'm trying to restore to. I do not see any transaction visible in the DTC GUI. I've tried restarting DTC a few times but it doesn't have any affect.
September 23, 2011 at 10:00 am
ggoble (9/23/2011)
Unfortunatly no. default is set to master.Another note... I've went onto both the production server where the database is being backed up from and the server i'm trying to restore to. I do not see any transaction visible in the DTC GUI. I've tried restarting DTC a few times but it doesn't have any affect.
So is the problem the backup? If you created a new backup file do you still have the issue? I am wondering if there was some sort of distributed transaction that was going on while the backup was being made or something like that.
September 23, 2011 at 10:23 am
ggoble (9/23/2011)
I have the alter database command running right now and it is blocked by -2.I've executed:
select req_transactionUOW
from master..syslockinfo
and it gives me 00000000-0000-0000-0000-000000000000
but when i try and kill it. I get:
Msg 6110, Level 16, State 1, Line 1
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.
I think that that dummy GUID (0) indicates something that doesn't have a DTC Transaction GUID because is not a DTC transaction.
Instead of looking in syslockinfo (which is long deprecated), try looking instead in sys.dm_tran_locks, and look for rows where request_request_id is -2.
Tom
September 27, 2011 at 9:14 am
I've found my issue... My restore script was trying to restore a file that didn't exist.
I would have thought that the restore would error out, instead it restored the database and seemed as everything was normal until I tried to run an alter/drop on the database.
RESTORE DATABASE @TargetDatabaseName FROM DISK = @BackupPathName
WITH REPLACE,
MOVE 'default_db_dat' TO @default_31_db,
MOVE 'default_db_log' TO @default_31_db_log,
MOVE 'ftrow_QCFTCAT' TO @default_db_ft1
--I commented this out along with where i declared the variables and restores continued to work.
--MOVE 'sysft_QCFTCAT' TO @default_db_ft2
December 14, 2012 at 7:24 am
WOW!
I am having the exact same issue and have been beating my head because every single blog and post on this says to kill the process with the UOW!
I am going to have my customer test this out (The restore shows 4 files, the DB shows 3) and if it works I'll make sure to spread the word!
THANKS for finding this.. how on earth did you stumble on it?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply