October 11, 2007 at 9:12 am
Hello,
I am having a peculiar problem in SQL Server 2005
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
with one of the databases. I need to change the file locations, but actions that need there to be no connections to the database fail. I cannot successfully run any of the following:
alter database [dbname] set offline with rollback immediate
alter database [dbname] set offline with rollback after 60 seconds
sp_dboption 'dbname','single user','true'
sp_detach_db 'dbname'
However, I have already killed all the spids, and there are none to the database as the following returns no records:
select * from sysprocesses where dbid = 24
or sp_who (for the [dbname] in question)
There are no spids for the dbname database.
I tried performing similar actions on a different database on the server with success. I compared the database properties and they are the same - simple recovery mode, torn page detection etc.
The database that I have the problem with is a user database, and I have searched online and found nothing that has helped so far. It seems as though there is some kind of rogue connection/transaction in the database that cannot be rolled back or rolled forward.
Please respond if you have run into something like this or heard of something like this before.
October 11, 2007 at 9:37 am
i usually use "alter database set single_user with rollback immediate"
October 11, 2007 at 10:07 am
Yeah, alter database is better than sp_dboption. It allows for more flexibility. In addition the sp_dboption is only available for backward compatibility from what I understand. I will get into the habit of using the alter database versus the sp_dboption starting right now.
The problem is still persisting however.
October 11, 2007 at 10:13 am
I'm about out of ideas then, short of stopping the sql server if that is a possibility.
October 11, 2007 at 10:34 am
See if you've got anything hanging out here:
SELECT request_session_id, request_owner_guid
FROM
sys.dm_tran_locks
October 11, 2007 at 10:39 am
Yeah, it's one of those problems that there just doesn't seem to be anywhere to go with.
If I stopped the SQL Server Service I still don't think I would accomplish what I need to do though. I need the files to be relocated, so if I were to do that and then restart the service the datafiles would not be where SQL Server expects them to be (sysfiles) and it wouldn't be able to bring the database up. However, I do suppose I could then just drop that database and recreate it via sp_attach_db. This would be a whole lot more cumbersome than should be required, but the situation doesn't leave me with any alternatives that I can see.
Do you know off hand if there would be risks associated with starting the SQL Server service with the data files for a database moved? I would expect it might show the database as being corrupt or offline.This wouldn't be a problem just so long as it lets me alter or drop it.
October 11, 2007 at 10:44 am
Actually yes, there are several records returned for
SELECT request_session_id, request_owner_guid
FROM
sys.dm_tran_locks
I am not sure what these map to with regards to processes or activity on the server. (not too familliar with dm tables. . .)
Could you give me any guidance/hints as to how I might deal with this?
October 11, 2007 at 10:47 am
What is the data you're getting back? It's possible you have orphaned transactions (which don't show up via other routes). If that's the case, you'll have to kill them via their GUID.
October 11, 2007 at 10:57 am
yeah, It's been a while since I had to do that, but I believe you would have to stop sql server. move the files. start sql server using traceflag 3608 (this will start your server and only recover master database). Then you need to drop your database, restart sql server without the trace flag, then attach the files.
Just restarting the service may clear up the rouge process that has it's grips on your database and allow you to detach, I have had sucess using both of these approaches.
October 11, 2007 at 10:58 am
of course if you choose to do the drop db method, always a good idea to grab a backup first.
October 11, 2007 at 12:24 pm
The data it returns is
7000000000-0000-0000-0000-000000000000
8900000000-0000-0000-0000-000000000000
6900000000-0000-0000-0000-000000000000
8300000000-0000-0000-0000-000000000000
8000000000-0000-0000-0000-000000000000
for a top 5 with an actual total of 43 records just like this (000...Request_owner_guid)
When killing processes by the guid don't I have to do that at the windows level?
October 11, 2007 at 12:27 pm
The other problem I see with this is that there really isn't a way to map the data from sys.dm_tran_locks back to a particular database that I am aware of. How do I know any of the records returned pertain to the database in question?
October 11, 2007 at 1:00 pm
What's the request_session_id?
KILL 'TheGuid'
GO
KILL 'TheGuid' WITH STATUSONLY
GO --to check the status of the rollback
works just fine in QA
There's a resource_database_id column in sys.dm_tran_locks. Just filter to your troubled DB. Or run:
Select DB_NAME(resource_database_id), * from sys.dm_tran_locks
to get the database name for each entry.
October 11, 2007 at 2:47 pm
The request_Session_ID's are
54
69
83
80 --this is one of the culprits
87 --this is the other culprit
of these spid 80 and 87 actually have master (dbid=1) context in sysprocesses. They are both SQLAgent jobs that have a last batch of 2007/07/09 (3 months ago) The cmd for both are Killed/Rollback. If I try to kill them I get
SPID 80: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
It looks like they have been in rollback status for 3 months. Any ideas for dealing with these 2?
The following record is from sysprocesses for spid 80
block 0
waittime 0
lastwaittype Writelog
dbid 1
uid 1
cpu 891
physical_io 10
memusage 2
login_time 2007-07-09 11:18:22.743
last_batch 2007-07-09 11:18:22.743
open_tran 0
status runnable
program_name SQLAgent - TSQL JobStep (Job 0xE44A5D477BC6874496EEE69D0CB57A12 : Step )
cmd KILLED/ROLLBACK
October 11, 2007 at 3:14 pm
What kind of jobs are they?
Have you tried stopping and restarting the Agent?
Other than that, I'd try KILL of the GUID.
Beyond that, I don't know.
Do let us know what you find out.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply