Importing a server-side trace into a table and recycling the trace file

No Comments

Written by Luis Miguel – Originally published on November 6, 2011

Bringing the data captured by a server-side trace into a table is a very convenient way to review the trace results when monitoring and analyzing database activity. One thing I immediately thought of when implementing this kind of solution was to do it periodically and incrementally.

SQL Server 2008 does not come with built-in functionality to append data to an existing trace file or to overwrite it and start fresh using the same file name.

One possible arrangement to achieve this has been explained in other blogs and requires the use of several trace files which is SQL Server’s default behavior. Since this technique is not without flaws and I can afford to stop the data capturing process for a minute while I import the data and refresh the trace file I did come up with the following script which lets me simulate the “refreshing” feature not available in SQL Server 2008.

Run this command first to build the table that is going to store the trace data before you put the script in a store procedure or an SQL Server Agent job


SELECT *
INTO   db_name.schema_name.table_name
FROM   Fn_trace_gettable(‘d:\trace\my_trace_file.trc’, DEFAULT) trace 

Once you have the table in place you can use the script below to keep adding data to it as frequently as necessary or possible


DECLARE @trace_id INT
-- Use the path to your trace file to get the Id of the trace you are working with
SET @trace_id = (SELECT id
                 FROM   sys.traces
                 WHERE  [path] = ‘d:\trace\my_trace_file.trc’)
-- If you get something back the trace is running
IF @trace_id IS NOT NULL
  BEGIN
      -- Stop the trace so it dumps the data into the file
      EXEC Sp_trace_setstatus
        @traceid = @trace_id,
        @status = 0
      -- Import the data from the trace file into a table
      INSERT INTO db_name.schema_name.table_name
      SELECT *
      FROM   Fn_trace_gettable(‘d:\trace\my_trace_file.trc’, DEFAULT) trace
      EXEC Sp_trace_setstatus
        @traceid = @trace_id,
        @status = 2
      -- Delete the trace file
      EXEC Xp_cmdshell ‘erase d:\trace\my_trace_file.trc’
  END 

Now you are ready to kick start the trace again using the same (fixed) file name you used before, probably another step in an SQL Server Agent job.

Building a centralized backup monitoring solution

No Comments

Written by Luis Miguel – Originally published on October 19, 2010.

A complete solution would include sending data from several servers into a central server, in this example I’m going to show you how to do it for one server. Also the final presentation of the data should use some form of reporting tool that could display this data in a portal for easy consumption.

Step 1 – Create a table to store the backup information that will be captured periodically

USE utility
GO
CREATE TABLE [dba].[backups](
[backup_log_id] [int] IDENTITY(1,1) NOT NULL,
[server_name] [nvarchar](64) NULL,
[database_name] [nvarchar](128) NULL,
[start_date] [datetime] NULL,
[finish_date] [datetime] NULL,
[expiration_date] [datetime] NULL,
[backup_type] [nvarchar](64) NULL,
[backup_size] [int] NULL,
[physical_device_name] [nvarchar](128) NULL,
[backupset_name] [nvarchar](128) NULL,
[user_name] [nvarchar](64) NULL,
[backup_log_date] [datetime] NULL,
CONSTRAINT [PK_backups] PRIMARY KEY CLUSTERED(
[backup_log_id] ASC
)
WITH(
PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 2 – Create the store procedure that will capture the data and save it to the backup information table

USE msdb
GO
CREATE PROCEDURE [dbo].[usp_backup_status]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @backup_log_date DATETIME
SET @backup_log_date = (SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0))
INSERT INTO utility.dba.backups
SELECT RTRIM(CONVERT(CHAR(100), SERVERPROPERTY('Servername'))) AS Server,
a.name,
b.backup_start_date,
d.backup_finish_date,
b.expiration_date,
CASE b.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential database'
WHEN 'F' THEN 'File or filegroup'
WHEN 'G' THEN 'Differential file'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
WHEN NULL THEN 'Null'
END AS backup_type,
b.backup_size / 1024000 AS backup_size,
c.physical_device_name,
b.name AS backupset_name,
b.user_name,
@backup_log_date AS backup_log_date
FROM sys.databases AS a
LEFT OUTER JOIN
(SELECT
database_name,
MAX(backup_finish_date) AS backup_finish_date
FROM msdb.dbo.backupset
GROUP BY database_name
) AS d
ON a.name = d.database_name
LEFT OUTER JOIN msdb.dbo.backupset AS b
ON (a.name = b.database_name) AND (b.backup_finish_date = d.backup_finish_date)
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS c
ON b.media_set_id = c.media_set_id
ORDER BY a.name
END

Step 3 – Create a job to run the stored procedure as frequently as needed

Step 4 – Query the “backups” table to see information about today’s backups

SELECT
[server_name],
[database_name],
[start_date],
[finish_date],
[expiration_date],
[backup_type],
[backup_size],
[physical_device_name],
[backupset_name],
[user_name],
[backup_log_date]
FROM [utility].[dba].[backups]
WHERE [backup_log_date] = (SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0))

Set the recovery model to “SIMPLE” programmatically

No Comments

Written by Luis Miguel – Originally published on September 18, 2010.

Here is a tiny stored procedure to set the recovery model to SIMPLE on every user database in your instance.

CREATE PROCEDURE [dbo].[Dba_reset_recovery_model]
AS
  DECLARE @db_name VARCHAR(128) -- Database name
  DECLARE @alterdb VARCHAR(512) -- Dynamic SQL command
  DECLARE db_cursor CURSOR FOR
    -- Get the USER DATABASES that ARE ONLINE and their recovery model IS NOT SIMPLE
    SELECT name
    FROM   sys.databases
    WHERE  database_id > 4
           AND [state] = 0
           AND [recovery_model] <> 3
  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @db_name
  WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @alterdb = ‘ALTER DATABASE ’ + @db_name + ‘ SET RECOVERY SIMPLE’
        EXEC (@alterdb)
        FETCH NEXT FROM db_cursor INTO @db_name
    END
  CLOSE db_cursor
  DEALLOCATE db_cursor 

Warnings:
1 – This script is known to run well in SQL Server 2008, I have not tested it in any other version of SQL Server.
2 – Do not use this script in a Production system without prior extensive testing in a lab environment.

Powered by Netfirms