Post reply

Db Maintenance Plan

  • 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 

  • May 12, 2004 at 11:39 pm

    #506231

    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,

  • May 13, 2004 at 10:18 am

    #506348

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 14, 2004 at 12:33 am

    #506416

    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

  • May 14, 2004 at 12:34 am

    #506417

    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.

  • May 14, 2004 at 12:38 am

    #506419

    ok. Thank you very much.

    All the best,

    Ionel

  • July 13, 2004 at 2:33 pm

    #514463

    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

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

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