Skip to main content
Our Tech Ideas

TSQL Queries Used by SQL DBA in day to day

In this article, we’ll explore some of the most commonly used TSQL queries for DBAs, how to use them effectively, and the benefits they offer. Whether you’re a beginner or an experienced DBA, this article will have something for you.

Find log file used and free space?

DBCC SQLPERF(LOGSPACE)
SQL

Find free space on the disk?

exec xp_fixeddrives
SQL

Find SQL Server version installed in your server?

Print @@VERSION
SQL

Find when your SQL Server was last restarted?

SELECT sqlserver_start_time from sys.dm_os_sys_info
SQL

Find database size in SQL Server?

sp_helpdb
SQL

How to check free space and space used by database data files and log files in SQL Server?

SELECT
    DB_NAME() AS DbName,
    name AS FileName,
    size / 128.0 AS CurrentSizeMB,
    (size / 128.0) - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS FreeSpaceMB
FROM
    sys.database_files;
SQL

How to get session details in SQL Server?

select * from sys.dm_exec_sessions;
SQL

Find users that are connected to the server

The following example finds the users that are connected to the server and returns the number of sessions for each user.

SELECT login_name,
    COUNT(session_id) AS session_count
FROM sys.dm_exec_sessions
GROUP BY login_name;
SQL
exec sp_who2;
SQL

How to get a blocking query in SQL Server?

SELECT * FROM sys.sysprocesses WHERE blocked <> 0;
SQL
exec sp_who2;
SQL

How to get the list of databases which are offline in SQL Server?

SELECT name
	,database_id
	,create_date
FROM sys.databases
WHERE state_desc = 'offline';
SQL

How to get database backup history details in SQL Server?

SELECT TOP 100
    s.database_name,
    m.physical_device_name,
    CAST(CAST(s.compressed_backup_size / 1048576 AS NUMERIC(14, 2)) AS VARCHAR(14)) + ' ' AS "bkSize in MB",
    CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' AS TimeTaken,
    s.backup_start_date,
    CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
    CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
    CASE s.[type]
        WHEN 'D' THEN 'Full Backup'
        WHEN 'I' THEN 'Differential Backup'
        WHEN 'L' THEN 'Transaction Log Backup'
    END AS BackupType,
    s.server_name,
    s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name IN (
    SELECT name FROM sys.databases WHERE name NOT IN ('tempdb', 'master', 'msdb', 'model')
)
    AND s.backup_start_date >= DATEADD(day, -1, GETDATE())
ORDER BY s.database_name, s.backup_start_date DESC, s.backup_finish_date
SQL
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SERVER
	,d.name AS 'DatabaseName'
	,d.database_id AS ID
	,d.state_desc AS STATUS
	,MAX(CASE 
			WHEN bu.TYPE = 'D'
				THEN bu.LastBackupDate
			END) AS 'Full DB Backup Status'
	,MAX(CASE 
			WHEN bu.TYPE = 'I'
				THEN bu.LastBackupDate
			END) AS 'Differential DB Backup Status'
	,MAX(CASE 
			WHEN bu.TYPE = 'L'
				THEN bu.LastBackupDate
			END) AS 'Transaction DB Backup Status'
	,CASE d.recovery_model
		WHEN 1
			THEN 'Full'
		WHEN 2
			THEN 'Bulk Logged'
		WHEN 3
			THEN 'Simple'
		END RecoveryModel
FROM MASTER.sys.databases d
LEFT OUTER JOIN (
	SELECT database_name
		,TYPE
		,MAX(backup_start_date) AS LastBackupDate
	FROM msdb.dbo.backupset
	GROUP BY database_name
		,TYPE
	) AS bu ON d.name = bu.database_name
GROUP BY d.Name
	,d.recovery_model
	,d.database_id
	,d.state_desc
SQL

How to get the last index statistics updated date in SQL Server?

SELECT name AS index_name
	,STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
-- Put table name here
WHERE OBJECT_ID = OBJECT_ID('[Person].[EmailAddress]');
SQL

How to get statistics of all objects of an entire database in SQL Server?

SELECT a.id AS 'ObjectID'
	,isnull(a.name, 'Heap') AS 'IndexName'
	,b.name AS 'TableName'
	,stats_date(id, indid) AS stats_last_updated_time
FROM sys.sysindexes AS a
INNER JOIN sys.objects AS b ON a.id = b.object_id
WHERE b.type = 'U'
GO
SQL

How to get index fragmentation in SQL Server?

SELECT OBJECT_NAME(DMV.object_id) AS TABLE_NAME
	,SI.NAME AS INDEX_NAME
	,avg_fragmentation_in_percent AS FRAGMENT_PERCENT
	,DMV.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS DMV
LEFT OUTER JOIN SYS.INDEXES AS SI ON DMV.OBJECT_ID = SI.OBJECT_ID
	AND DMV.INDEX_ID = SI.INDEX_ID
	--WHERE avg_fragmentation_in_percent > 10
	AND index_type_desc IN (
		'CLUSTERED INDEX'
		,'NONCLUSTERED INDEX'
		)
	AND DMV.record_count >= 2000
--ORDER BY TABLE_NAME DESC
ORDER BY FRAGMENT_PERCENT DESC
SQL

How to get indexes with column on which index is created in SQL Server?

SELECT OBJECT_NAME(ind.object_id) AS ObjectName
	,ind.name AS IndexName
	,ind.is_primary_key AS IsPrimaryKey
	,ind.is_unique AS IsUniqueIndex
	,col.name AS ColumnName
	,ic.is_included_column AS IsIncludedColumn
FROM sys.indexes ind
INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id
	AND ind.index_id = ic.index_id
INNER JOIN sys.columns col ON ic.object_id = col.object_id
	AND ic.column_id = col.column_id
INNER JOIN sys.tables t ON ind.object_id = t.object_id
WHERE t.is_ms_shipped = 0
	AND t.name IN (
		'ACCOUNT'
		,'ACCOUNT_VIEW'
		)
ORDER BY OBJECT_SCHEMA_NAME(ind.object_id) --SchemaName
	,OBJECT_NAME(ind.object_id) --ObjectName
	,ind.is_primary_key DESC
	,ind.is_unique DESC
	,ind.name --IndexName
	,ic.key_ordinal
SQL

How to get % completion of a currently running job in SQL Server?

SELECT percent_complete, r.estimated_completion_time / 1000 AS estimated_completion_time_in_seconds
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
WHERE s.login_name NOT LIKE '%sa' -- login name
  AND s.status = 'running';
SQL

How to get jobs and respective owner details in SQL Server?

select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
SQL

To get job run history –run_duration column(HHMMSS format) in SQL Server?

SELECT j.name AS JobName,
       run_date,
       run_time,
       msdb.dbo.agent_datetime(run_date, run_time) AS RunDateTime,
       run_duration
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobhistory AS h ON j.job_id = h.job_id
WHERE j.enabled = 1
ORDER BY JobName, RunDateTime DESC;
SQL

How to know whether a job is running slow in SQL Server?

SELECT job.Name, job.job_ID, job.Originating_Server, activity.run_requested_Date,
       DATEDIFF(minute, activity.run_requested_Date, GETDATE()) AS Elapsed
FROM msdb.dbo.sysjobs_view AS job
INNER JOIN msdb.dbo.sysjobactivity AS activity ON job.job_id = activity.job_id
WHERE activity.run_Requested_date IS NOT NULL
  AND activity.stop_execution_date IS NULL
  AND job.name LIKE 'Adhoc_Maintenance.Subplan_1%';
SQL

How to manage Tempdb issue in SQL Server?

-- Check Size:
select name,SUM(size)*1.0/128 AS [Size in MB] 
from tempdb.sys.database_files group by name;
exec sp_spaceused;
-- If tempdb is not releasing space, then execute below two commands.:

a) DBCC FREEPROCCACHE
b) dbcc shrinkfile (tempdev, 'target size in MB')
-- Temp DB Usage:

select reserved_MB= convert(numeric(10,2),
round((unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count +internal_object_reserved_page_count+mixed_extent_page_count)*8/1024.,2)),
unallocated_extent_MB =convert(numeric(10,2),
round(unallocated_extent_page_count*8/1024.,2)),user_object_reserved_page_count,user_object_reserved_MB =convert(numeric(10,2),
round(user_object_reserved_page_count*8/1024.,2))from sys.dm_db_file_space_usage

How to get unused indexes in SQL Server?

SELECT object_name(i.object_id) AS TableName, i.name AS [Unused Index]
 FROM sys.indexes i
 LEFT JOIN sys.dm_db_index_usage_stats s ON s.object_id = i.object_id
       AND i.index_id = s.index_id
       AND s.database_id = db_id()
 WHERE objectproperty(i.object_id, 'IsIndexable') = 1
 AND objectproperty(i.object_id, 'IsIndexed') = 1
 AND s.index_id is null
 OR (s.user_updates > 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0) ORDER BY object_name(i.object_id) ASC

How find DB wise open connection?

SELECT DB_NAME(dbid) as "Database", COUNT(dbid) as "Number Of Open Connections",
loginame as "LoginName"
FROM sys.sysprocesses
WHERE dbid >4
GROUP BY dbid, loginame

How check the active connection in SQL Server?

SELECT DB_NAME(dbid) as "Database", COUNT(dbid) as "Number Of Open Connections",
loginame as "LoginName"
FROM sys.sysprocesses
   WHERE dbid > 0
GROUP BY dbid, loginame

How to check connections for a particular DB in SQL Server?

select spid,loginame,DB_NAME(dbid) AS DB_NAME ,status
from sys.sysprocesses where DB_NAME(dbid)='DB_Name';

How to get maximum user connection settings in SQL Server?

select * from sys.configurations
where name ='user connections'

To know current actual connection settings in SQL Server?

select * from sys.dm_os_performance_counters
where counter_name ='User Connections';

To get table name having particular data type in SQL Server?

SELECT OBJECT_NAME(c.object_id) as table_name
, c.name
, t.name
, c.precision
, c.scale
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE t.name IN ('numeric')
AND c.scale <> 0
ORDER BY 1, 2

How to get DB Users and their role privileges in SQL Server?

SELECT (select name from sys.databases where name='359STG') DBNAME,m.NAME USERNAME, p.NAME DBROLE
FROM sys.database_role_members rm
JOIN sys.database_principals p
ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals m
ON rm.member_principal_id = m.principal_id

How to Rename a database in SQL Server?

USE master
GO
ALTER DATABASE
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
EXEC master..sp_renamedb 'DBName','DBNewName'
GO
ALTER DATABASE DBNewName
SET MULTI_USER

How to Rename logical data file/log file name in SQL Server?

USE [master];
GO
ALTER DATABASE [DB Name] MODIFY FILE ( NAME = oldname, NEWNAME = naewname );
ALTER DATABASE [DB Name] MODIFY FILE ( NAME = odname_Log, NEWNAME = newname_Log );
GO

How to get Query from currently running session in SQL Server?

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = (select session_id from sys.dm_exec_sessions where status='running' and login_name='LoginName')
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)

How get SQL Text using session-id in SQL Server?

DBCC INPUTBUFFER (session id)

How to get expensive queries in the database in SQL Server?

SELECT TOP 20
       st.text,
       qp.query_plan,
       qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
Where st.dbid=9 and qs.last_execution_time>'2017-07-03 21:00:29.583'--and qs.last_execution_time< '2017-07-01 00:00:29.583'
ORDER BY total_elapsed_time DESC
GO

How to get currently running query in SQL Server?

SELECT sqltext.text,
      req.session_id,
      req.status,
      req.command,
      req.cpu_time,
     req.total_elapsed_time
FROM   sys.dm_exec_requests req
CROSS apply sys.Dm_exec_sql_text(sql_handle) AS sqltext

How to get long-running Query in SQL Server?

SELECT TOP 5 t.text
             AS
             'SQL Text',
             st.execution_count,
             Isnull(st.total_elapsed_time / st.execution_count, 0)
             AS 'AVG Excecution Time',
             st.total_worker_time / st.execution_count
             AS 'AVG Worker Time',
            st.total_worker_time,
             st.max_logical_reads,
             st.max_logical_writes,
             st.creation_time,
             Isnull(st.execution_count / Datediff(second, st.creation_time,
                                        Getdate()), 0) AS
             'Calls Per Second'
FROM   sys.dm_exec_query_stats st
       CROSS apply sys.Dm_exec_sql_text(st.sql_handle) t
ORDER  BY st.total_elapsed_time DESC

How to get SQL Text of recently executed queries in SQL Server?

DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate='2017-02-08 20:02:29.007', @EndDate='2017-02-08 23:42:29.007'
SELECT
    deqs.last_execution_time AS [Time],
   SUBSTRING( dest.TEXT,1,200) AS [Query]
 FROM
    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
   
    where deqs.last_execution_time >=@StartDate AND   deqs.last_execution_time<=@EndDate
ORDER BY
    deqs.last_execution_time DESC

How to Move Data files or log files to the different physical locations in SQL Server?

--Step: 1
ALTER DATABASE MODIFY FILE ( NAME = , FILENAME = 'E:\MSSQL.1\MSSQL\Data\datafile1.mdf');
ALTER DATABASE MODIFY FILE ( NAME = , FILENAME = 'F:\MSSQL.1\MSSQL\Log\Logfile.ldf');

--Step 2:
ALTER DATABASE SET OFFLINE WITH ROLLBACK IMMEDIATE;

--Step 3:
Copy data file/log file to new location.

--Step 4:
ALTER DATABASE SET ONLINE;

How to get from which backup database was restored in SQL Server?

SELECT [rs].[destination_database_name] ,
[rs].[restore_date] ,
[bs].[backup_start_date] ,
[bs].[backup_finish_date] ,
[bs].[database_name] AS [source_database_name] ,
[bmf].[physical_device_name] AS [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

How to know memory usage in SQL Server?

select * from sys.dm_os_process_memory;
select
total_physical_memory_kb/1024 AS total_physical_memory_mb,
available_physical_memory_kb/1024 AS available_physical_memory_mb,
total_page_file_kb/1024 AS total_page_file_mb,
available_page_file_kb/1024 AS available_page_file_mb,
100 - (100 * CAST(available_physical_memory_kb AS DECIMAL(18,3))/CAST(total_physical_memory_kb AS DECIMAL(18,3)))
AS 'Percentage_Used',
system_memory_state_desc
from sys.dm_os_sys_memory;

How to get some insight of Locking and blocking sessions in SQL Server?

Select * from sys.dm_tran_locks;
Select * from sys.dm_os_wait_stats;

How to reset of login in SQL Server?

ALTER LOGIN <login Name> WITH PASSWORD = '<PasswordHere>';

How to get statistics collection date in SQL Server?

SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('dbo.account')
GO

How to find dependent objects based on columns name in SQL Server?

SELECT OBJECT_NAME(OBJECT_ID),definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'AC__CUST_ADDR' + '%' order by 1;

How to Add data file or log file in SQL Server?

ALTER DATABASE [DB NAME]
ADD FILE
(
NAME = [Data4],
FILENAME = 'K:\MSSQL\Data\Data4.mdf',
SIZE = 3072 KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP [PRIMARY]

ALTER DATABASE [DB NAME]
ADD LOG FILE
(
NAME = DellTest_Log,
FILENAME = 'K:\MSSQL\Log\DB_Log.ldf',
SIZE = 5MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB
);
Go

How to identify an object using some code SQL txt in SQL Server?

select * from syscomments where text like '% select * into SQLlist%'

Searching database name by data files in SQL Server?

select DB_NAME(database_id) AS "DB_NAME",name AS "Logical_Name", physical_name 
from sys.master_files where DB_NAME(database_id)not in
('master','model','msdb','tempdb')
and physical_name like '%E:\%'

How to check DB features enabled in your SQL server instance?

sp_CONFIGURE 'show advanced', 0
GO
RECONFIGURE
GO
sp_CONFIGURE
GO
sp_CONFIGURE 'show advanced', 1
GO
RECONFIGURE
GO
sp_CONFIGURE
GO

How to take the database offline immediately in SQL Server?

ALTER DATABASE [DB Name] SET OFFLINE WITH ROLLBACK IMMEDIATE;

Schema authorization change in SQL Server?

SELECT * FROM sys.schemas WHERE principal_id = USER_ID('id)
ALTER AUTHORIZATION ON SCHEMA::db_datareader TO db_datareader;

How to get DB isolation level in SQL Server?

EXEC ('DBCC USEROPTIONS WITH NO_INFOMSGS')

How to Change the transaction level in SQL Server?

ALTER DATABASE OurTechIdeasDB -- DB Namne
SET READ_COMMITTED_SNAPSHOT ON

How to see the Isolation level enabled or not using T-SQL?

Script:

SELECT is_read_committed_snapshot_on
	,name
FROM master.sys.databases
WHERE name NOT IN (
		'master'
		,'model'
		,'tempdb'
		,'msdb'
		)
	AND is_read_committed_snapshot_on <> 1
SQL

Output:

How to generate a script for killing multiple sessions in SQL Server?

Script:

SELECT DB_NAME(dbid) AS "Database"
,COUNT(dbid) AS "Number Of Open Connections"
,loginame AS "LoginName"
,'kill ' + convert(VARCHAR, spid) + ';'
FROM sys.sysprocesses
WHERE dbid > 0
AND DB_NAME(dbid) = '<DB Name>'
GROUP BY dbid
,loginame
,spid
SQL

Output:

How to check SQL Server restart time in SQL Server?

Script:

SELECT *
FROM sys.dm_os_sys_info
SQL

Output:

How to generate a script to take multiple databases offline in SQL Server?

Script:

SELECT 'alter database ' + name + ' set offline with rollback immediate;'
FROM sys.databases
WHERE name NOT IN (
		'master'
		,'msdb'
		,'tempdb'
		,'model'
		);
SQL

Output:

alter database AdventureWorks2008 set offline with rollback immediate;
alter database AdventureWorks2008R2 set offline with rollback immediate;
alter database AdventureWorks2012 set offline with rollback immediate;
alter database AdventureWorks2014 set offline with rollback immediate;
alter database AdventureWorks2016 set offline with rollback immediate;
alter database AdventureWorks2017 set offline with rollback immediate;
alter database OurTechIdeasDB set offline with rollback immediate;
SQL

How to troubleshoot TSQL Queries?

There are a number of ways to troubleshoot TSQL queries. Here are some tips:

  • -Read the error message carefully and try to understand what it is saying.
  • -Check the syntax of your query carefully. Make sure all keywords are spelled correctly and that all parentheses and quotation marks are in the right places.
  • -If you’re using variables, make sure they are declared correctly and that they have the right data type.
  • -Make sure the table you’re querying exists and that you have permission to access it.
  • -If you’re querying a remote server, check that the connection is working and that you have the right permissions.
  • -Check that the data you’re expecting is actually in the table you’re querying. Sometimes data can be missing or incorrect.
  • -Try running your query in a different environment, such as in a test database, to see if that makes any difference.
  • -If all else fails, ask for help from a more experienced DBA or developer.