SQL Server provides two ways to trace an instance of SQL Server: you can trace with SQL Server Profiler, or you can trace using system stored procedures.
SQL Server Profiler is still a tool used to monitor our relational databases and our multidimensional ones. We used for performance and security purposes. However, in the SQL Server 2016, they announced that the SQL Profiler will be deprecated in future versions.
After quickly describing SQL Server Profiler, it is worth mentioning that there is another tool provided by SQL Server that can read and analyze the traces created by the Profiler. This tool is called SQL Server Tuning Advisor. You can access it from SQL Server Management Studio.
Under Trace properties > Events Selection tab > select show all columns. Now under column filters, you should see the database name. Enter the database name for the Like section and you should see traces only for that database.
In SQL 2005, you first need to show the Database Name column in your trace. The easiest thing to do is to pick the Tuning template, which has that column added already.
Assuming you have the Tuning template selected, to filter:
I always save the trace to a table too so I can do LIKE queries on the trace data after the fact.
By experiment I was able to observe this:
When SQL Profiler 2005 or SQL Profiler 2000 is used with database residing in SQLServer 2000 - problem mentioned problem persists, but when SQL Profiler 2005 is used with SQLServer 2005 database, it works perfect!
In Summary, the issue seems to be prevalent in SQLServer 2000 & rectified in SQLServer 2005.
The solution for the issue when dealing with SQLServer 2000 is (as explained by wearejimbo)
Identify the DatabaseID of the database you want to filter by querying the sysdatabases table as below
SELECT *
FROM master..sysdatabases
WHERE name like '%your_db_name%' -- Remove this line to see all databases
ORDER BY dbid
Use the DatabaseID Filter (instead of DatabaseName) in the New Trace window of SQL Profiler 2000
In the Trace properties, click the Events Selection tab at the top next to General. Then click Column Filters... at the bottom right. You can then select what to filter, such as TextData
or DatabaseName
.
Expand the Like node and enter your filter with the percentage %
signs like %MyDatabaseName%
or %TextDataToFilter%
. Without the %%
signs the filter will not work.
Also, make sure to check the checkbox Exclude rows that do not contain values' If you cannot find the field you are looking to filter such as DatabaseName
go to the General tab and change your Template, blank one should contain all the fields.
Create a new template and check DBname. Use that template for your tracefile.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With