August 14, 2011 at 5:00 am
Dear friends,
I am curious to know, is it possible to get reports of previous sessions in SQL Server 2005 ? Like we can get them in Oracle, such as AWR or ASH reports.
If we can get that kind of reports it will be very helpful for us. I am aware of "default trace" but that trace doesn't capture much useful data. It is not showing the text for any SPID, not even the EndTime for the SPIDs. Most of the columns return as NULL :hehe:
Any help is highly appreciated 🙂
Regards,
Sujeet
August 14, 2011 at 6:50 am
What you need is to make your own trace.
Openup profiler (windows, start, sql, performance).
then select what you need there. There are dmvs also but it depends on what kind of work you need to do.
August 14, 2011 at 7:22 am
Thanks for the reply Ninja 🙂
As I said earlier in my post, I am looking for something where I can see session history. Profiler is not an option because profiler cannot be run on production all the time.
Why I am asking this question is, suppose you are looking after a SQL database & the application that is connecting to it starts running very slow all of a sudden in the night when you are not there.
Now, you come to the office next morning & check the database, you found that database is working very fine. There's no load on it, no high CPU, no nothing. Therefore although you are quiet sure that the problem didn't happen because of SQL Server, you want to know what sessions were running on the database server when application was running slow.
So, if we can get that kind of historical information it will be very useful ;-). If there is any DMV which can do that, kindly let me know.
I hope I am clear now 🙂
Regards,
Sujeet
August 14, 2011 at 7:30 am
Profiler (server side trace) is very light.
This is exactly the kind of work it was built to do.
I actually preffer that to the dmvs (just my own pref.)
August 14, 2011 at 7:38 am
Hi
This may be of interest
http://www.red-gate.com/products/dba/sql-monitor/
free trial available
...I dont work for Redgate.
regards gah
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 14, 2011 at 8:38 am
Thanks for providing the useful links although they dont provide the exact thing. However, Gail Shaw has always written very useful articles & these are one of them 🙂
Regards,
Sujeet
August 14, 2011 at 8:42 am
Thanks Gah,
I will try that tool on test server. I was looking for some SQL Server query (probably DMVs) to that job actully 🙂
Regards,
Sujeet
August 16, 2011 at 6:03 am
You could also pull this data from dmv's and write it to a table using a job.
Here's a helpful query:
SELECT session_id, connect_time, last_read, last_write, TEXT
FROM sys.dm_exec_connections
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS ST
Personally, I use Quest Spotlight to retain my session data in a central repository.
Good Luck!
Hawkeye DBA
August 18, 2011 at 9:22 am
Sujeet Pratap Singh (8/14/2011)
Thanks for providing the useful links although they dont provide the exact thing.
The short answer is, you have to implement this yourself in SQL Server. I second the recommendation to use a Server-side Trace for this kind of work. Don;t confuse Profiler with Trace...Profiler is a client application that allows you to setup a Server-side Trace definition and optionally use it to start and view results of the Trace data as it is running. A common and safe approach is to run Profiler locally to setup the Trace definition since it is more friendly to choose the options using the GUI, however do not run the Trace using Profiler...that is where the problems can start. Save the Trace definition from Profiler to the Server, then start the Trace directly on the server using T-SQL it have it save its results to a file directly on the server. Trace's are more efficient and reliable that way. Trace can be expensive, or lightweight, it depends on what events and columns you decide to capture. Be judicious in your choices. If you were to look at what Oracle is capturing to build their report you could map those events and data elements to SQL Server Trace Events and Data Columns. The fact that Oracle is always capturing this information in the background by default is a waste of resources if you do not need it IMHO.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 18, 2011 at 10:07 am
Thanks opc.three,
I will surely explore & try server side trace for this kind of work. I think Oracle reports cannot be said a waste of resources, because these reports certainly provide you valuable data in the time of crisis. They do it for you becuause you never know when the problem comes ;-).
Regards,
Sujeet
August 18, 2011 at 1:48 pm
Sujeet Pratap Singh (8/18/2011)
Thanks opc.three,I will surely explore & try server side trace for this kind of work. I think Oracle reports cannot be said a waste of resources, because these reports certainly provide you valuable data in the time of crisis. They do it for you becuause you never know when the problem comes ;-).
Regards,
Sujeet
It was just an opinion, you are free to disagree 🙂
SQL Server most certainly "does a lot of things for you" too in an attempt to let you bail yourself out in a time of crisis. However capturing every SQL statement that has been run by each user in the near past is not one of them...at least not in such a way that is able to be easily broken down and presented in the way I think you are expecting. Hit us up again if you need more help or info. Good luck!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply