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...
> fail
> 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,
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...[vbcol=seagreen]
> 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:

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...
> fail
> This may help. I don't remember if I wrote this, or copied it from someon
e
> 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,
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...[vbcol=seagreen]
> 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:
>

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
>>

how to find backup batches?

how and where to check if any backup batch is active on my sql server database?

What version of SQL Server?

If your using 2005 and

if by “backup batch is active” you mean a backup currently running.

in Object Explorer /Management look at the Activity Monitor

or

ff the backup is part of a job goto SQL Server Agent / Jobs /Job Activity Monitor

how to filter output based on user's AD group membership?

I want to filter reports based on a users active directory security i.e. what
parameters they can select, what columns they can see
But I can't figure out the best way of doing this and the documentation on
AD seems to assume that the user already understands AD.
From what I can tell I have two options
1) use sql with the openquery syntax - don't think this is going to be an
option due to the hassle here of setting up linked servers (bureaucracy in
the extreme)
2)use system.directoryservices with some code built into to the report - i
don't really understand how to do this, none of the code samples i have seen
seem to do what I want e.g. pass in username from global report parameters
along with the groupname i want to check against, and return whether they are
in that particular group as true/false
help greatly appreciated!
do you know a better way of doing this? code samples? etc
thanks!AD queries in SQL Server using the ADSI can be a problem... I am told it will
only search the first 1000 rows returned by the AD..
if you are using SQL 2005... Take a look at the sys.login_token
It shows all of the AD groups the user is a member of.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"adolf garlic" wrote:
> I want to filter reports based on a users active directory security i.e. what
> parameters they can select, what columns they can see
> But I can't figure out the best way of doing this and the documentation on
> AD seems to assume that the user already understands AD.
> From what I can tell I have two options
> 1) use sql with the openquery syntax - don't think this is going to be an
> option due to the hassle here of setting up linked servers (bureaucracy in
> the extreme)
> 2)use system.directoryservices with some code built into to the report - i
> don't really understand how to do this, none of the code samples i have seen
> seem to do what I want e.g. pass in username from global report parameters
> along with the groupname i want to check against, and return whether they are
> in that particular group as true/false
> help greatly appreciated!
> do you know a better way of doing this? code samples? etc
> thanks!