drop constraint hanging.

I'm trying to fix a corrupt database, complains about indexes. I've been
waiting for drop constraint to come back, its been running for 1.5 hrs and
the drive activity is at 100%. Is this normal?
Everything is possible once you have a corrupt database. Check your system
event log for errors as well. It is possible for disk I/O to slow down
significantly if there are hardware problems.
Going forward, your course of action should be to identify what caused
corruption and restore your database from the last good backup.
Adrian
"michael" <michael@.discussions.microsoft.com> wrote in message
news:D044278A-7F75-43E9-A1F4-C5D0CE807397@.microsoft.com...
> I'm trying to fix a corrupt database, complains about indexes. I've been
> waiting for drop constraint to come back, its been running for 1.5 hrs and
> the drive activity is at 100%. Is this normal?

drop constraint hanging.

I'm trying to fix a corrupt database, complains about indexes. I've been
waiting for drop constraint to come back, its been running for 1.5 hrs and
the drive activity is at 100%. Is this normal?Everything is possible once you have a corrupt database. Check your system
event log for errors as well. It is possible for disk I/O to slow down
significantly if there are hardware problems.
Going forward, your course of action should be to identify what caused
corruption and restore your database from the last good backup.
Adrian
"michael" <michael@.discussions.microsoft.com> wrote in message
news:D044278A-7F75-43E9-A1F4-C5D0CE807397@.microsoft.com...
> I'm trying to fix a corrupt database, complains about indexes. I've been
> waiting for drop constraint to come back, its been running for 1.5 hrs and
> the drive activity is at 100%. Is this normal?

drop constraint hanging.

I'm trying to fix a corrupt database, complains about indexes. I've been
waiting for drop constraint to come back, its been running for 1.5 hrs and
the drive activity is at 100%. Is this normal?Everything is possible once you have a corrupt database. Check your system
event log for errors as well. It is possible for disk I/O to slow down
significantly if there are hardware problems.
Going forward, your course of action should be to identify what caused
corruption and restore your database from the last good backup.
Adrian
"michael" <michael@.discussions.microsoft.com> wrote in message
news:D044278A-7F75-43E9-A1F4-C5D0CE807397@.microsoft.com...
> I'm trying to fix a corrupt database, complains about indexes. I've been
> waiting for drop constraint to come back, its been running for 1.5 hrs and
> the drive activity is at 100%. Is this normal?

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 1 article from publication and add it back, sql server 2000 s

Recently I have experienced problems with subscriptions getting out of sync.
Some of the published tables are very large and are connected to the
subscriber via t1 connection. To avoid re-initializing the entire publication
I ran sp_dropsubscription followed by sp_droparticle, both specifying the
article that is out of sync. Then I opened the publication properties from
the replication monitor, went to the articles tab, and added the article back
to the publication. When I run the snapshot again a new snapshot is generated
for that article and it subsequently is pushed to the subscriber. Sometimes
the artilces will get out of sync again in a day or two and some articles
stay synchronized without any problems.
The script I run to drop the article is:
use db1
go
declare @.p varchar(128)
declare @.a varchar(128)
declare @.db varchar(128)
set @.p = 'db1_Publication'
set @.a = 'tbl1'
set @.db = 'db1'
exec sp_dropsubscription @.publication = @.p ,@.article = @.a
,@.subscriber = 'rptServer' ,@.destination_db = @.db
exec sp_droparticle @.publication = @.p
,@.article= @.a
,@.force_invalidate_snapshot = 0
I believe this is the correct way to drop 1 article from a publication that
contains many articles and adding it back again to reinitialize, but I
haven't been able to find any examples of this anywhere.
The question I pose to the replication gurus is, does this work? I am unsure
at this point due to the inconsistent results I've been getting.
Thanks,
Jason
Jason,
this is much the same way I do it as well (see
http://www.replicationanswers.com/AddColumn.asp). I would like to
investigate the non-synchronization of these tables though. Do you see any
commands in the distribution database for these tables (sp_browsereplcmds)?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Drill Through bringing back too many results

I have created a Matrix Report through the Report Builder and specified a filter on a Date field so it only shows the current months work. This works for the initial Totals within the Matrix but when you click on the Totals to show the Drill Down it will show all the results regardless of the Parameter.

How do I make the results in the Drill Through look at the Parameter I am passing to the Report?

Many Thanks

I'm not sure I understand your scenario, and I can't repro the behavior you describe using my best guess. Can you clarify:

1. What fields are in your report?

2. Did you actually create a report parameter by setting Prompt on the filter condition, or did you just create a normal report filter?

Any other info you can provide to further clarify your scenario would be helpful. Thanks!