Sleeping session ID: Where art thou?

This has been an age old confusion ever since the Dynamic Management Views changed the way SQL Performance monitoring was done.

A SPID/Session ID will show up as Sleeping/Awaiting Command when it has no active work request that it needs to execute on the Database Engine. Consider the following scenario:

1. You connect to a SQL instance using Management Studio

2. You execute: SELECT @@SPID

3. You then open another SSMS query window and try and lookup the Session ID returned by the output of Step #2 using the DMV: sys.dm_exec_requests.

If you didn’t know of this behavior, then you will be in for a surprise! This is because the Session ID associated with the query in Step #2 has no active work to perform which is why the DMV didn’t report a row for that particular session. However, when you look up this session id using sys.dm_exec_connections or sys.sysprocesses. The reason sys.sysprocesses reports this SPID is because it doesn’t differentiate between a session with/without an active work request. It will report all SPIDs currently connected to the database engine.

The above behavior is expected and by-design.

Bob Dorr has mentioned about this in his post on the CSS SQL Escalation blog and also talks about how this can affect concurrency if such a session has open transactions:

How It Works: What is a Sleeping / Awaiting Command Session

How fetch sysaltfiles information using Powershell

I recently have developed an affinity for using Powershell. I saw a question on #sqlhelp hashtag for fetching database properties using Powershell. There are multiple posts out there on the web to do this using SMO. A crude way would be to use the Invoke-Sqlcmd cmdlet to do this.

Command:

Invoke-Sqlcmd -Query "SELECT filename,size,dbid FROM sys.sysaltfiles;"

If you wanted a cleaner output or some post processing done on the results fetched and wanted to use foreach, them this could also be done:

Example:

$dbprop = Invoke-Sqlcmd -Query "SELECT filename,size,dbid FROM sys.sysaltfiles;"
foreach ($db in $dbprop)
{
Write-Host $db.filename
}

If you are using SQLPS, then the above command to give the information that you want by invoking SQLCMD using Powershell.

Other ways to do this are mentioned here:

Get SQL database size using Windows Powershell

Get database properties using PowerShell in SQL Server 2008 by Tim Chapman (Blog)

How to move data files of the mirror database

The ideal scenario for database mirroring would be to have the principal and secondary server instances be exact clones of each other from a hardware and disk space standpoint. This would ensure that the performance on the mirror instance is the same as the principal instance. But due to multiple constraints, this is not always possible. I recently had a question on how to move a secondary data file to a different physical location on the mirror instance. The catch here is that the physical data file locations on the principal and mirror instances are not the same. Now this can be tricky if not attempted or tested in your environment. The Microsoft SQLCAT(Twitter|Blog) team has a blog post on how to move the transaction log of a mirrored database to a secondary location. We shall use the same logic for the data file as well.

Steps

  1. If the server on which the data file relocation has to be done is not the mirror server currently, then failover the mirrored database so that the server on which the OS File has to be physically moved is the current Mirror.
  2. Change to High Performance mode if running under High Safety.
  3. Execute the ALTER DATABASE statement to move the data file to the new location on the mirror instance.
  4. Pause Mirroring to prevent any automatic failover while OS File Copy is happening.
  5. Stop the Mirror instance.
  6. Copy the file across to the new location (OS File Copy).
  7. Start the Mirror Instance.
  8. Resume mirroring and switch back to High Safety if the mirroring was running under High Safety. The mirroring should continue without any issues. Failback to original server if needed.

If the data file locations are the same on the principal and mirror and the data files need to be moved to different locations on both the server instances, then this is much easier. You would need to fire the ALTER DATABASE command on the principal server instance and then move the data files to the new location after failing over the database. The new data file location would take effect only after the server instance is restarted.

How to use Reporter to analyze SQL Profiler traces

I had a question on the SQL Nexus discussion forum regarding the following error while using Reporter:

When I run Reporter, with the default settings (i.e. DB=PerfAnalysis), I get an error :
Server: (local) Error: 4060 Severity: 11 State: 1 Line: 65536 Source: .Net SqlClient Data Provider
Cannot open database "PerfAnalysis" requested by the login. The login failed.

When you launch Reporter which is part of the RML Utilities installation, the PerfAnalysis database is the default database that the Report will attempt to connect to. See illustration below:

image

You will need to change the baseline database to the database into which you imported the profiler traces using SQL Nexus. In my case, I had imported the SQL Profiler Traces captured into a database called nexus_blocking.

I shall cover a walkthrough on importing data into SQL Nexus sometime later.

Script to remove a constraint and drop columns

Recently there was a need to get rid of a replication column after replication was disabled on a SQL Server 2000 database. The customer wanted to get rid of a column for all the tables which replication had added. And the flip side was that this column had a constraint defined on it. I looked at the constraint names and found that they had a common sub-string “msrepl_tran”. So by joining the system catalogs syscontraints and syscolumns, we were able to create the necessary T-SQL scripts to drop the constraint and the column from the tables involved.

select 'ALTER TABLE [' + OBJECT_NAME(a.id) + '] drop constraint [' + OBJECT_NAME(a.constid) + ']' + CHAR(13) +

'alter table [' + OBJECT_NAME(a.id) + '] drop column ['+ b.name + ']' + CHAR(13) + 'go'

from sysconstraints a

inner join syscolumns b on a.id = b.id

where OBJECT_NAME(a.id) like '%msrepl_tran%'

NOTE: Use CTRL+T and F5 to generate the script above. Inspect the output to ensure that no unintended table gets affected.

How to find out BINN folder path using WMI

A reply to a Tweet on #sqlhelp prompted me to look this up. The question was on finding out the SQL Server BINN folder path. This can be done using WMI in the following manner for SQL Server 2008:


strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")

Set colItems = objWMIService.ExecQuery( _

"SELECT * FROM SqlService WHERE SQLServiceType = 1 and ServiceName = 'MSSQLSERVER'",,48)

For Each objItem in colItems

Wscript.Echo "-----------------------------------"

Wscript.Echo "SqlService instance"

Wscript.Echo "-----------------------------------"

Wscript.Echo "BinaryPath: " & MID(objItem.BinaryPath,1,InStr(objItem.BinaryPath,"sqlservr.exe")-1)

Next

The SQL Server 2008/R2 WMI namespace has visibility for SQL Server 2005 also. So the above snippet of code can be used to retrieve the BINN path (PathName property contains the fully qualified path to the service binary file that implements the service) for the SQL instance. You can change the SQLServiceType to a different value to get the Binary Path folder for other services as well. The value is the above code is set to 1 for the Database Engine. The ServiceName parameter can be used to filter down the results.

For a named instance, you would need to change the service name to MSSQL$INSTA if your instance is called INSTA.

The same is possible through the much talked about Powershell as well.

Other ways to do this would be to use the xp_instance_regread XSP which is not recommended as it is an undocumented command. An example is show here.

Thanks to @afernandez for pointing out another way through a CLR TVF.

Powershell script to calculate folder size

Just published a PS Script to calculate the TOP 5 folders for a UNC share. The need for this script arose when I needed to find out which folders were consuming the largest amount of disk space on a File Server. I know there are graphical utilities like DiskMon, Space Monger etc. but this script provides me the flexibility of automation like identifying the folder owners or deleting based on some criteria etc.

Click here to download the script.