Comments (0)

The SQL Server Error Log tracks activity as it is occurs in the database server.  This information is helpful in monitoring the server and in trouble shooting database problems. A new log file is automatically created each time that the SQL Server service is restarted.

The easiest way to view the error log is using the Log File Viewer in SQL Server Management Studio (SSMS).  The log files are simple text files so they can also be opened in Notepad or any text file editor.

Log File Viewer

To access the log file viewer, in the SSMS Object Explorer expand the Management folder under a connected server, then expand the SQL Server Logs folder right clicking on one of the archived logs gives you the option to select “View SQL Server Log”.  Selecting this opens the log file viewer

Using the log file viewer, you can change the log file that you are viewing by (de)selecting the check boxes in the upper left panel of the log file viewer

The buttons at the top of the viewer provide the additional capabilities to; load log files stored on disk, export the log file to disk, refresh the data being displayed, the ability to filter the log, as well as the ability to search the log.

Very large error logs

One of the issues that I encounter on occasion is the size of the current error log has gotten extremely large.  A production server that is rebooted infrequently continues writing to the current log which can cause it to grow significantly.  Attempting to open a very large file in the Log File Viewer or a text editor can take an inordinate amount of time.

Archives

By default, SQL Server keeps an archive  history of 6 error logs.  The current file named “ErrorLog” with no extension and each of the archives is named “ErrorLog.n” where .n is .1 for the most recent archive, .2 next archive and so on.

To verify the actual location you can use the “undocumented” property ErrorLogFileName

SELECT SERVERPROPERTY (‘ErrorLogFileName’);

Cycling the error file

In order to keep the log file at a manageable size the system stored procedure sp_cycle_errorlog can be called to cycle the log.  The log file is cycled just as with a service restart

EXEC msdb.dbo.sp_cycle_errorlog 

The ability to execute the system stored procedure is restricted to members of the sysadmin server role

Changing the number of error log archives

Depending on the frequency at which you choose to cycle the error log the default of 6 archive copies may not be enough to maintain an adequate amount of history.

To change the number of archive copies kept using SSMS is a simple task. Open the Management Folder, then select the SQL Server Logs folder and right click.  Select the Configure option.

In the resulting dialog, check the option “Limit the number of error log files before they are recycled” and specify the maximum number to keep, then simply click OK. I set the limit to 45 in the example below.

How many to keep?

The number of error logs to keep in the archive depends on how frequently the error log is cycled and any other specific business requirements concerning log file retention.  Generally, I will create a nightly job to cycle the error log and set the number of error logs to keep to 45 which give me the current error log along with history spanning the last 45 days.  Alternatively, in some environments, I’ve set up the job to cycle the log files to run weekly, keeping 8 to 10 archives.

Comments (0)

Jen McCown’s (blog | twitter) latest assignment in the Applied SQL series deals with creating a simple database to be used to demonstrate backup and restore procedures.  The assignment takes us through creating a database with a single table, scripting full, transactional and differential backups and finally several restores of the database.

On to the assignment…

Setup:

• Create a database named testing.
• Set it to FULL recovery mode.
• Create a simple table (T1) in it with at least one varchar(100) column.
• Insert a few rows into your table.
• Add a “drop database; GO ” at the beginning of your script, so you’ll have a clean start each time.

USE master;
GO
/*
** Drop the Testing Database if it exists
*/
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Testing')
BEGIN
      DROP DATABASE Testing;
END
GO

/*
** Create the Testing Database
*/

CREATE DATABASE Testing
ON
(
      NAME = testing_dat,
    FILENAME = 'C:\MSSQL\testing.mdf'
    ,SIZE = 10MB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 5MB
)
LOG ON
( NAME = testing_log,
    FILENAME = 'C:\MSSQL\testing.ldf'
    ,SIZE = 5MB
    ,MAXSIZE = UNLIMITED
    ,FILEGROWTH = 5MB
) ;
GO

ALTER DATABASE Testing
      SET RECOVERY FULL;
GO

USE Testing
GO


CREATE TABLE T1
(
      KeyVal   INT IDENTITY(1,1) NOT NULL
      ,DataVal VARCHAR(100) NOT NULL
);
GO

INSERT INTO T1
VALUES ('First Row - After CREATE TABLE')
,('Row 002')
,('Row 003')
,('Row 004')
,('Row 005')
,('Row 006');
GO

Level 1 Assignments: Backups – after the Setup section in your script, add the following:

[1-1] Full backup

• Backup the database to disk, filename testing.bak
• Insert a few more rows into your table.

-- FULL BACKUP
BACKUP DATABASE Testing
      TO DISK = 'C:\MSSQL\BACKUPS\Testing.bak'
      WITH INIT, FORMAT;
GO

INSERT INTO T1
VALUES ('Row Inserted After FULL Backup')
,('Row 008')
,('Row 009')
,('Row 010')
,('Row 011')
,('Row 012');
GO

[1-2] TLOG backup

• Backup the LOG to disk, filename testingLOG1.bak
• Insert a few more rows into your table.

-- TLOG
BACKUP LOG Testing
      TO DISK = 'C:\MSSQL\BACKUPS\TestingLOG1.bak'
      WITH INIT, FORMAT;
GO


INSERT INTO T1
VALUES ('Row Inserted After First TLOG Backup')
,('Row 014')
,('Row 015')
,('Row 016')
,('Row 017')
,('Row 018');
GO

[1-3] DIFF backup, one more TLOG backup

• Take a DIFFERENTIAL backup of the database, filename testingDIFF2.bak
• Insert a few more rows into your table.
• Backup the LOG to disk, filename testingLOG3.bak
• Insert a few more rows into your table

-- DIFFERENTIAL 
BACKUP DATABASE Testing
      TO DISK = 'C:\MSSQL\BACKUPS\TestingDIFF2.bak'
      WITH DIFFERENTIAL, INIT, FORMAT;
GO

INSERT INTO T1
VALUES ('Row Inserted After Differential Backup')
,('Row 020')
,('Row 021')
,('Row 022')
,('Row 023')
,('Row 024');
GO

-- TLOG
BACKUP LOG Testing
      TO DISK = 'C:\MSSQL\BACKUPS\TestingLOG3.bak'
      WITH INIT, FORMAT;
GO

INSERT INTO T1
VALUES ('Row Inserted After Second TLOG Backup')
,('Row 026')
,('Row 027')
,('Row 028')
,('Row 029')
,('Row 030');
GO

Setup2: The next step was to go back and modify the BACKUP statements to include INIT and FORMAT.   INIT and FORMAT fall under Media Set Options of the backup command.

INIT specifies that the backup set should be overwritten if it exists.  The default NOINIT, specifies that the backup set would be appended to.

FORMAT, specifies that a new media set be created by writing a new media header on all volumes used in the backup operation.  Whereas, NOFORMAT, the default behavior,  specifies that the existing media headers are kept

Level 2 Assignments: Restores

Now that we’ve done a variety of backups in the level 1 assignments we move on to level 2 where we drop the database and apply different restores to demonstrate restoring the various backups done above.

Prior to dropping the database if we should view the current data.

SELECT * FROM T1;
GO

Table T1 contains 30 rows.

[2-1] Restore FULL

For this exercise, we’re to restore the FULL backup taken in exercise [1-1] and then select the data from the restored table.

-- FULL Restore
DROP DATABASE Testing;
GO

RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\Testing.bak'
      WITH RECOVERY;
GO

USE Testing;
GO
SELECT * FROM T1;
GO

Selecting all rows from the table retrieves 6 rows, rather than 30.  Which is correct, these were the only rows inserted into the table T1 prior to the FULL backup taken in exercise [1-1].  The 24 rows, not included in the restore were those rows inserted after the FULL backup

[2-2] Restore FULL, DIFF

• Restore the FULL and DIFFERENTIAL backups of the database (from steps 1-1 and 1-3 above).

-- FULL Restore followed by DIFFERENTIAL
USE master;
GO
DROP DATABASE Testing;
GO

RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\Testing.bak'
      WITH NORECOVERY;
     
GO
RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\TestingDIFF2.bak'
      WITH RECOVERY;
GO

USE Testing;
GO

SELECT * FROM T1;
GO

The select against T1 returns the following result set consisting of the first 18 rows inserted into the table, which were all the rows inserted just prior to the differential backup.

One statement/question from the assignment is:

• Get the tlog1/diff relationship straight in your head…do you know why we skipped right over the tlog1 backup?

Which raises a good point.  SQL Server supports 3 types of backups:

• FULL Backup: Contains all of the data in the database being backed up.  And acts as the baseline in the event of needing to restore the database.  Meaning, that the FULL backup must be restored first and then any subsequent DIFFERENTIAL and/or TRANSACTION LOG backups would be applied.

• DIFFERENTIAL Backup: A DIFFERENTIAL backup contains the data which has changed since the last differential base.  The differential base, is the latest FULL backup of the database. 

  The size of the DIFFERENTIAL Backups increase over time as more data changes within the database, this increases the amount of time, as well as the size of the DIFFERENTIAL backups.  Eventually, it becomes beneficial to do a FULL backup, generating a new differential base.

  When restoring,  the latest DIFFERENTIAL backup contains all the changes made to the database since the differential base (FULL backup).  The FULL backup is restored first, followed by the latest DIFFERENTIAL backup and then any subsequent TRANSACTION LOG backups made after the just restored DIFFERENTIAL backup.   The DIFFERENTIAL or  TRANSACTION LOG backups between the FULL backup (differential baseline) and the last DIFFERENTIAL backup can be skipped, as the last DIFFERENTIAL backup has these database changes.

• TRANSACTION LOG Backup: Are only applicable to databases in FULL or BULK-LOGGED recovery modes.  When the transaction log is backed up, the active part of the transaction log is backed up which includes all log records not backed up in a previous TRANSACTION LOG Backup.

  When restoring the database, the FULL Backup is restored first, then if there are any DIFFERENTIAL Backups, the latest  is restored next.  Finally, all TRANSACTION LOG Backups completed after the FULL or DIFFERENTIAL Backup are restored. 

  It is important to note, that the TRANSACTION LOG backups must be restored in the order that they were taken, and none can be skipped. Finally, for databases in FULL or BULK-LOGGED  recovery, transaction log backups are necessary, not only to decrease the amount of potential data lose, but are the only way that the transaction log can reclaim used log space and control the growth of the  transaction log.

Additional information regarding SQL Server backups can be found in the this TechNet Article

[2-3] Restore FULL, DIFF, TLOG

• Restore the FULL, DIFFERENTIAL, and TLOG3 backups of the database (from steps 1-1, 1-3, 1-4 above)
• Select all data from your table to see where you stand.

-- FULL Restore followed by DIFFERENTIAL and TLOG....
USE master;
GO
DROP DATABASE Testing;
GO

RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\Testing.bak'
      WITH NORECOVERY;
     
GO
RESTORE DATABASE Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\TestingDIFF2.bak'
      WITH NORECOVERY;
GO
RESTORE LOG Testing
      FROM DISK = 'C:\MSSQL\BACKUPS\TestingLOG3.bak'
      WITH RECOVERY;
GO

USE Testing;
GO

SELECT * FROM T1;
GO

And checking the results, we see that we’ve only restored 24 of the original 30 rows.

The reason that we don’t see rows 25 – 30, is because they were inserted to the table, after the TRANSACTION LOG backup to TestingLog3.bak occurred.  So, anything added after the last transaction log backup is not backed up – therefore, not restored

Assignment completed…

Comments (1)

Recently I inherited support of a SQL Server that had a number SQL Agent Jobs.  In trying to get a feel for the server activity, knowing the job schedule seemed important. I was trying to figure out the best way to cobble something together to list the jobs and their schedules using the msdb database system tables specific to SQL Agent Jobs scheduling

• msdb.dbo.sysjobs
• msdb.dbo.sysjobschedules
• msdb.dbo.sysschedules
• msdb.dob.syscategories

It was easy enough to join the tables together, the difficult part it seemed would be translating and formatting the various columns in the msdb.dbo.sysschedules table to decipher the different scheduling options.  I did notice that the BOL page for msdb.dbo.sysjobschedules, made reference to the stored procedure sp_help_jobschedule. Which, while helpful, not exactly what I was after until I noticed the argument @include_description.  Setting @include_description to true, returned an extra column, the schedule description. For the nightly maintenance job scheduled on this server it returned – “Every day at 10000”.  Now that was pretty close to what I was looking for. But the formatting of the time – pretty lame.

How was the schedule description formatted?

Looking at the sp_help_jobschedule stored procedure, it made a call to another system stored procedure, sp_get_schedule_description. Of course it turns out this is one of those pesky undocumented system stored procedures. The particular procedure lives in the msdb database. To figure out what it does so we could either use our applied SQL and query the sys.sql_modules table or simply right click on the procedure name in the object explorer and select modify to generate an alter script in a new query window.

SELECT definition
FROM	sys.sql_modules
WHERE	object_id = OBJECT_ID('sp_get_schedule_description');

What’s the problem with sp_get_schdeudle_description?

There’s no problem with sp_get_schdeudle_description, it does what it was intended to do. Ok, the lack of formatting with the times kinda bothers me.

Where it’s lacking is only in what I was hoping to use it for. I wanted to produce the job schedule result in a single query.  If I were to use the procedure as is that would necessitate using a cursor to loop through each job and call the SP to get the formatted schedule.

So here’s what I did

Since I already scripted out the system stored procedure to see what it did, I decided to wrap the functionality of the procedure in user defined function,  dbo.udf_get_schedule_description.  A udf can be called from my select statement thereby removing the need to write a cursor.  I simplified the argument list to accept a single argument for the schedule_id.

I also added a second user defined function, dbo.udf_format_integer_time to prettify the displayed time in string as HH:MM:SS rather than just an integer value.

Both of these functions were created the the administrator’s database (_dba) on this server.

User defined function: dbo.udf_get_schedule_description

/*
 ****************************************************************************************
 * Function:	dbo.udf_get_schedule_description
 *
 * Notes:
 * Wrap the functionality of the system stored procedure
 * msdb.dbo.dbo.sp_get_schedule_description in a UDF.
 *
 * Call the function udf_format_integer_time to format
 * returned time values to a nicer format.

 * Modification History:
 ****************************************************************************************
 * 2011-09-22 Jay Bonk
 *	- Inital Release
 ****************************************************************************************
 */
CREATE FUNCTION dbo.udf_get_schedule_description ( @schedule_id INT )
RETURNS  NVARCHAR(255)
AS
BEGIN

DECLARE
  @freq_type              INT
  ,@freq_interval          INT
  ,@freq_subday_type       INT
  ,@freq_subday_interval   INT
  ,@freq_relative_interval INT
  ,@freq_recurrence_factor INT
  ,@active_start_date      INT
  ,@active_end_date        INT
  ,@active_start_time      INT
  ,@active_end_time        INT
  ,@schedule_description NVARCHAR(255)

  DECLARE @loop              INT
  DECLARE @idle_cpu_percent  INT
  DECLARE @idle_cpu_duration INT

SELECT
  @freq_type = freq_type
  ,@freq_interval = freq_interval
  ,@freq_subday_type = freq_subday_type
  ,@freq_subday_interval = freq_subday_interval
  ,@freq_relative_interval = freq_relative_interval
  ,@freq_recurrence_factor = freq_recurrence_factor
  ,@active_start_date = active_start_date
  ,@active_end_date = active_end_date
  ,@active_start_time = active_start_time
  ,@active_end_time = active_end_time
FROM msdb.dbo.sysschedules AS s
WHERE schedule_id = @schedule_id

  IF (@freq_type = 0x1) -- OneTime
  BEGIN
    SELECT @schedule_description = N'Once on ' + CONVERT(NVARCHAR, @active_start_date) + N' at ' + dbo.udf_format_integer_time(@active_start_time)
    RETURN @schedule_description
  END

  IF (@freq_type = 0x4) -- Daily
  BEGIN
    SELECT @schedule_description = N'Every day '
  END

  IF (@freq_type = 0x8) -- Weekly
  BEGIN
    SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' week(s) on '
    SELECT @loop = 1
    WHILE (@loop <= 7)
    BEGIN
      IF (@freq_interval & POWER(2, @loop - 1) = POWER(2, @loop - 1))
        SELECT @schedule_description = @schedule_description + DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @loop)) + N', '
      SELECT @loop = @loop + 1
    END
    IF (RIGHT(@schedule_description, 2) = N', ')
      SELECT @schedule_description = SUBSTRING(@schedule_description, 1, (DATALENGTH(@schedule_description) / 2) - 2) + N' '
  END

  IF (@freq_type = 0x10) -- Monthly
  BEGIN
    SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on day ' + CONVERT(NVARCHAR, @freq_interval) + N' of that month '
  END

  IF (@freq_type = 0x20) -- Monthly Relative
  BEGIN
    SELECT @schedule_description = N'Every ' + CONVERT(NVARCHAR, @freq_recurrence_factor) + N' months(s) on the '
    SELECT @schedule_description = @schedule_description +
      CASE @freq_relative_interval
        WHEN 0x01 THEN N'first '
        WHEN 0x02 THEN N'second '
        WHEN 0x04 THEN N'third '
        WHEN 0x08 THEN N'fourth '
        WHEN 0x10 THEN N'last '
      END +
      CASE
        WHEN (@freq_interval > 00)
         AND (@freq_interval < 08) THEN DATENAME(dw, N'1996120' + CONVERT(NVARCHAR, @freq_interval))
        WHEN (@freq_interval = 08) THEN N'day'
        WHEN (@freq_interval = 09) THEN N'week day'
        WHEN (@freq_interval = 10) THEN N'weekend day'
      END + N' of that month '
  END

  IF (@freq_type = 0x40) -- AutoStart
  BEGIN
    SELECT @schedule_description = FORMATMESSAGE(14579)
    RETURN @schedule_description
  END

  IF (@freq_type = 0x80) -- OnIdle
  BEGIN
    EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                           N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                           N'IdleCPUPercent',
                                           @idle_cpu_percent OUTPUT,
                                           N'no_output'
    EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                           N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                           N'IdleCPUDuration',
                                           @idle_cpu_duration OUTPUT,
                                           N'no_output'
    SELECT @schedule_description = FORMATMESSAGE(14578, ISNULL(@idle_cpu_percent, 10), ISNULL(@idle_cpu_duration, 600))
    RETURN @schedule_description
  END

  -- Subday stuff
  SELECT @schedule_description = @schedule_description +
    CASE @freq_subday_type
      WHEN 0x1 THEN N'at ' + dbo.udf_format_integer_time(@active_start_time)
      WHEN 0x2 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' second(s)'
      WHEN 0x4 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' minute(s)'
      WHEN 0x8 THEN N'every ' + CONVERT(NVARCHAR, @freq_subday_interval) + N' hour(s)'
    END
  IF (@freq_subday_type IN (0x2, 0x4, 0x8))
    SELECT @schedule_description = @schedule_description + N' between '
           + dbo.udf_format_integer_time(@active_start_time)
           + N' and '
           + dbo.udf_format_integer_time(@active_end_time)

RETURN @schedule_description
END

GO

User defined function: dbo.udf_format_integer_time

/*
 ****************************************************************************************
 * Function:	dbo.udf_format_integer_time
 *
 * Notes:
 * Takes an intger where the time is stored in as HHMMSS, the
 * active_start_time in msdb.dbo.sysschedules for example, and returns
 * it formatted as a string HH:MM:SS
 *
 * Modification History:
 ****************************************************************************************
 * 2011-09-22 Jay Bonk
 *	- Inital Release
 ****************************************************************************************
 */
CREATE FUNCTION dbo.udf_format_integer_time ( @IntegerTime INT )
RETURNS CHAR(8)
AS
    BEGIN
    DECLARE @StringTime CHAR(6)
    SET @StringTime = RIGHT('000000' + CAST(@IntegerTime AS VARCHAR(6)),6)

        RETURN
			LEFT(@StringTime, 2) + ':'
				+ SUBSTRING(@StringTime, 3, 2)
				+ ':' + RIGHT(@StringTime, 2)

    END 

GO

And finally the query to pull it all together

As mentioned earlier, in my case, the udf(s) were created in the administration database (_dba), so when referenced I use the three part name which includes the database name. I could also omit the database name and run the query inside the _dba database.

SELECT  job.name AS JobName
      , job.description AS JobDescription
      , cat.name AS Category
      , CASE WHEN job.enabled = 1 THEN 'Enabled'
             ELSE 'Disabled'
        END AS [Enabled]
      , _dba.dbo.udf_get_schedule_description(jobsch.schedule_id) AS Schedule
      , CASE WHEN jobsch.next_run_date = 0 THEN NULL
             ELSE CONVERT(CHAR(8), CAST(CAST(jobsch.next_run_date AS CHAR(8)) AS DATETIME), 101)
        END AS NextRunDate
      , _dba.dbo.udf_format_integer_time(next_run_time) AS NextRunTime
FROM    msdb.dbo.sysjobs AS job
        INNER JOIN msdb.dbo.sysjobschedules AS jobsch
            ON job.job_id = jobsch.job_id
        INNER JOIN msdb.dbo.syscategories AS cat
            ON job.category_id = cat.category_id

Comments (0)

Jen McCown’s (blog | twitter) third assignment in the Applied SQL series is all about setting up a Server Side Trace with SQL Server.

A server side trace can provide the dba the same information they can obtain by running SQL Profiler.  Actually, we’ll use SQL Profiler to create the definition used for the server side trace.   Which may cause one to ask why, if we have a tool like SQL Profiler why not just use that rather than scripting a server side trace.  The main reason, a client side trace which is what we do when running SQL Profiler has significantly more of an impact on the monitored server, meaning that you will experience performance degradation.  Linchi Shea has an excellent blog post Performance Impact: Profiler Tracing vs. Server Side SQL Tracing demonstrating the difference between running a client side versus a server side trace.

On to the assignment…

Level 1 Assignments: Generate the script for a server side trace.

[1-1] Generate the script using these options:

1. Save to file: c:\temp\Trace
2. Enable file rollover
3. Enable trace stop time.
4. In the events selection, add “DatabaseID” to all selected events
5. Add a filter for DatabaseID = 5

To complete assignment 1-1, we need to open SQL Profiler and start a new trace.  For the assignment, I left the trace template as the Standard trace.

To add the database id to the trace definition, we need to switch tabs to the Events Selection, check Show all Columns and the select the DatabaseID for all events.

For adding the filter for DatabaseID = 5, click on Column Filters, select DatabaseID and enter 5.

In order to generate the trace definition needed to build the server side trace (and complete the assignment) we need to start and then stop the trace.  Once the trace is stopped, go to the File Menu, chose Export, then Script Trace Definition and finally, For SQL Server 2005 – 2008 R2 …

[1-2] Open the script and add these queries to the bottom, commented out:

1. Add a trace stop command.
2. Add a trace delete command.
3. Add a query to get information about all existing server-side traces. (This will take just a little research.)

/*
** [1-2] Open the script and add these queries to the bottom, commented out:
**
**    - Add a trace stop command.
**    - Add a trace delete command.
**    - Add a query to get information about all existing server-side traces.
**    (This will take just a little research.)
**
** For the @traceid parameter, we need to set it to the Trace Id returned when
** starting the trace, or looking it up using fn_trace_get_info or from
** the sys.traces cataloged view
*/

-- sp_trace_setstatus  @traceid =  ?,  @status =  0 -- Trace stop
-- sp_trace_setstatus  @traceid =  ?,  @status =  2 -- Trace delete

/*
** Get Information about all existing server side traces.
**
** The is_rowset is used to distinguish the Server Side Trace from
** the Client Side traces. If is_rowset = 0 it means the trace data
** is written directly to disk
*/

--SELECT *
--FROM  sys.traces
--WHERE is_rowset = 0 -- Server Side Traces
--      AND is_default = 0; -- Omit the default trace

Level 2 Assignment: Edit the script.

[2-1] Open the generated script and make these edits:

• Stop time should be 30 minutes after the start of the script.
• Max file size should be 20 Mb.
• Trace output file should be c:\temp\MyTestTraceOutput.
• Add the option for trace file rollover.

declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = DATEADD(MINUTE,30, GETDATE())
set @maxfilesize = 20

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create
           @TraceID OUTPUT
           , 2 -- TRACE_FILE_ROLLOVER
           , N'c:\temp\MyTestTraceOutput'
           , @maxfilesize
           , @Datetime
           , 10 -- @filecount

[2-2] Find and comment out all the code that adds tracing for the “SQL:BatchCompleted” event.

First, we need to determine what trace event “SQL:BatchCompleted” is:

SELECT *
FROM sys.trace_events
WHERE name = 'SQL:BatchCompleted';

Now, find and comment out the sp_trace_setevents

exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
--exec sp_trace_setevent @TraceID, 12, 15, @on
--exec sp_trace_setevent @TraceID, 12, 16, @on
--exec sp_trace_setevent @TraceID, 12, 1, @on
--exec sp_trace_setevent @TraceID, 12, 9, @on
--exec sp_trace_setevent @TraceID, 12, 17, @on
--exec sp_trace_setevent @TraceID, 12, 6, @on
--exec sp_trace_setevent @TraceID, 12, 10, @on
--exec sp_trace_setevent @TraceID, 12, 14, @on
--exec sp_trace_setevent @TraceID, 12, 18, @on
--exec sp_trace_setevent @TraceID, 12, 3, @on
--exec sp_trace_setevent @TraceID, 12, 11, @on
--exec sp_trace_setevent @TraceID, 12, 12, @on
--exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on

Complete script for the server side trace…

/****************************************************/
/* Created by: SQL Server 2008 R2 Profiler          */
/* Date: 09/10/2011  10:57:07 AM         */
/****************************************************/

-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime

set @DateTime = DATEADD(MINUTE,30, GETDATE())
set @maxfilesize = 20

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create
           @TraceID OUTPUT
           , 2 -- TRACE_FILE_ROLLOVER
           , N'c:\temp\MyTestTraceOutput'
           , @maxfilesize
           , @Datetime
           , 10 -- @filecount

if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 3, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 3, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 3, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
--exec sp_trace_setevent @TraceID, 12, 15, @on
--exec sp_trace_setevent @TraceID, 12, 16, @on
--exec sp_trace_setevent @TraceID, 12, 1, @on
--exec sp_trace_setevent @TraceID, 12, 9, @on
--exec sp_trace_setevent @TraceID, 12, 17, @on
--exec sp_trace_setevent @TraceID, 12, 6, @on
--exec sp_trace_setevent @TraceID, 12, 10, @on
--exec sp_trace_setevent @TraceID, 12, 14, @on
--exec sp_trace_setevent @TraceID, 12, 18, @on
--exec sp_trace_setevent @TraceID, 12, 3, @on
--exec sp_trace_setevent @TraceID, 12, 11, @on
--exec sp_trace_setevent @TraceID, 12, 12, @on
--exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 3, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on

-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

set @intfilter = 5
exec sp_trace_setfilter @TraceID, 3, 0, 0, @intfilter

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - e57686a1-a5b3-4a0a-8f9f-1f12a6424f72'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:

/*
** [1-2] Open the script and add these queries to the bottom, commented out:
**
**    - Add a trace stop command.
**    - Add a trace delete command.
**    - Add a query to get information about all existing server-side traces.
**    (This will take just a little research.)
**
** For the @traceid parameter, we need to set it to the Trace Id returned when
** starting the trace, or looking it up using fn_trace_get_info or from
** the sys.traces cataloged view
*/

-- sp_trace_setstatus  @traceid =  ?,  @status =  0 -- Trace stop
-- sp_trace_setstatus  @traceid =  ?,  @status =  2 -- Trace delete

/*
** Get Information about all existing server side traces.
**
** The is_rowset is used to distinguish the Server Side Trace from
** the Client Side traces. If is_rowset = 0 it means the trace data
** is written directly to disk
*/

--SELECT *
--FROM  sys.traces
--WHERE is_rowset = 0 -- Server Side Traces
--      AND is_default = 0; -- Omit the default trace

go