Who’s doing what to your SQL Server?

When doing any kind of troubleshooting in your SQL Server first thing you ask is who is currently connected to the SQL Server having problem and what the heck is going on? First thing you do? Run sp_who2. That will simply tell you about the sessions currently on in your server and the status if its ACTIVE, SUSPENDED, AWAITING or what have you. Luckily for us DBAs, in SQL Server 2008 a lot of DMVs have been created primarily for health checks, troubleshooting and verifications.

To check connections and their activity connected to your SQL Server and to know what they are actually doing you can run the following statement

SELECT dec.client_net_address, dec.host_name, dest.text

FROM sys.dm_exec_sessions des INNER JOIN sys.dm_exec_connections dec

CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest

If you want to filter only those activity that is using SSMS, you can add a WHERE clause like below

SELECT dec.client_net_address, dec.host_name, dest.text

FROM sys.dm_exec_sessions des INNER JOIN sys.dm_exec_connections dec

CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest

WHERE des.program_name LIKE ‘Microsoft SQL Server Management%’

You can then flag red any activity that you may deem suspicious or flag any client net address not associated with any workstations within your development team. The DMV dm_exec_sessions has so much information on the sessions that is currently open in your SQL Server. I will tackle this on my next post. 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

SHE GOES GLOBAL

Travel the world, change your life.

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: