Drop database fails because of aspnet_wp.exe

Hey everyone.

I'm having some problems dropping a database because the aspnet_wp.exe process is accessing it. Whenever I try to drop it, I get an error stating that the database is being used. If I access the task manager and end the aspnet_wp.exe process manually and then retry the database drop it works... :\

Any help? Thaks in advance,

Sérgio

You can't drop the database because it is using by some .NET application. aspnet_wp.exe is a worker process in which .NET runs code, you can take a look at this article:

http://www.xefteri.com/articles/show.cfm?id=14

|||A connection is being kept open by an asp.net application. Either it forgot to close the connection after it was rendering a page, or (more likely), the connection was kept open in the connection pool.

DROP DATABASE <@variable>

Hi,
I am getting an error for a very simple SQL statement below. I cannot figure
out why it fails. Is specifying a variable for the Drop Database command not
supported?
DECLARE @.mydb varchar(255)
set @.mydb = 'mytestdb'
DROP DATABASE @.mydbYou have to put it in dynamic sql:
DECLARE @.mydb varchar(255)
set @.mydb = 'mytestdb'
EXEC('DROP DATABASE ' + @.mydb)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kevin" <nospam@.discussion.com> schrieb im Newsbeitrag
news:OT1ssOxWFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am getting an error for a very simple SQL statement below. I cannot
> figure out why it fails. Is specifying a variable for the Drop Database
> command not supported?
> DECLARE @.mydb varchar(255)
> set @.mydb = 'mytestdb'
> DROP DATABASE @.mydb
>
>|||Hi
Variables are not supported.
Build a string up and use EXEC
Regards
--
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/
"Kevin" <nospam@.discussion.com> wrote in message
news:OT1ssOxWFHA.3676@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am getting an error for a very simple SQL statement below. I cannot
> figure out why it fails. Is specifying a variable for the Drop Database
> command not supported?
> DECLARE @.mydb varchar(255)
> set @.mydb = 'mytestdb'
> DROP DATABASE @.mydb
>
>|||Your sample statement worked. Thank you!
"Jens S $B!& (Bmeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wro
te in
message news:%23LKU$UxWFHA.2540@.tk2msftngp13.phx.gbl...
> You have to put it in dynamic sql:
> DECLARE @.mydb varchar(255)
> set @.mydb = 'mytestdb'
> EXEC('DROP DATABASE ' + @.mydb)
>
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Kevin" <nospam@.discussion.com> schrieb im Newsbeitrag
> news:OT1ssOxWFHA.3676@.TK2MSFTNGP10.phx.gbl...
>|||Now the statement worked. Thank you.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23tnFKWxWFHA.2256@.TK2MSFTNGP14.phx.gbl...
> Hi
> Variables are not supported.
> Build a string up and use EXEC
> Regards
> --
> 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/
> "Kevin" <nospam@.discussion.com> wrote in message
> news:OT1ssOxWFHA.3676@.TK2MSFTNGP10.phx.gbl...
>

drop column fails

Hi all,
Want to drop a column of an existing table with
alter table TE20_AREA
drop column E20IN_NODE_NUMBER
But get:
The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
'E20IN_NODE_NUMBER'.
What is the object DF... checked for indexes, constraints, relations
haven't found somthing with this name '
BTW, deleting the row in the gui works like a charme '? (Does not
help because db modification needs to be in a batch job :-( )
Any help is highly appreciated.
TIA
DanHi Dan,
An object name starting with DF_ and ending in a number is a automatically
generated name for a default constraint. You will see these names when you
create a column with a default in a table in Enterprise Manager, or with for
example CREATE TABLE some_table(some_column INT DEFAULT 0). You can
explicitly name default constraints with:
CREATE TABLE some_table(some_column INT CONSTRAINT
DF_some_table__some_column_is_zero DEFAULT 0).
You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
--
Jacco Schalkwijk
SQL Server MVP
"Dan Ackermann" <dummy@.intos.ch> wrote in message
news:%2313rW$4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Want to drop a column of an existing table with
> alter table TE20_AREA
> drop column E20IN_NODE_NUMBER
> But get:
> The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
> 'E20IN_NODE_NUMBER'.
> What is the object DF... checked for indexes, constraints, relations
> haven't found somthing with this name '
> BTW, deleting the row in the gui works like a charme '? (Does not
> help because db modification needs to be in a batch job :-( )
> Any help is highly appreciated.
> TIA
> Dan
>

drop column fails

Hi all,
Want to drop a column of an existing table with
alter table TE20_AREA
drop column E20IN_NODE_NUMBER
But get:
The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
'E20IN_NODE_NUMBER'.
What is the object DF... checked for indexes, constraints, relations
haven't found somthing with this name '
BTW, deleting the row in the gui works like a charme '? (Does not
help because db modification needs to be in a batch job :-( )
Any help is highly appreciated.
TIA
DanHi Dan,
An object name starting with DF_ and ending in a number is a automatically
generated name for a default constraint. You will see these names when you
create a column with a default in a table in Enterprise Manager, or with for
example CREATE TABLE some_table(some_column INT DEFAULT 0). You can
explicitly name default constraints with:
CREATE TABLE some_table(some_column INT CONSTRAINT
DF_some_table__some_column_is_zero DEFAULT 0).
You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Dan Ackermann" <dummy@.intos.ch> wrote in message
news:%2313rW$4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Want to drop a column of an existing table with
> alter table TE20_AREA
> drop column E20IN_NODE_NUMBER
> But get:
> The object 'DF__TE20_AREA__E20IN__26509D48' is dependent on column
> 'E20IN_NODE_NUMBER'.
> What is the object DF... checked for indexes, constraints, relations
> haven't found somthing with this name '
> BTW, deleting the row in the gui works like a charme '? (Does not
> help because db modification needs to be in a batch job :-( )
> Any help is highly appreciated.
> TIA
> Dan
>

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
Thanks
Have you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks
|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. I
s
there a way to drop the clustered index without creating new indexes? Or hav
e
the new indexes ignore dulicates?
ThanksHave you got a complete repro? I'm wondering if there is a foreign key from
another table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks|||My guess is that you have a corruption problem. Seem you have a unique nc in
dex in which you have
managed to get duplicates. So the dropping of the clustered index will re-cr
eate the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem.
Is
> there a way to drop the clustered index without creating new indexes? Or h
ave
> the new indexes ignore dulicates?
> Thanks

drop clustered index - fails on duplicate key

When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem. Is
there a way to drop the clustered index without creating new indexes? Or have
the new indexes ignore dulicates?
ThanksHave you got a complete repro? I'm wondering if there is a foreign key from
another table.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
When I try to drop my unique clustered index with the following code:
if exists (select * from dbo.sysindexes
where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
drop index [dbo].[my_Table].[my_Index]
GO
it fails with the error:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 2. Most significant primary key is '23497698'.
The statement has been terminated.
I understand that when you drop a clustered index unclustered indexes are
atomatically created and I am guessing one of them is causing the problem.
Is
there a way to drop the clustered index without creating new indexes? Or
have
the new indexes ignore dulicates?
Thanks|||My guess is that you have a corruption problem. Seem you have a unique nc index in which you have
managed to get duplicates. So the dropping of the clustered index will re-create the nc index and it
fails because of this. I'd do DBCC CHECKDB and see what it says.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:7007DC7D-5C2D-46B8-B912-5F9D85F1F694@.microsoft.com...
> When I try to drop my unique clustered index with the following code:
> if exists (select * from dbo.sysindexes
> where name = N'my_Index' and id = object_id(N'[dbo].[my_Table]'))
> drop index [dbo].[my_Table].[my_Index]
> GO
>
> it fails with the error:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 2. Most significant primary key is '23497698'.
> The statement has been terminated.
>
> I understand that when you drop a clustered index unclustered indexes are
> atomatically created and I am guessing one of them is causing the problem. Is
> there a way to drop the clustered index without creating new indexes? Or have
> the new indexes ignore dulicates?
> Thanks

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a database. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly appreciated.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LDF'"
Thanks,
Ram
Ram
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/************************************************** *************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
************************************************** **************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>
|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
>news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a datab
ase. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is
there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly apprecia
ted.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS
', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LD
F'"
Thanks,
RamRam
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/ ****************************************
***********************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
****************************************
************************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
> )
>AS
>BEGIN
>/ ****************************************
*****************
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
> ****************************************
******************
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx
.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
> )
>AS
>BEGIN
>/ ****************************************
*****************
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
> ****************************************
******************
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
> news:15e7101c416a4$9ad58a70$a501280a@.phx
.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>

Drop a suspect database

a sql 7.0 database goes suspect because the HD fails. Now i cant restore the database from the backup neither delete the database. How can i do it
In the fail i lose the mdf archiveRestart the sqlserver in mimum configuration mode.
Then delete the entry in the master.dbo.sysdatabases.
Restart the sqlserver.
"Rafael Araujo" <raraujoh@.hotmail.com> wrote in message
news:B1E10C04-6C75-4B0B-9958-2D38B709CF7C@.microsoft.com...
> a sql 7.0 database goes suspect because the HD fails. Now i cant restore
the database from the backup neither delete the database. How can i do it?
> In the fail i lose the mdf archive|||So you want to drop the database in order to be able to restore it from a
backup?
If so, first try DROP DATABASE from query analyzer. If that doesn't work,
try sp_dbremove.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rafael Araujo" <raraujoh@.hotmail.com> wrote in message
news:B1E10C04-6C75-4B0B-9958-2D38B709CF7C@.microsoft.com...
> a sql 7.0 database goes suspect because the HD fails. Now i cant restore
the database from the backup neither delete the database. How can i do it?
> In the fail i lose the mdf archive