This week I promised my DBA buddy that I’d behave myself. So any mayhem that occurred was not a direct result of anything I did… at least this week… for the most part… I think…
Anyway, we thought we were going to get an Oracle database that would be used for the new pass-through web portal. *psych!* Nope! It’s back to SQL Server… first it was Oracle then it was SQL Server… back to Oracle then SQL Server…Oracle again… now we’re back to SQL Server… Actually, I’ve lost count. This is supposed to be it, though. They just need to get approval for the price tag. I’m actually a bit bummed. I was starting to look forward to learning how to administer an Oracle database. That’s okay because I’m sure there will be other opportunities to spread mayhem…*cough* I mean, to learn other platforms…
For some reason earlier this week, I thought back to when I first became a DBA. It was sorta-kinda-not-really accidental. The company I worked for as a developer had an opening for a SQL Server DBA. At the time there were two SQL Server DBAs and one Oracle DBA. The Oracle DBA left for a job with Oracle. So the senior SQL Server DBA took over as the Oracle DBA. Which left an opening. *woo hoo!* The DBAs recommended me as having some kind of potential. *who? me?* Possibly because they let me manage my own SQL jobs and thankfully I didn’t crash the server. I waited until I became a DBA to do that. Well, not really. One of the server guys actually dubbed me “Server Crasher” because I was usually the one who reported a server that had crashed. No one told me that if you report it, you must have done something to it. So I applied for the job figuring it couldn’t hurt… this time. Besides, there was talk of steering me towards some sort of contract management position. At the time I wanted to stay technical and it sounded like fun! *What do you mean I just inherited a cluster? You mean like a cluster of nuts? hmm…that would explain all the secret squirrel meetings…*
So they interviewed me and then, lo and behold, offered me the job. *welcome to the dark side… we have cookies!* I happily accepted not really having an inkling of what I was getting myself into. *drat! no cookies!* Don’t get me wrong. I don’t regret it. In fact, I’ve enjoyed the challenges the job has posed over the years. I remember feeling very overwhelmed at first. *what the heck is merge replication and why do I care?* Although, after 5 years the feeling comes back every so often. *time to reindex the brain* At the time, I was fortunate to have two DBAs to help me get started. Not everyone is as lucky.
I know there are lots of great checklists out there on what to look for during server checks. So I then wondered if anyone else out there has or had the same problem I had when I started out. How does one check for these things efficiently if you have a lot of servers? I’m sure everyone has their own method. This one is mine. Or at least it’s part of my process. *Please bear with me, I’m getting to the point*
So I thought I’d do an experiment for this week’s blog. I thought I’d do something different and offer up my script for anyone to hack apart, criticize, try out, chew up/spit out, line the bottom of the cat box with it, or whatever floats your boat. It needs some major updating, though. I wrote it for SQL Server 2000 and then started to update it for SQL Server 2005. I haven’t implemented all the great DMVs yet so it is most definitely a work in progress. When I get to updating it, I’ll try to post the changes. It’s most definitely a work in progress.
Disclaimer: If you decide to use this script in whole or in pieces, please try it out on a development or test instance first. I’ve never had issues with it but one never knows how it’ll work on another system. Since it’s a bit long and could be a bit overwhelming for someone, I thought I’d break it out into pieces.
Still interested? Really? Seriously? Okay, then! You have been warned! 🙂 Since it’s so long, I will write separate posts on parts of it. If you’re feeling brave and want the whole enchilada, just click “sql_server_quick_check“. Otherwise, below is the listing for the next several weeks unless I get flamed for spreading crappy code all over the place. I will be the first to admit it’s not the greatest code ever. I’m not an expert. This is just my stab at it and is meant to be more of a guideline. 🙂 Depending on the feedback, I may stop and move onto something else more fun. If no one says anything good or bad, I’ll probably just continue to post my code and see how it goes.
- SQL Server error log and agent log (this week)
- System level triggers & How to check for new or missing linked servers
- Failures: Backup jobs & Database mail (SQL 2005/2008)
- Drive space, Transaction log sizes, & How many databases do you see? (I have been surprised with new mysterious databases showing up unannounced in the past or some disappearing)
- Logins: Admin logins, Logins with default database set to master, Logins created/updated in the last month (I see you!)
- Processes: What processes are running, Multiple processes with same spid, How those processes are connecting to the instance
- File growth, Databases created in the last week, & Databases not being backed up
- Jobs: Job failures, Job owners, Jobs that are currently running (i.e. jobs that suck, err… I mean could be stuck; I once had a job that ran the entire weekend because the procedure was stuck… no it didn’t suck)
1. Check your SQL Server Error Log and Agent Log (i.e. Is it me or is that an error in your log?)
When I run the script as a whole, I like to send the results to text. For me, it’s easier to read than scrolling through all the separate grids. To show the results as text, you can use the Ctrl + T keyboard shortcut, or you can puruse the menu via Query –> Results –> Results to Text. Note: This is using Management Studio.
Since I usually run the script as a whole on SQL Server 2000 and 2005 instances, I declare and set a couple of variables to check the version throughout the script. This allows me to run SQL 2000 or 2005 code as needed. I haven’t checked this against 2008 yet but I plan to. I would think it’d work on 2008 without problems, though.
declare @chvServer varchar(50), @chvVersion varchar(20) select @chvServer = convert(varchar, SERVERPROPERTY('ServerName')) select @chvVersion = convert(varchar, SERVERPROPERTY('ProductVersion')) select 'Server'=@chvServer , 'Version'=@chvVersion
Displaying the server name just confirms to me that I’m on the server that I think I’m on and that the version hasn’t changed. If it has changed, then at least I know about it and can start interrogating, err… I mean, investigating who did what to my server and why.
So when I first log onto a server (you’ll probably need admin privileges by the way), I like to check the SQL Server error logs and the agent logs. The code is different depending if you’re on SQL Server 2000 or 2005.
----SQL 2005-------- if left(@chvVersion,1) = 9 or left(@chvVersion,2) = 10 begin --Read SQL error log exec master..xp_readerrorlog 0, 1, 'error', NULL, NULL, NULL --Read agent log exec master..xp_readerrorlog 0, 2, 'error', NULL, NULL, NULL end else ----SQL 2000-------- if @chvServer = 'SQLTEST' begin --sql error log exec master..xp_readerrorlog 1,'g:\MSSQL\log\ERRORLOG','error' --agent exec master..xp_readerrorlog 1,'g:\MSSQL\LOG\SQLAGENT.OUT','error' end else if @chvServer = 'SQLTEST\PRINCESS' begin --sql server error exec master..xp_readerrorlog 1,'f:\MSSQL$PRINCESS\log\ERRORLOG','error' --agent exec master..xp_readerrorlog 1,'f:\MSSQL$PRINCESS\LOG\SQLAGENT.OUT','error' end else --check the default location begin --sql error log exec master..xp_readerrorlog 1,'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG','error' --agent exec master..xp_readerrorlog 1,'C:\Program Files\Microsoft SQL Server\MSSQL\log\SQLAGENT.OUT','error' end
Parameters for SQL Server 2000:
- Indicates which error log number to read with “0” being the current log. I included “1” as an example.
- The file name.
- The line number.
- A string value to search for.
Parameters for SQL Server 2005:
- Indicates which error log number to read with “0” being the current log.
- Tells SQL Server which log to read. For the error log, use 1. For the agent log, use 2.
- A string value to search for.
- Another string value to search for.
Note: xp_readerrorlog is an undocumented procedure. However, there is a lot of good info on it out there. I could probably do an entire blog post on reading the logs alone but for this purpose, I’m just listing the code I use. I usually search for more than the word “error” but for the sake of brevity, I just put a few examples here.
Depending on feedback, if any, next week I’ll show you more of my code and why you might want to check for system level triggers and checking for new or missing linked servers. If you don’t like where I’m headed, just let me know. 🙂
Next in the series: Ready, Aim, Fire That Trigger!