Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What is a quick way of finding out what is hitting your database especially when the log files are going crazy. SQL profiler? If so, how?

share|improve this question
    
From "SQL profiler" I am going to assume you are using SQL Server... –  Gaius Apr 11 '11 at 12:51
add comment

4 Answers 4

up vote 7 down vote accepted

The quickest (and cheapest) way to find out what is hitting your databases is to use sp_whoIsActive (http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx)

share|improve this answer
    
+1 for sp_whoIsActive –  Jeff Apr 11 '11 at 16:45
add comment

Can you add more details? I hope you are using SQL Server and which version + edition? Are your log files are filling up? Are you taking transaction log backups regularly? What are the result of the below?

select name, log_reuse_wait_desc from sys.databases

DBCC OPENTRAN()

You can use profiler to trace all the current activity but on a busy system, it can hurt the performance? Do you know how to do server side trace? You can try that below. But you need to share more details to get good responses.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

share|improve this answer
add comment

If you want to use profiler for this without being resource intensive on server, I would do the following:

  • Start profiler, connect to the server and choose a blank trace
  • Add event: security Audit -> Audit Schema Object Access Event and choose these columns : ApplicationName, ObjectName, LoginName, Permissions, DatabaseID, DatabaseName, SPID, ObjectType, OwnerName, StartTime
  • Script it out
  • In the script, assign a file name for @tracefile, @maxfilesize variables and put a rollover value in sp_trace_create. Normally I set maximum file size to 10 (MB) and then when it fills up, SQL creates a new file if the rollover bit is set to 2.
  • then run it from a remote location via TSQL

Read THIS for sp_trace_create stored procedure

share|improve this answer
add comment

In Oracle if you have the diagnostics pack available, simply go to the Performance tab in Grid Control or DBConsole and see which sessions are currently playing with your database. If it happened longer ago, take a dive into the awr reports and find the top SQL based on various metrics. It would help, if you mentioned a bit more about the actual database you are using, for example, brand, sometimes platform and versions.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.