Alternative Approach: Using SQLCmd to Execute a Large Script

Scenario: You’ve scripted the data of a largish table to a file (perhaps you need to deploy a solution you’ve built and SSIS is not expedient). You’re all set to execute your script in SSMS when…”Gadzooks!…an ‘out of memory’ error?!”

InsertFileTooBigForSSMS

“Out of Memory Exception”

Solution: Use the sqlcmd command-line utility to execute the script. What ostensibly overwhelmed my RAM in SSMS above works using sqlcmd. Here’s an example script:

sqlcmd -E -d AdventureWorks2008R2 -i D:\DBA\Misc\ProductBloatInsert.sql -o D:\DBA\Misc\ProductBloatOutput.txt

Where:

-E: indicates a trusted connection

-d: is your target database name

-i: is your bloated script file you want to execute

-o: is an output file (very helpful to catch errors)

See “sqlcmd utility” for exhaustive treatment.

Conclusion: When confronted with a largish script file that overwhelms SSMS, try using the sqlcmd utility as an alternative.

Posted in Alphabetical, Alternative Approach | Tagged , , , | 2 Comments

Automation: Get System Monitor Counters Into LogMan With ReLog

Scenario: System Monitor (née Performance Monitor in NT4.0) also know as “SysMon” is a Windows application we use to measure a computer’s performance. There are literally hundreds of metrics from which to choose. We can use the tool in a real-time mode where we first manually select the appropriate counters, then initiate a real-time trace to capture the performance data.  We use the SysMon GUI to view the real-time performance data.

If we want to be a bit more deliberate, we can forgo the real-time approach and instead record counter data to a log file, or a “Counter Log” in SysMon parlance.

Microsoft gave us the “System Overview” counter log example for our learning and emulation.

But much like default “System Monitor” view, to create a counter log definition, we likewise need to wade through a number of manual steps:

1) Define a counter log

2) Manually select each specific counter or performance object:

This manual approach quickly becomes unwieldy–death by a 1,000 clicks if you will. How do we automate these counter-log definitions?

Solution: To automate counter-log definitions, we need to avail ourselves to two command-prompt tools (listed with their endemic, Microsoft TechNet descriptions)

  1. ReLog: Extracts performance counters from performance counter logs into other formats, such as text-TSV (for tab-delimited text), text-CSV (for comma-delimited text), binary-BIN, or SQL.
  2. LogMan: Manages and schedules performance counter and event trace log collections on local and remote systems.

Or in plain English, with ReLog we can extract to a text file performance counter information from an existing performance log; and, LogMan is the command-prompt equivalent of System Monitor. So, at this point, perhaps you can see where this is going.

Without getting bogged down with Relog’s many options, know that its “-q” option allows us to extract the performance counters names that exist in a given input file. In other words, if we provide ReLog an SysMon input file, having the performance counters we care about, it could extract all the long, hard-to remember, non-intuitive counter names and stuff them into a text file for us. (And if we take this a step further, we could use this approach to build a reference text file or Excel document of all of SysMon’s bazillion counters!)

And without getting bogged down with LogMan’s many options, know that we can use its “-cf” option to tell it to use a text file that lists the performance counters we with to collect. This intelligent feature is our remedy to the “1,000-click death.” As you’ll see in a minute, with a little up-front work, we’ll have a portable, repeatable SysMon automation solution.

So, without further ado, synthesizing what we know, here are the steps we’ll take to automate counter-log definitions:

1) Note: This step is one that you will need to do only as often as you change your performance counter choices.

Using the SysMon GUI, create a dummy counter log having those objects and/or counters that you find interesting (see Brent Ozar’s thoughts on this)

2) Now run your SysMon counter log for an interval long enough to capture performance data for your hand-chosen metrics–say 3 minutes.

3) Invoke ReLog, with the aforementioned “-q” option to export our performance counter names to a text file:

We should find a text file named “ThePerformanceCountersIChose.txt” in the C:\perflogs path:

And we should discover a list of the performance counter names we chose:

Now we have our counter name list that we’ll feed into LogMan.

4) If I want to programmatically invoke SysMon, by way of LogMan, I’d issue a command like the following:

Where:

  • -si: Sample interval for performance counter data collectors
  • -b: Begin the data collector at specified time
  • -e: End the data collector at specified time
  • -f: Specifies the log format for the data collector
  • -cf: File listing performance counters to collect, one per line
  • -o: Path of the output log file

5) After running this command, we see the new “MyPerfCheck” counter-log definition:

6) We can refine and generalize this automation attempt by putting the above LogMan command in a batch script with a few parameters:

Note: I store these scripts on Box.net. Click on this link to download the below script.

Now, armed with our above batch script and the text file having performance-counter names, we have a portable, repeatable SysMon automation solution.

Conclusion: DBAs need to understand their SQL Servers in a holistic sense. And we use System Monitor to gain visibility into a server’s performance. It is one inexpensive way we can discover if our server is under duress from memory, I/O, or CPU pressure. We can also use SysMon glean many SQL-Server specific performance counters. With this article, I’ve demonstrated my SysMon-automation technique. In a future article, I’ll share further automation steps and make a plug for the free PAL analysis/reporting tool.

Posted in Alphabetical, Automation, DBA Best Practice, Windows Server 2003 | Tagged , , , , | Comments Off on Automation: Get System Monitor Counters Into LogMan With ReLog

“Email Available Disk Space” Tool for SQL Server 2005

Scenario: You’d like to receive a periodic email relaying your SQL Server’s available disk space.

Solution: This straight-forward post is a bolt-on to my previous “‘Get Available Disk Space’ Tool for SQL Server 2005” post. This script presumes the “DBA.dbo.AvailableDiskSpace” table exists. The aforementioned “‘Get Available Disk Space’ Tool for SQL Server 2005” script creates and, if you create a job, populates said table.

Put the below script in a job to automate this “available disk-space report.” I stagger the coupled jobs as follows to ensure I receive current data:

Email Available Disk Space Script Sequence

Script Highlights:

This procedure has three parameters:

  1. @ProfileName: A valid DB Mail profile name
  2. @RecipientsProxy: A semicolon delimited list of email addresses to which you wish to send this available-space report
  3. @Debug: A bit to either reveal or suppress verbose output (variable expansion, etc.)

The Script:

Note: I stored these scripts on Box.net. Click on this link to download the below script.

USE [DBA]
GO

IF OBJECT_ID('dbo.EmailAvailableDiskSpace', 'P') IS NOT NULL DROP PROCEDURE dbo.EmailAvailableDiskSpace
GO

CREATE PROCEDURE dbo.EmailAvailableDiskSpace

	@ProfileName						SYSNAME			= NULL,	-- DB Mail profile
	@RecipientsProxy					VARCHAR(MAX)	= NULL,	-- semicolon-delimited list of e-mail addresses
	@Debug								BIT				= 0		-- bit to show or hide verbose output

AS

/*
* Author:	Mark Holahan
* Date:		Wed, 22-Jun-2011
*
* Revised:
*			Wed, 22-Jun-2011: Changed @ProfileName & @RecipientsProxy to be input params; added @Debug param
*
* Version:  3
* Purpose:	Push out a SQL Server instance, available-disk-space report
*
* ToDo:
*/

SET NOCOUNT ON;

DECLARE

	@InstanceName						SYSNAME,
	@MachineName						SYSNAME,
	@ServerName							SYSNAME,
	@SQLServerInstanceName				SYSNAME,
	@YES								BIT

-- DB Mail Variables
DECLARE 

	@AttachQueryResultAsFileProxy		BIT,
	@Body								NVARCHAR(MAX),
	@BodyProxy							NVARCHAR(MAX),
	@Query								VARCHAR(MAX),
	@QueryProxy							VARCHAR(MAX),
	@Recipients							VARCHAR(MAX),
	@Subject							NVARCHAR(255),
	@SubjectProxy						NVARCHAR(255)

-- Set vars

SET @InstanceName		= CAST(SERVERPROPERTY('InstanceName') AS SYSNAME)
SET @MachineName		= CAST(SERVERPROPERTY('MachineName') AS SYSNAME)

IF @InstanceName IS NULL
	SET @SQLServerInstanceName = @MachineName
ELSE
	SET @SQLServerInstanceName = @MachineName + '\' + @InstanceName

-- Constants
SET @YES = 1

-- Set DBMail variables
SET @SubjectProxy					= @SQLServerInstanceName + ': Available Drive Space'
SET @QueryProxy						= 'SET NOCOUNT ON;SELECT' + SPACE(1) + CHAR(39) + @SQLServerInstanceName + CHAR(39) + ' AS SQLServerInstanceName, a.driveletter AS Drv, CAST(a.volumename AS VARCHAR(10)) AS Vol, CAST(RTRIM(LEFT(CAST(a.percentfree AS VARCHAR(23)), (CHARINDEX(''.'', CAST(a.percentfree AS VARCHAR(23))) + 2))) AS VARCHAR(6)) AS [%Free], CAST(a.availableMB AS VARCHAR(15)) AS Available_MB, CAST(a.totaldiskspaceMB AS VARCHAR(15)) AS TtlDskSpce_MB, CAST({fn YEAR(a.dtstamp)} AS CHAR(4)) + ''-'' + CAST({fn MONTH(a.dtstamp)} AS VARCHAR(2)) + ''-'' + CAST({fn DAYOFMONTH(a.dtstamp)} AS VARCHAR(2)) + SPACE(1) + CAST({fn HOUR(a.dtstamp)} AS VARCHAR(2)) + '':'' + CASE LEN(CAST(DATEPART(mi, a.dtstamp) AS VARCHAR(2))) WHEN 1 THEN CAST(DATEPART(mi, a.dtstamp) AS VARCHAR(2)) + ''0'' WHEN 2 THEN CAST(DATEPART(mi, a.dtstamp) AS VARCHAR(2)) END AS LoadDate FROM DBA.dbo.AvailableDiskSpace a JOIN (SELECT MAX(dtstamp) MaxLoadDate FROM DBA.dbo.AvailableDiskSpace) x ON YEAR(a.dtstamp) = YEAR(x.MaxLoadDate) AND MONTH(a.dtstamp) = MONTH(x.MaxLoadDate) AND DAY(a.dtstamp) = DAY(x.MaxLoadDate)'

IF @Debug = @YES SELECT @QueryProxy QueryProxy

SET @AttachQueryResultAsFileProxy	= 1

BEGIN TRY

	EXECUTE msdb.dbo.sp_send_dbmail
		@profile_name					= @ProfileName,
		@Recipients						= @RecipientsProxy,
		@Subject						= @SubjectProxy,
		@Body							= @BodyProxy,
		@body_format					= 'TEXT',
		@query_result_header			= @Yes,
		@Query							= @QueryProxy,
		@attach_query_result_as_file	= @AttachQueryResultAsFileProxy,
		@query_result_width				= 500
END TRY

BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber
		,ERROR_MESSAGE() AS ErrorMessage
		,ERROR_SEVERITY() AS ErrorSeverity
		,ERROR_STATE() AS ErrorState
		,ERROR_LINE() AS ErrorLine
		,ERROR_PROCEDURE() AS ErrorProcedure

END CATCH

Execution Example: Here’s an example of how to execute this script:

DECLARE
	@ProfileName		SYSNAME,
	@RecipientsProxy	VARCHAR(MAX),
	@Debug				BIT

SET @ProfileName		= 'Studio'
SET @RecipientsProxy	= 'mark@heydba.net'
SET @Debug				= 0

EXECUTE [DBA].[dbo].[EmailAvailableDiskSpace]
   @ProfileName
  ,@RecipientsProxy
  ,@Debug

After a few seconds, I received an email with this attachment:

Email Available Disk Space Email Attachment

Everything looks good.

Conclusion: If you find solace in home-grown tools, use my script to keep informed of your disk-space situation. If your environment has significant data flux, you could run this job several times a day to ensure no surprises find you.

Posted in Alphabetical, DB Mail, DBA Best Practice, DBA Tools | Tagged , , , , , | Comments Off on “Email Available Disk Space” Tool for SQL Server 2005

“Email SQL Server Errors” Tool for SQL Server 2005

Scenario: You want the skinny on your SQL Server error log. That is, you don’t want to skim hundreds of lines for possible embedded errors (“Error Log,” after all, is a bit of a misnomer as this file holds much non-error related information). So let’s get to the point and find the salient errors (like your DBCC CheckDB messages).

We could rely on the undocumented “xp_ReadErrorLog” system stored procedure (see Dan McClain’s concise article about this system stored procedure) to ferret out a specific term or even a combination or terms. But if time is of the essence, then let’s find all the error messages that interest us in one fell swoop. In fact, let’s send an email of this error-log compendium.

Solution: The below script does indeed use the “xp_ReadErrorLog” system stored procedure to read the SQL Server error log into a work table where we then use standard query technique to distill select error-log information. And the whole point of this tool is to provide a more robust error-log filter. That is, a way to hand pick the error-log entries that interest us. You’ll see in the script that I use an arbitrary list of filter items, namely:

Of course, edit to suit your needs.

We then use DB Mail to send a message having the result set based on the given search terms.

N.B. I’ve used this script successfully in production 2005 environments for over two years. I am not, yet, able to get it to work on a 2008 R2 system. I can execute this stored procedure in a query window without issue but I cannot execute the stored procedure within a job. I receive the following error from SQL Server Agent:

It will yield to analysis one day soon.

Conventions: Two conventions bear comment.

  1. It’s my standard practice to create a fixed-space “DBA” database on a given SQL Server instance. It’s a workspace where I’ll perform DBA analysis tasks and, optionally, persist related metrics (akin to SQL Server’s usage of its “tempdb” database). I’ll place DBA objects, like this script’s stored procedure and related tables, in the DBA database.
  2. I use a work table and bcp to export dynamic SQL to a text file. I then use the text file output for debugging. I.e. I copy and paste the text-file verbiage into a SQL Server query window for parsing/execution.

Script Highlights: The procedure has five parameters:

  1. @ProfileName: Our valid DB Mail profile name
  2. @RecipientsProxy: A semicolon-delimited list of e-mail addresses
  3. @DaysToRetain: The number of days of error-log information we’d like to keep
  4. @SendEmail: A bit to allow us to either invoke or avoid invoking DB Mail (yes, this may seem odd to have this option for a tool that has “email” in its name but I currently remotely manage an environment where I do not have a functioning DB Mail system)
  5. @Debug: A bit to allow us to either show or hide verbose output (like the expansion of variables)

I divide this script into six sections:

  1. Parameter validation
  2. bcp enabling
  3. Detritus removal (old records)
  4. Error log replication to work table
  5. Current day’s error-log scrape
  6. Message dispatch

The Script:

Note: I store these scripts on Box.net. Click on this link to download the below script.

USE [DBA]

IF OBJECT_ID(N'EmailSalientSQLServerErrors', 'P') IS NOT NULL DROP PROCEDURE dbo.EmailSalientSQLServerErrors
GO

CREATE PROCEDURE [dbo].[EmailSalientSQLServerErrors]

	@ProfileName						SYSNAME = NULL,		-- DB Mail profile
	@RecipientsProxy					VARCHAR(MAX) = NULL,	-- semicolon-delimited list of e-mail addresses
	@DaysToRetain						TINYINT	= 21,		-- the number of days we'll retain error-message data
	@SendEmail						BIT = 1,	        -- to invoke or not invoke DB Mail
	@Debug							BIT = 0	         	-- to show or not show verbose output

/*
* Author:	Mark Holahan
* Date:		Saturday, 18-June-2011
*
* Revised:
*		Fri, 17-Jun-2011: Added @SendEmail param so we can, @ least, table the error-log scrape, if emailing
*		isn't working.
*
* Version:	5
* Purpose:	Proactive error notification where dearth of 3-party tools but
*		have DB Mail
*
* Comment:
* Theory:
*		See: http://www.sqlteam.com/article/using-xp_readerrorlog-in-sql-server-2005
*		This undocumented system stored procedure has four params:
*		Parameter 1 (int), is the number of the log file you want to read, default is "0" for current log. The extended
*		stored procedure xp_enumerrorlogs will come in handy in determining what SQL server error logs or SQL server Agent
*		logs exist and when they were created. NOTE: extended stored procedure xp_enumerrorlogs parameter (1) works just like
*		xp_ReadErrorLog parameter (2). A value of 1 shows available SQL error logs and a value of 2 shows Agent logs.
*		The default value is 0.
*		Parameter 2 (int), value of 1 reads SQL error logs, value of 2 reads SQL Server Agent logs, with a default value of 1.
*		Parameter 3 varchar (255), is a search string for the log entry, with a default value of NULL.
*		Parameter 4 varchar (255), is another search string for the log entry, with a default value of NULL.
*
*		Although I can filter with params 3 & 4, I'll proceed, nonetheless, with my m.o.
*/

AS

SET NOCOUNT ON;

DECLARE
	@DateIndex						SMALLDATETIME,
	@InstanceName						SYSNAME,
	@MachineName						SYSNAME,
	@MaxErrorLogEntryLength				        INT,
	@NO							BIT,
	@SQL							VARCHAR(500),
	@SQLServerInstanceName				        SYSNAME,
	@RetVal							INT,
	@YES							BIT,
	@ValueInUse						SQL_VARIANT,
	@XP_CMDSHELL						INT,

-- Error message vars
    @ErrorMessage						NVARCHAR(4000),
    @ErrorNumber						INT,
    @ErrorSeverity						INT,
    @ErrorState							INT,
    @ErrorLine							INT,
    @ErrorProcedure						NVARCHAR(200),

-- DB Mail Variables
	@AttachQueryResultAsFileProxy		                BIT,
	@recipients						VARCHAR(MAX),
	@subject						NVARCHAR(255),
	@SubjectProxy						NVARCHAR(255),
	@body							NVARCHAR(MAX),
	@BodyProxy						NVARCHAR(MAX),
	@query							VARCHAR(MAX),
	@QueryProxy						NVARCHAR(MAX)

-- Define Constants
SET @NO								= 0
SET @YES							= 1
SET @XP_CMDSHELL						= 16390

-- Set DBMail variables
SET @SubjectProxy	= @SQLServerInstanceName + ': Salient Error Messages'

-- Other variables
SET @InstanceName		= CAST(SERVERPROPERTY('InstanceName') AS SYSNAME)
SET @MachineName		= CAST(SERVERPROPERTY('MachineName') AS SYSNAME)

IF @InstanceName IS NULL
	SET @SQLServerInstanceName = @MachineName
ELSE
	SET @SQLServerInstanceName = @MachineName + '\' + @InstanceName

-- 1) Parameter Validation
IF @ProfileName IS NULL
	BEGIN
		RAISERROR( 'Please provide a DB Mail Profile Name', 16, 1 )
		RETURN
	END

IF @RecipientsProxy IS NULL
	BEGIN
		RAISERROR( 'Please provide a receipent email address', 16, 1 )
		RETURN
	END

-- Profile name validity check

BEGIN TRY
	EXEC msdb.dbo.sysmail_help_profile_sp @profile_name = @ProfileName
END TRY

BEGIN CATCH
	SELECT
		@ErrorNumber		= ERROR_NUMBER(),
		@ErrorSeverity		= ERROR_SEVERITY(),
		@ErrorState		= ERROR_STATE(),
		@ErrorLine		= ERROR_LINE(),
		@ErrorProcedure		= ISNULL(ERROR_PROCEDURE(), '-');

	RAISERROR
		(
		@ErrorMessage,
		@ErrorSeverity,
		1,
		@ErrorNumber,    -- parameter: original error number.
		@ErrorSeverity,  -- parameter: original error severity.
		@ErrorState,     -- parameter: original error state.
		@ErrorProcedure, -- parameter: original error procedure name.
		@ErrorLine       -- paramreter: original error line number.
		);
		RETURN

END CATCH

-- 2) Enable xp_cmdshell so we can bcp
BEGIN TRY
	-- Enable advanced options
	EXEC sp_configure 'show advanced option', '1'; RECONFIGURE;

	-- Ensure xp_cmdshell's on so we can bcp out
	EXEC sp_configure 'xp_cmdshell', '1'; RECONFIGURE;

	-- Test...
	SELECT @ValueInUse = Value_In_Use
	FROM master.sys.configurations
	WHERE configuration_id = @XP_CMDSHELL

	IF CONVERT(INT, @ValueInUse) <> @YES
		BEGIN
			SET @ErrorMessage = CHAR(13) + 'Although we attempted to enable the ''xp_cmdshell'' server option, this option is still disabled.' + CHAR(13)
				+ 'This script depends upon this extended stored procedure''s availability. Please investigate.'
				+ CHAR(13) + 'NOTE: ''xp_cmdshell'' execution requires ''CONTROL SERVER'' permission (See SQL Server Books Online for more information).'

			RAISERROR(@ErrorMessage, 16, 1)
		END

END TRY

BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber
		,ERROR_MESSAGE() AS ErrorMessage
		,ERROR_SEVERITY() AS ErrorSeverity
		,ERROR_STATE() AS ErrorState
		,ERROR_LINE() AS ErrorLine
		,ERROR_PROCEDURE() AS ErrorProcedure

		RETURN
END CATCH

IF OBJECT_ID('DBA.dbo.ThisMorningsErrorLogHighlights_STAGING') IS NOT NULL
	TRUNCATE TABLE DBA.dbo.ThisMorningsErrorLogHighlights_STAGING
ELSE
	BEGIN
		CREATE TABLE DBA.dbo.ThisMorningsErrorLogHighlights_STAGING
		(
			ErrorLogDTStamp				DATETIME NULL,
			ProccessInfo				NVARCHAR(35) NULL,
			ErrorLogEntry				NVARCHAR(MAX) NULL,
			UserName					NVARCHAR(256) NULL
			CONSTRAINT DF_ThisMorningsErrorLogHighlights_STAGING_UserName DEFAULT (SUSER_SNAME()),
			DTStamp						SMALLDATETIME NULL
			CONSTRAINT DF_ThisMorningsErrorLogHighlights_STAGING_DTStamp DEFAULT (GETDATE()),
			PK							INT IDENTITY
			CONSTRAINT PK_ThisMorningsErrorLogHighlights_STAGING PRIMARY KEY CLUSTERED,
			Processed					BIT
			CONSTRAINT DF_ThisMorningsErrorLogHighlights_STAGING_Processed DEFAULT (0),
		)
	END

IF OBJECT_ID('DBA.dbo.ThisMorningsErrorLogHighlights') IS NULL
	BEGIN
		CREATE TABLE DBA.dbo.ThisMorningsErrorLogHighlights
		(
			DateIndex					SMALLDATETIME NOT NULL,
			PK							INT IDENTITY,
			ErrorLogEntry				        NVARCHAR(MAX) NOT NULL,
			ErrorLogDTStamp				        SMALLDATETIME NULL,
			UserName					NVARCHAR(256) NOT NULL
			CONSTRAINT DF_TMELH_UserName DEFAULT (SUSER_SNAME()),
			DTStamp						SMALLDATETIME NOT NULL
			CONSTRAINT DF_TMELH_DTStamp DEFAULT(GETDATE())
		)
		CREATE CLUSTERED INDEX PK_ThisMorningsErrorLogHighlights
		ON DBA.dbo.ThisMorningsErrorLogHighlights
		(DateIndex, PK)
	END

-- 3) Remove records older than a given number of units from DBA.dbo.ThisMorningsErrorLogHighlights

DELETE
FROM DBA.dbo.ThisMorningsErrorLogHighlights
WHERE
		DATEDIFF(dd, DTStamp, GETDATE()) > @DaysToRetain

-- 4) Load current error log into staging table using undocumented system proc
INSERT DBA.dbo.ThisMorningsErrorLogHighlights_STAGING( ErrorLogDTStamp, ProccessInfo, ErrorLogEntry )
	EXEC master.sys.xp_readerrorlog

-- 5) Scrape today's errors from log

-- If offending records exist from the last 24 hours, table 'em and send as an email query to DBAdmins
IF EXISTS
	(
	SELECT (1) FROM DBA.dbo.ThisMorningsErrorLogHighlights_STAGING
	WHERE
		ErrorLogDTStamp >= (DATEADD(dd, -1, GETDATE()))
		AND
		(
			ErrorLogEntry LIKE '%deadlock%'
			OR
			ErrorLogEntry LIKE '%Error%'
			OR
			ErrorLogEntry LIKE '%fail%'
			OR
			ErrorLogEntry LIKE '%I/O requests taking longer%'
			OR
			ErrorLogEntry LIKE '%insufficient%'
			OR
			ErrorLogEntry LIKE '%level 16%'
			OR
			ErrorLogEntry LIKE '%level 17%'
			OR
			ErrorLogEntry LIKE '%level 21%'
			OR
			ErrorLogEntry LIKE '%severity: 16%'
			OR
			ErrorLogEntry LIKE '%severity: 17%'
			OR
			ErrorLogEntry LIKE '%severity: 21%'
			OR
			ErrorLogEntry LIKE '%table corrupt%'
			OR
			ErrorLogEntry LIKE '%warning%'
		)
		AND
		(
			ErrorLogEntry NOT LIKE '%backup log%'
			AND
			ErrorLogEntry NOT LIKE '%errorlog%'
		)
	) 

	BEGIN
		-- Remove time portion of GETDATE() to create an index value
		SET @DateIndex = GETDATE() - {fn CURRENT_TIME}

		-- Remove stuff for existing Date Index so we can run repeatedly if need to

		DELETE FROM DBA.dbo.ThisMorningsErrorLogHighlights
		WHERE DateIndex = @DateIndex

		INSERT DBA.dbo.ThisMorningsErrorLogHighlights( DateIndex, ErrorLogEntry, ErrorLogDTStamp )
			SELECT
				@DateIndex,
				ErrorLogEntry,
				ErrorLogDTStamp
			FROM DBA.dbo.ThisMorningsErrorLogHighlights_STAGING
			WHERE
				ErrorLogDTStamp >= (DATEADD(dd, -1, GETDATE()))
				AND
				(
					ErrorLogEntry LIKE '%deadlock%'
					OR
					ErrorLogEntry LIKE '%Error%'
					OR
					ErrorLogEntry LIKE '%fail%'
					OR
					ErrorLogEntry LIKE '%I/O requests taking longer%'
					OR
					ErrorLogEntry LIKE '%insufficient%'
					OR
					ErrorLogEntry LIKE '%level 16%'
					OR
					ErrorLogEntry LIKE '%level 17%'
					OR
					ErrorLogEntry LIKE '%level 21%'
					OR
					ErrorLogEntry LIKE '%severity: 16%'
					OR
					ErrorLogEntry LIKE '%severity: 17%'
					OR
					ErrorLogEntry LIKE '%severity: 21%'
					OR
					ErrorLogEntry LIKE '%table corrupt%'
					OR
					ErrorLogEntry LIKE '%warning%'
				)
				AND
				(
					ErrorLogEntry NOT LIKE '%backup log%'
					AND
					ErrorLogEntry NOT LIKE '%errorlog%'
				)

		SET @BodyProxy						= 'Here are the salient error messages from the SQL Server error log from the last 24 hours.'
									+ SPACE(1) + 'These error "highlights" are tabled in DBA.dbo.ThisMorningsErrorLogHighlights.'

		-- Trim down the ErrorLogEntry filed for tighter presentation in the below query ouput
		SELECT @MaxErrorLogEntryLength		= MAX(LEN(ErrorLogEntry)) FROM dbo.ThisMorningsErrorLogHighlights

		SET @QueryProxy	= 'SET NOCOUNT ON;SELECT ErrorLogDTStamp, LEFT(ErrorLogEntry,'
				+ CAST(@MaxErrorLogEntryLength AS VARCHAR(4)) + ') AS ErrorLogEntry'
				+ SPACE(1) + 'FROM DBA.dbo.ThisMorningsErrorLogHighlights'
				+ SPACE(1) + 'WHERE DateIndex =' + SPACE(1) + CHAR(39) + CAST(@DateIndex AS VARCHAR(25)) + CHAR(39)
				+ SPACE(1) + 'ORDER BY ErrorLogDTStamp DESC;'

		IF @Debug = @YES SELECT @QueryProxy QueryProxy

		SET @AttachQueryResultAsFileProxy	= 1
	END

ELSE

	BEGIN
		SET @BodyProxy				= 'No errors this morning Jack.'
		SET @QueryProxy				= NULL
		SET @AttachQueryResultAsFileProxy	= 0
	END

IF @SendEmail = @YES

	BEGIN

	-- 6) Send email
			IF @Debug = @YES SELECT @RecipientsProxy, @SubjectProxy, @BodyProxy, @QueryProxy

			IF @Debug = @NO
				BEGIN TRY

					EXECUTE msdb.dbo.sp_send_dbmail
						@profile_name				= @ProfileName,
						@recipients				= @RecipientsProxy,
						@subject				= @SubjectProxy,
						@body					= @BodyProxy,
						@body_format				= 'TEXT',
						@query_result_header			= @YES,
						@query					= @QueryProxy,
						@attach_query_result_as_file	        = @AttachQueryResultAsFileProxy,
						@query_result_width			= 500
				END TRY

				BEGIN CATCH
					SELECT
						@ErrorNumber		= ERROR_NUMBER(),
						@ErrorSeverity		= ERROR_SEVERITY(),
						@ErrorState		= ERROR_STATE(),
						@ErrorLine		= ERROR_LINE(),
						@ErrorProcedure		= ISNULL(ERROR_PROCEDURE(), '-');

					RAISERROR
						(
						@ErrorMessage,
						@ErrorSeverity,
						1,
						@ErrorNumber,    -- parameter: original error number.
						@ErrorSeverity,  -- parameter: original error severity.
						@ErrorState,     -- parameter: original error state.
						@ErrorProcedure, -- parameter: original error procedure name.
						@ErrorLine       -- parameter: original error line number.
						);

						Goto Cleanup
				END CATCH

	END

Cleanup:

-- Ensure xp_cmdshell's off
EXEC sp_configure 'xp_cmdshell', '0'; RECONFIGURE;

Execution Example: Here’s an example of how to execute this script:

DECLARE
	@ProfileName		SYSNAME,
	@RecipientsProxy	VARCHAR(MAX),
	@DaysToRetain		TINYINT,
	@SendEmail		BIT,
	@Debug			BIT

SET @ProfileName	= 'Studio'
SET @RecipientsProxy	= 'mark@heydba.net'
SET @DaysToRetain	= 21
SET @SendEmail		= 1
SET @Debug		= 0

EXECUTE [DBA].[dbo].[EmailSalientSQLServerErrors]
   @ProfileName
  ,@RecipientsProxy
  ,@DaysToRetain
  ,@SendEmail
  ,@Debug

I received an email having the following attachment (here’s a partial screen scrape):

Email Salient Error Log Example Output

Conclusion: The SQL Server error log provides essential system information. A DBA needs to make point to visit it often. If you’re in a company that lacks the resources to purchase robust third-party tools, the above script can help you implement a proactive, error-log distilling tool.

Posted in Alphabetical, DB Mail, DBA Best Practice, DBA Tools | Tagged , , , , , | Comments Off on “Email SQL Server Errors” Tool for SQL Server 2005

“Turn On Debugging Trace Flags” Tool for SQL Server 2005 Part I

Scenario: You’re suspicious that deadlocks are lurking in your system. And you’d like to capture as much information as possible in the SQL Server error log. You need to enable trace flags 1204 and 1222 to have SQL Server output deadlock event information to its error log. These trace flags show complimentary information about the same deadlock.

Books Online tells us:

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

I’ve enabled these trace flags using the DBCC TRACEON command in SQL Server Management Studio, i.e.

DBCC TRACEON(1204, 1222, -1)

But these settings do not persist through a server/service reset.

Solution: To persist these trace-flag settings, I put the appropriate logic in a startup stored procedure (startup procedures live in the “master” system database and execute whenever the SQL Server service starts).

Script Highlights: This script is straight forward. There aren’t any parameters as startup procedures cannot, by definition, have any.

I divide this script into three sections:

  1. Enable “scan for startup procs” configuration setting
  2. Turn on, globally, trace flags 1204, and 1222
  3. Set auto execution flag

The Script:

Note: I store these scripts on Box.Net. Click on this link to download the below script.

 

USE [master]
GO

IF OBJECT_ID('dbo.TurnOnDebuggingTraceFlags', 'P') IS NOT NULL DROP PROCEDURE dbo.TurnOnDebuggingTraceFlags
GO

CREATE PROCEDURE dbo.TurnOnDebuggingTraceFlags

AS

/*
* Date:		Monday, 19-April-2011
* Author:	Mark Holahan
*
* Version:	1
* Purpose:	Ensure trace flags 1204, 1222 are enabled by putting in a start-up proc.
*		I'll rely on my log-scrapper procedure to alert me of this procedure's failures
*		and not add additional o/h of an embedded email call
*
* Theory:	See: http://support.microsoft.com/kb/832524/en-us
*		BOL states:
*		"The scan for startup procs option is an advanced option. If you are using the
*		sp_configure system stored procedure to change the setting, you can change scan for
*		startup procs only when show advanced options is set to 1. The setting takes effect after
*		the server is restarted."
*/

SET NOCOUNT ON;

DECLARE

	@ErrorMessage								NVARCHAR(4000),
	@ErrorSeverity								INT,
	@ErrorState								INT,

	@SCAN_FOR_STARTUP_PROCS							INT,		-- constant: to test to see if 'scan for startup procs' is enabled
	@ValueInUse								SQL_VARIANT,	-- to test to see if 'scan for startup procs' is enabled
	@YES									BIT


-- To hold DBCC TRACESTATUS output
DECLARE @t TABLE
(
	TraceFlag INT,
	[Status] BIT,
	[Global] BIT,
	[Session] BIT
)

-- Constants
SET @Yes			= 1
SELECT @SCAN_FOR_STARTUP_PROCS	= configuration_id FROM master.sys.configurations WHERE name = 'scan for startup procs'

-- 1) Enable "scan for startup procs" configuration setting

-- In order to for this start-up proc to work, we need "scan for startup procs" enabled.
-- It's disabled by default.
-- BOL says that the proc will get enabled automatically by setting this proc to a "start-up" proc; but a
-- service re-start is required

BEGIN TRY
	-- Enable advanced options
	EXEC master.dbo.sp_configure 'show advanced option', '1'; RECONFIGURE;

	-- Ensure scan for startup procs's on so we can use a start-up proc
	EXEC master.dbo.sp_configure 'scan for startup procs', '1'; RECONFIGURE WITH OVERRIDE;

	-- Test...
	SELECT @ValueInUse = Value_In_Use
	FROM master.sys.configurations
	WHERE configuration_id = @SCAN_FOR_STARTUP_PROCS

	IF CONVERT(INT, @ValueInUse) <> @YES
		BEGIN
			SET @ErrorMessage = CHAR(13) + 'Error: Although we attempted to enable the ''scan for startup procs'' server configuration option, this option is still disabled.' + CHAR(13)
				+ 'We need to re-start the SQL Server service for this configuration change to take effect.' + CHAR(13)
				+ 'This script''s success depends on this server configuration being enabled.'
			RAISERROR(@ErrorMessage, 16, 1) WITH LOG, NOWAIT
		END

END TRY

BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber
		,ERROR_MESSAGE() AS ErrorMessage
		,ERROR_SEVERITY() AS ErrorSeverity
		,ERROR_STATE() AS ErrorState
		,ERROR_LINE() AS ErrorLine
		,ERROR_PROCEDURE() AS ErrorProcedure

   SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState )
		RETURN
END CATCH


-- 2) Turn on, globally, trace flags 1204, and 1222
BEGIN TRY

	DBCC TRACEON ( 1204, 1222, -1 )
	
	INSERT @t (TraceFlag, Status, Global, Session)
		EXEC ('DBCC TRACESTATUS( -1 )')

	IF NOT EXISTS ( SELECT 1 FROM @t )
		BEGIN
			SET @ErrorMessage = CHAR(13) + 'Error: We attempted to enable globally the 1204 and 1222 trace flags using the ''TurnOnDebuggingTraceFlags'' stored procedure.' + CHAR(13)
			+ 'This attempt failed.'
			RAISERROR(@ErrorMessage, 16, 1) WITH LOG, NOWAIT
		END
	
	IF ( SELECT COUNT(1) FROM @t WHERE TraceFlag IN (1204, 1222) ) <> 2
		BEGIN
			SET @ErrorMessage = CHAR(13) + 'Error: We attempted to enable globally the 1204 and 1222 trace flags using the ''TurnOnDebuggingTraceFlags'' stored procedure.' + CHAR(13)
			+ 'This attempt failed.'
			RAISERROR(@ErrorMessage, 16, 1) WITH LOG, NOWAIT
		END

END TRY

BEGIN CATCH
	SELECT
		ERROR_NUMBER() AS ErrorNumber
		,ERROR_MESSAGE() AS ErrorMessage
		,ERROR_SEVERITY() AS ErrorSeverity
		,ERROR_STATE() AS ErrorState
		,ERROR_LINE() AS ErrorLine
		,ERROR_PROCEDURE() AS ErrorProcedure

   SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

		RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState )
		RETURN

END CATCH

GO

-- 3) Set auto execution flag
EXEC master.dbo.sp_procoption
	@ProcName	= 'TurnOnDebuggingTraceFlags',
    @OptionName		= 'startup',
    @OptionValue	= 1

Conclusion: DBAs need to understand and depose their deadlocks. We can use trace flags 1204 and 1222 to direct deadlock event information to the SQL Server error log. Use the above startup stored procedure to ensure that trace flags remain enabled between server/service restarts. In a follow-up post, I’ll describe how to set up a SQL TRACE trace to capture deadlock graphs. Between the error-log information and the deadlock graph, you’ll have a good understanding of the your system’s deadlock dynamics.

Posted in Alphabetical, DBA Best Practice, DBA Tools, DBCC | Tagged , , , , , , , , | Comments Off on “Turn On Debugging Trace Flags” Tool for SQL Server 2005 Part I