Two commands in the sp_monitor syntax, archive and report, support archiving monitoring data to user tables, and reporting from the archived data, for offline analysis.
sp_monitor 'archive [using prefix=<string>]', {<monitoring type>}
sp_monitor 'report [using prefix=<string>]', {<monitoring type >}
[,<options supported for monitoring_type>]
Example 1 Both archive and report commands include an optional using clause, that introduces the properties of the archive table. Data is archived into a table in the database from which the procedure is executed. The archive tables are assigned the same names as the original monitoring tables. Example 1 archives data into a table named monDeadLock in the current database, mondb.
use mondb
go sp_monitor archive, deadlock go
Example 2 Contains the subclause USING prefix=daily, to archive monitoring data in a table named daily_monDeadLock, in the database mondb:
mondb..sp_monitor "archive USING prefix=daily_", deadlock
Example 3 This use of reports on all deadlock events in the archived table monDeadLock, in the current database, mondb:
sp_monitor "report", deadlock
If the current database is the master database, this command is equivalent to the next example, in that both commands process a table using the default name: monDeadLock.
This command also processes a table using the default name:
sp_monitor deadlock
Example 4 The report command reports from an archived table, and passes any reporting arguments supported by deadlock. The examples below process a table with the default name monDeadLock, and generate a report on deadlock events archived in a table called daily_monDeadlock, using various reporting modes.
# Report for deadlock ID 10 mondb..sp_monitor "report USING prefix=daily_", deadlock, '10'# Report for deadlock ID 10, in verbose mode, # including page diagnostics. mondb..sp_monitor "report USING prefix=daily_", deadlock, '10', "verbose,pagediag"# Report in verbose mode for all deadlock events # on a given date mondb..sp_monitor "report USING prefix=daily_" deadlock, "Mar 16 2006", "verbose" # Report a frequency count of deadlocks by date # from the archived data. mondb..sp_monitor "report USING prefix=daily_", deadlock, NULL,'count by date'
Example 6 These examples show how to send monitoring data to an archive carrying the default name, and processing it to identify deadlocks for a specified date, group it by various attributes, and so forth.
1> tempdb..sp_monitor archive, deadlock 2> go Created new archive 'tempdb.dbo.monDeadLock'. Archived 62 rows to archive table 'tempdb.dbo.monDeadLock' with timestamp 'Jul 11 2006 5:24AM' (return status = 0) 1> tempdb..sp_monitor report, deadlock, NULL, 'count by object' 2> go Frequency of deadlocks, by 'DBName, ObjectName' from 'tempdb.dbo.monDeadLock': DBName ObjectName Frequency --------- ----------- ----------- pubs2 mycols 12 pubs2 myobjs 12 pubs2 mycols_apl 12 sa_tempdb sysdepends 10 sa_tempdb syscomments 10 tempdb sysdepends 3 tempdb syscomments 3 (1 row affected) (return status = 0) 1> tempdb..sp_monitor report, deadlock, NULL, 'count by date' 2> go Frequency of deadlocks, by 'ResolveDate' from 'tempdb.dbo.monDeadLock': ResolveDate Frequency ------------ ----------- Jul 11 2006 31 (1 row affected) (return status = 0) 1> tempdb..sp_monitor report, deadlock, 'Jul 11 2006', 'count by object' 2> go Frequency of deadlocks, by 'DBName, ObjectName' from 'tempdb.dbo.monDeadLock': DBName ObjectName Frequency --------- ---------- ----------- pubs2 mycols 12 pubs2 myobjs 12 pubs2 mycols_apl 12 sa_tempdb sysdepends 10 sa_tempdb syscomments 10 tempdb sysdepends 3 tempdb syscomments 3 (1 row affected) (return status = 0)
The examples above demonstrate how the existing filter and output_modes parameters, documented in sp_monitor, the Reference Manual: Stored Procedures, can process data from an archive exactly as they process data directly from the monitoring table.
Choose default archive names based on the archived monitoring table.
You can change the default archive name with a user-supplied prefix.
Use report to re-create the deadlock event trace from archived data.
If you attempt to archive the data to a table without enabling select into in the database where you create the archive table, an error similar to the following results:
mondb..sp_monitor archive, deadlock ------------------------------- Cannot use database 'mondb' as an archive database for monitoring data as it does not have the select into/bulkcopy/pllsort' set. return status = 1
You cannot use archive in system databases: master, model, sybsystemdb, sybsystemprocs, sybsecurity.
You cannot use proxy databases as archive databases.
Sybase recommends that you do not use temporary tables as archive repositories: they are deleted when the session generating the archive disconnects from the server.
For sp_monitor, mon_role and sa_role privilege are required to archive data from the monitoring tables, and select privilege is required to access data on the archive table.
