A simple way to get the default SQL Server data and log path (finally!)

Prior to SQL Server 2012 extracting the default and data path was not straightforward, but now, there is a simple way to get this information via a query.

For things like automated SQL Server build scripts (and probably many other reasons) finding the root data and log file paths is essential. Prior to SQL Server 2012 there were all sorts of options ranging from e.g. running sp_helpfile against the master database (which is not foolproof) or constructing a string to establish the right […]

Error code lookup tool from Microsoft

Err.exe is a great tool for getting the error message and app generating an error when you have nothing more than a numeric or hexadecimal error code from a Microsoft product.

When I worked in CSS for MS there were a lot of cool internal tools we could use to help us in our day to day work, and this one tool is great for Microsoft error codes. Over the years some of these have been made public – the most famous of which is PSSDIAG […]

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 […]