Question

How to find out what is happening in a Database?

SQL Server, Triggers, Unknown processes, SQL Server logs, Temp Tables

We have an old server that runs SQL Server 7 on an NT system. One database has processes running that are not documented. I have written triggers to report any deletions, inserts or updates made against the database's tables. All I can report is that an insert, update or delete has occurred, the Date and Time it occurred, and the table the operation was against.

I have read that temporary/virtual tables are created whenever an insert, update or delete occurs. I have not found any documentation about these temporary/virtual tables.

I have not been able to get the logs running. Viewing the logged data should give me an idea of what actions are taking place. The way that I enabled the Log Security Events was by Right-Clicking on the registered server and choosing properties, then clicking on the security tab, and enable ‘All’ audit level. To view the logs, in SQL Enterprise Manager-->Management-->SQL Server logs. I have 6 old logs that span a few hours for a given day. These days are either late last year and very early this year.

Does anyone have information about the temporary tables or logging an SQL Server 7? Any help would be appreciated. Thanks

The virtual tables which can be accessed via a trigger are called inserted and deleted. The inserted table contains the new values, while the deleted table contains the old values.

Logging the security events won't help you see what commands are being run against the database server. It will only log when someone logs into the database, not what they do.

Fire up SQL Server Profiler (Start > Programs > Microsoft SQL Server 7 > SQL Server Profiler). This will allow you to see all the commands which are running, as well as what application is calling them.

When you create a new trace the events you are going to be most interested in are:
SQL:BatchCompleted
RPC:Completed

They should be selected by default. You can remove the other events which are there by default. You can then leave SQL Profiler running for a couple of days and get a full view of everything that is happening. If you have a high load SQL Server what ever machine you run SQL Profiler on will need a lot of disk space. It's recommended that you don't run SQL Profiler on the SQL Server it self as SQL Profiler is a very system intensive application which can cause performance problems when running it on the servers console. I typically run SQL Profiler from my workstation against our production databases.