Disabled jobs run automatically in SQL Server 2005/2008.

A common myth  is that there is a bug in SQL Server 2005/2008 that a disabled job will run automatically in  SQL Server unless its schedule is disabled. I was also thinking in the same way  till today this is when I decided to test the same.

The first step was to create a SQL Server Test job that executes sp_who2 in its step. The schedule of the job was run daily every minute. As usual job was running every minute.

Now decided to update the status of job in sysjobs  table in msdb.I disabled it using the query

update sysjobs set enabled=0 where name='Test_SQLBugJob'

The job was disabled as shown in the screenshot.

But to when I noted the job history what I found was the job was still executing.

The next thing to do was to update sysjobactivity table since it has a column next_scheduled_run_date so if I can make it null probably the job won’t execute.The query  given below was executed.

update sysjobactivity set next_scheduled_run_date=null where job_id=(select job_id from sysjobs where name='Test_SQLBugJob')

But still after the job was executing as per schedule. Though the next_scheduled_run_date was null but the job will continue running and  once its executed it will update the next_scheduled_run_date column with a new value.

Decided to give a try with SQL Server Management Studio. When disabled through SQL Server management studio things were good and the job never ran. Now what  commands does SQL Server management studio executes.

Started a profiler and captured the commands. Found that Management studio executes a command

sp_update_job.This stored proc in turn executes  another stored procedure sp_sqlagent_notify

which again executes an extended stored procedure xp_sqlagent_notify which is in SQLServer library file xpstar.dll.

SQLServer agent caches the data in memory.Unless  sp_aqlagent_notify is executed the cache is not refreshed.When you  use SSMS then the command gets executed and SQL Server Agent refreshes its cache will the new setting.

One of my friend  pointed out that the bug is logged in Microsoft lab

https://connect.microsoft.com/SQLServer/feedback/details/552707/job-disabled-and-schedule-enabled#details

But if the workaround is to restart the SQL Server agent which means its refreshing the cache.So the point here is if you want to disable a SQL Server agent job never execute plain TSQL update statement.Either you use SSMS or execute the stored proc sp_update_job.

 

SQL Server could not spawn FRunCM thread

Today got an annoying error with SQL Server 2008.The error when trying to start SQL Server service was

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

This error was logged in application log of Windows Event viewer.On checking the Widows system log the error was

The SQL Server (SEREENASQL2008) service terminated with service-specific error %%-2146885628.

Tried changing the startup account to LocalSystem account but it didn't help to fix the issue.

The error logged in both windows event viewer log also was not quite helpful.

At this stage I decided to check SQL Server error log which was in the path C:\Program Files\Microsoft SQL Server\MSSQL10.SEREENASQL2008\MSSQL\Log.

The SQL Server error log has an entry like

2011-02-18 22:57:43.43 spid15s     Clearing tempdb database.
2011-02-18 22:57:43.62 Server      Error: 26014, Severity: 16, State: 1.
2011-02-18 22:57:43.62 Server      Unable to load user-specified certificate [Cert Hash(sha1) "BCF96EB3346FFC3580D3D833B7C39D1021D88152"]. The server will not accept a connection. You should verify that the certificate is correctly installed. See "Configuring Certificate for Use by SSL" in Books Online.
2011-02-18 22:57:43.62 Server      Error: 17182, Severity: 16, State: 1.
2011-02-18 22:57:43.62 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

Now this was interesting. I remember some two weeks back created a self signed certificate with IIS in my laptop and then installing it. The same certificate was used to enable SSL over IIS and SQL Server.Please note I have IIS and SQL Server in my laptop.I also remember deleting this certificate last week.So concluded that there is some issue with certificate generated by SQL Server.

From registry editor went to the path

HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\MS$SQLINSTANCE_NAME\MSMSQLSERVER\SUPERSOCKETNETLIB

Deleted the value in Certificate key and then restarted the SQL Server service. Whoop it fixed the issue.

Read SQL Server Trace file using C# in VS 2010

DBAs love using the function fn_trace_gettable  to read  contents from a  trace file . Just pass the path of the trace file to this function and it will show the content of trace file.But what if developers want to read the same using .NET framework.You  can’t have the luxury of having SQL Server installed in every machine so that you can execute fn_trace_gettable. This function can’t be executed remotely using a linked server.

First thing what you need to do is open VS 2010 and give reference to SQL Server SMO  assembly Microsoft.SqlServer.ConnectionInfoExtended.dll and Microsoft.SqlServer.ConnectionInfo.dll

Once you have reference to these dll’s in your project then you can use  an object of  TraceFile class to read the content of TraceFile.

//Reads the trace file 

TraceFile trcFile = new TraceFile();
 
trcFile.InitializeAsReader(strFileName);

while (trcFile.Read())
{                   
     string strTextData = trcFile.GetString(trcFile.GetOrdinal("TextData")).ToString();

     string  strApplication=trcFile.GetString(trcFile.GetOrdinal("ApplicationName")).ToString();

}   

trcFile.Close();

But before reading these values you may need to check if these columns are null by using the code

trcFile.IsDBNull(trcFile.GetOrdinal("TextData"));

Once you have these values you can load it into a SQL Server remote database and then create reports based on these values.Please don’t forget to include Microsoft.SqlServer.Management.Trace namespace in your code.