Archive for the ‘T-SQL Tuesday’ Category

T-SQLTuesdayThe T-SQL Tuesday question this month is from Robert Pearl and is a topic that is near and dear to me. The topic overall is keeping your SQL Server Healthy, and this is the primary function of my current job. The team I am on watch a number of servers for over 100 clients. This is so near and dear to me that by chance I just finished up a blog post that is going to be a 10 part series about the common pitfalls I see on SQL Servers. The first one, by far is what I consider the most important. Backups.

Here is the complete blog post…

Tip Number 1 – Check your Backups

Top 10 Tips for SQL Server Performance and Resiliency

I have been a consultant for a number of years and have lost track of how many different servers I have worked on a long time ago. However today as sit down and think, it occurred to me that so many of the countless hours I have spent working on correcting issues could have been solved by not making some of the common mistakes I see. My goal in this series of blogs is to highlight each of the common mistakes that I have seen and some of the resolutions associated with them.

Unlike other top 10 lists we are going to start at the number one rather than looking at number 10 and then counting down in dramatic fashion. The number biggest mistake that I see on a pretty regular basis that will impact SQL Servers is how the databases are being backed up.

Not Backing up Servers

I am not sure if companies don’t realize that SQL Server should be backed up independently of the operating system or if they just assume that the servers don’t need to be backed up at all. Granted most of the servers that I see that have no backup plan at all are smaller companies, and I am seeing the less and less each day. What I do see most often is backups that don’t match the company’s data protection needs.

When looking at how to backup your SQL Server you need to start with the question of how much data can you lose. Once that question has been answered it can be used to drive your backup strategy. There are other questions you will need to address such as where to store the backups, how long do you keep the backups around and how fast do you need your system back up and running if you are having to restore the file.

Not Testing the Restores

As crazy as it sounds, getting a backup of your server is not the last step in this backup task. There is only one way to make sure that your backups are going to work for you the way that you want it to, and the time to find out that the strategy is flawed is not when you need to restore your database. There are many aspects to restoring your database that are often forgotten.

Consider this, if your backup is stored at an offsite facility, how long will it take to get that backup back on site where a DBA can start restoring the database. What are the steps required to retrieve this backup from the offsite location? Then when that backup file is back on site with your server, who needs to be there to move a back from one medium to another. This doesn’t only impact organizations that have tape media, if you are storing your backups in the cloud how long will it take to get that backup from the cloud and bring it back to your servers, and what happens if the event that has you searching for backups has also impacted your internet connection?

Before you need to restore your database, test it. With the technology that we have access to today it is simple to turn up a VM so you can test restore your backups. To take it one step further this should be part of your monthly maintenance. I work with some companies that know how important those backups are and they restore every backup they take to make sure it is a solid backup.

Not taking backups offsite

I hate to make a point by using a scare tactic, but the reality of the situation is that disasters happen. There are floods, fires, and earthquakes to just name a few. I live in Colorado and over the course of a couple summers we saw 2 major fires that destroyed many homes and businesses. We saw the loss of a restaurant that has been a spotlight of our community for over 40 years, we saw major shipping companies that shut down, but because of solid disaster recovery plans they were back up and delivering packages with 8 hours and they didn’t even have their trucks.

There are so many technologies that can assist you with your backup strategies, you can store your backup in the cloud, you can send it over the internet to a data center in another state. No matter the solution, if your data is critical to your operations your backups should be stored in a location separated from your SQL Servers.

Every layer of your backup strategy needs to consider the protection of your backup. This includes not storing your backups on the same storage where your data file is located. As solid as SAN technology is, there is always the potential of losing your storage. Backups that are stored next to the data and can be a costly mistake.

Securing your backups

If you have been watching the news lately you may have noticed a number of organizations that have had their data compromised. The larger the organization the more difficult the public relations become after such a compromise. The last thing any DBA wants to be part of is a letter to customers letting them know the data they are responsible for has been compromised.

Protect your database, encrypt them with a password and manage the permissions on your backup locations like they contain your personal social security number and your debt card PIN code.

Not backing up everything

SQL Server today can have many dependencies outside the databases. CLR code allows the database to use code that is not stored in the database, and certificates are used to encrypt the data. If those are not backup with the system, restores can become a serious problem.

Make sure everything is backed up that your server would need to continue operation. This is another good reason for a test restore.

Maybe the primary question you need to answer is; how important is your backup? What is the impact of not being able to restore your database, can the data be recovered from other means? What is the impact on your backup strategy to your business? Once you have a good solid answer to how important your backups are, look at your existing strategy and determine if your backup strategy matches your backup plan.


Wow, I could not think of better timing for the question today being presented and hosted by a personal friend Chris Yates (T|B). Chris wants to know what the SQL Community means to me. Sure I agree that the end of the racing season is never a good thing, I did however miss the opportunity to go to the race last Sunday, and I am really regretting it after catching up with what happened (Chris Yates made mention that this time of year is difficult for those of us seeing our sport seasons come to an end). The timing for me however is spot on here because of a number of situations that I have been faced with over the last few weeks…

Currently I am faced with a number of design questions, questions that often are only answered by experience, trial and error, along with testing. Questions like best way to store images with the database, or should a PK have the name of ID. I am also working on the fine line between being flexible, and standing my ground when I believe that something could potentially impact a database. These are just a few of my recent challenges. A great source for helping me navigate many of the pitfalls that surround implanting such things is the SQL Community. If there is a something that I have not had the opportunity to see in production then I know I can always count on my #SQLFamily to be a great sounding board. I often come back to this train of thought, even last week when I tweeted…

There are certain things or emotions that you just cannot explain, things like love, or in an easy reference point for something us Jar Heads call esprit de corps. Words like brother, cohesion, family, morale, and support all come to mind. In my own words I explain it as the brotherhood with Marines who have come before and will come after me. It is the all for one and one for all attitude with the never ending support and trust because when your face is in the dirt and the enemy is over the hill right in front of you. Your trust and your safety lie with the Marine that is next to you. You count on them to watch your back as you sleep and they count on you to watch their back as they sleep.

The SQL Community is a group of people who have gone so far beyond just having the same career path as I do, they help me when I fall or when I need someone to cover my back. When my family is in need, the SQL Community has helped me. When I am not sure if I have the right approach to a problem, they talk it out with me. When they have a question or are looking for work, I hope they count on me for help. We raise a glass together and share the great times, we laugh together when we can. We work side by side, when the mountain is too tough to climb alone.

Outside of the Marine Corps, I have never run across a better group of people. So when it comes to my Marine brothers and sisters, sleep tight, I got my watch and you can count on me. When it comes to my SQL Family, move ahead without fear, not only will I watch behind you, you have the support of thousands of SQL Professionals around the world… United we stand and divided we will fall. Some may only see the next task as a failed job, or a slow running query. I see these as situations where a community member needs assistance, and I will be there because I know when I need assistance they will be there as well.



Nick Haslam (T|B) is hosting the T-SQL Tuesday question for this month. Nick makes a reference to an older Sci-Fi movie that well to be honest, I am not sure I have ever heard of. I will take his word for it, and assume it is a great movie. It must be a good movie if it got him thinking about the question that has been prompted to us for the month. This month’s question is:

“So, what I’d like to know is, what is your most horrifying discovery from your work with SQL Server?”

It did not take me long to come up with the answer on this one, sure I have a number of stories about different situations that I have been in with SQL Server, here is a few of the notable ones, before I dive deeper into what I think is the most horrifying.

  • At one point in time in my career, a long, long time ago… I was asked by the CFO of the company that I worked for to grant him direct read access to our client table, rather than him having to use stored procedures for the website. For some reason this made me raise an eyebrow, and well end result was I denied the request. I leaned an important lesson that day, do the right thing even if you know it will be over ruled by someone else, or make you look foolish by not cooperating. The CFO when to the President of the company and requested the same access, the President hurried right over to my office and made sure that I carried out the exact permissions. Well, this is a long story, the end result? 6 months later we found out that the CFO passed the list to his wife, and they formed a company in her name. The new company, then directly solicited our customers. Sometimes I really hate it when I am right.
  • I could tell you about company I worked with that ended up building the server room under the water main. Here in Colorado it is not unheard of that we have these break on occasion, well… it did.
  • One of my first “real jobs”, I was working on tracking down an audible alert one weekend. I called my manager, the guy who set up the hardware and when he got there it determined that it was one of the drives in the RAID 5. He pulled the drive to force a rebuild over to the hot spare, but when the noise didn’t go away he determined he pulled the wrong drive, there was another one that was not flashing green as much as the others so he determined to pull that one as well. He had plenty of space on the RAID so he could lose that potential storage. If you don’t know what happened then I will let you in on a little secret. Don’t pull 2 drives of a RAID 5. Because it will turn into a RAID nothing, and your data goes down. What makes a stupid mistake like this approach the top of my list? Well.. He said, “The only mistake I made here was hiring you”. He had a long outage; I had a long weekend (and a new job within 3 hours), and a learning experience that has impacted me 15 years later. Don’t guess at solutions or problems. Develop an educated theory, confirm it to be true, and then test the solution.

So, what is my number 1?

One of my first clients was with a company who had paid for a consulting firm to come in and do a DR plan for him. The quoted him $500,000 for a system that that could be down a week, and had very few data changes. The data was less than 3 gigs, and rarely changed. He and I developed a backup rotation plan, and a way to get new hardware at the local electronics store, for less than 5k. What makes this just sick to my stomach was during the review of his back up process, I noticed a lot of transaction log backups but not a single full backup. When I asked the client, he mentioned that the company that configured the server, made a full backup, gave him the backup on removable media and then told him all he would need from that point was t-log backups. That was 4 years before I reviewed the system, so I checked and he was spot on, there were close to 14,000 transaction log files sitting on the hard disk. I mentioned it looked like we were missing a few (about 16,000), and if he know where they were. He replied yes, he needed some added space so they had been deleted.

Here is a client who things his system is recoverable and how paid for that service. Yet there was no way I could recover it without all the files, and even if I had them no telling how long it would have taken to restore 35,000 T-Logs. I felt a lot of satisfaction in helping my first client. This was all done remotely and I had a chance to meet him in person a year later. He and his employees thanked me for my help with a card and took me to dinner.

Another awesome discussion for T-SQL Tuesday, and big thank you to Mike Fal. The question is what sort of tips and tricks you use with SQL Server. The first thing that came to mind for me was how I track the history of Windows cluster failovers. I am not a big fan of having to dive into error logs, events and emails to just find the history of my servers. Recently I ran into a situation where a Clustered SQL Server Install was performing better on one node of the cluster than the other node. I have a history of the performance, but I did not have a quick easy way to look at the history of what node was hosting when. The solution I came up with was to use my dbUtility database to track when a node was hosting. This way, I don’t have to go looking around error logs, or emails looking for any failover events. The solution is quite simple.

First I created a table to host the information that I wanted to track.

Simple enough? Below is a small version of the information that I track.



CREATE TABLE [CLU].[ClusterLogging](

[ClusterLoggingID] [int] IDENTITY(1,1) NOT NULL,

    [ClusterName] [sysname] NOT NULL,

    [PrimaryHostName] [sysname] NOT NULL,

    [ReadingDate] [datetime] DEFAULT getdate() NULL,




Once the table and the Schema are there, I created a stored procedure that will check and find out the host is that is currently running. This information can be pulled from the Server Property Function without much difficulty, once I have that information, I checked to see if this is a new host, if it is a new status and log the information if it is new. This is the procedure that I use.






DECLARE @CurrentStatusID INT


SET @PrimaryHostName = CAST(SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’) AS SYSNAME)

— Is this a new entry? If so insert seed.

If Not Exists (Select ClusterName, PrimaryHostName From CLU.ClusterLogging

            WHERE ClusterName like @ClusterName

            AND PrimaryHostName like @PrimaryHostName)


        Insert CLU.ClusterLogging (ClusterName, PrimaryHostName)

        VALUES (@ClusterName, @PrimaryHostName)


— What is the ID of the current Status

SET @CurrentStatusID = (Select Max(ClusterLoggingID) From CLU.ClusterLogging

            WHERE Clustername like @ClusterName

            AND PrimaryHostName like @PrimaryHostName)

— Does the current hostname match the most recent status?

If @PrimaryHostName not like (Select PrimaryHostName From CLU.ClusterLogging

            WHERE ClusterLoggingID = @CurrentStatusID)


        Insert CLU.ClusterLogging (ClusterName, PrimaryHostName)

        VALUES (@ClusterName, @PrimaryHostName)



All that is left is to schedule the execution of the stored procedure, I imagine it could be triggered as a startup procedure, but I just choose to run it on a schedule basis. I know that the times may be off a little depending on how often I run it however; it gets me in the ball park. Now I know that I can query the CLU.ClusterLogging table in my utility database and I will have a decent idea of what happened and when. Much more efficient than digging through large error logs that only go back so far.

I am getting such a kick out of the question this month, I cannot tell you how many times I have been asked what I do each day, or how did I get started with SQL Server and databases. For someone considering this career field I spend a lot of time recommending training, or conferences. All my recommendations have been directed to learning the SQL Server skills, but there is so much more to become successful. So when I saw this question, I thought wow, this is going to be telling but I never guessed my day would turn out like it did. At the end of the day, I did however sit back and start to review what I had done and I compared it to my normal day, boy was surprised at how common this day was, minus a few notable events. So have fun with the answer, I hope you enjoy.

This month’s T-SQL Tuesday post is about you and your job. Specifically, on Wednesday July 11th or Thursday July 12th, track what you do for an entire day and then write about it. Hopefully one of those days is a “typical” day and not a vacation day (if it is, then just pick another day or do your best), but ideally, everyone writes about what they did on one specific day. The host of this month’s question is Erin Stellato (B|T) . Be sure to check out Erin’s blog for not only good SQL Stuff, but look at the comments to the question. How does your day compare, is it that different?

5:00 AM – Time to start the 2 hour drive to the Littleton office. (I do this for a couple days a week, and then work from home or out of the Xtivia Colorado Springs office for the rest of the week.)

5:10 AM – Go back because I forgot something.

5:45 AM – Ok I need food.

7:17 AM – Whew made it to the office, time to get started. I hit my daily checklist that is in my inbox, I want to make sure none of the servers are feeling sick, and I have the process automated. I updated my project list, made updates and found some coffee.

8:05 AM – The afternoon before a developer had sent me an email asking me to review a couple tables, and a stored procedure to ensure that everything was running as well as it should. I had worked on it 2 hours the night before I had documented the average run time of the stored procedure at less than 1 second and also saw there was about 22 logical reads per execution. Not a prime candidate for the cause of a performance issue, but I moved ahead because this may get slower over the course of time, the developer had a reason to ask me to do it, so off to it. I did notice there was a cluster index scan, so there is something I can do for sure.

9:00 AM – The poor soul in the cube next to me walked in and asked me if I live in a forest (we often discussions start like this), she proceeded to show me a number of bug bites on her neck and seeing that I live in the mountains, I guess that makes me the official office expert on Bug Bites. So I did what all of us Entomology experts of my level do. I goggled it. I was able to have a little fun and convince her that it was a bed bug, and that she might want to seek assistance from the medical field. But eventually I realized I should let her off the hook, and I was back to performance tuning about 10 minutes later.


10:30 AM – I had a foreign key that I had found I needed to add, and in addition a covering index to make the hash a lot less intrusive, with my code in hand that I was so proud of I wandered over to the developer to share the good news. It was then I learned that the chances are this table will never grow by all that much, so the 4 hours to this point I had spent on tuning this has saved about 20 logical reads an execution, at 500 executions at most a day times the 2 tenths of a second that I trimmed off the execution time, for a total of 10 seconds a day on saved performance. I revisited my action plan and determined that they next time I did a performance tuning task, I need to spend a little more time understanding the total impact of the stored procedure to determine of the if the tuning time involved, would make a bigger impact. There are some true side benefits that came out of this, but impact on the system will not be noticed.

10:40 AM – A client sent an email asking if he was being changed for some data that I had sent the night before. After talking to a couple people I was able to determine that a different consultant that the client had hired, was looking at trying to add some work to the contract they were on. The client was not aware that the discovery the other consultant was doing would include engaging some of my time.

10:50 AM – Back to work, looking at results of a Database Mirroring failover test, to see what items I need to make sure get corrected and what items we were expecting. The test had a couple issues that we had anticipated, end result was future tasks and projects are going to resolve these issues. I could not have been much happier with the results we saw, and I am overjoyed at how fast we were able to move all of our databases over 21 miles with only 2 20-second outages (Moved the data, and then moved it back).

12:30 PM – Had lunch planned, I had a few discussion points around the client I mentioned before. It sounds like the information is going to be passed along to the other consultant, and corrections will be made.

1:45 PM – In the afternoon I had the opportunity to sit with some of the key players in relation to the DR failover, there were a couple issues we wanted to address, but the primary was keeping our reporting services install online when we do a mirroring or AlwaysOn failover. The reporting services configuration that is being used has one SSRS web server, there is a second one that the report deployment is kept in sync in the other datacenter. However, the 2 are not load balanced, and the data sources for the reports do not use the failover server in the connection string.

4:00 PM – Time to head home, for the first couple miles I ran into a couple traffic spots that slowed me down, but this is where the day gets a little odd, and out of norm for me. During the second traffic slowdown a Toyota had been weaving in and out of traffic because of the slowdown. Inside the car were 2 people but because of the sun I was not sure, either way they were dancing around in the car just having a good time. For the next 20 miles, I was amused by the activity. Around 4:30 or so, we reached the stoplight were I jump onto another freeway and the Toyota pulled up next to me. As I looked around the lady in the front seat waved at me to roll down the window. I was sure something was wrong with my truck and they were going to warn me. My eyes darted over to make sure the lap top bag and the iPhone was where it should be, because well I am the guy who likes to drive away with stuff on top of my car. They were pretty adamant about talking with me, but once I had the window rolled down, the only thing they had mentioned was they like my Truck. Man I love living outside of town…

5:45 PM – I talked to the wife a bit earlier and she had asked if I would pick up some Pizza for dinner. So when I stopped to pick up the order I had called in from the road, I was caught off guard when someone came up and asked me if I wanted to sign up to win cash, grass or gas. I guess some local radio station was promoting something over the summer and this was the contest, I usually don’t have much luck with this stuff, but the drawing was in 5 minutes, and you had to be present to win. I looked around and well I had a 33% chance of winning. They called my name, I ran up and gave the wheel a spin. Eventually when the wheel came to a halt, I learned I won some grass. What the heck is grass, they cannot be giving away what I think they are, are they? Thank goodness no, it appears that grass is grass seats for a local concert where BB King will be jamming.

6:30 PM – I got home, had some pizza pie, and then sat down to finish up some emails and work. I look back on my day, start this blog and at first I thought I had a crazy day. But the more I think about it, was it crazy? Na, this is just a day in the life of a DBA.


I like really open T-SQL Tuesday questions, they really promote a lot of thinking, and I enjoy reading all the different perspectives. If you look at this month’s question from Aaron Nelson (B|T), it promotes some good discussion on logging. So a simple question on what your take is on logging, I hope will show there is a lot of different ways that people log what they are doing, and what the systems are doing. If you spend some time reviewing the answers my guess is that you may find a few tips on how you might be able to log things differently than you are now, and maybe even cover some areas that might have some gaps in your logging strategy. On a quick side note… I hosted T-SQL Tuesday #30, and it was an awesome experience. I would highly recommend that you talk to Adam Machanic (B|T) and really consider hosting one for yourself.

There are a couple different thoughts that come to mind when I start thinking about how I use logging. The Utility Database session that I am working on has a lot of logging in it, to help me with understanding what is going on and how I might be able to start predicting things like growth, or patterns when performance becomes an issue. The logging that I wanted to mention in this post is something that I didn’t design and I really didn’t do a lot of work on. The developer that was working on it wanted to come up with a solution to an issue, this was a great match. Now that it is in place and it is starting to be used, I see so many uses for it that I am going to push for its use enterprise wide.

The Case: The environment I am working in has a number of different applications or services, each one of them does something drastically different and most of them hit the database. There are so many of them, and the development process is moving so fast that it is really difficult to keep up on all of them. So from my viewpoint when they come to me with an error, or something that I need to address there are so many questions that I need to have answered. For example, a month or so ago, they service I am talking about started to get very intermittent connection errors. The errors before the logging solution were logged in a text file, this text file made it difficult to see things like error patterns, or patterns in the time of day. Doing any sort of deep analysis on these would require us to load them in another tool so we could see these patterns. End result, we had log files in multiple locations because of the HA solution we have, and tracking down issues was a pain at best.

The Solution: As basic and as simple that it sounds the solution was a simple table in a database. Rather than the application logging the messages in a file somewhere, they were redirected to log them into a database table. After we were able to see some of the benefits that this had, we were able to start to expand the capabilities to pro-active monitoring, alerting and history of the application. Tables were added to normalize the database a little more, and then an archive plan was created. Now there is a single location for these messages and they are already in a format that allows us to use tools that we have to filter, and review the data. One simple table cuts down on a lot of trouble shooting time, and gives us a new view into the health of the applications.

I have to admit. I am a little surprised at all the different opinions that were shared during the t-SQL Tuesday #30 from a couple weeks ago. Let me tell you why…

First I have to admit there are times when I look at a situation and I say to myself, what is the problem here. The solution appears to be so obvious to me that there can only be one reason that it is not in place, and that is because no one cares enough to make sure it happens. So how does this relate? The question I asked was do we need some sort of ethics code in our industry. To me the answer appeared to be so obvious that I was really surprised that we did not have one.

I have discussed the topic many times before with a number of people, there have even been some really good attempts as having a code drawn up. But I think for the most part most of the people that replied to the question either in the thread of this discussion, in person or on Twitter that if someone were to try to do this, it sure as heck would not be an easy thing to do. There were discussion on how to enforce, and how a small group of people could even determine a code of ethics for the whole industry. It was even mentioned what one person may feel as ethical another may not.

It sounds like of those people who did oppose it, were not doing so because they were not ethical, but because they were not sure how it could be managed or enforced. But a common theme that I did hear and see was the need for everyone to be ethical. Enough of hearing me blab about it, here is what the link backs have said.

SSWUG in a newsletter a while back discussing what some of the options are.

Mike Donnelly – I love this quote…. “If you need an ethics class or a signed code of ethics to help shape your behavior it is probably not going to change how you behave”

Rebecca MitchellA list of some of the Ethical issues she has had to work on.

Jonathan Gardner – A look at some cross over with the PMI code of Ethics.

Rob Farley – I like his companies view on this. “do the right thing by the other parties involved, even if it costs us money”.

Robert Pearl – The first post that had me looking at the question of what is considered ethical.

Tamera Michelle Clark – Your Company asks you to do something you consider un-ethical but they have no issue with it? This happened to me (a long time ago), and I understand all too well.

Jason Strate – Some really good arguments as to why we should not look at and industry wide ethics statement. A great post that was very well thought out, but now he is off performance tuning.

Airborne Geek – A different look at the same question.

So there you have it, I have talked to a few people and I even saw a couple links but I did not catch the link back so I cannot remember where they were. I can tell you that this ended up with some really deep discussions that took me in directions I did not think I would go. Have I changed my mind? Yeah I guess you could say that I did. For a long time I would have said yes I think we need an industry standard for ethics, but as I read through the opinions and had discussions I think I am going to stick to this idea.

I will have my ethics or if you will a set of standards that I will choose to live up to. I am sure you have yours as well. Thank you to everyone who participated.

I was in the first few months of my second database administrator job when the CTO told me that I needed to give the CFO direct table access into the database that I had designed.  Not 3 months later we were having a company meeting so the executive staff could explain to the company that the CFO had stolen our client list and was out luring our customers away.  Sound like a security issue? Not the way that I see it.

I had an ethics issue on my hands.   From that day in 1997 I have always had my eye out for ethical issues, and more importantly looking for ways we can police ourselves.  It does not take long for a new database professional to see that when you have access to data that there is going to be sensitive data in there somewhere.  The obvious ones are the HR databases, or the financial databases that reside on our SQL Servers.  But there are so many more areas that we need to look before we can get a good handle on how to solve these ethical dilemmas. Take a look at something that I posted a while back that threatened the security of the United States.  I cannot imagine that it would take long for an ethical person to say, “Really?”

A few months ago I had to get a security clearance, and pass the Security + certification so I could do a short contract with the Air Force.  As I was going over study material in a book I was supplied, I ran across a couple of short notes about ethics.  I followed a link or two and I ended up here.  When I first started to look at the list of ethics that they had listed, I was really impressed.  As I got deeper into what they were saying I became a bit concerned, however. The company that produced this is a corporation, not an organization that has the best interests for the industry as a primary goal.  I don’t believe there is anything wrong with being a for-profit, I know I work for one, and well, as an individual I am for-profit.  My issues with the code is the code itself appears to be pointed and making the company a profit, at least it does to me.  If that is the reason they sponsored the Code of Ethics, then well they violate their own ethics when they say:

“I will not advance private interests at the expense of end users, colleagues, or my employer“.

 So here is where that leaves us:

For this month’s t-sql Tuesday question I wanted to highlight the need for Ethics in our industry.  Don’t consumers and business owners have to trust someone at some time with their data?  This month, take time to participate by talking about DBA ethics.  I really hope to see someone address topics such as:

         Should we have an ethics statement?

         Have ethics issues impacted you? What did you do about it?

         Security Audits: how do you police what you and others are doing in the database?

         Does a Code of Ethics mean anything to anyone? How do we as a community enforce a Code of Ethics?

         Do you have an issue with this Code of Ethics?

         What do you believe our Code of Ethics should say if we the SQL Server Community have one?

Have fun, but take the time to dig deep and do some real soul searching.  I know with large number of really smart professionals that we have in our community we can think of something.  I will do up a summary once I have returned from my trip that week, but to be honest I hope this discussion goes on long after May 8th.

As with each of the T-SQL installments I ask that you follow some basic rules.


         Your post must be published between 00:00 GMT Tuesday May 7th, 2011, and 00:00 GMT Wednesday May 8th, 2011

         Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.

         Trackbacks should work, but if you don’t see one please link to your post in the comments section below so everyone can see your work

Optional, but highly encouraged…

         Include a reference to T-SQL Tuesday in the title of your post

         Tweet about your post using the hash tag #TSQL2sDay

         Consider hosting T-SQL Tuesday yourself. Adam Machanic keeps the list.

I am a bit late this month for the April #Meme15 question. But after I started thinking about it and looking at the question, I found this to be useful for me at least so I thought I would go ahead and post it.

See there are a few really good #meme threads this month I think the question asked by Jason Strate (B|T), points to the other one called T-SQL Tuesdays that normally post around the second Tuesday of the month. The #meme15 was started and is hosted by Jason Strate and for the most part is related to the skills that a Database Professional needs that are not just technical. If you want to see this month’s question just click on the #meme15 logo, the question for this month was:

What are ten blogs that you think other SQL Server professionals should be following but might not be?

So, #meme15 I would like to introduce you to T-SQL Tuesday. Now granted I know that you understand each other for the most part, and to be honest I am not sure but you may have even met in the past. However, I wanted to point out that when I have a little extra time, and I am looking at the different blogs that are out there I came to the conclusion that there are just way to many really good ones to get the list down to just 10. So if I were talking to a new database Professional, and I was trying to point them in the direction of blogs that they must follow, I would have them start by following a T-SQL Tuesday topic that they really enjoyed. By the way, I would make sure that I recommend that Database Professionals follow #meme15 as well. I think there are a lot of Soft Skills here that are not taught anywhere else.

T-SQL Tuesday was started by Adam Machanic (B|T) and has a great participation ratio. The premise of T-SQL Tuesday that I like so much is the fact that it is hosted by someone different almost each month. This means that you get questions from a lot of different viewpoints. Some are from development, some from administrators. I have seen some BI questions and even a good second chance topic. I have to be honest and tell you that next month I am hosting T-SQL Tuesday and I have a question for the community that I am really passionate about. So I hope you have the opportunity to participate, and if you are not a blogger than I hope we have a lot of participation and this can bring a lot of information to you.

So, where does that leave us? Well one thing that I did when I learned I get to host a T-SQL Tuesday was to go out and find out all the past questions that have been asked, I didn’t want to ask a question that had already been asked. Then as I was looking at the past questions I thought, hey… I should jot these down; put them up on the blog.

What are the ten blogs I think you should follow; I think you should start here. I know there is a lot more than 10. But as you follow the links you will find the people that you like, and the topics that you like to read about. You can develop your own set of favorites and then you will be well on your way. I hope to see you come back in a couple weeks to see the next T-SQL Questions and answers. And if you want to see the question, well I hope to have it posted a week from today.

1 11/30/09 Adam Machanic Date/Time Tricks
2 1/4/10 Adam Machanic Write a blog post on a “puzzling” topic .
3 2/2/10 Rob Farley Relationships
4 3/1/10 Mike Walsh IO
5 4/5/10 Aaron Nelson Reporting
6 5/3/10 Michael Coles LOB
7 6/1/10 Jorge Segarra What’s your favorite hot new feature in the R2  ?
8 7/13/10 Robert Davis How to do a good interview for your first interview?
9 8/3/10 Jason Brimhall What do you do as a DB professional to earn a little “Beach Time?”
10 9/7/10 Michael Swart Indexes
11 10/4/10 Sankar Reddy Misconceptions in SQL Server
12 10/25/10 Paul Randal Why are DBA skills necessary?
13 7/12/10 Steve Jones What issues have you had in interacting with the business to get your job done?
14 1/10/11 Jen McCown Resolutions
15 2/1/11 Pat Wright Automation in SQL Server
16 2/26/11 Jes Borland Aggregate Functions
17 4/5/11 Matt Velic Apply Knowledge
18 4/29/11 Bob Pusateri Common Table Expressions
19 6/7/11 Allen Kinsel Disasters and Recovery
20 7/5/11 Amit Banerjee T-SQL Best Practices
21 8/3/11 Adam Machanic Reveal your crap to the world
22 9/6/11 Robert Pearl Data-Presentation
23 9/27/11 Stuart Ainsworth Joins
24 10/31/11 Brad Schultz Prox ‘n’ Funx
25 12/5/11 Allen White What T-SQL tricks do you use today to make your job easier?
26 1/2/12 David Howard Please pick any one of the previous 25 T-SQL Tuesday topics and write about it .
27 2/14/12 Steve Jones What big data problems you’ve solved, or interesting ways of working with big data ?
28 3/5/12 Argenis Fernandez Why you specialized, or why you’d like to specialize?
29 4/10/12 Nigel Peter Sammy What do you think is a useful feature of SQL Server 2012?

I really try to take part in as many T-SQL Tuesdays as I can, but to be honest I just forget about it.  So as I try to figure out how to remind myself I ponder what my favorite tip is.  I think this is a great Question asked by Allen White.  If you don’t know about the whole T-SQL Tuesday thing you can either follow the Logo or check it out on twitter using the hash tag #TSQL2sday. Allen had mentioned that in the early days of SQL Server that some of the best tips and tricks for managing SQL Server came from the community.  The key here is that you need to make sure that you were taking part in the community to get all that you could from the tips people had to share.  

What Is My Tip?

I think it should go without saying that you need to be checking your error logs on all your SQL Servers every day. If you fail to do this then you are just waiting for your servers to fail on you.  With the ever growing popularity of SQL Server (I know this surprises you), there are so many of these servers that we need to manage.  This means that each day you should be opening the error log on each of the SQL Servers that you manage.  But what happens when you have 10 or even 100 even more?  Obviously the time involved becomes that much more intrusive.  Try telling your supervisor that you could not get any work on your projects done because you were reviewing error logs, see how far that gets you.  So how do you make this easier?  How can you complete this action without taking all your day reading logs?

Use the extended stored procedure that Microsoft provides you to help you automate your task. 

EXEC sp_executesql N’xp_readerrorlog’

Now you can see that you have your error log in a result set that you can manage.  I will be going much more in depth with this during in my Utility Database Session SQLSaturday #104 Pre-Conference What’s in your Utility Belt. Learn how you can use this information to help you even more.  Considering putting your record set into a table, this can be done by:

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


EXEC sp_executesql N’xp_readerrorlog’

Once you have the data in a table I think that this is where it is the most powerful. You can filter the data and remove the rows that you don’t want to see, you can look for keywords and take action on issues that you see in your error log.  If you add the code to a SQL Server job, you can automate these items.  My favorite part is that I can use SQL Mail to send me results or I can format it in a SSRS report with a subscription.

End result, I use the methods that I am most familiar with, when the data is in a table. I know how to move through it, act on it, report on it and make sure that each of the issues have been addressed.