Category: Administration

Angela Henry has a script to tame percent growth on database files:

I decided I needed to do something else other than just send an email notification, I needed to take corrective action when it occurred.  So I wrote a little stored procedure that will take the ALTER DATABASE statement as a parameter, parse it and take the appropriate corrective action. 

Simple enough, right?  Now I just need to add the call to my newly created stored procedure in my server level trigger and we are good to go.  But wait, you can’t ALTER a database within an ALTER DATABASE statement (don’t believe me? Use this as a learning exercise to see what happens when you try).  So what could I do?  There are several things you could do, but I chose to create a table that could hold this newly created ALTER DATABASE statement and insert the record there.  Then I created a SQL Agent job that runs once every hour and reads that table and executes any entries it finds, then deletes them after successfully executing.

Read the whole thing, including the disclaimer.

The CSS SQL Server Engineers have a guide for solving connectivity issues:

In addition to providing a quick checklist of items that you can go through, the doc provides step by step troubleshooting procedures for the following error messages:

• A network-related or instance-specific error occurred while establishing a connection to SQL Server

• No connection could be made because the target machine actively refused it

• SQL Server does not exist or access denied

• PivotTable Operation Failed: We cannot locate a server to load the workbook Data Model

• Cannot generate SSPI context

• Login failed for user

• Timeout Expired

• The timeout period elapsed prior to obtaining a connection from the pool

Click through for the guide.  It’s in choose-your-own-adventure format, though without nice graphics.

Dimitri Furman explains that database availability is a trickier problem than it may first appear:

To check the availability of the database, the application executes the spCheckDbAvailability stored procedure. This starts a transaction, inserts a row into the AvailabilityCheck table, flushes the data to the transaction log to ensure that the write is persisted to disk even if delayed durability is enabled, explicitly reads the inserted row, and then rolls back the transaction, to avoid accumulating unnecessary synthetic transaction data in the database. The database is available if the stored procedure completes successfully, and returns a single row with the value 1 in the single column.

Note that an execution of sp_flush_log procedure is scoped to the entire database. Executing this stored procedure will flush log buffers for all sessions that are currently writing to the database and have uncommitted transactions, or are running with delayed durability enabled and have committed transactions not yet flushed to storage. The assumption here is that the availability check is executed relatively infrequently, e.g. every 30-60 seconds, therefore the potential performance impact from an occasional extra log flush is minimal.

This ends up being much more useful than a simple “is the service on?” heartbeat, as it shows that the database is available, not just that the engine is running.

Jen McCown has a script to compare column names between tables to find case inconsistencies:

I’m reviewing the code for the upcoming Minion CheckDB, and one of the things we’re checking for is case consistency in column names. For example, if Table1 has a column named Col1, and Table2 has COL1, well that’s no good.

But, how do we easily find those mismatches on a system that’s not case sensitive? Easy: collations.

Click through for the script.

Lonny Niederstadt has a half-baked idea on dead connections:

In SQL Server 2005, a valuable addition was made.  SQL Server would use a “Keep Alive” value specified specifically per instance, rather than the KeepAliveTime specified by the Windows registry.  It defaulted to 30000 ms/30 seconds.

The blog post below explains this new addition, and mentions that the interval for SQL Server will be a fixed 1000 ms/1 second, regardless of the KeepAliveInterval specified in the Windows registry. At the time SQL Server 2005 was introduced, TCPMaxDataRetransmissions from the Windows registries still controlled the maximum number of probes.

Read on for more.