Category Archives: Scripts

How to fetch all Login Failed messages from default traces

Posted on by

I was recently investigating a login failed issue on a SQL instance and required to quickly find out all the Login Failed events that had occurred on the server. I could have scoured the SQL Errorlogs but I didn’t want to do this due to the bloated size of the Errorlogs. An easier way would be to read the Default Traces (available from SQL Server 2005 and above) and look for all the Audit Login Failed events (event class = 20) using fn_trace_gettable function.

The query that can be used to get this information is given below:


declare @path varchar(1000)

select @path = path from sys.traces where id = 1

select trc.Spid, TextData, State, Error, StartTime, EventSubClass, LoginName, NTDomainName, NTUserName, ClientProcessID, HostName, ApplicationName

from fn_trace_gettable(@path,DEFAULT) trc

where EventClass = 20

order by StartTime desc

How to find out top version store generating objects

Posted on by

I recently found the need to write a query to find out which tables are contributing to version store usage on the server, then you can use the following piece of T-SQL:

EXEC sp_MSforeachdb 'IF ((select DB_ID(''?'')) in (select distinct database_id from sys.dm_tran_top_version_generators)) begin print ''Database Name :'' + ''?'' + char(13) select a.*,object_name(b.object_id) as TableName from sys.dm_tran_top_version_generators a inner join ?.sys.partitions b on a.rowset_id = b.partition_id end'

The reason this got so convoluted is because the Object_Name function doesn’t accept a partition ID or a allocation hobt ID. So, I have to get the object ID from sys.partitions for the associated database and then use the object_name function.

If anyone has an alternative quicker way, then please feel to leave a comment.

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

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)

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.