Monitoring Our Database When Accessing From Outside Or Applications In SQL Server

Vignesh Mani

9y11.3k5100Article

SQL Server

5 Comments

Join the conversation! Your thoughts help the community grow.

sorosh FT9y ago
Thanks. its good but i have a question: i have c# project that work with database so the log file of my data base is big . how to see in my log file & shrink it? i read many article but i do not have enough information & i confuse for example what is recovery data model:(.can you help me?
Prabu Elavarasan9y ago
Is this will show with SPs which is consuming more time. Just assume from Client side application is taking more time for request and response. From db side is this help us to identify which objects consuming more time?
- Vignesh Mani9y ago
  Hi Prabhu. Using thisarticle can not find which object consuming more time. Normally we can check which object wondering our sql server. We can find which object consuming more time, I will write that article next.
Ganapathi Raman9y ago
BRO. Thanks for the great information.
- Vignesh Mani9y ago
  Welcome dear bro. Thnks for feedback..

Introduction

This article explains how to monitor our database when accessing from outside or by any other applications, in an easy way. We normally use SQL Server as a database, so we want to monitor our database or what happens in our database from the application call. We can find what SQL Server object is executed and when it is executed in our SQL server. We can monitor our database using SQL Profiler in SQL Server.

Using SQL Server Profiler, we can find what stored procedures and views are executed, and what other processes happened or are happening.

Steps for monitoring database in SQL Server

Step 1

Open SQL Server and select server name, authentication type, then enter SQL server username, password and connect to the Server.

Step 2

Here, we are using Sample database. In samples, the database contains many tables and has one user-defined procedure and views.

Step 3

We need to monitor our sample database, so we are going to open SQL Server Profiler. Select Tools >> SQL Server Profiler.

Now, SQL Server Profiler window will be opened. Select Server name, authentication type, then enter SQL server username, password and now Connect to the Server

Step 4

"Trace Properties" window will open. Enter the Trace name in general tab.

SQL Server

Select "Event Selection" tab to select the “Show all events" and "Show all columns” checkbox. We can see what events there are in the event selection tab.

There are many databases on our Server, so we can monitor either specific or all databases.

Here, we monitor sample database only, so filter for sample database.

Click “Column Filters”. "Edit Filter" window will open.
Select “DatabaseName” then expand "Like".
Enter your database name, that looka like the below screenshot.
Click Ok followed by click on Run button.

Step 5

Our SQL Profile has started, if anything executes in the sample database, we can see in our SQL Server Profiler window. Before executing anything in our database, we can see the status, which looks like the below screenshot.

Now, let's run the stored procedure manually.

After running the stored procedure, we can see in profiler which stored procedure ran and when it started and when it ended and all other information. If any events are executed in our database all events will be monitored by SQL Profiler.

SQL Server

Now we execute one view in our database. After executing view we get all details of view by profiler, which looks like below screenshot.

In the same way as mentioned above we can monitor all the events in the database. We can monitor all databases at the same time using mentioned same steps but no need to select column filter do not select anything and do not give anything in Like option.

SQL Server

Note

SQL profiler should be running in SQL server otherwise we cannot monitor it. If we stop profiler we can not monitor it.

Conclusion

This article explained how to monitor our database when accessed from outside or any other applications in an easy way. I hope this is helpful to new SQL Server learners.