Collect user stats before working to decommission the server

Question

Collect user stats before working to decommission the server

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 19, 2019 at 6:27 pm

#3709210

Hello,
Before decommissioning the server (2 node AlwaysOn), thought to capture the user stats to know if any of the application is connecting to the instance before taking the decision to take off the servers.
How do you advise to collect the user stats? Just running the selective commands from sys.sysprocesses and running a job to capture the details in a table. Is that the best method?

Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

You could certainly query sys.dm_exec_query_stats. That's going to show the queries that are currently in the cache, recent activity. It includes a last execution time too, so you can get an idea of when the queries were run. This will miss anything that's aged out of cache or didn't go into cache in the first place, however if the server is on the way out, I suspect this may not be a problem.

For detailed analysis of what/when queries are being run, I'd use Extended Events. rpc_completed and sql_batch_completed will tell you all you needed to know.

"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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

If you're only interested in connections, rather than what they're actually doing, then you only need to capture login events. If your position is that nobody is connecting, but you just want to make sure, I'd do it by setting the errorlog to capture successful logins. You can use Extended Events or SQL Audit if you prefer, though.

John

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 3

If you wanted to be absolutely sure, you could change what is captured in the error log to log both successful and unsuccessful logins and then bounce the service to force anything that has a long term login active to have to re-login. It's a BSOFH method but it would be a guarantee not to mention being able to provide you with a list if you interrogate the log.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)