Focus Your Monitoring: Capturing Events from a Single Database in SQL Server Profiler

1. Open SQL Server Profiler.
2. Create a new trace or open an existing one.
3. Go to the trace Properties (usually by right-clicking the trace and selecting Properties).
4. Navigate to the Events Selection tab.
5. Enable the checkbox for "Show all columns". This ensures you have access to the database name column for filtering.
6. Click the Column Filters button on the bottom right.
7. In the Edit Filter window, you'll see a list of available data columns for filtering.
8. Locate the DatabaseName column.
9. Choose the Like operator from the dropdown menu.
10. Enter the name of the specific database you want to track (enclose it with wildcard characters % like %MyDatabaseName%). This captures events where the database name partially matches your input.
11. Click OK on all open windows.

Explanation:

• By enabling "Show all columns," you ensure the "DatabaseName" column is available for filtering.
• The "Like" operator with wildcards allows capturing events from the database whose name partially matches what you entered. For example, %MyDatabaseName% would capture events from databases named "MyDatabase," "OtherDatabase_MyDatabase," etc.

Additional Notes:

• Make sure to define your filter before starting the trace.
• You can use other comparison operators (Equals, Not Like, etc.) depending on your specific needs.

Steps:

1. Open SQL Server Profiler and create a new trace.
2. In the Events Selection tab, you don't need to write any code. Just select the events you want to capture (e.g., SQLBatch:Starting, SQLBatch:Completed).
3. Now, click the "Column Filters" button.
4. In the Edit Filter window, imagine the following selections:
   • Column: DatabaseName (This is automatically available, not written as code)
   • Operator: Like (This is chosen from a dropdown menu, not written)
   • Value: %Sales% (This value is what you type)

Even though you're not writing code directly, these selections translate to a filtering condition similar to:

WHERE DatabaseName LIKE '%Sales%'

This condition ensures the trace captures events only where the "DatabaseName" column contains "Sales" (or any string with "Sales" within it due to the wildcards).

Remember:

• The actual interface involves selecting options and entering values, not writing code.
• This example clarifies the logic behind the filtering process.

This method leverages the unique identifier (DatabaseID) assigned to each database in SQL Server.

• 1. Instead of using the Events Selection tab filtering, navigate to the General tab in the trace properties.
  2. Locate the Database ID filter section.
  3. Enter the DatabaseID of the specific database you want to track. You can find the DatabaseID by querying sysdatabases table in SQL Server Management Studio (SSMS):

  SELECT dbid, name FROM sys.sysdatabases 
  WHERE name = 'YourDatabaseName'
  

  Replace 'YourDatabaseName' with the actual name of the database you're interested in.

  1. Click OK to save the trace properties.

Using Transact-SQL (T-SQL) Stored Procedure:

While less common, you can achieve trace filtering through a T-SQL stored procedure. This method involves creating a stored procedure that captures the desired events and includes filtering logic based on the database name.

Here's a general outline (consult SQL Server documentation for specific syntax):

1. Create a stored procedure that uses sp_trace_start to initiate the trace.
2. Within the stored procedure, use sp_trace_setfilter to specify the DatabaseName filter criteria.
3. Define the events you want to capture using sp_trace_setevent.
4. Start the trace using sp_trace_run.

Benefits of Alternate Methods:

• DatabaseID Filter: This method can be quicker if you readily know the DatabaseID.
• T-SQL Stored Procedure: Offers more flexibility for complex filtering logic or automation through scripts.

• The first method using Trace Properties with "Show all columns" and filtering by DatabaseName is generally the most straightforward approach.
• Choose the method that best suits your comfort level and specific needs.