By Rob Symonds
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)
By Rob Symonds
Never use the sp_ prefix for a user-stored procedure. One reason is that it can make it hard to tell which sprocs are delivered and which are user-defined. Another is that if you prefix your sprocs with sp_, SQL searches for the prefix in the master db each time the procedure is called, before looking in the local database. Use usp_ instead.
From Microsoft eLearning Course 3595: Establishing Database Conventions and Standards for Microsoft® SQL Server™ 2005.
By Rob Symonds
When you add new disks to a server and you intend to use them for SQL Server log and data files, there are some suggested best practices in preparing the disks. I suggest you read this post, then read the resources below before taking any action. Some of the configuration values in the Partitioning and Formatting steps below are not universally agreed upon despite strong evidence to support their validity. It is important that you understand the nature of your environment and come to your own conclusions regarding configuration before you proceed. [Read more…] about Partitioning and Formatting Disks for SQL Server Performance
By Rob Symonds
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. [Read more…] about The Top 3 Responsibilities of a DBA
By Rob Symonds
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. [Read more…] about How to Disable Transaction Logs in SQL Server
By Rob Symonds
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) + ')'