Querying the Default Trace File

  It has always been a bit of a pain having to open up a SQL Server trace file with SQL Server Profiler and try to find the information I am seeking. It's true that the trace can be saved to a table and queried, but this method is SO EASY. It is also great for auditing if you do not have SQL Server 2008 R2 Enterprise installed (We run SQL Server 2008 R2 Standard here at work). 

  There are several ways to run this query. Here is one that I am currently using in production to track any configuration changes:

DECLARE @TRC_PATH VARCHAR(500)

SELECT @TRC_PATH = CONVERT(VARCHAR(500), value) 
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2 

SELECT TEXTData, HostName, ApplicationName, DatabaseName, LoginName, SPID, StartTime
  , EventSequence
FROM fn_trace_gettable(@TRC_PATH,1) fn
WHERE TEXTData LIKE '%configure%'
AND SPID<>@@spid
ORDER BY StartTime DESC

  Of course you can change things around to suit your own needs - different fields in the SELECT, change the conditions of the WHERE clause, etc.

FYI, I got this info from this great, great article available at mssqltips.com: http://www.mssqltips.com/sqlservertip/2364/capturing-and-alerting-on-sql-server-configuration-changes/

Disable All Enabled Jobs and Re-enable Them

  I recently came across a situation where I was working with a vendor that was upgrading one of our applications. He asked me to backup the production database and disable all jobs on the production SQL Server. I could just go through the SSMS GUI and do all of this, but I like to automate anything I can. It keeps me on my toes and (most of the time) makes it to where I don't have to be remoted in or at my desk if something needs to happen during off hours. 

  Simply disabling all of the jobs should be pretty easy, right? Jobs in SQL Server are stored in the msdb database in the sysjobs table. Since I want to disable all the jobs, I need to change the [enabled] tinyint field in the sysjobs table and change it to 0 (0 for disable, 1 for enable). Easy peasy:

USE [msdb]

UPDATE sysjobs
SET [enabled] = 0

However, there is one small problem with this. I this particular situation, there are 15 jobs on this SQL Server and only 13 of them are enable. There are 2 disabled jobs on this server, but we do use those jobs from time to time, so I don't want to delete them. The jobs will need to be re-enabled when the vendor finishes the work, but if I simply run:

USE [msdb]

UPDATE sysjobs
SET [enabled] = 1

that enables ALL of the jobs, including the 2 that need to stay disabled. So here is how I got around it. I decided to create a table and store the job_id (the unique identifier for the job in the sysjobs table) for each enabled job in it. I can use our good friend, the SELECT INTO statement, to create this table on the fly:

USE [msdb]

SELECT job_id
INTO enabled_jobs
FROM sysjobs
WHERE [enabled] = 1

So now I have a snapshot of the enabled jobs at a given point in time. I can then run an UPDATE statement and disable all of these jobs for the vendor:

USE [msdb]

UPDATE sysjobs
SET [enabled] = 0
WHERE job_id IN
  (SELECT job_id
  FROM enabled_jobs)

But there is just one more little problem: I want to automate this. Since I need to take my snapshot of which jobs are enabled at a point in time, I may need to create and recreate the enabled_jobs table over and over - especially if this code is to be reusable. So let's try this:

USE [msdb]

-- DROP TABLE if it already exists
IF OBJECT_ID (N'dbo.enabled_jobs') IS NOT NULL
  DROP TABLE enabled_jobs

-- Get the job_id of all jobs that are currently enabled and create a new table
-- on the fly to hold those IDs
-- Store these job_ids in a table so that there is a record of the jobs that were 
-- enabled and the ones that were not
SELECT job_id
INTO enabled_jobs
FROM sysjobs
WHERE [enabled] = 1

-- Disable all jobs that are enabled
UPDATE sysjobs
SET [enabled] = 0
WHERE job_id IN
  (SELECT job_id
  FROM enabled_jobs)

Now I can put all of this in a stored procedure and reuse it. Since we do not drop the enabled_jobs table at the end of our statement, it still exists and I can use it later to re-enable these jobs when our vendor has completed the work. It will re-enabled the ones that were previously enabled and keep the ones that were disabled disabled:

USE [msdb]

UPDATE sysjobs
SET [enabled] = 1
WHERE job_id IN
  (SELECT job_id
  FROM enabled_jobs)

I hope you enjoyed this entry and that maybe it even saved you a little time or that you learned something.