Microsoft SQL Server
Send email when log or data file grows
One way to keep track of your data- and log file sizes in SQL Server, is to send an email notification whenever a file growth occurs.
Microsoft SQL Server
sys.dm_exec_cached_plans
The dynamic management view sys.dm_exec_cached_plans returns information on query plans currently in memory. This information can be useful for a lot of reasons: checking total memory allowed for cached plans, which plans aren’t getting reused, searching for certain query plan operators (for instance, scans), which query plans are high memory consumers etc.
Microsoft SQL Server
Capturing Blocked Queries
In a previous post, I presented a solution for monitoring and logging long running queries in SQL Server. In this post I’ll show an extended version of that solution that also sends an email when a request has been blocked for more than 20 seconds.
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.
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.
Microsoft SQL Server
SQL Agent Jobs Summary
A script that summarizes information about SQL Agent job execution history. Much in the same way as the Job Activity Monitor, but with some additional information. Also, this script allows you to run it from T-SQL solutions.
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.
SQL Server Alert not firing
When you set up SQL Server alerts on your SQL Server instance, make sure to run some tests to verify that they are actually firing when errors occur.
Microsoft SQL Server
Performance Monitor Data into SQL Server tables
On a machine running SQL Server there are a lot of performance counters that can be used to create baselines or to investigate performance problems. Those specific to SQL Server can be sampled regularly from the system table sys.dm_os_performance_counters. But those belonging to the OS must be gathered in some other way. Since I’m a SQL Server DBA, of course I want the counters saved to a table in SQL Server 🙂
Microsoft SQL Server