August 23, 2003 at 7:36 am
We have some nightly maintenance whose bottom line is we want to detach a database called DW.
Detach requires getting people off the database, "people" might include sqlagent as well as connected users as well as linked servers. Some of these run with dbo privileges (e.g. some SQLAgent jobs).
What we do now (that is not reliable) is this:
Loop a few times (like 6)
- Go through sysprocesses and syslocks for anyone touching it
- Kill them
- set "dbo use only true"
After we do this a bunch of times we then try a detach.
The problem is that sometimes this occurs when other dbo-level processes, often from linked servers, are hitting the database. The timing of these is just not something we can schedule accurately, there are two many asynchronous processes involved including human ones in remote warehouses. They are all forgiving of being killed or disconnected.
Any suggestions? What I really want is "allow no further connections to this database" while I kick everyone out. The problem is not can I kill their processes, but that as I kill process A, B, C, D by the time I get to D or so, A reconnects. Or if not reconnections, some other process D reconnects that wasn't visible when it was looking in sysprocesses.
Can I lock sysprocesses perhaps? Would that prevent a new process starting until I can get the database detached?
August 23, 2003 at 10:22 am
ALTER DATABASE SET SINGLE_USER WITH [ROLLBACK IMMEDIATE | ROLLBACK AFTER integer [SECONDS]]
Joseph
August 23, 2003 at 11:05 am
Sounds promising. We had not noticed that. I take it that it's unconditional regardless of who has the transactions open. Will try it.
August 24, 2003 at 8:45 pm
ALTER DATABASE [add database name here] SET OFFLINE WITH ROLLBACK IMMEDIATE
This statement will kill all users and take the database off line. Then when you are done, you will then run
ALTER DATABASE [add database name here] SET ONLINE WITH ROLLBACK IMMEDIATE
This will bring it back.
There is only one catch, you need to be in Master to do this and must have sysadmin rights.
You can try to create a system stored procedure in Master with these statemenst and call it from whatever database you are in...
Good luck with it.
Johnny
quote:
We have some nightly maintenance whose bottom line is we want to detach a database called DW.Detach requires getting people off the database, "people" might include sqlagent as well as connected users as well as linked servers. Some of these run with dbo privileges (e.g. some SQLAgent jobs).
What we do now (that is not reliable) is this:
Loop a few times (like 6)
- Go through sysprocesses and syslocks for anyone touching it
- Kill them
- set "dbo use only true"
After we do this a bunch of times we then try a detach.
The problem is that sometimes this occurs when other dbo-level processes, often from linked servers, are hitting the database. The timing of these is just not something we can schedule accurately, there are two many asynchronous processes involved including human ones in remote warehouses. They are all forgiving of being killed or disconnected.
Any suggestions? What I really want is "allow no further connections to this database" while I kick everyone out. The problem is not can I kill their processes, but that as I kill process A, B, C, D by the time I get to D or so, A reconnects. Or if not reconnections, some other process D reconnects that wasn't visible when it was looking in sysprocesses.
Can I lock sysprocesses perhaps? Would that prevent a new process starting until I can get the database detached?
August 24, 2003 at 9:41 pm
Interesting, although status offline when detached, after being attached it is online. No need to explicit set it to online.
ALTER DATABASE test SET offline WITH ROLLBACK IMMEDIATE
GO
EXEC sp_detach_db 'Test', 'true'
GO
EXEC sp_attach_db @dbname = N'Test',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Test_Data.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Test_Log.ldf'
GO
Exec sp_helpdb 'Test'
GO
April 12, 2004 at 3:07 pm
I am attempting to create a script to move db's from one server to another. I have run into this problem as well. 5409045121009, Do I need to be using the MASTER db to use your method? Here is a sample of my script printout so far... (this is the ASPState db which is for asp.net sqlServer session management).
server currently going down:
ALTER DATABASE ASPState SET offline WITH ROLLBACK IMMEDIATE
GO
sp_detach_db 'ASPState'
USE master EXEC xp_cmdshell 'copy \\server1\....\ASPState.mdf server2...\MSSQL\data2', NO_OUTPUT
GO
USE master EXEC xp_cmdshell 'copy \\server1...\ASPState_log.LDF \\server2...\data2', NO_OUTPUT
GO
server taking over:
sp_attach_db 'ASPState' , 'server2....\data2\ASPState.mdf', 'server2....\data2\ASPState_log.LDF'
GO
USE ASPState sp_change_users_login 'AUTO_FIX','...my user name...'
GO
I query the msdb table to get this information and create the sql to run. Am I on the right track, or is there a USE master I need to put in there somewhere to get the process-killing to work?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply