A basic approach to SQL Server administration

Menu

Tag Archives: collation

This script looks much more intimidating than the results it produces, so I recommend running it before reading through it. I’ll run this when I first get on a unfamiliar server as a basic assessment of what’s there and find some hard to find issues with no effort.

The issues you’ll find include:

DBCC CheckDB – When is the last time this was successfully run. You need to know this, but it’s not as easy to find as it should be. Personally, I feel it should be in sys.databases, but I’ll settle for having this script saved to find it for me.

Backups – Where are they, when did they last occur, how big, etc.

I always look for databases (excluding model) that are in full or bulk logged recovery with no log backups. This is a common issue, especially with vendor databases, that will fill your drives with data you didn’t care to have.

To know how critical it is when you find this, and you probably will, I include the log size and percent full.

There are variables in this script for a cutoff date which will ignore old backups. If you set @use_cutoffdate to 1 then any database not backed up since the value of @cutoffdate will show up as never being backed up. I love this feature because there’s no chance that you’ll misread a year-old backup as being taken last night.

Warning, the size of the backup is the size of the data, not the backup file. To keep this compatible with SQL 2005, I’m not grabbing the compressed_backup_size field.

Differential backup results are commented out for a reason…most people don’t use them. There is absolutely no reason to comment this out other than limiting the number of columns in my results.

Database Size – Both allocated and used space. It’s best that you store this and trend the data somewhere, such as on my post Monitoring Database and Table Sizes, but having a static value is a start.

Compatibility Level – If you upgrade a server it’s easy to leave a database behind. I’ve seen SQL 2000 compatibility on SQL 2008 R2 for no other reason than “oops”.

Collation name – If this isn’t consistent you could run into issues that are next to impossible to debug. The exceptions, ironically, are Microsoft databases. Report Server and SharePoint databases specifically will be a different collation than anything else, but they were written with that in mind and handle it gracefully and shouldn’t be changed.

Auto Close – Ok, I admit it, I actually have this one commented out in my personal copy of this code. If this is turned on it will typically blow up your error logs and make them half-way unreadable, and I’ll see this when I attempt to read them. However, it’s good to know if this is ever turned on as it will kill performance. I should uncomment this in my version with the mentality that too much information will slightly annoy you, too little will kill you.

Especially with most of us running more vendor databases than home-grown, don’t trust that this stuff was done right.

Rate this:

Post navigation

Consulting

I am now working for Plus Consulting on a team that provides database performance, business intelligence, and database administration consulting services. In the past there has been requests for me to help during business hours that I had to decline, and I'm glad to say that's no longer the case.

Category Pages

I wrote a lot about these categories. Since I had trouble finding my own posts, I organized them and wrote a quick sentence or two about each one for my personal use. Then I decided I should share these pages, too.

Two pieces of advice:

Give thanks

If you benefit from something, say something. Also, if you would speak up if something wasn't done right, you should do the same if it is done right. While saying something is easy to do, for some reason it's more difficult to follow through with than it should be.

I'm personally working on this. Specifically, if I'd visit someone's cube if they didn't resolve a ticket as quick as I expected then I'll visit their cube to say thank you if it's done quicker or better than expected.

Give back

My career benefited greatly from the work others made public, so I thought I should do the same...it was a selfless concept. Then I realized how much I learned by putting my knowledge together in posts and presentations. I have yet to write a post, prep a presentation, deliver a presentation where I didn't learn more, even on topics I thought I knew well enough to just do everything off the top of my head.