Archive
Determining Estimated Completion Time from sys.dm_exec_requests
According to Books Online, the following list of commands will generate a percent complete and an estimated completion time in the sys.dm_exec_requests DMO:
You can use this to determine approximately how long a backup, restore or DBCC command will run. This query will get you a nicely formatted time value.
USE master
GO
SELECT
CASE
WHEN estimated_completion_time < 36000000
THEN '0'
ELSE ''
END
+ RTRIM(estimated_completion_time/1000/3600)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)
+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining],
percent_complete,
*
FROM sys.dm_exec_requests
WHERE percent_complete > 0
Chicago SQL Server User Group October Meeting
Last Thursday was the October meeting of the Chicago SQL Server User Group. This was the fourth meeting that Aaron Lowe (b|t) and I have run and things have gone remarkably smoothly. Many thanks to Bill Lescher (t), Ross LoForte and all of the folks at the Microsoft MTC for providing us a solid foundation and great facilities to work with.
The sponsor for this month's meeting was SWC Technology Partners. Erica Lee and Lowry Kozlowski spoke about the company and the positions they have open. Thanks to Erica, Lowry and SWC for their time and for the pizza.
That brought us to the presentation portion of the evening. I was on deck to give my first presentation in front of a user group, entitled Interrogating the Transaction Log. While I hadn't practiced as much as I would have liked, I'd run through the various sections enough to be confidently nervous. I feel like I did a decent job, but would have definitely benefited from some additional practice. The only glitch I ran into was that my laptop refused to go into duplicate presentation mode, which meant I had to keep looking over my shoulder during the demos. Despite that, the feedback I received was positive and a number of people told me they had learned something new, which was why I wanted to present in the first place.
Thanks to everyone who attended the meeting and provided feedback. Further thanks to Jes Borland (b|t), Paul Randal (b|t), Kimberly Tripp (b|t), Brent Ozar (b|t), Aaron Lowe and all of the others who have provided encouragement, inspiration and advice.
The August Chicago SQL Server User Group Meeting
Last night was the August meeting of the Chicago SQL Server User Group. It was the second meeting Aaron Lowe and I have run. Ira Whiteside of Melissa Data was kind enough to sponsor the meeting and was scheduled to present. Unfortunately, Ira ran into car problems on the way down and could not make it. In Ira's absence, Aaron and I decided that we would each do a presentation. Aaron presented on Writing Professional Database Code, which was excellent. Statement terminators and version control for database code are two of Aaron's suggestions I will bring back to my team.
I started off the meeting with a short presentation introducing Adam Machanic's sp_whoisactive. If you do any troubleshooting of SQL Server issues, sp_whoisactive is a tool you should be using. It consolidates data from sp_who2, sys.dm_exec_requests and a number of other DMOs to give you a quick and complete picture of how each process is performing on your system. You can pull the execution plan for each process and have the option to generate deltas for a number of metrics over a period of time. The best part about the procedure is that it is free for download. The fact that Adam Machanic provides the code for free is a testament to the strength of the SQL Server community.
If you live in the Chicago area and are not registered for the user group, you can do so here. You will receive e-mail notification for upcoming meetings. We are looking for group members to present at upcoming meetings. If you are interested, you can e-mail me here. I hope to see you at an upcoming meeting.
Chicago Data Professionals
