How to force a database detach?

Hi.
I need to force the detaching of a database, ie. make the detach not to fail
due to active connections. I need the active connections to be broken.
What I really need is to remove the LDF, and since MSSQL lokcs it I need
either to stop the SQL Server or to detach the database from it
momentaniously.
Thanks in advance,
RODOLFO"RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
> Hi.
> I need to force the detaching of a database, ie. make the detach not to
fail
> due to active connections. I need the active connections to be broken.
> What I really need is to remove the LDF, and since MSSQL lokcs it I need
> either to stop the SQL Server or to detach the database from it
> momentaniously.
> Thanks in advance,
> RODOLFO
This may help. I don't remember if I wrote this, or copied it from someone
else. If I did copy it from someone else, thank you.
Essentially, it is a stored proc (needs some work to ensure it doesn't try
to kill it's own process), that will go through each database and kill
connections.
You could then do something like the following in your code:
EXEC sp_KillProcess 'DatabaseToBeDetached'
EXEC sp_detach_db 'DatabaseToBeDetached'
etc.
HTH
Rick
USE master
GO
CREATE PROC dbo.sp_KillProcess
@.dbname nvarchar(128) = ''
AS
-- This will kill all spid's associated with a particular database.
-- If no database name is given, then all non-system databases will
-- be assumed.
BEGIN
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
DECLARE @.KillSpid int,
@.Count int,
@.SQL varchar(500)
IF (@.dbname IS NULL) OR (DATALENGTH(@.dbname) = 0)
-- Assume that we want all connections dropped.
BEGIN
DECLARE KillCursor CURSOR FOR
SELECT a.spid
FROM master.dbo.sysprocesses a, master.dbo.sysdatabases b
WHERE a.dbid = b.dbid
AND b.[name] NOT IN ('master', 'model', 'msdb', 'tempdb')
END
ELSE
-- We only want connections to this specific database to be dropped.
BEGIN
DECLARE KillCursor CURSOR FOR
SELECT a.spid
FROM master.dbo.sysprocesses a, master.dbo.sysdatabases b
WHERE a.dbid = b.dbid
AND b.[name] NOT IN ('master', 'model', 'msdb', 'tempdb')
AND UPPER(b.[name]) = UPPER(@.dbname)
END
OPEN KillCursor
FETCH NEXT FROM KillCursor INTO @.KillSpid
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SET @.SQL = 'KILL ' + CONVERT(varchar, @.KillSpid)
EXEC(@.SQL)
FETCH NEXT FROM KillCursor INTO @.KillSpid
END
CLOSE KillCursor
DEALLOCATE KillCursor
END
GO|||Hi
Take the DB offline.
ALTER DATABASE pubs
SET OFFLINE
WITH ROLLBACK IMMEDIATE
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
> Hi.
> I need to force the detaching of a database, ie. make the detach not to
> fail
> due to active connections. I need the active connections to be broken.
> What I really need is to remove the LDF, and since MSSQL lokcs it I need
> either to stop the SQL Server or to detach the database from it
> momentaniously.
> Thanks in advance,
> RODOLFO|||Hi and thanks... I tried the script but it gives me an error: Can't use KILL
to kill your own process... I'm trying to solve it now, but Im not a SQL
programmer.. any help would be very appreciated :-D
Thanks again,
RODOLFO
"Rick Sawtell" wrote:
> "RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
> news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
> > Hi.
> > I need to force the detaching of a database, ie. make the detach not to
> fail
> > due to active connections. I need the active connections to be broken.
> >
> > What I really need is to remove the LDF, and since MSSQL lokcs it I need
> > either to stop the SQL Server or to detach the database from it
> > momentaniously.
> >
> > Thanks in advance,
> > RODOLFO
> This may help. I don't remember if I wrote this, or copied it from someone
> else. If I did copy it from someone else, thank you.
> Essentially, it is a stored proc (needs some work to ensure it doesn't try
> to kill it's own process), that will go through each database and kill
> connections.
> You could then do something like the following in your code:
> EXEC sp_KillProcess 'DatabaseToBeDetached'
> EXEC sp_detach_db 'DatabaseToBeDetached'
> etc.
>
> HTH
> Rick
>
> USE master
> GO
>
> CREATE PROC dbo.sp_KillProcess
> @.dbname nvarchar(128) = ''
> AS
> -- This will kill all spid's associated with a particular database.
> -- If no database name is given, then all non-system databases will
> -- be assumed.
> BEGIN
> SET NOCOUNT ON
> SET QUOTED_IDENTIFIER OFF
> DECLARE @.KillSpid int,
> @.Count int,
> @.SQL varchar(500)
>
> IF (@.dbname IS NULL) OR (DATALENGTH(@.dbname) = 0)
> -- Assume that we want all connections dropped.
> BEGIN
> DECLARE KillCursor CURSOR FOR
> SELECT a.spid
> FROM master.dbo.sysprocesses a, master.dbo.sysdatabases b
> WHERE a.dbid = b.dbid
> AND b.[name] NOT IN ('master', 'model', 'msdb', 'tempdb')
> END
> ELSE
> -- We only want connections to this specific database to be dropped.
> BEGIN
> DECLARE KillCursor CURSOR FOR
> SELECT a.spid
> FROM master.dbo.sysprocesses a, master.dbo.sysdatabases b
> WHERE a.dbid = b.dbid
> AND b.[name] NOT IN ('master', 'model', 'msdb', 'tempdb')
> AND UPPER(b.[name]) = UPPER(@.dbname)
> END
> OPEN KillCursor
> FETCH NEXT FROM KillCursor INTO @.KillSpid
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> SET @.SQL = 'KILL ' + CONVERT(varchar, @.KillSpid)
> EXEC(@.SQL)
> FETCH NEXT FROM KillCursor INTO @.KillSpid
> END
> CLOSE KillCursor
> DEALLOCATE KillCursor
> END
> GO
>
>
>|||Hi and thanks a lot.
I tried it and I get this error: ALTER DATABASE statement failed. User must
be in the master database.
Im using the sa user... I also tried with a different user with almost every
combination of permissions I could imagine.
Any more ideas?
Thanks a lot.
RODOLFO
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Take the DB offline.
> ALTER DATABASE pubs
> SET OFFLINE
> WITH ROLLBACK IMMEDIATE
>
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
> news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
> > Hi.
> > I need to force the detaching of a database, ie. make the detach not to
> > fail
> > due to active connections. I need the active connections to be broken.
> >
> > What I really need is to remove the LDF, and since MSSQL lokcs it I need
> > either to stop the SQL Server or to detach the database from it
> > momentaniously.
> >
> > Thanks in advance,
> > RODOLFO
>
>|||Hi,
Try this:-
USE Master
GO
ALTER DATABASE <DBNAME> SET OFFLINE WITH ROLLBACK IMMEDIATE
After this detach the database using
sp_detach_db <DBNAME>
Thanks
Hari
SQL Server MVP
>
"RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
news:999DBD22-91B1-41E6-BC04-652DB8B6157A@.microsoft.com...
> Hi and thanks a lot.
> I tried it and I get this error: ALTER DATABASE statement failed. User
> must
> be in the master database.
> Im using the sa user... I also tried with a different user with almost
> every
> combination of permissions I could imagine.
> Any more ideas?
> Thanks a lot.
> RODOLFO
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Take the DB offline.
>> ALTER DATABASE pubs
>> SET OFFLINE
>> WITH ROLLBACK IMMEDIATE
>>
>> --
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "RODOLFO" <RODOLFO@.discussions.microsoft.com> wrote in message
>> news:4558BA4E-B20E-451D-AFE2-6F4D7CE98A3A@.microsoft.com...
>> > Hi.
>> > I need to force the detaching of a database, ie. make the detach not to
>> > fail
>> > due to active connections. I need the active connections to be broken.
>> >
>> > What I really need is to remove the LDF, and since MSSQL lokcs it I
>> > need
>> > either to stop the SQL Server or to detach the database from it
>> > momentaniously.
>> >
>> > Thanks in advance,
>> > RODOLFO
>>