Post reply

SQL Profiler

  • May 21, 2009 at 1:46 pm

    #205033

    Hello,

    We had a database emergency few days back and the temp db started to get huge. We solved it by restarting the SQL Server but we would like to investigate the transaction that caused this to happen.

    I am using SQL Profiler to achieve this goal by creating a trace file and setting the filter for start dae, end date and database. The date i enter here is that of 3 days back.

    I do not get anything on the profiler. It just says "Trace start" and the trace runs for a long time with no other output.

    Am I off the track ? Please help me.

    Thanks

    Shruti

  • May 21, 2009 at 1:56 pm

    #998546

    Profiler, and SQL Trace, show you what is happening now. It cannot go back in time. It is a real time tool.

    There is a default trace, but I'd guess that it doesn't have information from 3 days back.

    http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

  • May 22, 2009 at 11:53 pm

    #999277

    SQL Profiler can show you currently what is happening to your database and who is doing what and how the sql server is responding to it. It cant show past data.

  • May 24, 2009 at 4:33 am

    #999435

    As an alternative to running Profiler continuously, there are a number of third-party products which would have helped you here. One I am familiar with and can recommend (also for its excellent analysis views and intuitive UI) is Spotlight from Quest.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 24, 2009 at 4:48 am

    #999437

    Steve Jones - Editor (5/21/2009)

    There is a default trace, but I'd guess that it doesn't have information from 3 days back.

    It might, depends how active the server is and how often SQL is restarted. That said, it's a very limited number of events. It won't show queries that were running.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 26, 2009 at 1:18 pm

    #1000135

    Thank you all for your inputs.

    I managed to get a file by querying the default trace. But, I find that the default trace contains only the transaction ids and not the names. Im interested in knowign what transactions were going on. So, is there a way i can get the transaction names maybe by joining the default trace with a system/dynamic view that has the transaction names?

    Please let me know

    Thanks

    Shruti

  • May 26, 2009 at 1:21 pm

    #1000137

    Transactions don't have names, unless explicitly marked them with a BeGIN TRANSACTION and a name.

  • May 26, 2009 at 1:30 pm

    #1000143

    ok. is there a way to see the queries for those transactions? Like the textdata column while running a trace in sql server profiler ?

    Thanks

  • May 26, 2009 at 1:37 pm

    #1000149

    Only what's in the default trace. not sure how much information there is.

    You can get a tool like ApexSQL Log to read the log itself if you have backups or the actual file.

  • May 26, 2009 at 3:20 pm

    #1000222

    If you name a transaction with something like BEGIN TRANSACTION transaction_name, and trace the SQLTransaction event in Profiler, the transaction name appears in the ObjectName column. This is true for both Begin (0) and Commit (1) event sub classes.

    Paul

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 26, 2009 at 3:42 pm

    #1000228

    shruti.ratnagirish (5/26/2009)

    ok. is there a way to see the queries for those transactions?

    Not from the default trace. The SQL Batch/Statement start/complete events aren't there. The SP completed/started events aren't there.

    You can see what events are in the trace with this:

    SELECT DISTINCT t.EventID, e.name as Event_Description

    FROM fn_trace_geteventinfo(1) t

    JOIN sys.trace_events e ON t.eventID = e.trace_event_id

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 4, 2009 at 5:16 am

    #1004523

    use this command to shrink the temp db

    select * from sysfiles

    -- use this to get the temp log file name

    backup log tempdb with truncate_only

    dbcc shrinkfile (templog, 20)

    so do this instead of restarting the SQL Server and to avoid users getting a error during logging to the database

Viewing 12 posts - 1 through 11 (of 11 total)

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