SQL SERVER – Get Last Running Query Based on SPID

We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.

To know which sessions are running currently, run the following command:

SELECT @@SPID
GO

In our case, we got SPID 57, which means the session that is running this command has ID of 57.

Now, let us open another session and run the same command. Here we get different IDs for different sessions.

In our case, we got SPID 61. Please note here that this ID may or may not be sequential.

In session with SPID 61, we will run any query. In session with SPID 57, we will see which query was run in session with SPID 61.

Let us run a simple SELECT statement in session with SPID 61 and in session with SPID 57 run the following command.

DBCC INPUTBUFFER(61)
GO

Now, here in DBCC command we have passed the SPID of previous session; we will see the text below. The following image illustrates that we get the latest run query in our input buffer.


There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM
sys.dm_exec_sql_text(@sqltext)
GO

The following image portrays that we get the latest run query in our input buffer.


Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.

There is one more way to achieve the same thing – using function fn_get_sql

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM
::fn_get_sql(@sqltext)
GO


All the three methods are same but I always prefer method 2 where I have used sys.sysprocesses.

Today, we have explored a very simple topic. Let me know if you find it useful.

Reference : Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reseed Identity of Table – Table Missing Identity Values – Gap in Identity Column

Some time ago I was helping one of my Junior Developers who presented me with an interesting situation. He had a table with Identity Column. Because of some reasons he was compelled to delete few rows from the table. On inserting new rows in the table he noticed that the rows started from the next identity value which created gap in the identity value. His application required all the identities to be in sequence, so this was certainly not a small issue for him.

The solution to this issue regarding gap in identity column is very simple. Let us first take a look at his application’s situation wherein there is missing identity and then we will move on to the solution.

Developers can easily deter the above issue by avoiding gap in sequence of identity column through two additional SQL Tricks of reseeding identity.

We will now see the same example with the solution to the above gap issue. On deleting records, table was reseeded with identity, which was deleted. Download complete SQL Script here.

USE AdventureWorks
GO
/* Create a table with one identity column */
CREATE TABLE TableID (ID INT IDENTITY(1,1), Col VARCHAR(10))
GO
/* Insert 10 records with first value */
INSERT INTO TableID (Col)
VALUES ('First')
GO 10
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Delete last few records */
DELETE
FROM
TableID
WHERE ID IN (8,9,10)
GO
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Get current Max Value and reseed table */
DECLARE @MaxID INT
SELECT
@MaxID = MAX(ID)
FROM TableID
DBCC CHECKIDENT('TableID', RESEED, @MaxID)
GO
/* Insert 10 records with second value */
INSERT INTO TableID (Col)
VALUES ('Second')
GO 5
/* Check the records in table */
SELECT *
FROM TableID
GO
/* Clean Database */
DROP TABLE TableID
GO

I hope is solution is clear to all my readers and they will use it to avoid problems related to gap in identity column. Do send me your feedback on this article and let me know if you all need further explanation.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation

Just a day ago I have received following email from Siddhi and I found it interesting so I am sharing with all of you.

Hello Pinal,

I have seen many blogs from you on SQL server and i have always found them useful and easy to understand. Thanks for all the information you provide.

I have one query about shrinking NDF and MDF files.

Can we shrink NDF and MDF files?? If you do so is there any data loss?

I have been shrinking the .LDF files every now and then but I am not too sure about NDF and MDF files.

Can you please answer my query.

Waiting for your early response.

Regards,
Siddhi

Answer:
Shrinking MDF and NDF file is possible and there is no chance of data loss.

It is not always advisable to shrink those file as those files are usually growing. There are cases when one database is separated in multiple database of any large table is dropped from database MDF and NDF can contain large empty space. This is the time they should be shrank. Shrinking database can be many hours process but it there are very slim chances of data lose.
Following is the script to shrink whole database.
DBCC SHRINKDATABASE (dbName)

Following is the script to shrink single file.
DBCC SHRINKFILE (logicalLogFileName)

To find logicalLogFileName following command has to be ran.
USE dbName
EXEC sp_helpfile

Let us understand this using database AdventureWorks.
/* Shrink Whole AdventureWorks Database */
DBCC SHRINKDATABASE (AdventureWorks)
GO
/* Get the Logical File Name */
USE AdventureWorks
EXEC sp_helpfile
GO
/* Shrink MDF File of AdventureWorks Database */
DBCC SHRINKFILE (AdventureWorks_Data)
GO
Following image of the same process show when whole process is done there will be resultset with information about the new states of the database files.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2

Just another day I was playing with my query which I posted earlier SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL and I found that I got error devide by zero. I have fixed this error in following query as well I have updated query to return time in millisecond instead of microsecond. Jerry Hung has also posted similar solution in comments of original article.

I strongly suggest to read original article to now more about introduction and learn about DBCC command which clears cache.

SELECT DISTINCT TOP 10
t.
TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Number of Rows and Disk Space Reserved – Using sp_spaceused Interesting Observation

Previously I posted SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. Today we will look into the same issue but with some additional interesting detail.
We can find the row count using another system SP sp_spaceused. This SP gives additional information regarding disk space reserved on database as well. Well, when I ran the SP on AdventureWorks first time, I suspected that database SP is not providing me correct results.

After a bit investigating I found that it may be possible that due to any reason may be the usage on AdventureWorks database might not be updated. I ran same SP with additional param as shown below and it provided me updated information for the same. Once I ran the SP with @updateusage param, it updated the status of the database. When I ran it again next time without param it gave the same results.

The reason for above behavior is when dellocation of space happened in database (e.g. dropping table or truncating table) it does not release the used space right away. However, when @updateusage param is used database engine scans whole database and update the sapce usage in respective tables.
Let me know if you guys have any interesting observation like this.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – 2005 – Find Longest Running Query – TSQL

UPDATE : Updated this query with bug fixed with one more enhancement SERVER – 2008 – 2005 – Find Longest Running Query – TSQL – Part 2.

Recently my company owner asked me to find which query is running longest. It was very interesting that I was not able to find any T-SQL script online which can give me this data directly. Finally, I wrote down very quick script which gives me T-SQL which has ran on server along with average time and maximum time of that T-SQL execution. As I keep on writing I needed to know when exactly logging was started for the same T-SQL so I had added Logging start time in the query as well.

The reason I started to use this script to find out longest running query as if query is changed a bit it will display it as new row and new log start time. This way when I am improving T-SQL query or Stored Procedure I can check their progress in the query and does not have to get bothered with previous data.

I always run following DBCC command before I started to use my query. Following DBCC commands clears the cache of the server and starts fresh logging of the query running time.

DBCC FREEPROCCACHE

Run following query to find longest running query using T-SQL.

SELECT DISTINCT TOP 10
t.
TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO

You can also add WHERE clause to above T-SQL query and filter additionally.

If you have not ran query like this previously on your server I strongly recommend to run this. I bet you will find surprising results.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Check Database Integrity for All Databases of Server

Today we will see quick script which will check integrity of all the database of SQL Server.

EXEC sp_msforeachdb 'DBCC CHECKDB(''?'')'

Above script will return you lots of messages in resultset. If there are any errors in resultset they will be displayed in red text. If everything is black text there is no error. Typical output of above script will be like image included in the article.

Image displayed above is only partial image.

Reference : Pinal Dave (http://www.SQLAuthority.com)