Review of DB Activity

Question

Review of DB Activity

HeadScratcher

SSC Enthusiast

Points: 116
More actions
November 30, 2023 at 10:07 am

#4337244

Hi Folks,

I have been tasked with reviewing all the activity on a SQL Server 2000 - Version 8.00.2666 (SP4)
I have monitored DB's before and via execution of Master DB stored procs, watching activity and so on, however never really when in to this depth of attempting to find out all the activity that is taking place within a SQL server, particularly one this old. Some of the DB's are live, whilst some are probably redundant.
I am wondering how is the best way to go about this. I have a few scripts that i am going to run checking execution cache(hopefully they work in SQL Server 2000) One has an xml output field(returning the query), doubt it will work. I have also started opened the SQL profiler, however there is a whole lot of things going on in there, that appear to be SQL server related actions, can i eliminate these easily and just focus on the connections, queries being fired, what application is calling them?
It is also SQL Enterprise manager and not SSMS that is running - not sure it would cope with SSMS or any other tools such as spotlight being added.

Any advice would be appreciated.

Thanks

Gordon
December 29, 2023 at 2:35 am

This was removed by the editor as SPAM

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

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

So, probably, most of your scripts for looking at queries and query behavior use DMVs. Which were released in 2005. That means, those scripts won't work. If you want to do this kind of thing in 2000, you're really dependent on Profiler/Trace. There simply weren't the same kinds of tools back then that we have now.

As to SSMS, it likely will not work. It might though. Get as old a version as you can. Otherwise, yeah, Enterprise Mangler, uh, I mean Manager, is the tool to use.

Good luck on this.

"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

HeadScratcher SSC Enthusiast Points: 116 More actions · Answer 2

Thank you for the response Grant,

you are correct and as i suspected, the scripts do not work and i have cobbled together a script using sysprocesses and executing DBCC INPUTBUFFER in order to retrieve the spID related Query. Every step had a few hoops to jump through, but i have got something going.

I did not try SSMS, as i knew it would just be a minefield and i would be updating .net frameworks and all sorts, no doubt, whilst being oblivious to all the systems across the company being down!!

I will look in to SQL Profiler a bit more and also trace, as you say, i saw this mentioned today somewhere else on my travels down many tangents.

Thanks Again

Gordon

Ken McKelvey SSCoach Points: 18856 More actions · Answer 3

It may also be worth speaking to you network team as something like Wireshark could tell you what is communicating with the server. Also, given the age of the server, the packets are unlikely to be encrypted.

HeadScratcher SSC Enthusiast Points: 116 More actions · Answer 4

Thank you for the advice Ken, I will look in to wireshark.

HeadScratcher SSC Enthusiast Points: 116 More actions · Answer 5

Hi Instrumentalguy,

Many thanks for response, i have begun using the SQL Profiler in conjunction with code i wrote to pull as much as possible.

The Profiler has a lot of noise going on, but i have created a couple of templates that allow me to tweek the return, insert to table and query from there.

thanks Gordon