This post goes through the SQL Server DBCC command which deals with displaying the actual underlying statistics to a particular SQL Server index.

Read more →

I was just adding a small Ad-Hoc maintenance plan on our shiny new SQL Server 2012 test boxes, when I received the following error message:

Adding tasks to the maintenance plan failed: Object reference not set to an instance of an object.

This is a short post that goes through some reasons as to why this error message might appear.

Read more →

Recently one of our Data Warehouse SQL server instances started throwing a bunch of alerts (severity level: 20), the alert error message looked like this:

The client was unable to reuse a session with SPID [SPID], which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing

In this post I will go through the initial steps to diagnosing this issue, as well as introduce the new (SQL Server 2008+) Connectivity Ring Buffer feature, which lives in the sys.dm_os_ring_buffers DMV, and captures any server-side initiated SQL Server connection closures.

Read more →

A simple T-SQL command that checks the progress of a particular SQL request, such as the progress of DBCC SHRINKDATABASE.

Read more →

In this post I will go through the SQL Server Wait Type CXPACKET, what is this wait type a symptom of, and how you could go about diagnosing and optimizing the CXPACKET wait type.

Read more →

I was having issues today with my SQL Server Activity Monitor, it kept timing out every time I tried to see active transactions, and so I devised this wee script which returns the currently active transactions in the instance, as well as the text of the procedure running.

Read more →

Sometimes you might need to re-organize your SQL server files around the server in order to improve I/O efficiency or simply because the current structure cannot coupe with the transaction log file or data file growth rate, and you need to expand onto other drives.

This post goes through how one might go about changing the SQL Server 2008 transaction log or data file location, commit this file move, and also diagnose and resolve any errors that could arise from performing such a move.

Read more →

You have probably reached this page after receiving the following error message: Server ‘SERVERNAME’ is not configured for RPC. [SQLSTATE 42000] (Error 7411). This article will explain why you are receiving this error, and what can you do resolve it.

Read more →

Managing space on your SQL Server Transaction Log can sometimes be daunting, usually if you have a good backup strategy, and a well moderated database (in terms of monitoring and flagging long running transactions), then you will rarely run into unexpected large growth of your transaction log file. But in the real world this is rarely the case, and there will be occasions when you leave a very long transaction running over night (or days), or there is a schedule clash between two queries, or between a query and the backup schedule, which caused a failure in your log truncation strategy.

The daunting part is making sure that you recover from the situation with minimum data loss and disruption to the database’s recovery strategy. In this post I will go through the most common scenarios of transaction log growth, and how to resolve them.
Read more →

As you probably already know, SQL Server has many DMVs (Dynamic Management Views) and System Objects that helps DBAs, these include performance counters, index statistics, resource locking information, user object IDs and many more.

Read more →