Greetings:
I have a SQL 2000 server with several databases on it, and I have a
maintenance plan that includes both a backup and a CHECKDB Integrity
Check.
The backups appear to run correctly each night, but if a user leaves a
connection open to a database all night, the CHECKDB fails.
I'd like to be able to kill all client connections before each backup
window, so that the checkdb succeeds.
Is there a way to do this?
Thanks,
DW 9 2164
On 18 Feb 2005 09:02:44 -0800, DW wrote: Greetings:
I have a SQL 2000 server with several databases on it, and I have a maintenance plan that includes both a backup and a CHECKDB Integrity Check.
The backups appear to run correctly each night, but if a user leaves a connection open to a database all night, the CHECKDB fails.
I'd like to be able to kill all client connections before each backup window, so that the checkdb succeeds.
Is there a way to do this?
Thanks,
DW
I used to use the procedure below. It usually worked, but I was never able
to figure out what was wrong when it didn't.
create procedure mysp_kill_Spids_Using_Db
( @dbname varchar(80) )
AS
declare @spid int
declare @cmd varchar(80)
declare c cursor for
select spid from sysprocesses
inner join sysdatabases on sysprocesses.dbid = sysdatabases.dbid
where name =@dbname
open c
fetch next from c into @spid
while @@fetch_status=0
begin
select @cmd='kill ' + convert(varchar(10),@spid)
exec @cmd
fetch next from c into @spid
END
close c
deallocate c
GO
Then I'd schedule a T-SQL task to run this:
exec master.dbo.mysp_kill_spids_using_db 'FirstDB'
exec master.dbo.mysp_kill_spids_using_db 'SecondDB'
Thanks, Ross... I'll see if I can make that work for me.
Cheers,
DW
"DW" <dw*******@shaw.ca> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com... Greetings:
I have a SQL 2000 server with several databases on it, and I have a maintenance plan that includes both a backup and a CHECKDB Integrity Check.
The backups appear to run correctly each night, but if a user leaves a connection open to a database all night, the CHECKDB fails.
I'd like to be able to kill all client connections before each backup window, so that the checkdb succeeds.
Is there a way to do this?
Thanks,
DW
You can use ALTER DATABASE to close all connections to a database, and limit
access to sysadmin/db_owner only:
alter database MyDB set restricted_user with rollback immediate
Then after the plan completes:
alter database MyDB set multi_user
This assumes that your users don't have any special privileges in the
database, of course. If they do, you might need to set the database offline
then online again before the plan runs, to ensure that all connections are
killed, regardless of their privilege level.
Simon
On Fri, 18 Feb 2005 19:59:09 +0100, Simon Hayes wrote: This assumes that your users don't have any special privileges in the database, of course. If they do, you might need to set the database offline then online again before the plan runs, to ensure that all connections are killed, regardless of their privilege level.
I've tried sp_dboption 'MyDatabase','offline','true' in the past when users
were in the database, and it refused to do so.
Ross Presser (rp******@imtek.com) writes: On Fri, 18 Feb 2005 19:59:09 +0100, Simon Hayes wrote:
This assumes that your users don't have any special privileges in the database, of course. If they do, you might need to set the database offline then online again before the plan runs, to ensure that all connections are killed, regardless of their privilege level.
What I always use is:
alter database MyDB set single_user with rollback immediate
(Most users in our development databases have special priviledges. That is,
they are sa.)
I've tried sp_dboption 'MyDatabase','offline','true' in the past when users were in the database, and it refused to do so.
Yeah, but then you did not have the power of WITH ROLLBACK IMMEDIATE.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Okay, this isn't working out quite as I'd expected. I tried the
following:
-Go into the job for "Integrity Checks Job for DB Maintenance Plan '4.
Backup Databases'
-Added a step before and a step after, labeling them 'single mode' and
'multi mode', respectively
-In the commands for these two steps, I added "alter database MyDB set
single_user with rollback immediate" and "alter database MyDB set
multi_user".
-Put the steps in the right order, set the success and failure action,
and saved it.
I went into the Maintenance Plan, and got an error that the job could
not be parsed.
Presumably standard SQL commands will not go in as a job command. Is
this correct? If so, I'd have no idea what the correct syntax would
be...
Any wisdom out there?
Thanks!
DW
DW (dw*******@shaw.ca) writes: Okay, this isn't working out quite as I'd expected. I tried the following:
-Go into the job for "Integrity Checks Job for DB Maintenance Plan '4. Backup Databases' -Added a step before and a step after, labeling them 'single mode' and 'multi mode', respectively -In the commands for these two steps, I added "alter database MyDB set single_user with rollback immediate" and "alter database MyDB set multi_user". -Put the steps in the right order, set the success and failure action, and saved it.
I went into the Maintenance Plan, and got an error that the job could not be parsed.
Presumably standard SQL commands will not go in as a job command. Is this correct? If so, I'd have no idea what the correct syntax would be...
If I understand this correctly, you got the error when you try to look
at it as a maintenance plan. As you added custom steps, the job is no
longer a maintenance plan in that sense. I don't know too much about
the maintenance plans, but I guess it reads the commands for the
various steps and matches them against know activities. So when it
finds you ALTER DATABASE commands, it's get quite confused.
You should be able to run the job anyway.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
It's very weird. I can put the SQL statement in the command window, and
hit the 'parse' button and get a '0 errors' indicator.
Then I'll go back to the maintenance plan, get a warning that I
shouldn't add steps to jobs that are created by maintenance plans, and
then a parse error. But it seems to run properly, booting all sessions,
and then re-enabling multiuser after the integrity check.
And if I exit and go back into the maintenance plan again, my EM window
bombs with an access voilation. Yeesh.
Maybe I should put these commands in separate jobs, rather than
separate tasks within the same job.
It's very weird. I can put the SQL statement in the command window, and
hit the 'parse' button and get a '0 errors' indicator.
Then I'll go back to the maintenance plan, get a warning that I
shouldn't add steps to jobs that are created by maintenance plans, and
then a parse error. But it seems to run properly, booting all sessions,
and then re-enabling multiuser after the integrity check.
And if I exit and go back into the maintenance plan again, my EM window
bombs with an access voilation. Yeesh.
Maybe I should put these commands in separate jobs, rather than
separate tasks within the same job. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: jing_li |
last post by:
Hi, you all,
I am a newbee for php and I need your help. One of my coworker and I
are both developing a webpage for our project using php. We have a
copy of the same files in different location...
|
by: dchow |
last post by:
Our database size is currently 4G and is incrementing at a rate of
45M/day. What is the max size of a SQL database? And what is the
size beyond which the server performance will start to go down?
|
by: DW |
last post by:
Greetings:
I have a SQL 2000 server with several databases on it, and I have a
maintenance plan that includes both a backup and a CHECKDB Integrity
Check.
The backups appear to run correctly...
|
by: Daniel Walzenbach |
last post by:
Hi,
I have a web application which sometimes throws an “out of memory”
exception. To get an idea what happens I traced some values using performance
monitor and got the following values (for...
|
by: pinorama123 |
last post by:
I have an ASP.NET application that contains a few classes that I have
built. One of my objects is a user object. I have a pretty basic
question about how this would work.
If I have multiple...
| |
by: julian_m |
last post by:
i'm finishing my 2nd php project. It's a sort of catalog and I used
css/mysql as well. All the functionality of the site is mainly beacause
the great number of arguments I pass to every page on...
|
by: RoB |
last post by:
Hi all,
I'm coming from the Informix world and I have a customer using DB2
8.2.3 for Linux on Red Hat Enterprise ES.
The customer is performing filesystem backups of the containers etc
every...
|
by: jer006 |
last post by:
Hi,
This is more of a general question...
I have a series of stored procedures (chain of procedures) in db2 on
an AS400 which are executed from a SQL Server Job, when I execute the
job manually...
|
by: Spam Catcher |
last post by:
Hello Everyone,
Not sure if this is the place to ask ... I'm new to WCF.
I'm building a WCF single service which needs to:
1. Track User Sessions
2. Share data amongst sessions (singleton)...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |