Getting Feedback from Stored Procedures

I'm one of those people who puts as much business logic in the database. Stored procedures, views and functions are my "middle tier". That can sometimes lead to long running procedures with little feedback from the UI. I've often wished that you could return interim status information back from SQL.


Well, it turns out you can. The MSDN blog post, "Getting feedback / progress from batches and stored procedures", details how using RAISERROR.

BTW, the article also notes why there's a missing "E" in RAISERROR.

Putting SQL to Sleep

Have you ever wanted to test how your application would handle a long running stored procedure, but didn't want to create a multi-gigabyte database to do so?  Add a WAITFOR statement. 

WAITFOR is similar to .NET's Thread.Sleep() in which you can stop a thread from processing for a specified period of time.  You can either pass WAITFOR a delay time for the batch to pause for or a specific time for the batch to continue afterwards as follows:

WAITFOR DELAY '00:00:45' --Delays the batch for 45 seconds.

WAITFOR TIME '12:30'  --Restarts the batch at the next line at 12:30.

Note that there is no date specified in the WAITFOR TIME statement.  You can only delay it within a 24 hour period using the TIME clause.

One thing to watch out for are transactions.  If you put a WAITFOR within a transaction, any locked resources will remain so for the duration of the delay, potentially causing timeouts and deadlocks.

WAITFOR is also used for waiting for Service Broker messages, but that's a whole other blog entry.

I'm sure there are other uses for WAITFOR but for now, happy napping!

SQL Connection Kill Switch

Aaaarrrrg……

OK, so a few SSMS functions, such as Detach Database, have a checkbox letting you kill all current connections before performing the requested operation.  But some, say Restore, don’t (could someone give me a good reason why?), and end up failing if someone else (or yourself in another query window) has an open connection.  Your solution is to open the Activity Monitor, sort by database and kill each of the connections and hope that no new ones are opened in the mean time.  And what if you want to do this all in a script anyway?

To solve the above problems you can run the following script:

USE master

DECLARE @SQLStatement VARCHAR(MAX)
SET @SQLStatement = ''

SELECT
        @SQLStatement = @SQLStatement
            + 'KILL '
            + convert(VARCHAR(10), spid)
            + CHAR(13) + CHAR(10)
    FROM master.dbo.sysprocesses
    WHERE DB_NAME(dbid) = 'MyDatabase'
        AND dbid <> 0
        AND spid <> @@SPID

EXEC(@SQLStatement)

GO

So what exactly is this doing?  The SELECT statement is building a collection of KILL n statements where n is a process ID of a process connected to the specified database (in this case “MyDatabase”).  The CHAR(13) + CHAR(10) are really only there for aesthetics and debugging purposes.  Note that the WHERE clause explicitly excludes the connection of the current process.

Once this collection of KILL statements is built it executes them.  By using @SQLStatement = @SQLStatement + {next KILL statement}, it’s continually appending the next KILL statement to itself and the resulting value in the @SQLStatement variable is the entire list of KILL statements.

Giving credit where credit is due, I would like to note that I copied and tweaked the above statement from the Aaquagua blog.  In that blog, it is noted that the following statements will have the same effect:

ALTER DATABASE MyDatabase
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDatabase
    SET MULTI_USER WITH ROLLBACK IMMEDIATE

What we’re doing here is setting the database to single user mode, which immediately kills any current connections, and then setting back to multi-user mode.  Although this will in fact have the same effect, there’s something hackish about it that makes me a tad uncomfortable.

But that’s just my neuroses speaking.  You can pick whichever one works for you.

UPDATE:

OK.  So after writing this, I was thinking about the method of setting the database to single user mode first.  My issue with using it to kill connections is that your using a statement that does one thing, set a database to single user mode, to perform something else, kill all the connections.  Yes it works, but if someone were to come along and review your script, they may wonder what you’re doing.

Anyway, upon further pondering, I decided that setting the database to single user mode before performing an action, such as a restore or major update, is probably a good idea anyway by preventing any other connections from interfering with the action.  The idea would be to wrap your action inside the two set statements.