Tracing Object Creation and Deletion

The vast majority of functionality improved from SQL Server 7.0 to SQL Server 2000. However, the system stored procedures supporting the scripting of tracing lost some important functionality. In SQL Server 7, we could grant execute rights to users on the trace procedures and they could run SQL Profiler. This was particularly useful in allowing developers to run Profiler on a development server. In SQL Server 2000, when Profiler is started the first command issued after the login is:

     SELECT is_srvrolemember('sysadmin') 

Therefore, unless we give our developers sa rights on the development server, they will not be able to run SQL Server 2000 Profiler. This is one of the few disappointments that we've encountered in SQL Server 2000.

In addition, the SQL Server 2000 SP_TRACE extended stored procedures are limited in functionality, as compared to the SQL Server 7.0 XP_TRACE procedures. In SQL Server 7.0, it was possible to send a procedure-generated trace to a SQL Server table. In SQL Server 2000, we can only send a procedure-generated trace to a flat file. While SQL Server 2000 does expose a system function to read the flat file back into a table, we lose the real time value of the trace. All that said, we've coded a small application that uses system stored procedures to write object creation and deletion events to a flat file. Every hour, a SQL Agent job then wakes up to consume the flat file into a object history table. Changes of interest are then mailed out to us.

In the code bundle (you can download it http://www.apress.com) you will find a script for writing the trace to a file named as Object\Objects_Created_YYYYMMDD_HHMMSS in the SQL Server error log folder. Before the new trace file is created, any current file is consumed and loaded into a table called object_hist. For our purposes, we placed the table in the master database, but you could easily redirect it to the database of your choice. You will also find scripts to create two tables and a notification procedure.