How to find the data file location of the Database ?

While creating the database the two types of data files such as MDF & LDF will be created in the drive.

If we want to see the data file location of the particular database we can find the location via T-SQL query.

SYNTAX

SP_HELPFILE

EXAMPLE

I just executed the below query to find the location of the adventure works database in my instance.

The above command will return the name of the data files , location and its filegroup etc as shown above.

You can easily identify the location using the above command in your instance.

How to Get the Running query text using DMV ?

Following is the one of the way to find the running query in SQL server.

In the query window 1 i ran the below query and  my server process ID is 55 and it is highlighted below.

For demonstration purpose i used the following query.

In the query window 2 i ran the below query to find the running query as shown below.

How to find the time of running query in MilliSeconds?

In SQL server we always found the running query in Seconds but we have never the seen the time in Milliseconds.

To find the time in Milliseconds we can use the following Syntax.

SYNTAX

SET STATISTICS TIME ON

T-SQL STATEMENT

SET STATISTICS TIME OFF

EXAMPLE 

The following example i used the STATISTICS TIME ON & OFF statement to find the time take by the query in milliseconds.

In the message tab we can find the CPU time & Elapsed time in milliseconds as shown below.

How to find all the user defined Stored Procedure in SQL server ?

In SQL server we can get the name and definition of the stored procedure & function using System View called INFORMATION_SCHEMA.

The following example provides you the list of stored procedure in the AdventureWorks database.

We can get the definition of the stored procedure in the ROUTINE_DEFINITION Column.

In above query i used the Routine_Type as PROCEDURE , So it returns only the stored procedure list and you can get the list of functions by passing the Routine_Type as Function.

How to find the Database last Restored Date ?

In SQL server we have used many times to restore the database for Developing and testing purposes, But we don't consider the restored date.

In SQL server we have the option to find the restored date of a particular database in the MSDB database.

There you can find the restored date in the Restore_date column and the database name etc. 

How to Get the Running query text using SPID ?

While performing the tuning in SQL server sometimes we found that some of the queries are running for a long time and it would block the other transaction.

To get the running query in SQL server we can use the Database Console Command (DBCC) to get the running query.

SYNTAX

DBCC INPUTBUFFER (SPID)

If you know the SPID of the blocking query then the above command would be helpful to get the blocking query.

EXAMPLE

In the query window 1 i ran the below query and  my server process ID is 55 and it is highlighted below.

For demonstration purpose i used the following query.

In the query window 2 i ran the DBCC command by passing the Server Process ID obtained from the previous query window 1 as shown below.

After executing the above command I found the Running query in the EventInfo column as shown above.

From this you can find the running query in SQL server.
Refer here to find another way of getting the running query text.

Use of SPID in SQL server ?

SPID stands for Server Process ID. SPID returns the session ID of the Current User Process.

In SQL server each session is identified by its own SPID.

If you get the SPID then you can get the running query and also possible to KILL the query.

SYNTAX

SELECT @@SPID

EXAMPLE

The following example provide you the clear idea about the Session ID of the session.

In the above example i have used the system function @@SPID to get the session ID.

It returns ID value as 55.

Without using the system function you can also get the Session ID at the bottom of the query window (Highlighted in Red Box)

This session ID will be helpful when you identifying the blocking queries in SQL server.