This post contains a SQL script to get estimated completion times and process information for your currently running database backups & restores in SQL Server.
Backing up a database can often take a very long time. It depends on various factors why a backup can take longer to complete. One more obvious reason is, that the bigger your database gets, the longer it will take to backup & restore.
Other backup performance considerations include whether your server is busy with transactions/queries, or you may be running on old hardware, and I’ve seen having fragmented heaps cause extended backup runtimes too.
Get Estimated Database Backup or Restore Time MSSQL
When you’re waiting on a database backup or restore to complete in SQL Server, we can run the script below to get an estimated time of how long the backup has left to go –
We have one 5GB database here currently being backed up at the time of executing the DMV script. The backup progress is at 27% and is due to complete 1 minute after the backup was initiated.
This script will also be useful for getting the full command of the currently running SQL process on your SQL Server Instance, by removing the filter (WHERE Clause) on the query.
This is a post on how to get the last backup dates and times for all databases on a SQL Server Instance.
Database backups are as important as the availability of your server. If your database gets corrupted, or there’s an accidental data deletion and you need to recover data, then we must rely on backups to get the data back. If you have no backups, you have no way to recover your lost data.
The following script returns the most recent Full, Differential & TLog Backup times for all databases on a SQL Server Instance.
We can see from the above screenshot all databases on this SQL Server Instance have been backed up in the last 30 days. Only the demoBlog has had recent Differential and TLog Backups.
The type column within the backupset table gives us a way of checking whether the backup was a Full, Differential or Transaction Log.
This script is more useful if you are reviewing the history of database backups, ensuring they are being backed up on a regular schedule/interval.
The following script will show all backups in the log, in the past 2 months.
You can simply remove the WHERE Clause to bring back the full history if necessary.
The above screenshot includes examples of Full, Differential & Transaction Log backups. This data can be very valuable for verifying backup chains are not broken, as well as backup sizes (including backup compression savings).
While looking at an old script today, one that kills SPIDs on a SQL Server database… I had an instant urge to try kill a system SPIDs, just to see what would happen. And the result was…
That’s a good thing though right? One less thing to be worrying about. You can’t kill system SPIDs.
There are many other facts and warnings in the Kill command’s documentation page, and here’s a few other tests I’ve done;
You’ll see your SPID at the top of the query window next to the pin icon, or you can use @@SPID.
You can check the progress of a killed SPIDs rollback
This won’t work for all the SPIDs you kill. For example, it doesn’t work for a 20 minute running delete statement has been killed and you’re sitting waiting for it to come back to life. It’s probably whatever sys.dm_exec_requests can pick up on, but don’t take my word for anything.
ALTER INDEX REORGANIZE AUTO_SHRINK option with ALTER DATABASE BACKUP DATABASE DBCC CHECKDB DBCC CHECKFILEGROUP DBCC CHECKTABLE DBCC INDEXDEFRAG DBCC SHRINKDATABASE DBCC SHRINKFILE RECOVERY RESTORE DATABASE ROLLBACK TDE ENCRYPTION
Hesitate before the kill?
On the Microsoft documentation linked above it states that you should not kill processes with the following wait types:
AWAITING COMMAND CHECKPOINT SLEEP LAZY WRITER LOCK MONITOR SIGNAL HANDLER
I’ve yet to encounter issues as a result of a bad kill – when the occasional kill of a blocked or optimistic query does come along, it’s usually all good a few moments after the kill. Sometimes, perhaps there will be a calm 1 hour wait before a shrink db gets run too, who knows!
Even if you are equipped with a SQL Server monitoring tool, you still may be having trouble finding that slow/blocking query that runs during the night. Running sp_whoisactive periodically (e.g. every 30/60 seconds) and logging the results to a table will help you here.
As mentioned in the previous blog, there’s many parameters to choose from, including one that brings back the Execution Plan to allow you to tune if necessary. With a 2 week retention period set, my table had grown to 2GB in size (plans are heavy). This will vary per instance work-load and you should monitor closely if applying to a production server.
Also, avoid storing this within system databases. I’ve had this stored within a very large user database before, although a separate admin database would always be preferred.
For properly documented info whoisactive.com is the place to be. Below is my simple take on it;
It’ll take a bit of time to customise perfectly the way you want it. If you have time, great!
Parameters can also be tweaked to your needs. One I have added might be of interest: “@get_transaction_info = 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers” For further reading on CXPACKET waits, look no further. Read this post by Nikola Dimitrijevic and it’ll explain it all!
We’re now ready to create the table. Enter your database/schema/table name and un-comment the EXEC statement when ready.
Test sp_whoisactive Logger
Again, just amend the database/schema/table name and hit go.
Test Trim Period
You don’t want your table to get noticeably large, and it may grow relatively quickly. Set the retention period to whatever you think you’ll need.
Create & Schedule Agent Jobs
1. Open up a new SQL Server Agent Job.
2. Name and categorise appropriately.
3. Jump to Steps on the left-hand menu and click on New Job.
4. Set the step name and ensure the correct database is selected (or include a USE statement to the code).
5. Apply appropriate job step logic within Advanced. If you’d rather run deletes during a quiet period of the day, add as a separate job (this’ll change the logic of-course).
6. Once the above has been OK’d, navigate to the Schedules tab and click New.
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.
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.
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:
