SQL DBA Blog

Category: SQL Server

SQL Server Blog Archives, by Peter Whyte (Database Administrator).
Includes: Database Admin, Management Studio (SSMS), Windows Admin, PowerShell, WSL, MSSQL Backups, Restores & more…

sp_who, sp_who2 & sp_whoisactive

Sp_whatnow? sp_who, sp_who2 and sp_whoisactive are stored procedures that allow you to view current users, sessions, and processes within a SQL Server instance. You’d want to see this for identifying things like blocking or checking general activity.

sp_whoisactive is definitely one of my favourite stored procedures. Created by Adam Machanic, this tool gives you an instant view of current SQL user process. Similar to sp_who/sp_who2 but doesn’t show system processes and gives you more useful information.

Below is a quick run-through of each of these stored procedures.

sp_who

This’ll get you information on all background and user processes going on in SQL at the time of execution. Info includes login names, hostnames, what kind of SQL command is running and which database it’s running against.

The first 50 results are system SPIDS which you’d never kill. Unless, maybe you identify the lazy writer is broken or something? I never have a need for sp_who personally as sp_who2 & sp_whoisactive do the trick.

See my other post for more on killing SPIDs.

sp_who2

An undocumented stored procedure by Microsoft, which means that it can be deprecated or modified without any notice. It shows the same as above but with some additional columns. I use this to kill off user connections on rare occasions but that’s about it really.

sp_whoisactive

You’ll see replication SPIDs such as your log reader agent, monitoring collections as well as any end users running reports / manual SQL queries. Info includes full SQL queries, query plans, hardware usage, tempDB allocations, blocks and more. This 4 minute video by Brent Ozar gives the perfect run-through:

To test this in less than 5 minutes, download sp_whoisactive, copy/paste the contents into a new query window and run the code against any database. It’s likely you will have no active user sessions in your test environment, so before we run it we’ll setup a blocking process. Run the following in sequential order, highlighting until each line break.

Now run a select statement in a new query window on the same table. The insert has a lock on the table until committed, so it’ll continue to execute as a result of being blocked.

Running sp_whoisactive now will look something like this.

As you can see I’ve highlighted the blocking sessions. For more information on encountering such events, see my blog post – Killing SQL Server Processes.

On rare occasions I’ve see my own exec sp_whoisactive SPID come back within the results. Likely it’ll depend on performance. You’ll know if your server is burning alive if running sp_whoisactive takes a long time to return results.

Most of the time I’m using sp_whoisactive with no parameters as shown above or with @get_plans = 1, but there are many that can be utilised depending on the task.

Another common use of this stored proc is to have it running as an agent job every 30-60 seconds, storing the results into a table. See my other blog post – Logging sp_whoisactive to a table for a run-through on that.

May 21, 2018
Get Database Growth Events in SQL Server

This post contains a script to get database and log file growth events on a SQL Server instance. I often find a need to use this script to check what databases have been growing on unmonitored SQL Servers. You know, those SQL Servers that you only need to touch when something breaks. It’s not your fault there’s no room for proactivity, don’t worry.

When there are a lot of growth events in the query result I tend to copy it out into Excel and filter it. You might want to save it to a table and sum the total growth per database.

If you want to know more about database growth events, see my other post here, where I try to explain what it all is with some other useful links.

Script to Show Growth Events

Run the following to get database growth events –

May 20, 2018
Geographical Spatial Environmental Mental

The title is just words thrown together. I’m glad you noticed. This post is the start of a “Study Blog” category, which is more or less a scribble pad for when I’m watching a video or reading stuff.

My thoughts recently have been with the 70-461 Querying Microsoft SQL Server 2012/2014 exam. I was converted into the DBA-land from an IT Operations role and so far I’ve always recognised querying as a weak area.

It’s sometimes tough keeping on-track with certification routed studies though. The main focus has always been to learn the important areas required within my role at work. I’ve been lucky enough to have moved around quickly, but that adds to the difficulty level. It’s been great experiencing massive changes to the technologies I work with on a daily basis as well as big changes to how different scales of companies operate.

Another chunk of what I’d call study time is, keeping up with email subscriptions from other blogs! It’s so easy spending an hour reading through link to link. There’s so many interesting features that I want to play with but it always comes down to how much time you have left after weekly responsibilities have been met!

To add to this, when I’m watching videos, I have to question things like, what would it be like having to work with spatial aggregates & geographical datatypes as a job? How complex does this get? For this example, I found an amazing Stack Exchange post that blew my mind. I love digressing into particular things I don’t know during videos & reading. I remember watching Microsoft SQL Server 2016: it just runs faster for the first time a few years ago, it must have taken 4 times as long to watch the video due to how much I needed to clarify before I could take it all in.

Anyway. The above is explained to give an idea of how I work. The information below contains notes taken during directed 70-461 and whatever else I’m reading. I’ll create new study blog posts every so often.

DIFFERENCES BETWEEN IMPLICIT AND EXPLICIT CONVERIONS

#– Microsoft’s documentation on data type conversions.

#– The above includes this image that would be perfectly suitable for any bedroom wall I’m sure.

DIFFERENCES BETWEEN CAST AND CONVERT

#– Microsoft’s documentation on CAST and CONVERT.

#– CAST is available so SQL Server adheres to ANSI / ISO standardisation meaning it’ll never be a problem running that code against a non SQL Server database.

#– The CONVERT function allows an optional styling parameter that CAST does not.

THE FORMAT FUNCTION

#– The performance difference is insane.

INSERTS, NULLS, ALERT, IDENTITY & PRIMARY KEYS

Opening SSMS and seeing where your mind takes you is always a good plan…
The default value will be NULL if values are unspecified.

But if we ALTER the column to not allow NULL values, what happens?

Primary Key columns cannot be NULL, but it’s fun to see errors. I also created a separate post on changing the order of columns which relates to adding new IDENTITY columns.

We don’t need to include identity columns upon insert.

As stated within the Microsoft link above, gaps may happen when using identity. You can sort this by creating another table with the same schema, import all data excluding the identity column, drop the first table and rename the new table. Note, this is a complex task if the ID column is used as a foreign key on other tables, so you may want to leave as is.

May 19, 2018
Show Line Numbers in SQL Server Management Studio (SSMS)

This is a tip on how to show line numbers in the SQL Server Management Studio (SSMS) query window.

This is useful when you receive an error message with reference to a line in your code, although it’s not a necessity to have it enabled as you’ll see in the demo below. Also, some folks might prefer to have this enabled as a standard when writing code.

In this first part of this info post, I’m creating a test table and throwing an intentional error message (dateofbirth column on the 4th insert is not in the correct format) –

If we count down the lines of code we’ll get to a problem on the 4^th insert, line number 8.

To enable line numbers, click Tools at the top menu bar and select Options.

Navigate to Text Editor > Transact-SQL > General and tick Line numbers.

Now if we look at our query window, we’ll see numbers displayed.

If we double-click the error line, as shown above, it’ll jump to the referenced row whether you have lines shown or not. You can also use the Go To Line feature by hitting CTL+G.

In this next image, I’ve entered a breaking line in one of the default database system stored procedures to which SQL results in an error when I try to run/save.

The fail-line referenced is not always entirely accurate, as you can see above. I’d say most of the time the problem area is easy to spot, however, will be times when it’s not for sure.

Enabling line numbers will visually make it easier to see problem lines of code and it can also be handy for presenting. Other than that, it’s a general preference really.

May 16, 2018
Show Available Disk Space in SQL Server
This blog post is to share a script that you can run in SQL Server (versions 2008 R2 and above) which shows the available disk space for all local volumes on a SQL Server host. I’m also including a quick note on how to get this information with remote PowerShell.

Running out of disk space is one of the most common issues that happens on unmonitored database servers. If you do not monitor your disk space usage of a SQL Server, then things will randomly come to a halt one day. Your drives are full and your SQL Server will not accept any more updates until you get more space.

Everything performance related in SQL Server depends on your unique workloads, queries, application, platform & environment. Database files need to grow and database log files need to hold as much as it needs (depending on Recovery Models). Whether it’s a very slow ever-increasing table that bites you, or it might be a user query that bloats log files, when an unmonitored SQL Server is having issues checking available disk space on the host is one of the first steps you should take when you connect & bein diagnosing.

As mentioned at the top of this post, this blog post is a how-to on getting your available disk space a SQL Server host, using a TSQL script and PowerShell:
# Get Disk Space Info in SQL Server
# Get Disk Space Info PowerShell

Get Disk Space Info in SQL Server

The TSQL script below queries sys.master_files and sys.dm_os_volume_stats SQL Server internal system metadata views.

and will return the disk space and available space within each volume attached to the SQL host.
```
-- get available disk space sql server
SELECT DISTINCT
    vs.volume_mount_point,
    vs.file_system_type,
    vs.logical_volume_name,
    CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0) AS [Total Size (GB)],
    CONVERT(DECIMAL(18,2), vs.available_bytes/1073741824.0) AS [Available Size (GB)],
    CONVERT(DECIMAL(18,2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY vs.volume_mount_point OPTION (RECOMPILE);
```
SQL disk space query output

Now get this info sent to your email via Database Mail if the availability of the SQL Server means anything to you. If it’s business-critical that no new data stops being inserted into an online database, then you’d already have a decent 3rd party monitoring tool in place.

Get Disk Space PowerShell

Get-Volume is the command to show local volume information for your server. If running on a remote server, use Enter-PSSession to connect to the target server first (requires Local Admin permissions).
May 13, 2018
Killing SQL Server Processes (SPIDs)

There are many reasons why you’d KILL a SPID in SQL Server. Whether it’s impending disk space doom due to data or log file bloat, or a long running query that’s lost in space… When alerted to this situation I’d run sp_whoisactive in the first instance. When the picture is clear, the kill happens.

To test this, first create a blocking SPID by running the following:

The above should result in something looking like this.

As highlighted, you can see that my select statement is being blocked by the uncommitted insert. For a bit of further reading, have a look at Isolation Levels by Kendra Little.

Now onto the killing!

When you have identified the correct SPID to kill, the SQL is simple:

Be aware as mentioned in the code, rollbacks may continue to block things up and can last longer than you’d ever expect. Listen to this story of a 2 month rollback, crazy to even imagine.

“WITH STATUSONLY” won’t always return results. I’ve also had an occasion where the lead block SPID didn’t show up when running sp_whoisactive. In this case I had to look for the process to kill via Activity Monitor then all was good again.

Killing SPIDs can be also useful if you’re having trouble dropping or detaching a database. This can happen if there’s still activity going on such as remote users or an application using connection pooling. The following script will kill all SPIDs on a specified database:

April 17, 2018
Renaming a computer that hosts SQL Server
When changing the hostname of a computer running SQL Server, system metadata must be updated so that remote connections and applications do not lose connectivity. Those that use @@SERVERNAME or query sys.servers for the hostname that is.

Microsoft’s documentation pages are a must read as always. Some considerations include; failover clustering, mirroring, replication, SSRS, local authentication and remote logins currently connected.

Updating SQL Server after a Hostname Change

(SQL Server has been installed – you now need to change the hostname of the server.)

1. Rename Computer & reboot.

2. Open SSMS & connect to your SQL Instance.
If the default Server name remains, you’ll receive a connection error:
Change the Server name to localhost to get connected.

3. Get SQL’s impression on hostnames.
xp_getnetname is an undocumented extended stored procedure that returns your current hostname. We want both to return the same result.

4. Drop currently registered name (sp_dropserver).

5. Register new hostname (sp_addserver).

6. For the change to be applied, restart the SQL Server service.

7. Get the SQL Server hostname info again.

That’s it done!

When you next open SSMS, ‘localhost’ should be shown in the Server name field. Changing this to your new hostname will now work.

SQL Code
```
-- Get currently registered hostname: 
SELECT @@SERVERNAME AS 'Old Server Name' 
-- Get 
EXEC xp_getnetname 

-- Drop current hostname registration. 
USE [master] GO sp_dropserver 'enter old hostname returned by @@SERVERNAME above' 
GO 

--Register new hostname. 
USE [master] GO sp_addserver 'enter hostname returned from xp_getnetname above',local -- having local updates the metadata for @@SERVERNAME 
GO 

-- Restart SQL to apply changes
```
April 16, 2018
Installing SQL Server 2017

Installing SQL is a straight-forward task… but it’s important that things are in-place from the get-go to prevent having to make changes on a live system. There are a few best practises that can be defined within the installation wizard and many to be applied post install.

This is a basic guide on installing SQL Server 2017. Before you get started, have a look at the Microsoft documentation, Planning a SQL Server Installation and Install SQL Server 2016.

The software used; SQL Server 2017 Developer and SSMS 17, can be downloaded for free for your test environment.

How to Install SQL Server 2017

1. Open Setup.exe within the SQL Server installation files or run your ISO.

2. Click on Installation at the left hand side, and then select the top option.

3. The Edition should be pre-selected as determined by your installation media – click to continue.

4. Accept but don’t read the license terms.

5. Ignore the Firewall warning and continue.
You may have already needed to reboot before getting this far.

6. Select required Features and continue.
When selecting which features to install, a general rule to go by is, if you don’t know what it is then don’t install it. Maybe you’re thinking you’ll use a feature such as SSRS down the line – it’s dead easy to add stuff on so there’s no need to until decisions have been made. Plus, you might want to have as little load on the SQL box due to it having a license per core model.

7. Select Instance Type and continue.
If working with multiple instances per server, go for named instances. If one server per instance, the default MSSQLSERVER should be suitable.

8. Specify Service Accounts and continue.
If your SQL Server will be authenticating to other network locations (e.g. backups to a file server), then it’s best to have a domain service accounts created and insert them in here.

9. Choose your method of Authentication, entering the password for the ‘sa’ account if using mixed mode.

10. Choose Root Directories within the Data Directories tab.
The root directory should fit well on the C:\ drive, as long as your user database directories are set to use separate drives. To ensure optimal performance & for added reliability, store your data, log and tempdb files on separate drives.

11. Configure TempDB within the TempDB tab.
In SQL Server 2016 on-wards you can specify the number of TempDB files to use within the installation wizard. This number should be automatically populated which is driven by the number of CPU cores you have on the machine. There’s a fair amount of reading to be done over how to manage TempDB, but I’m going to leave it at that for now.

The FILESTREAM tab is for storing files in SQL – not something required in a standard SQL install.

12. Ready to install.
The SQL Server installation wizard has created a ConfigurationFile.ini, which is used for unattended installs. Feel free to have a look or save it for documentation purposes.

13. We’re done!

Now what?

Your SQL Server services should be running and it’s waiting on you create your databases. As from SQL Server 2016, SSMS was no longer included within the installation media, so this’ll need to be downloaded and installed too.

April 15, 2018
Get Estimated Database Restore Time
If you’re backing up or restoring a large database in SQL Server, you can run the SQL script in this post to find out how long it has left to complete. The SQL script will get an estimated database restore date/time & percentage completed so far.

The performance of a backup or restore in MSSQL will depend on many things. For example, your server hardware, backup options, or user/client queries running in the background.

The estimated time returned by the query below is based on the performance at the time of execution.
```
-- get estimated db restore time sql
-- works in sql server 2022
SELECT 
    command,
    s.text,
    start_time,
    percent_complete,
    CAST(((DATEDIFF(s,start_time,GETDATE()))/3600) AS VARCHAR) + ' hour(s), '
        + CAST((DATEDIFF(s,start_time,GETDATE())%3600)/60 AS VARCHAR) + 'min, '
        + CAST((DATEDIFF(s,start_time,GETDATE())%60) AS VARCHAR) + ' sec' AS running_time,
    CAST((estimated_completion_time/3600000) AS VARCHAR) + ' hour(s), '
        + CAST((estimated_completion_time %3600000)/60000 AS VARCHAR) + 'min, '
        + CAST((estimated_completion_time %60000)/1000 AS VARCHAR) + ' sec' AS est_time_to_go,
    DATEADD(second,estimated_completion_time/1000, GETDATE()) AS est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG', 'DbccSpaceReclaim','DbccFilesCompact')
```
As shown in the screenshot above, the SQL script returns process info for all BACKUP, RESTORE & DBCC commands running right now. It includes the start date-time, SQL command, percent_complete, and estimated completion time.

I’ve seen a multi-terabyte database take 1.5 days to complete a full backup. This information can often be vital for reviewing database backup & restore strategies, or you may want to try to improve backup restore performance.

We can also use sp_whoisactive to get similar SQL process information (excluding est_completion_time) by viewing the percent_complete column in the sys.dm_exec_requests; SQL Server system DMV.
April 9, 2018