Making the SQL Server error log easier to view

Making the SQL Server error log easier to view by renaming the error log filename to give it a default extension so it can be associated with your default viewer.

The SQL Server Error log At some point every dba will have had to look at the current SQL Server error log using their favourite text editor. However, as the default error log file is literally just called errorlog (with no file extension), there’s the awkward process of selecting the application with which to open […]

Working out how long a SQL Server backup/restore will take

How to work out how long a SQL Server backup/restore will take using a T-SQL query that will calculate the predicted restore time.

Working out when a database backup or restore operation will take to complete can be a real pain. Fortunately, the sys.dm_exec_requests DMV has some useful columns which can help us predict this. Here’s the query I put together whilst waiting for my backup performance tests to complete: SELECT  r.session_id, CONVERT(NVARCHAR(22),DB_NAME(r.database_id)) AS [database],  r.command AS [backup_process], […]

SQL Server wait stats

SQL Server wait statistics provide the most objective indication of the perfomance of a SQL Server instance and this post explores how to use them to get more details about performance issues.

The single biggest clue to the source of a performance problem will be from something referred to as the wait stats. What are waitstats? In short wait stats are statistics gathered on what resources SQL Server is waiting on internally whilst executing queries. Why are they useful? They can often provide very quick and objective […]

SQL Server and Disk IO

Disk IO against traditional spinning disks is the most critical factor affecting SQL Server performance and this post starts to explores how to mitigate disk performance issues

Many aspects of a database system’s configuration will affect the performance of queries running on that DBMS. However, there is one single component that has the greatest impact on DBMS performance, and that, of course, is disk (IO) access. It’s a broad topic and all aspects of it are pretty well covered in various blogs […]

Misaligned disk partition offsets and SQL Server Performance

I’m starting off this series of posts with a discussion about partition offsets. Diving off at the deep end a bit perhaps, but if your disk setup is not based on firm foundations, you’re setting yourself up for one of the most common and easily-avoidable performance issues right from the off, and a IO performance […]

The SQL Server default trace

The SQL Server default trace reveals a lot about the recent history of the system. Find out how to troubleshoot common database issues using it.

The SQL Server default trace is useful! The SQL Server default trace (introduced from SQL Server 2005 onwards) is a background trace that runs continuously and records event information that, despite the adverse comments on the web about its value, can be useful in troubleshooting problems. Not only that, but it’s there out-of-the box so […]

Upgrading SQL Server editions

How to change upgrade SQL Server edition via the command line or GUI.

How to upgrade your SQL Server edition (without reinstalling) With the updated SQL Server 2008 installation program, upgrading SQL Server editions has finally been made as straightforward process as it always should have been. In SQL Server 2005 upgrading/changing the edition involved running the setup program from the command line with a multitude of specific […]

Troubleshooting SQL Server listener connectivity

Verifying and troubleshooting SQL Server listener connectivity is a key component in a DBAs armoury but often overlooked until now this post shows how to confirm connectivity to SQL Server using any of its connectivity options such as TCP/IP, Named Pipes or Shared Memory.

SQL Server listeners Verifying and troubleshooting SQL Server listener connectivity is a key component in a DBAs armoury but often overlooked until now (hopefully!). SQL Server can listen for connection requests via multiple listeners, such as TCP/IP, shared memory or named pipes. Sometimes it’s necessary to check connectivity by a specific listener. The first place […]

Orphaned MSDTC transactions (-2 spids)

Understanding and troubleshooting orphaned MSDTC transactions

SPIDS with a value of -2 Any dba looking after a server that takes part in a distributed transaction will probably have come across situations where a SPID that does not show up in master..sysprocesses or sys.dm_exec_sessions (depending on your version of SQL Server) ends up blocking other users. Looking at sp_lock or the syslockinfo […]