Category: Administration

Andrea Allred hits identity integer absolute zero:

Recently we had a system go down because we ran out of integers. (Mental note to create an alert when we are getting near to running out of integers.) If we upped the column to a bigint we were going to have to drop all the indexes. This server doesn’t have the capacity needed to do an operation of that size and it was estimated that it would be down for 8 hours while we dropped indexes, upped the column type and added back the indexes. This was way too long as it was early in the work day (had it been evening, it would have been fine).

There was also a concern about how many stored procedures were expecting an int but would need to be modified to a bigint, along with any code. That was a big undertaking and we were in an emergency down.

This reseeding typically works well, though it’s important to know if the code will fail upon getting negative numbers.

David Fowler looks for the biggest, meanest database on a SQL Server instance:

I’m sure we’ve all been there at some point, sometimes it’s easy to tell. We might only have a handful of databases on the server with one known to be the heaviest utilised. But sometimes things might not be so obvious, there could be a large numbers of databases or no obvious resource hog.

In those instances we need some way to figure out what how much time each database is spending on the CPU if that’s what we’re interested in or perhaps the total number of page reads or writes if IO is our problem.

Read on for one way to do this, assuming that the instance has been up long enough to give you reliable results.