Post reply

SQL Profiler

  • April 3, 2012 at 9:42 pm

    #271486

    I have 2 databases db1 and db2 with 5 tables each t1, t2, t3, t4, t5. I want to create a trace in such a way that when tables t1, t2, t3 from db1 and tables t1, t3 and t4 from db2 accessed but not the rest of the tables, the trace should log the results in 1 file.

    I tried sql profiler but its not allowing to filter 2 databases with their respective tables.

    Is it even possible in SQL Profiler or there is some other way.

    Thanks

  • April 4, 2012 at 2:41 am

    #1468680

    see if this helps:

    http://www.databasejournal.com/features/mssql/article.php/3887996/Determining-Object-Access-Using-SQL-Server-Profiler.htm

  • April 4, 2012 at 2:55 am

    #1468681

    you can also export the trace definition to a .sql script and modify that in SSMS which may improve things also

  • April 4, 2012 at 5:35 am

    #1468757

    I wouldn't recommend using Profiler on a production system. The way it gathers data can lead to performance problems. Better to set the trace up using TSQL scripts.

    If you can't filter by both databases, set up to traces, one for each database, then you can combine the data later if needed.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 4, 2012 at 5:49 am

    #1468764

    If I combine the data from 2 trace files and load them into a table then log gets filled because there are millions of records. We need to keep atleast 7 days data in a table.

  • April 4, 2012 at 6:05 am

    #1468772

    You just have to load it in smaller chunks and make sure your transaction log backups are running often enough (if the database is in FULL recovery). We use to load millions of records of trace data daily, keep about two weeks worth online and convert the rest to aggregate data for historical tracking. It's not hard, it's just work. Best way to get around it is to limit the size of the trace files and then capture them off the disk as they get full. You can do this with an SSIS package. That way you're only loading small chunks, you're just loading it constantly. Oh, and this is not on the same server that you're monitoring (of course, but worth mentioning).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply