Read more about the article Capturing Long Running Queries
Microsoft SQL Server

Capturing Long Running Queries

Long running queries in a database may or may not be a problem, depending on the type of system. In a data warehouse solution, queries that runs for several minutes may be perfectly normal and nothing to worry about. But in an OLTP system, long running queries may cause performance problems. Read on to see how to monitor for long running queries in SQL Server.

(more…)

Continue ReadingCapturing Long Running Queries
Read more about the article Capturing deadlock events
Microsoft SQL Server

Capturing deadlock events

Traditionally, trace flags 1204 or 1222 are used to write information about deadlock events to the SQL Server log. But since SQL Server 2012, there is a default Extended Event session running called system_health that can be used instead. Extended events are stored as XML, and in this post I’ll show a method to extract information when a deadlock occurs and store it in a regular table. By having the deadlocks stored in a table, it is possible to view the history of deadlocks in order to enhance deadlock investigations.

(more…)

Continue ReadingCapturing deadlock events
Read more about the article Automatic retries in a SQL Agent job
Microsoft SQL Server

Automatic retries in a SQL Agent job

Many SQL Agent jobs can be run automatically using the scheduler to run the job at specified times. But sometimes it would be better to run the job when specific criteria have been met, instead of at a specified time. For instance, when the data warehouse has finished loading.

This blog post will show how to create a job that starts and then waits / retries until given criteria are met.

(more…)

Continue ReadingAutomatic retries in a SQL Agent job
Read more about the article Mass editing of SQL Server objects
Microsoft SQL Server

Mass editing of SQL Server objects

Ever had the need to replace a piece of code in multiple SQL Server objects (Stored Procedure, Trigger, Function or Views)? Here’s a method to search for a string in all objects, replace it with some other code, and to finally script all changes. In other words, a method for mass editing SQL Server objects.

(more…)

Continue ReadingMass editing of SQL Server objects
Read more about the article SQL Server Default Trace
Microsoft SQL Server

SQL Server Default Trace

The default setting in SQL Server 2005 and later is to keep a trace running all the time that captures basic information about the instance. This is called the Default Trace. The created trace files can be viewed in Performance Monitor or by using T-SQL and they can be very useful in some troubleshooting scenarios.

(more…)

Continue ReadingSQL Server Default Trace