what is hitting your db?

Question

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?

Answer 1

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)

Answer 2

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/

Answer 3

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

Answer 4

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.