Archive for category: SQL Server DBA

SQL Server Query Index Statistics

19 Mar
March 19, 2012

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 →

SQL Server Maintenance Plan Object Reference Error

15 Mar
March 15, 2012

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 →

SQL Server Connection Reset for Connection Pooling

26 Feb
February 26, 2012

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 →

SQL Server Execution Completion Estimation

16 Jan
January 16, 2012

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

Read more →

SQL Server CXPACKET Wait Type

07 Jan
January 7, 2012

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 →

SQL Server Active Transaction Text

06 Dec
December 6, 2011

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 →

Change SQL Server Transaction Log or Data File Drive or Path

05 Nov
November 5, 2011

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 →

SQL Server RPC Out on Linked Server Properties

22 Oct
October 22, 2011

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 →

Troubleshooting a Large SQL Server Transaction Log File

07 Aug
August 7, 2011

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 →

List of all SQL Server DMVs & System Objects

26 Jun
June 26, 2011

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 →