The Multifunctioning DBA

Jan 22 2010   1:12PM GMT

Health Check into SQL Server

Colin Smith Colin Smith Profile: Colin Smith

I have recently posted about my new job and how I am walking into the unknown. Like I have said in the past, I did get a list of Host Names that are running SQL Server. I have written a script to go out and look at each of those hosts and get me all instances. I have put together a list of about 150 SQL Servers that are running but I still no very little about. I have also posted about a Powershell script that I have written that goes out and gathers information about the SQL Server instances in question. I ran this and I was shocked. After glancing at the output I see things that just should not be. I see many DB’s that have logs larger than the Database. I see many that are running from the system drive, I see some that are far behind on patching, I even see one with only system databases on it. Like I said I have about 150 identified running instances and just over 1000 databases. You read that correctly over 1000. I just really do not even know where to start.

Because I have so many servers and so many databases, I thought that a simple Excel Spreadsheet is just not going to do it for me this time. I need more than just a simple report. I want to put all this data in a database so that I can slice and dice the data more easily, when I finally figure out how to prioritize what needs to be done. So here is what I have so far. I will use the same Powershell script to go get all the data that I am after and then I will have it insert the data into the database. This is just the beginning for this database as I have some bigger plans brewing in my head now. For now though, I just need something quick and basic to get the data into so I can prioritize and identify the largest problem areas.

I have two tables consisting of the following:

Instance Table Database Table
ID          PK I_ID               FK to I.ID
Host_Name DB_Name
Instance_Name Recovery_Model
SQL_Version Data_Disk
SQL_SP Log_Disk
OS_Version Database _Size
  Type (Test or Prod)

I think that this will give me the ability to look at the basics and identify major problems. As I get things taken care of I will then start to add more to this Database so that I can do some more tunning, baselining, trending, and so forth.


Got any thoughts on what I should add to this initially. I am all ears.

3  Comments on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.
  • KansasCoder
    Hi Colin, Sounds like your new position is going to be alot of fun! I am an Accidental DBA with only 4 smallish databases and I'm finding so much stuff to learn about. The nice thing is the dbs have been running for several years without any improvements so the key, for me anyway, is to make changes very very carefully. That is after reading as much as possible about the change. Everything we need to know is based on our installation and business models so there's really no one size fits all solution as there can be with code development. It's all pretty exciting. I set up a directory under My Documents called DB_DBA. In it I keep my excel reports and my scripts as well as the output of my performance monitoring. If I were you I'd start running basic, low impact performance monitoring. I use Windows Performance Montitor. I set mycounters to sample every 15 seconds and I run them Monday throgh Monday..7 days. 24 hours a day. I save them to a csv file. Google on "PAL v1.3.5" and download this gem of a tool. Load your perfmon csv files into this tool and review the output. Save these as your baseline reports and don't overwrite from copies. The PAL report will point out any glaring issues. Don't rush to resolve anything because honestly there are many reasons why, for example, the Disk IO is slow. I discovered this issue last week. This week I am running a performance monitor log on just the disk counters (7 full days 24x7) in order to have some better insight into what is actually happening. The other thing you want to find out is the growth over time of your databases. I use this t-sql and plug in my dbnames (only 4 so it works for me) to trakc the backup sizes over the life of my dbs: [CODE]select BackupDate = convert(varchar(10),backup_start_date, 111), --SizeInGigs=floor( backup_size/1024000000), SizeInMegs=floor( backup_size/1024000) from msdb..backupset where database_name = '' and type = 'd' order by backup_start_date desc[/CODE] I store this in an excel file and its useful for determining when a db may need a larger disk. I use this script to get a snapshot about the tables in my dbs: [CODE] create table #tmp( [name] varchar(100), [rows] int, [reserved] varchar(20), [data] varchar(20), [index size] varchar(20), [unused] varchar(20) ) insert into #tmp exec sp_msforeachtable' exec sp_spaceused ''?''' select * from #tmp order by rows desc [/CODE] The other thing I run weekly is DBCC SQLPERF(LOGSPACE) WITH no_infomsgs; To see if I'm in danger of having an outage due to a logfile thats overgrown its pen. its all pretty fun and a nice departure from my usual code writing. Good luck with the new job!
    40 pointsBadges:
  • KansasCoder
    Here is some more good informaiton about performance monitoring: Good overview of the counters and which should be viewed/ what they mean. Also a good piece on baselining your servers!
    40 pointsBadges:
  • Colin Smith
    Hey KansasCoder, Thanks a lot and I agree somuch information but it is a blast to learn. I appreciate all your insights here. Currently I have completed my PowerShell script and I m pulling information from each Instance and Each Database and putting that into my Database. From here I will be able to track growth as well as some other important things. My main goal, right now, is to identify any glaring problems in the basics. Logs and Data on the same spindle, Databases in the incorrect recovery Model, no Backups running, behind on SP, things of that nature. Then I will be starting a lot of base-lining. I will defenatly check out that tool, but I think if I can save the perfmon output to CSV then I think I will move that content into a database for historical tracking as well. Thanks again.
    945 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: