How to Gather SQL Server Inventory Using the SQL Server Trace
This lesson goes into detail about how SQL Server Inventory is gathered in Asset Vision. Since the introduction of SQL Server 2005, there is a simple lightweight trace that is left running by default on every SQL Server. This provides some very valuable information about the running server. This useful information of the SQL server environment can be used to assist Asset Vision's inventory process. The default trace does not contain per-database/table activity information just per-database user/application connection information.
SQL Server Trace
Navigate to Admin > DataView Management > SQL Server Instance.
Right-clicking on an instance and selecting Collect Client Connections brings up the message above.
- By default, Clear usage will be checked which means that it will clear any usage information for the selected instance, and collect the past 30 days’ worth of connection information – there could be fewer than 30 days’ worth of data, as the default trace is restarted whenever the SQL Server instance is restarted. Because the default trace is ongoing, data can be collected any number of times and as long as Clear usage is de-selected, the data will be added to the existing data with no problems. Contrast this with activity data collection, where read/write counts are cumulative, so collecting from the same data results in duplication of counts, potentially skewing the data.
- The user can select Collect to proceed.
The SQL Server default trace feature is only available for version 2005 and later. If you try to do this on an SQL Server 2000 instance, you will receive this error message.
Activity Metering Settings
You can either run a new SQL Server Trace against the selected instance, or import data that has been previously collected from the instance. Select either the Run new trace option, or the Import from file option.
- If you select Import from file, enter a path/file name for a trace file that is accessible from the selected SQL Server instance.
- If you select Run new trace, select the trace definition to be used for the SQL Server Trace.
Warning: depending on the trace events/columns defined in the selected trace definition, the trace could generate a significant quantity of data and/or impact the performance of workloads on the selected SQL Server instance. Any trace definitions should be reviewed by a DBA and tested to initiating a SQL Server Trace.