Bertrand Rohrbock

Detecting if DTC is running

BertrandR — Fri, 14 Mar 2008 10:35:00 GMT

I've recently worked on a fix for one of our jobs that starts when the SQL agent is started and proceeds to contact a central server to report the new node the instance is running on. The problem is that sometimes the MSDTC isn't running when SQL agent starts and the job reports a failure because it can't write data over a linked server when the MSDTC isn't running. The following code detects if DTC is running, I use it in a while loop to wait for the DTC. The code is cluster compaitible.

SET NOCOUNT ON

DECLARE @DTC_Found INT

CREATE TABLE #output (line NVARCHAR(4000))

INSERT INTO #output
EXEC master..xp_cmdshell N'net start'

SELECT @DTC_Found = COUNT(1) FROM #output WHERE [line] LIKE '%Distributed Transaction Coordinator%'

IF @DTC_Found < 1
BEGIN
    TRUNCATE TABLE #output
    INSERT INTO #output
    EXEC master..xp_cmdshell N'cluster res'
    SELECT @DTC_Found = COUNT(1) FROM #output WHERE [line] LIKE '%MSDTC%Online%'
END

DROP TABLE #output

SELECT @DTC_Found

Problems starting the SQL Backup Agent service

BertrandR — Wed, 09 Jan 2008 07:57:00 GMT

Sometimes one or more of our backup proceses hang and when this happens the SQL Backup Agent service needs to be restarted in addition to the process being killed on the server. However for some reason (my thesis is that this happens when you do not kill the process on the SQL Server first) sometimes the SQL Backup Agent won't start giving error event 5000.

The following error message is displayed in the event viewer:

SQL Backup Agent startup error: error initialising IPC objects. Error creating mutex (already exists): Global\SQBMutex_<instancename>

Because I have a programming background I know what a mutex is, but if you don't you can find a global description on wikipedia.

My initial thoughts were that SQL Backup Pro was somehow keeping a handle on a mutex or that it didn't clean up properly. So I deinstalled SQL Backup Pro and reinstalled it, but got the same error. I was able to delete all files and there wasn't any SQB process on the server that could have held those handles.

Using Proces Explorer I found out it was actually SQL Server that was keeping the handle on the mutex so in order to be able to start the SQL Backup Agent you have to restart SQL Server.

Moving datafiles, the easy way

BertrandR — Mon, 19 Nov 2007 09:39:00 GMT

Sunday morning (I love maintenance windows) I had to move some datafiles that were placed incorrectly. I thought this would be an easy job. Detach, move the files and reattach, right ? Wrong.

It appears that SQL Server won't release handles on the datafiles until you put the databases in offline mode. However when you reattach a database that you previously put offline and detached you get an error message (it will attach the database though) and the database is in read-only mode.

Even worse for me was that I was able to move datafiles of an offline database that I hadn't detached (it was 2am I guess I wasn't so clear). This resulted in weird behaviour namely OS errors when trying to reattach the files. I was already getting visions of restoring the lastest backup and all transaction logs, but my last attempt at recovering the databases worked.

I finally ended up moving all the files back, putting the database in read-only mode (to prevent missing any changes to the data), creating a backup and restoring the backup with MOVE TO parameters and then turning off read-only mode, which is actually much easier and faster to do.

Restoring MSDB across instances, a bad idea

BertrandR — Wed, 14 Nov 2007 08:22:00 GMT

We've recently been migrating instances of SQL Server between different clusters in order to dismantle an old cluster. To move the jobs we've been copying the MSDB from the old instance to the new one by performing a restore and we didn't run into any problems ... until yesterday. A job that used CmdExec instead of a T-SQL jobstep wouldn't run, put the job in suspended state and give the following error:

Unable to start execution of step 1 (reason: The CmdExec subsystem failed to load [see the SQLAGENT.OUT file for details]; The job has been suspended). The step failed.

Checking the agent log provided the following error message:

Step 1 of job '<jobname>' (<job_id>) cannot be run because the CmdExec subsystem failed to load. The job has been suspended

After further examination of the agent log I found the following startup errors:

Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found)
Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)
Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found)
Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found)
Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found)
Subsystem 'Merge' could not be loaded (reason: The specified module could not be found)
Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found)
Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found)
Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found)
Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found)

As it turns out, SQL agent stores a path name to the DLL files required to perform the non T-SQL jobsteps in the MSDB. This is documented here: Microsoft KB 903205

Moving (reinstalling) the server caused the directory of these DLL's to change (in this case from MSSQL.6 to MSSQL.12) and the agent was unable to load them. As we rarely use any non T-SQL jobsteps we hadn't run into this problem yet.

The solution was performing an update to the msdb.dbo.syssubsystems table updating the values of subsystem_dll column to the current situation and restarting the agent.

EDIT: It appears only SQL 2005 is affected, SQL 2000 doesn't store these references in the MSDB. I've compiled a script to fix wrong references:

SET NOCOUNT ON
DECLARE @SQLRoot NVARCHAR(512)

IF @@VERSION LIKE ('% 9.%')
BEGIN
EXEC master.dbo.xp_instance_regread
N'HKEY_LOCAL_MACHINE'
      ,N'SOFTWARE\Microsoft\MSSQLServer\Setup'
      ,N'SQLPath', @SQLRoot OUTPUT

    UPDATE msdb.dbo.syssubsystems
    SET subsystem_dll = @SQLRoot+RIGHT(subsystem_dll,LEN(subsystem_dll)-CHARINDEX('\MSSQL\binn\',subsystem_dll)-5)
    WHERE
      subsystem_dll NOT LIKE @SQLRoot + '%'
      AND subsystem_dll <> '[Internal]'

IF @@ROWCOUNT > 0
    BEGIN
      PRINT 'Bad references found on '+@@SERVERNAME
      PRINT 'Server root: '+@SQLRoot
    END

END

Reporting version number on SQL Backup Pro

BertrandR — Fri, 26 Oct 2007 11:51:00 GMT

Just a quick update to share a piece of handy information. Red Gate does not document it in their help files, but there is a way to get the version number of the Red Gate DLL used to create backups with T-SQL with the following query:

DECLARE @version nvarchar(32)
DECLARE @edition int

exec master..sqbutility 21, @edition OUTPUT, @version OUTPUT

SELECT @version

Quite handy if you're doing a survey on what versions of SQB are in use in your SQL environment.

Reading the SQL errorlog

BertrandR — Thu, 25 Oct 2007 07:56:00 GMT

Yesterday I was doing an analysis of the SQL errorlog in response to a cluster node that had been crashing frequently.

First I noticed there's a rather annoying bug in SQL Server Management Studio: if you press the filter button before all the log files are read (if you select more then one) you will only filter based on the part of the log that's already read by the Management Studio. I saw huge holes in my log files with entire days missing and thought we might have a serious problem until I discovered that data I had read previously was now gone.

Besides this annoying bug it's rather painful to examine the tabular data of the errorlog window by hand and I was wondering if there were some way I could do this in SQL. I looked in the SQL help, but couldn't find anything to help me except a function in SQL-DMO I was unable to call from T-SQL. Today Google helped me out and provided me with the solution: sp_readerrorlog.

This stored procedure is nicely documented at http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm and it appears that you can read any text file that SQL Server can access into a table with this stored procedure, which could come in handy some other time.

Object references and SQL

BertrandR — Tue, 25 Sep 2007 12:04:00 GMT

While it might seem strange or even impossible to work with objects in SQL it is possible. I stumbled upon this recently while I was investigating a way to automatically drop and recreate indexes on a SQL 2000 server.

DECLARE @object INT
EXEC sp_OACreate 'SQLDMO.SQLServer', @object OUT
EXEC sp_OASetProperty @object, 'LoginSecure', TRUE

With this statement an object is created and the reference to it is stored as an integer in @object. We also set the LoginSecure property to true. We can get and set properties and call methods of the object with the sp_OA..... functions

SET @CmdStr = 'Connect('+@@SERVERNAME+')'
EXEC sp_OAMethod @object,@CmdStr

With this statement the Connect function on the object is called with the servername of the server you're running the script on as the parameter. After connecting it's possible to approach objects on the SQL server, this might not seem very usefull yet as SQL is the language to approach objects on a database server. However it is usefull in getting object definitions.

SET @CmdStr = 'Databases("testdatabase").Tables("table1").Indexes("index1").Script(5)'
EXEC sp_OAMethod @object, @CmdStr

Now it gets a little more interesting, we approach the object named index1 in the collection indexes in the collection of tables in the collection of databases and call the Script() method on it. The number 5 is a bitpattern that indicates the objects and the deletion of the object if it exists need to be scripted.

I wish I had some more time to dig into this, I am sure there are more gems to be found in this mechanism.

Writing better SQL scripts

BertrandR — Thu, 13 Sep 2007 12:19:00 GMT

Many organisations that have a platform for application hosting that includes different environments for development, testing and production use SQL scripts to migrate between environments. As a DBA I see a lot of these scripts and a lot of them are written very poorly while others (usually the generated ones) are small pieces of art.

The qualitity of scripts makes a huge difference in the time DBA's spend executing these scripts and as a result of that it influences the timeframe in which a script will be ran.

So, how to produce good scripts ? I'll give a few tips.

Note that code samples are all SQL 2000 compaitible. If your database uses SQL 2005 see the comment under 'Scripting other objects' on how to find out how to properly script your object(s) for that version.

Use a USE statement

A good script starts with a USE statement, this way you're sure your script will be executed in the right database. The reason for this is simple: it prevents your mutations from happening to the wrong database (usually the master database ;) ). Another good practise is to set NOCOUNT to ON so the messages window isn't filled with 'rows affected' messages.

SET NOCOUNT ON

USE [<database name>]

Scripting Stored Procedures

The most important thing with writing good scripts is that your script is repeatable on every environment no matter what version of the database it runs. In my experience most changes to a database will be to stored procedures. The easiest way to ensure the stored procedure code you provide will end up being the new code is to use drop and create procedure statements, never use alter statements (these won't run if the procedure does not exist for some reason).

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = OBJECT_ID(N'<stored procedure name>') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE <stored procedure name>
GO

CREATE PROCEDURE <stored procedure name>
<parameters>
AS
BEGIN
......
END

It's also a good practise to use BEGIN and END statement combined with indentation so it's obvious where your procedure starts and ends in case there's multiple stored procedures in scripts.

Scripting other objects

If you're curious how to properly script other objects you can check easily by setting the 'Include IF NOT EXISTS clause' option (Options->Scripting) in SQL Server Management Studio to TRUE and right click an object of the type and select a Script ... as -> CREATE To option. There's one important exception to the 'don't alter, but drop if exists before you create' guideline and that's the TABLE object. Your tables hold data, you probaly do not want this data lost.

Use transactions

You probably want your update to succeeded as a whole or not at all. Using repeatable scripting will make it less important, but you still don't want your system to be in some kind of version between version X and Y, you want it version X or Y. The solution is simple and native to a DBMS: use transactions ! If you're using SQL 2005 it's very easy to implement with TRY.. CATCH ROLLBACK constructions if you're using SQL 2000 test the results of your operations with @@ERROR and end operation with a GOTO rollback block statement.

Indentation and comments

Make your scripts readable by humans and not just for a SQL parser. Use comments so it's obvious what is happening and use proper indentation.

Finally if you want to show off....

Give status updates using PRINT messages. Provide information on what objects were created,deleted or altered and give numbers on the affected records of insertions, deletions or updates using @@ROWCOUNT. It's very helpful if something goes wrong, which won't happen because you've tested your scripts thoroughly........ right ?

Installing multiple instances of SQL server without rebooting

BertrandR — Tue, 11 Sep 2007 07:09:00 GMT

Figured I'd make my first post not just an introduction, but also give some usefull information on SQL cluster installations that isn't documented very well.

Let's start with the introduction. I am Bertrand Rohrbock, 25 years old and working for Info Support as a DBA (Database Administrator) with a large insurance company in the Netherlands. I am in the team that runs one of the largest if not the largest SQL server farm in the Netherlands with nearly 200 instances serving over 3900 databases. My daily work varies from monitoring the status and performance of our servers and designing and implementing structural fixes to working with internal clients to make sure their solutions run well on our platform.

We currently run both SQL 2000 and SQL 2005 instances on our platform and are in the (slow) proces of migrating everything to SQL 2005. However as the release date of SQL 2008 is coming up pretty fast a lot of projects will skip the 2005 release and migrate directly to SQL 2008. Another product I work a lot with is Red Gate SQL Backup Pro and as we have some problems with this product and my colleagues like to dump all of them on me I'm likely to write about it some.

Beside all the technical aspects of the DBA life ( it's not a job, it's a way of life ;) ) there's a lot of proces improvement going on as the organisation moves from CMM level 2 to 3. It's increasingly becoming a part of my job to make sure the proces is done right and as with all change this sometimes leads to frustrations for me or our clients which I might elaborate on occasionally on this blog.

Now for the usefull information: During the installation of two new instances on one of our production clusters the other day I ran into a problem. I am not sure if this problem exists in one form or the other with normal (non-clustered) installations !

SQL Server 2005 wants you to reboot the system it's installed on after installation, but I did not have this option at the time (even though the installation was during a maintenance window) and it works just fine without the reboot. However the installation puts the system in a 'reboot pending' state which is detected when installing a new instance. The setup gives a nice red cross at installation ( Pending Reboot Requirement ), but still gives the option to move on. However at the point in the installation where the nodes are selected the nodes on which another instance has been installed will be in the unavailable listbox and give the error 'a scc check failed on the node'.

The sollution is to delete (or rename if you're uncomfortable with deleting) the following registry key on both nodes and log out:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

It's a good idea to do this after every installation even after installing a single instance. The pending reboot state does not only affect SQL Server, but the entire system, there can be problems with rolling out security patches for example. Obviously it's best to reboot the system after you're done installing, but this option is not always available. It should be best practise to leave the system in the state it was in before the installation.