Monthly Archives: September 2010

Sleeping session ID: Where art thou?

Posted on by

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

Advertisement

How fetch sysaltfiles information using Powershell

Posted on by

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

Posted on by

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

Posted on by

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

Posted on by

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.