How long will this backup take?

It's a Friday afternoon. You just did an oops and need to fix it. You think you need a backup. You know you need a backup, before you do anything. You quickly kick off a backup job and let it start performing the backup. Now, you know that this job has executed in the past, daily, probably. You can check the history of the job and get an idea of how long it will take.

The last execution was 1:10. A previous execution completed in :45. So you can safely guess that you are in for at least an hour of wait time before completion. This displeases you as you realize that it is already 3:30pm, on a Friday. The backup is already going, so it's a waiting game at this point. Wait, wait, wait.

Your boss suggested that you take off and get some away time, knowing that once the backup completes that there will be a hectic time performing the needed tasks to fix the oops. You consider leaving, after all, the backup will be running for an hour or so... Maybe sooner, maybe longer.

Since this isn't a backup from the UI, you do not have the handy dandy percentage showing up in the corner of the screen, giving you an idea for how long it may take. All you have is a small window saying that the job started, and the spiny guy showing you it's still going. And will complete. At some point.

So, how long will it really take? Only a time machine will truly tell you. But we can make logical guesses. A quick few clicks in Google/Bing would show you several scripts that could help. Here is the one that I found.

SELECT

CONVERT(NVARCHAR(60),DB_NAME(database_id)) AS [database],
CASE command
WHEN 'BACKUP DATABASE' THEN 'DB'
WHEN 'RESTORE DATABASE' THEN 'RESTORE'
ELSE 'LOG BACKUP'
END AS [type],
start_time AS [started],
DATEADD(mi,estimated_completion_time/60000,GETDATE()) AS [finishing],
DATEDIFF(mi, start_time, (DATEADD(mi,estimated_completion_time/60000,GETDATE()))) - wait_time/60000 AS [mins left],
DATEDIFF(mi, start_time, (DATEADD(mi,estimated_completion_time/60000,GETDATE()))) AS [total wait mins (est)],
CONVERT(VARCHAR(5),CAST((percent_complete) AS DECIMAL (4,1))) AS [% complete],
GETDATE() AS [current time]
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE','BACKUP LOG','RESTORE DATABASE')


It simply queries the dm_exec_requests and looks for items that indicate a backup. The other fields will massage the resultant data into something that you can use to see how long it may take.

Once I executed this, I was sad, because it showed me over 2 hours to complete. The sad was the duration was longer than I had hoped. The good was that I now had a goal. A time when other tasks would start, after the backup completed. This allowed me a better chance to plan and prepare for those tasks.

Habit and Perception

The other day I took a shower. I do this when I intend on meeting other humans during the day, and this was to be a day of meeting other humans, so a shower was necessary. When the shower was complete I grabbed my handy dandy bath towel and proceeded to dry off. However, this time, the towel had a tang to it, an oldness, an odor... This has happened to me before in the past, and I am not properly equipped to deal with this situation. You see the bath towel is a bit of a mystery to me. Its simply there for my use. And for a while it will remain there, a blue towel, hanging where I expect it to hang, standing at the ready. Then one day it will change to another colored towel. This towel is also a bath towel and serves the same purpose, and by all accounts in my mind, is the same towel, simply different. I often wonder where the blue one went. But I end up seeing it again, some other day. They come and go from my use with a similar mystery as other items in my life that vanish, only to appear later where I originally found them. It sure makes life exciting to not understand it all, to live and witness such magical behavior.

So, there I stand with my smelly bath towel, wondering what to do. The magic task of switching towels is so magical, I was not sure where to start. Where to look. Where to go. I secretly hoped that if I closed my eyes the magic towel switched to the new one, solving my problem. I was alone. No one I could ask. No help to be received. I was alone.

As I wondered around the room and adjoining rooms, searching for a towel, I walked past 2 towels that were guarding the corner of the bedroom. 1 towel was from a work we had this past summer. It was a nice blue hue with a company logo on the corner. It was fairly new. The other was from a racquetball tournament. The first was given at a pool party event, while the latter was given during a tourney. Both seemed to have a specific purpose. And neither of those purposed seemed to be for post shower use. I dismissed them. They were not the intended targets. I needed a bath towel. I never thought of using them.

As I wondered around the adjoining rooms and storage areas, I encountered a fair share of similar items, but no true bath towel. I could not seem to decipher the runes indicating the direction and storage of these magical devices. Their purpose understood, yet their hiding place eluded me. I returned to the bedroom disappointed in my efforts and more so with their result. It was then that my eye was drawn to the 2 other items, similar to the elusive magical bath towel. Could I use either one of these objects? One was intended to live in a gym bag and dry my body after a shower from a work out. That was fairly similar to my morning bath. The other was used to dry me after a pool adventure from a work party. Also intended to be a drying device.

By golly, I think I had a bold, new, and exciting idea. I could use one of these items to complete the task I had at hand. Maybe they were not intended for this exact purpose, but they could do in a pinch. They would suffice. They would server the purpose and actually accomplish the goal. My morning wasn't actually in ruins anymore. Life could proceed, tasks could be completed, and so on. Yeah.

(Yes, I have fun writing up these little dramas, these little glimpses into the brains attempt at making entertainment of the simple, the mundane, the commonplace. I enjoy the dichotomy of a few moments of thought elongated into a tale that seemingly takes much longer to complete.)

What's the point of this little tale? Its about my perception. My habits. I had fallen into a habit of waiting for someone else to take care of my bath towel situation. I had the perception that the other towels in this tale were not intended as bath towels, as I needed. How often do we do this in our work? How often do we forgo a solution that is compatible, only because it is not the solution we expect to be the correct one?

Do we only use sp_who to diagnose an issue? Why didn't we use the DMVs? Did we use the DMVs when an sp_who or sp_who2 would have sufficed?

Do we open up a profiler session and monitor our production boxes instead of crafting a trace that will do this more nimbly, with less impact? And should we use the DMVs to dig into what we are looking for and skip the profiler and the trace all together?

Do you excuse an error that you see because you have seen it over and over and over, and believe that you have a grasp on what it means, why it occurs, and simply do not deem it worth your while?

Does the report run quick enough that no one is complaining, and why should you deal with it now? It works fine, doesn't it?

There are probably more questions I could ask. I hope that you are actually thinking of your own situations now. Those items that you have taken as habit, or have carefully crafted a perception that you can accept, be it correct or erroneous.

Take a step back. Look at the situation. Is this a habit, a bad habit? Should you do this differently? Look at how you perceive the situation. Is your perception jaded, twisted, erroneous?

If so, please have the courage to take the time to remediate the situation. Fix it. Make it better. Make it faster. Do it the right way. Investigate what the right way is.

Putting on my first Pre-Con for #SQLSat104

While at PASS 2011 I had the inspiration to discuss the possibility of combining my speaking efforts with those of Chris Shaw (blog | twitter) on utility databases. I have been speaking for a while in various venues and most of the topics I have covered seem to float around this idea of utilities that help me as a DBA perform my day to day job, with an emphasis on monitoring, collecting baseline information and reporting on said information. You can check out some of the past presentations I have done in this blog post.

I have seen Chris present on Utility Databases and was impressed at his approach and solution to the problems he has faced. I approached him and discussed us speaking together, combining our efforts (read:climbing on his coat tails and enjoying the ride). He was positive in the receipt of the idea. Various discussions have ensued since that day.

We have created, what we think, will be an enjoyable day of sharing of our experiences, actual code, actual data, actual demos and actual templates that you can use in your organization to solve some of the problems we all face as DBAs. There is no right or wrong way to perform these steps, and every implementation will vary based on your knowledge, skill, time and need. But these ideas will be things we have done, are doing, and continue to do to support our own organizations. We hope that they will help you out in yours as well.

Expanding on the idea of a Utility Database, we want to help you create a Utility Belt with tools you can use. Think of it like the Utility Belt that Batman wears. I would imagine there are some great gadgets in that belt that help in various situations. We hope to populate your Utility Belt with some of the tools we have learned and love to use ourselves.

We will start the day with some list of tasks to perform daily/weekly/monthly. Hoping that these ideas will get your brain juices flowing and thinking of how they may be implemented in your shop. We will discuss Chris's Utility Database, my Data File Size collection and reporting system, new techniques with SQL Server 2012 features, and even delve into documentation (a much needed, yet much ignored topic).

It's my hope that you will come away from this day long discussion with some tools that you can take home with you and use in your shop. Take them, tweak them, mash them into your square peg hole and make them work for you, just like Chris and I have done to gain this knowledge ourselves. We all end up learning from each other, sharing with each other and helping each other. Together we can make our jobs and our own effectiveness that much better.

Come see us. Come learn from us. Come share your experiences with us and teach us as well.

Homepage for the SQL Saturday Colorado Springs event is found here.

http://www.sqlsaturday.com/104/eventhome.aspx

Homepage for the pre-con we are putting on can be found here.

http://sqlsat104utilitybelt.eventbrite.com/