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 2185
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.db id = sysdatabases.db id
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_usi ng_db 'FirstDB'
exec master.dbo.mysp _kill_spids_usi ng_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******** *************@o 13g2000cwo.goog legroups.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','o ffline','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','o ffline','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****@sommarsk og.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****@sommarsk og.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 on the server (in our own
accounts on the same machine). When I am testing both versions of our
program using the same browser (IE on Windows or Konqueror on Linux)
the session variables will mix up and only the latest selection or
options will...
|
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 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
|
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 one day):
\\FFDS24\ASP.NET Applications(_LM_W3SVC_1_Root_ATV2004)\Errors During
Execution: 7
\\FFDS24\ASP.NET Apps v1.1.4322(_LM_W3SVC_1_Root_ATV2004)\Compilations
|
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 users logging into my application and using the user
object do they all share the same object or does ASP.NET know that each
user's object belongs to them? I'm seeing some strange behavior that
leads me to believe when one user dimensions an instance...
| |
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 the address bar.
For example
*number of items to display
*categories
*brands
*user_id
*price interval
*...
|
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 night but they are not shutting down the database server while
doing this. I only assume that this most likelly would leave an
inconsistant backup image as there is nothing assuring that the
modified pages in the buffer pool get written to disk...
|
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 during the day (or when I execute the stored procedures
in db2 on the AS400 directly) everything completes successfully.
However when the procedures are executed on its scheduled cycle at
3am
in the morning it fails most every morning with a...
|
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)
I've setup my contracts to be a singleton and track sessions successfully.
|
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing, and deploymentwithout human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
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 we have to send another system
| |