How to Verify SQL connectivity using SQL Profiler

First let’s find some information about the target database. In my case I’m trying to monitor any connections to the ‘AdeventureWorks’ database. I open the ‘Microsoft SQL server Management Studio’ and hit the ‘New Query’ button.

In the query window I enter the follow SQL transaction statement

SELECT * FROM master..sysdatabases WHERE name like ‘%adventureworks%’

Of course you will enter in your database target name where I entered ‘adventureworks’.

This tells us our target database has an ID of 23. Note the ‘dbid=23’.

Now, log into your SQL server and launch ‘SQL Server Profiler’
start>Microsoft SQL Server 2008 R2>Performance Tools>SQL Server Profiler

Enter in the appropriate information to connect to the target SQL server where the database is to be monitored.

Click Connect

Enter in the proper information for your trace and select the ‘Events Selection’ tab

Make sure the ‘Show all columns’ it checked. This provides a good start but we need to add in a couple things. Scroll to the right within this window. Check all boxes for the following columns: DatabaseID, DatabaseName, HostName, and NTDomainName.

Click ‘Column Filters…’, select DatabaseID, select Equals, type 23, and click OK.

Click Run.

 

As you can see we are seeing real time traffic to our SQL server. In this case I have no traffic to the database so I have no real time traffic in my trace window.

Now that we have the trace ready to go lets pause and then erase the data. This will give us a fresh slate when we are ready.

Get the excel worksheet ready and press start in the profiler. Refresh your connections…

Here I wanted to show you what a Windows account versus a SQL account login might look like.

Tags :

About the Author

Leave a Reply

%d bloggers like this: