Make sure when you are doing maintenance work on SQL Server (or any application) that you have the installation files handy. Although you might never need them, you’ll be happy the one time maintenance goes bad and you need to rebuild something.

I’m sure there are other ways to do this but this is pretty easy:

DECLARE
 @SPID INT,
 @Handle binary(20),
 @retVal varchar(8000)
SET
 @SPID = 148
SELECT
 @Handle=sql_handle
FROM
 master..sysprocesses
WHERE spid = @SPID
SELECT
 SqlCommand=convert(varchar(8000), [text])
FROM
 ::fn_get_sql(@Handle)

References

• http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59703
• http://www.databasejournal.com/features/mssql/article.php/10894_2189761_1/Find-Out-What-Theyre-Doing-with-fngetsql.htm

Things have been a little hectic at work lately and I’ve had a lot of non-DBA type work mixed in with my regular work. In times like these, it can be a challenge to set priorities. Continue Reading…

I see this topic come up over and over again on the web. Somebody’s transaction logs run wild and they start searching for how to disable the transaction log so they can maximize disk space. I suspect that the people searching for this are probably developers or sys admins that got stuck babysitting a SQL Server and might not have had a chance to get familiar with SQL.  Continue Reading…

This query works on SQL Server 2000 and above:

USE [master]
SELECT
CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '
+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('
+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'