SQL Server Performance Forum – Threads Archive

the "beloved" middle tier ….

Aha, hÃ©las, here we are in the middle tier issues … the middle tier, that service which allows applications to interact directly with a service which, in fact, solves the connection problem with the database and all the issues concerned …. Usually, those applications use a "generic" user (SQL user) to connect to the database and, also, to the pleasure of DBA’s, they stick on the databases endlessly with a permanent connection … The problem arises when you (and me, the DBAs) need to set up databases in "exclusive" mode, for instance, to run a backup or a "consistency" check. Then, u are never able to do so, unless you kill all active connections to your database … Should you perform that "off-line" or in a "batch" process, then u would rather be failing, as the process would not be capable of "killing" those connections … which, IN FACT, are not working at all (as there are no real users in the house!) So, any idea to fix that issue? Should we write a process to kill all existing connections and run it before the "sp_dbOption" procedure? thanks a lot, any clue will be welcome ….

Hi, You can run the majority of DBCC checks (if not all!?) and also perform backups without having to interrupt any users connections, although performance may suffer because of increased disk activity or blocking in the case of some DBCC checks.

hi Chappy, you are right, BUT in the Maintenance Plans (you see I am quite busy with this stuff recently) theres an option to "FIX and REPAIR" indexes and corrupted data (should that problem arise) … in that case, you need to set up sp_dboption to "single user", which fails in those cases …. yes, I could perform some of the tasks within a sp -I am still lazy to do so- (isnt that a way to prevent DBA’s from the potential issues you/we can find along the way??) Hey, when I say "I am lazy" u rather should know what I mean : I AM MOST OF THE DAY ANSWERING USERS, DEVELOPERS, BOSSES, CATS, RATS and MOUSES on the PHONE!!!

To kill the user connections you can use following code: USE master
go
DECLARE @spid int
WHILE EXISTS (select * FROM master..sysprocesses WHERE dbid IN (select dbid from master..sysdatabases where
name =’DataBase_Name’)) begin
SELECT TOP 1 @spid=spid FROM master..sysprocesses WHERE dbid IN (select dbid from master..sysdatabases where
name =’DataBase_Name’)
EXEC ( ‘kill ‘ + @spid)
end I prefer to kill the connections if the database is not real-time 24/7.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>