Db Maintenance Plan

Question

Db Maintenance Plan

Ionel Ciobanu-132742

Grasshopper

Points: 22
More actions
May 12, 2004 at 10:40 pm

#160874

Hi everybody.
My Db Maintenance Plan failes because Mr. X left open an app over night.
How to kill all processes at a time I want (for ex. at a 11PM)?
Thanks,
Ionel

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

TriggerMe SSChampion Points: 11752 More actions · Answer 1

Best way to do it is to create a T-SQL Script that kills all the users that still have a connection to that Database before executing the Maintenance plan.

Kindest Regards,

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

You must be rebuilding indexes or doing something I don't do. None of mine fail when a conneciton is open. Some of the options require an exclusive connection in the database, like the repair option.

dogramone Hall of Fame Points: 3216 More actions · Answer 3

Hi,

Here is the script I use to kill all users attached to a DB.

Declare @DatabaseName varchar(50)

Set @DatabaseName = 'YOURDBNAME'

--Cursor for all the spids running against this database

DECLARE SysProc CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR

SELECT spid

FROM master.dbo.sysprocesses

WHERE dbid =

(SELECT dbid FROM master.dbo.sysdatabases

WHERE name = @DatabaseName)

DECLARE @SysProcId smallint

OPEN SysProc --kill all the processes running against the database

FETCH NEXT FROM SysProc INTO @SysProcId

DECLARE @KillStatement char(30)

WHILE @@FETCH_STATUS = 0

BEGIN

SET @KillStatement = 'KILL ' + CAST(@SysProcId AS char(30))

EXEC (@KillStatement)

FETCH NEXT FROM SysProc INTO @SysProcId

END

WAITFOR DELAY '000:00:01'

if (select count(*) from master.dbo.sysprocesses where dbid =

(select dbid from master.dbo.sysdatabases where name=@DatabaseName)) <> 0

BEGIN

EXEC SetMessages

RAISERROR(60009,16,1)

RETURN

END

SET @DatabaseName = QUOTENAME(@DatabaseName)

EXEC ('DROP DATABASE ' + @DatabaseName )

Hope it helps.

Just place it in a job to run before your re-indexing or repairing job starts

dogramone Hall of Fame Points: 3216 More actions · Answer 4

Ooops, forgot to remove the drop database part at the end of the statement. Please don't forget to do it if you use the script.

Ionel Ciobanu-132742 Grasshopper Points: 22 More actions · Answer 5

Ionel Ciobanu-132742

Grasshopper

Points: 22

May 14, 2004 at 12:38 am

#506419

ok. Thank you very much.

All the best,

Ionel

SueB SSCommitted Points: 1668 More actions · Answer 6

Steve

I have also had Database Maintenance plans that I found had not failed but were still executing because someone had left a connection open. I have it set to reorganize data and index pages and change freespace percent to 10%. Also it tests database integrity including indexes. It is not set to repair any minor problems. I have it set to perform these tests before backing up the database or transaction logs. Could any of these settings be causing this issue?

This has occurred on two different occasions in different databases, and it was due to an open connection. I hate to kill spids when this seems to be the exception rather than the rule. Is there a way to see if this is taking too long?

Sue