Archive for December, 2013

“If you could give a DBA just one piece of advice, what would it be?”

John asked 20 successful and experienced SQL Server professionals this exact question. I share my own thoughts with you below and you can find all our answers together inside DBA JumpStart, a unique collection of inspiring content just for SQL Server DBAs. Be sure to get your free copy of DBA JumpStart.

This post is part of the SQL Community Project #DBAJumpStart by John Sansom.

Recently I was asked about the piece of advice I would pass along to a Jr. DBA or someone considering becoming a DBA. I tried to determine what my one piece of advice would be, the one piece above all others that I would pass along. I went back and forth thinking on this for weeks. The idea alone that someone would want my advice is humbling. I considered a number of things that I would pass along both technical and non-technical. I considered my personal pet peeves and if I could change the industry how would I change it? Each consideration I reflected on was based on what I wanted to tell someone and, well, that is what I am doing here. I even thought about what I would say if I was in an elevator at the PASS Summit and I was asked what the one piece of advice would be and I imagined I only had 30 seconds to answer before all of us in the elevator went our separate directions. Eventually I reached a point in my personal reflection where I started to think about the questions I have been asked in the past and I let that direct my answer. So when I am asked, “What is the one piece of advice I would pass along to new or Jr. DBA’s?” I won’t list 50 items making it sound like I just can’t grasp the whole concept of what that ONE thing is….

How can I become a DBA?

This must be the number one question I am asked. I have had this discussion so many times I debate if people are serious when they ask me this question. I have tried to encourage and motivate some of them by suggesting books, websites, even blogs and twitter accounts that they should follow, yet only a few have gone on to become Database Administrators. There are times I start to think I have failed my self-appointment as DBA Ambassador. I looked at my approach a few times and adjusted it thinking I must be answering in the wrong way. Now I am not so sure. I don’t think I have been fair when communicating how much of a commitment becoming a good DBA is (I have never considered myself to be a quick learner so this may just be my impression).

Granted, this collection of my thoughts is not meant to classify everyone who considers becoming a Database Administrator, but is simply a reflection on the most recent discussion I had during lunch at an event I was speaking at. This discussion happened at a table with a handful of people who, like me, were late to lunch and it was obvious a speaker ran late, yet again (that was me).

So the discussions started with the question, “How can I become a DBA?” with follow up questions along the lines of “What pays more, a Developer or an Admin?”, and the question if there is more money in networking.

I want to pause here and mention these are all valid questions. The difficulty I have with writing this post is the tone of this discussion. The obvious tone that I and others at the table received was that of someone who wasn’t all that interested in becoming a DBA because of what we do, but a tone of wanting to be a DBA because of the pay or because some IT magazine said there was room for a lot of growth (again, not a bad reason to choose this field). It would be like determining you wanted to become a church pastor without having the religious belief.

What advice did I pass along or what advice would I would pass along to anyone entering the field? Decide if this is a job or if it is a career – If you’re looking for a job, then you may just want to keep looking. Being a DBA isn’t a job, it is a career…maybe even more than a career. It becomes us. Some of us who are DBA’s fell into this field by accident (no one else wanted to do it) while others of us chose to be a DBA and planned our education to become a DBA. No matter how we became a DBA, the DBA’s that I know of who have succeeded in what they do, treat being a DBA as way more than just a job.

In my opinion, I would describe the difference between a job and a career as:

  • A job is something you do 8 hours a day, five days a week. Sure, sometimes it comes with a little overtime, however, for the most part, the time when you arrive at work and the time you leave work is the same each day. The motivation to go to the job is focused around the money, mostly because that is how one pays the bills. Some may think of a job as a bad thing but it isn’t. Jobs can sustain you while you study for another career. Some may not like the idea of becoming so ingrained with their work. Many people like the idea of leaving work at 5:00 PM each day to know they are making their way home or to school, or on their way to a hobby of some sort. There isn’t anything wrong with wanting a lifestyle like this. I like to think of a job as a light switch, something that can be turned off and on when the time is right.
    • A career is work that you can’t put down, somewhat like a good book. There is something there that draws your attention to it, something that makes you say, “You know what? I am going to skip book club today because I want to figure out why this does what it does.” (Whatever it may be!) A career will leave little traces of itself scattered throughout your life: for example, I sat down the other night to watch a TV show that I find entertaining but as a commercial came on, I found myself starting to concentrate on a design issue I had been working on. Before I knew it, I had an idea. I started up the laptop and thought about making a quick note so I could get back to the show. I did a couple tests and came to realize that not only had I missed my TV show but the next show as well, not to mention it was WAY past my bedtime!
    • Just a few days ago I saw a car accident just outside a local military base. I saw a longtime friend out directing traffic. My friend is over 70 years old and has spent his life working for the safety of the United States in the US Army. The police were already on scene along with the fire department and EMTs. Why was he standing out there even though he is retired? Because this was his career. He knew that by directing traffic he relieved a resource and he knew that resource could then focus on assisting with the rescue efforts or the cleanup. My friend may not have thought about this decision as I have. Why? Because being a first responder isn’t his job. It never was his “job”. At one time he may have been paid to do it actively, but this day he did it because it is just who he is.

The tasks of a DBA are difficult to define precisely. Sure, there are things like backups and restores that fit into the DBA category nicely, but what about performance tuning? Should that be done by whoever developed the code? What about building a data flow or the design? Being a DBA is like being a ‘Jack of all Trades’. There is so much that can impact the stability of the database, anything that touches the database can impact the primary role of the DBA. Aspects that relate to hardware, operating systems and even networking can impact the core responsibilities of the DBA. Throughout my 15 plus years as a DBA, I have worked on all those aspects and more.

If the idea of long nights, early mornings and solving problems while you sit at the dinner table does not appeal to you, don’t worry! It doesn’t to too many of us either but we do it. We do it because we are called to do it. There is a lot of satisfaction of fixing what other people can’t and doing what other people won’t. If all you want is to walk out the door after your shift is over or to turn off the light switch because your work for the day is done, I recommend that you keep looking for work that fits your lifestyle a bit better. Keep in mind also that if you are motivated and driven by recognition, awards or the admiration of everyone at a company, often you will not find those things as a DBA.

If you like the idea of leaving the office at 7:00 AM when the work day is about to begin, knowing that all the faces you see coming in as you walk out of the building are going to have a good work day because you were able to fight through the sleep deprivation, face the errors that caused others to run and hide and that you’ve muscled through the last 12 hours of your 24 hour shift without eating anything, then being a DBA may be right up your alley. When we as DBA’s are doing a good job, no maybe better said as… when we as DBA’s are doing a great job, the database performance is fast just like everyone expects it to be. The database is stable just as everyone expects it to be. After whole SAN storage failure the database is back online ready for business… that is what the organization expects it to be. When a DBA is doing a great job we are meeting expectations and all too often many who are not DBA’s don’t know all the work it takes to meet those expectations.

December 2, 2013 I posted a blog titled, SQL Server Error Log. Often the things I post about are the items that I am using that day, or recently. Many of the topics, are simply based on how I may have made using SQL Server a bit easier for me to use one way or another. In the SQL Server Error Log post I had mentioned a few of the basic things I do when configuring a server to make the Error Log a little easier for me to navigate, or get the information I need quicker. It occurred to me after a question from a well-respected community member that an example on how I use xp_readerrorlog, could add to the usefulness of the post. Thanks for the suggestion.

In the post I recommend tabling your filtered error logs. I am fond of basing all my work on the negative (I will explain more of that in a later post). The term working with the negative for me means working on the things that I am not expecting. For example, I expect that every hour my transaction logs are going to be backed up, I don’t need an alert or an email each time that the transaction log completes without any issues. I do however want the alarms blasting every time they don’t execute as planned. I apply the same logic to my error logs, however even if I could remove entries from my error logs I always want them as they are. In order for me to have the best of both worlds, I like to take all the entries that I am not expecting and store them in a table so I can filter them with a query, join them to other tables to get other relevant events that were occurring at that time and perform other tasks that may be a bit difficult with the error logs as they stand.

Where to start?

When I start working with a new server, if I have the opportunity I like to create a database I call dbUtilities. The Tag on this post will show you some other posts where I have used this database. After that database has been created, or you have picked another location to store your table, start with a table that is similar to this one. I create this table so I can store my information long term. You may want to add some additional fields such as a PK.

On a quick side note, you should customize this code to meet your needs; the posted version here is in the simplest form. Make sure you look at the added notes at the bottom of this post before executing this script on your servers.

Create
Table
MyErrorlog
(LogDate
datetime,
ProcessorInfo VARCHAR (100),ErrorMSG
VARCHAR(2000))

Once I have my final destination created, I want to insert all my errors that exist in the error log into a temp table where I can start the filtering process.

DECLARE
@Errorlog
TABLE (LogDate
datetime,
ProcessorInfo VARCHAR (100),ErrorMSG
VARCHAR(2000))

INSERT
INTO
@Errorlog

EXEC
sp_executesql
N’xp_readerrorlog’

From this point I start to remove the entries that I expect to see, for each server this is on, the filtering could be different. I leave each of the delete statements as an individual statement, for ease of understanding and customizing. If performance is of any concern it would be better to combine the statements.

Delete

FROM
@Errorlog

WHERE
ErrorMSG
LIKE
‘%Log was backed up%’;

Delete

FROM
@Errorlog

WHERE
ErrorMSG
LIKE
‘%Setting database option COMPATIBILITY_LEVEL%’;

Delete

FROM
@Errorlog

WHERE
ErrorMSG
LIKE
‘%were backed up%’;


Delete

FROM
@Errorlog

WHERE
ErrorMSG
LIKE
‘%DBCC TRACEON%’;

Delete

FROM
@Errorlog

WHERE
ErrorMSG
LIKE
‘%without errors%’;

Once the filtering is done I insert these rows into a final table. (I create this all as one stored procedure, and execute it once a day. I am really only concerned with the last days’ worth of information.)

INSERT
INTO
MyErrorlog

        SELECT
Logdate,
‘Error Log’,
SUBSTRING(ErrorMSG, 1, 2000)

        FROM
@Errorlog

        WHERE
LogDate
>
DATEADD(dd,
1,
GETDATE())

Some additional notes:

  • In the post SQL Server Error Log I mentioned cycling your error log to keep it small and manageable. This can be key when executing xp_readerrorlog. Large logs could impact the performance on your system, and the way I use the code I am expecting a smaller log.
  • In my case, I normally complete an additional step where I insert the rows into a table that I truncate and rebuild each day. The purpose of this table is so I can use it as a base for a reporting services report and pull information from that table and then subscribe to the report. This way I can other items such as failed jobs, replication status, and job executions that have failed to name a few. This is why I use the ‘ProcessorInfo’ column in the tables. It gives me the space to identify the source of the data in my final table.
  • Don’t forget to have an archive plan for your old data. I am a pack rat, and hate the idea of getting rid of info, I often get values from trends. So I will copy the data on occasion and store it somewhere offline if I have to.

Often when I am requested to look at an issue with SQL Server I have to spend some time in the error log.

On a side note, I think the log itself is a commonly overlooked tool for troubleshooting. Often it can be a bit cumbersome to work with if the default settings are used.

When SQL Server is installed, the error log is configured to start a new log when the service is restarted and only a handful of logs are retained long term. In some cases where troubleshooting has lead people to restart the service, or server a number of times valuable information can be lost simply because of the number of error logs that are retained.

On the other side of the spectrum if the server has been on line for quite some time, the error log can grow to extremely large sizes. Depending on a few settings that may have been made elsewhere many of these log entries are not really much assistance in working though issues. Imagine having an error log with half a million rows, and even though these half a million rows are sorted by date time, you have to scan the log to see when problems started. While you are considering how you may filter the log, or copy the log to a format that is easier for you to manage and review. Don’t forget that a log that size will need to be copied or loaded and sometimes both. Many times this has to be done while there is an issue of some sort, that very well may be causing production issues, and time is critical (nothing like having management wanting a problem solved and you have to wait for a huge error log file to load). The good news is there are easier ways to do this. Here are a few tips I like to stick to with my error logs.

  • Cycle your error log – Large error logs can be avoided by taking a couple steps, one of these can be simply cycling the error log. When execute sp_cycle_errorlog will simply start a new log, I like to schedule this to happen on a regular basis for example every Sunday night. On servers that have a lot error log entries, because of security logging or frequent transaction log backups then I may even consider cycling the error log more often.
  • Change the retention – By default you are going to have 7 error logs, if you are considering cycling your error log, (I believe you should) I would recommend also changing the retention time of your error log, often I will keep 60 days’ worth of logs, but your requirements may be different. The easiest way to do this is to Right click on the Error Logs folder in Management Studio, select configure and increase the Max number of error logs before they are recycled.
  • Table Filtered Logs – One thing I like to do, purely for troubleshooting on super busy systems is to use the stored procedure xp_readerrorlog to insert the rows of data into a table. When I do this, I can then filter out the items that I don’t need to see. This is also an easy way to compile logs from multiple servers into one table, and used in combination with a mail profile send your self the results. This will keep you from having to review many error logs on many servers. Often what I do is use the stored procedure to insert into a temp table, and since this is a filtered error log I will remove known entries from that table that I am aware of, items like transaction log backups successful, log on attempts (I often count those before I remove them to make sure the failed ones are not indicating another issue) and other repeated entries. Once I filter the temp table of everything I am aware of, I then insert the log into a permanent table for safekeeping. The permanent table then is managed to a retention time as well. Remember this table is filtered view, I use it in place of the error log for quick access so I can filter it as I would any other table, but it does not replace having the detailed error log. It just gives me more filtering options (Like joining to job history when a job keeps failing).

The end result is that for my use when the log grows to a size like that, it is frustrating to use the log to find the information that is critical. Managing your error log is important and the end result is that it will make your systems easier to manage and troubleshoot.

** Note: Fixed a typo change sp_readerrorlog to xp_readerrorlog.