Category: Administration

Kenneth Fisher describes startup stored procedures:

sp_procoption is a system stored procedure that lets us change the options on a stored procedure and in this case set it to run on startup. Note: The account that is running SQL Server needs to have permissions to start the Agent service. (Or do whatever your startup stored procedure does.) You can have as many stored procedures running on startup as you want but remember the more you have the longer it’s going to take for your instance to start.

There are a few uses cases in which startup stored procedures can be useful, but my reservation about them is similar to my reservation about triggers:  it’s not apparent to people that a startup stored procedure is in place, so if there is a problem with it, troubleshooting might be harder than normal without good documentation.

Aaron Bertrand looks at converting an identity integer into an identity bigint:

This is a very disruptive change to the structure of the table, obviously. (And an interesting side observation: the physical order of the columns, RowID and filler, have been flipped on the page.) Reserved space jumps from 136 KB to 264 KB, and average fragmentation bumps up modestly from 33.3% to 40%. This space does not get recovered by a rebuild, online or not, or a reorg, and – as we’ll see shortly – this is not because the table is too small to benefit.

Note: this is true even in the most recent builds of SQL Server 2016 – while more and more operations like this have been improved to become metadata-only operations in modern versions, this one hasn’t been fixed yet, though clearly it could be – again, especially in the case where the column is an IDENTITY column, which can’t be updated by definition.

Read the whole thing.  The clustered key scenario (which will be later in the series) is a bit more interesting to me, as that would be a more common use case for identity values.

Robert Sheldon has an introductory-level article on the various system views available within SQL Server:

System views are divided into categories that each serve a specific purpose. The most extensive category is the one that contains catalog views. Catalog views let you retrieve information about a wide range of system and database components—from table columns and data types to server-wide configurations.

Information schema views are similar to some of the catalog views in that they provide access to metadata that describes database objects such as tables, columns, domains, and check constraints. However, information schema views conform to the ANSI standard, whereas catalog views are specific to SQL Server.

In contrast to either of these types of views, dynamic management views return server state data that can be used to monitor and fine-tune a SQL Server instance and its databases. Like catalog views, dynamic management views are specific to SQL Server.

One of the best things the authors of SQL did was require that metadata management be in the same language:  you write SQL code to query metadata the same as if it were normal data.

Steve Jones explains database snapshots:

I’ve rarely dealt with database snapshots, outside of lab experimentation. They didn’t exist when I did most of my DBA work, and since then we haven’t seen the need for them at SQLServerCentral, though, I may suggest we add them to our deployment process since we can quickly roll back if something breaks.

However, I created one recently for a test and realized that I’d forgotten the syntax. Unlike the quick “create database xx” syntax I often use, with a snapshot I need to be more specific.

Word of warning:  don’t have more than one active snapshot of a single database.  If you do, you’ll likely have major performance problems.  My favorite use case for snapshots was building some semi-automated integration tests a few years back.  I created a tool for devs to create snapshots, and then they could run all the tests they wanted and revert the snapshot afterward.  There are some good uses in production environments as well.

Michael J. Swart has an Extended Event and a query to help monitor deadlocks:

Guess what? Apparently I “reinvented the wheel”. The extended events session I created is equivalent to one that Jeremiah Peschka wrote two years ago in Finding Blocked Processes and Deadlocks using SQL Server Extended Events. The embarrassing thing is that in Jeremiah’s article, he references a tool I wrote. And the first comment was written by yours truly.

There are a bunch of ways to capture deadlock information.  This is a good one.

Kenneth Fisher shows us how to start an instance in safe mode in case the drive hosting tempdb gets fried:

The situation: Your server is down. The drive/directory where tempdb is supposed to be doesn’t exist. Who knows why. Maybe those evil SAN guys forgot to re-attach your storage during a DR situation. You may or may not realize it but SQL Server will not start without tempdb. Which is fine. Just move it to a location that exists right? Well, yes. That is an important step. So here is how we

Move tempdb

I like the way Russ Thomas (and Kenneth Fisher) put it:  this is a low-occurrence, high-liability issue.