Some TSQL queries used by SQL DBA in day to day activities • Our Tech Ideas

Table of Contents

Database log file full. How to find log file used and free space?

--Run below script to any database, you will get result for all the database
DBCC SQLPERF(LOGSPACE)

1 2	--Run below script to any database, you will get result for all the database DBCC SQLPERF(LOGSPACE)

Low disk space. How to find free space on the disk?

--Run the below script to get the free space on the disks.
exec xp_fixeddrives

1 2	--Run the below script to get the free space on the disks. exec xp_fixeddrives

To get the SQL Server version installed in your server?

SELECT @@VERSION AS SQLVersion

1	SELECT @@VERSION AS SQLVersion

How to get when your SQL Server was last restarted?

SELECT sqlserver_start_time from sys.dm_os_sys_info

1	SELECT sqlserver_start_time from sys.dm_os_sys_info

How to check User database size in SQL Server?

SELECT DB_NAME(database_id) AS DatabaseName,
SUM((size*8)/1024.0/1024.0) SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) in (select name from sys.databases where name not in ('tempdb','master','msdb','model'))
group by DB_NAME(database_id)

SELECT DB_NAME(database_id) AS DatabaseName,

SUM((size*8)/1024.0/1024.0) SizeGB

FROM sys.master_files

WHERE DB_NAME(database_id) in (select name from sys.databases where name not in ('tempdb','master','msdb','model'))

group by DB_NAME(database_id)

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

USE [AdventureWorks2017]
GO

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;
GO

USE [AdventureWorks2017]

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;

How to get session details in SQL Server?

select * from sys.dm_exec_sessions;

1	select * from sys.dm_exec_sessions;

exec sp_who2;

1	exec sp_who2;

How to get a blocking query in SQL Server?

SELECT * FROM sys.sysprocesses WHERE blocked &lt;&gt; 0;

1	SELECT * FROM sys.sysprocesses WHERE blocked <> 0;

exec sp_who2;

1	exec sp_who2;

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';

1 2	SELECT name, database_id, create_date,* FROM sys.databases where state_desc = 'offline';

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", /*s.backup_size --use this if not compressed*/
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' 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&gt;=DATEADD(day,-1,GETDATE())
ORDER BY s.database_name,backup_start_date DESC, backup_finish_date

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", /*s.backup_size --use this if not compressed*/

CAST(DATEDIFF(second, s.backup_start_date,

s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' 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,backup_start_date DESC, backup_finish_date

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

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

How to get all indexes of a table in SQL Server?

exec sp_helpindex '[dbo].[ErrorLog]' -- Put table name here

1	exec sp_helpindex '[dbo].[ErrorLog]' -- Put table name here

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
WHERE OBJECT_ID = OBJECT_ID('[Person].[EmailAddress]'); -- Put table name here

SELECT name AS index_name,

STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated

FROM sys.indexes

WHERE OBJECT_ID = OBJECT_ID('[Person].[EmailAddress]'); -- Put table name here

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

use [AdventureWorks2017] -- Put database name here
GO

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

use [AdventureWorks2017] -- Put database name here

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'

How to get table/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

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

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

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

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

SELECT percent_complete ,r.estimated_completion_time/1000
"estimated_completion_time_in_second",* 
FROM sys.dm_exec_sessions s 
INNER JOIN sys.dm_exec_requests r 
ON s.session_id = r.session_id 
and s.login_name not like '%BeheraH'
and s.status='running'

SELECT percent_complete ,r.estimated_completion_time/1000

"estimated_completion_time_in_second",*

FROM sys.dm_exec_sessions s

INNER JOIN sys.dm_exec_requests r

ON s.session_id = r.session_id

and s.login_name not like '%BeheraH'

and s.status='running'

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

Use [AdventureWorks2017] -- Put database name here
Go

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

Use [AdventureWorks2017] -- Put database name here

select s.name,l.name

from msdb..sysjobs s

left join master.sys.syslogins l on s.owner_sid = l.sid

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

Use [AdventureWorks2017] -- Put database name here Go

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 j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
where j.enabled = 1 --Only Enabled Jobs
order by JobName, RunDateTime desc

Use [AdventureWorks2017] -- Put database name here Go

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 j

INNER JOIN msdb.dbo.sysjobhistory h

ON j.job_id = h.job_id

where j.enabled = 1 --Only Enabled Jobs

order by JobName, RunDateTime desc

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 job
INNER JOIN msdb.dbo.sysjobactivity activity
ON (job.job_id = activity.job_id)
WHERE
run_Requested_date is not null
AND stop_execution_date is null
AND job.name like 'Adhoc_Maintenance.Subplan_1%'

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 job

INNER JOIN msdb.dbo.sysjobactivity activity

ON (job.job_id = activity.job_id)

WHERE

run_Requested_date is not null

AND stop_execution_date is null

AND job.name like 'Adhoc_Maintenance.Subplan_1%'

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;

-- 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')

-- 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

-- 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

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

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

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';

1 2	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'

1 2	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';

1 2	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 &lt;&gt; 0
ORDER BY 1, 2

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

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

USE master

ALTER DATABASE

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

EXEC master..sp_renamedb 'DBName','DBNewName'

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

USE [master];

ALTER DATABASE [DB Name] MODIFY FILE ( NAME = oldname, NEWNAME = naewname );

ALTER DATABASE [DB Name] MODIFY FILE ( NAME = odname_Log, NEWNAME = newname_Log );

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)

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)

1	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

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

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

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

SELECT TOP 5 t.text

'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

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;

--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

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;

1	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;

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;

1 2	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>';

1	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

SELECT name AS index_name,

STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated

FROM sys.indexes

WHERE OBJECT_ID = OBJECT_ID('dbo.account')

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;

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

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

);

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

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

1	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:\%'

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

sp_CONFIGURE 'show advanced', 0

RECONFIGURE

sp_CONFIGURE

sp_CONFIGURE 'show advanced', 1

RECONFIGURE

sp_CONFIGURE

How to take the database offline immediately in SQL Server?

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

1	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;

1 2	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')

1	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

1 2	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

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

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

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

Output:

How to check SQL Server restart time in SQL Server?

Script:

<span class="SQLKeyword">SELECT</span> <span class="SQLOperator">*</span>
<span class="SQLKeyword">FROM</span> sys<span class="SQLOperator">.</span>dm_os_sys_info

1 2	<span class="SQLKeyword">SELECT</span> <span class="SQLOperator">*</span> <span class="SQLKeyword">FROM</span> sys<span class="SQLOperator">.</span>dm_os_sys_info

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'
		);

SELECT 'alter database ' + name + ' set offline with rollback immediate;'

FROM sys.databases

WHERE name NOT IN (

'master'

,'msdb'

,'tempdb'

,'model'

);

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;

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;

Facebook Comments Box

Comments

Ramprosad Mondal says:

May 23, 2020 at 6:22 AM

Great , Good Job .. Thanks for sharing

- Sanjay Humania says:
  
  October 5, 2020 at 7:08 AM
  
  Thanks
  
Chanda says:

July 12, 2020 at 12:23 AM

Actually when someone doesn’t know after that its up to other visitors that they will help, so here
it happens.

Linda says:

July 25, 2020 at 12:47 PM

Thanks so much pertaining to giving us an update on this subject
matter on your web-site. Please know that if a brand-new post appears or in the event any adjustments occur with the current posting, I would want to consider reading
more and learning how to make good usage of those tactics
you write about. Thanks for your efforts and consideration of others by making this website available.

Krystle says:

July 31, 2020 at 6:39 PM

Ahaa, its nice discussion about this article at this place at this website, I have read all that,
so now me also commenting here.

Cheryl says:

August 1, 2020 at 12:08 PM

Hi there would you mind stating which blog platform you’re
using? I’m looking to start my own blog in the
near future but I’m having a difficult time deciding between BlogEngine/Wordpress/B2evolution and Drupal.

The reason I ask is because your design and style seems different then most blogs and I’m
looking for something unique. P.S My apologies for getting off-topic but I had to ask!

Bridgette says:

August 7, 2020 at 4:50 AM

What’s up, its good article about media print, we all
be familiar with media is a wonderful source of facts.

- Sanjay Humania says:
  
  October 5, 2020 at 7:08 AM
  
  Thanks
  
Marcelino says:

August 11, 2020 at 10:40 PM

I believe you have remarked some very interesting details,
thank you for the post.

- Sanjay Humania says:
  
  October 5, 2020 at 7:08 AM
  
  Thanks
  
Venkat says:

October 5, 2020 at 3:00 AM

Good One

- Sanjay Humania says:
  
  October 5, 2020 at 7:05 AM
  
  Thank you !! 🙂
  
Google says:

February 10, 2021 at 1:02 AM

Google

Very handful of internet websites that come about to become detailed below, from our point of view are undoubtedly properly worth checking out.

Database log file full. How to find log file used and free space?

Low disk space. How to find free space on the disk?

To get the SQL Server version installed in your server?

How to get when your SQL Server was last restarted?

How to check User database size in SQL Server?

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

How to get session details in SQL Server?

How to get a blocking query in SQL Server?

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

How to get database backup history details in SQL Server?

How to get all indexes of a table in SQL Server?

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

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

How to get table/index fragmentation in SQL Server?

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

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

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

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

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

How to manage Tempdb issue in SQL Server?

How to get unused indexes in SQL Server?

How find DB wise open connection?

How check the active connection in SQL Server?

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

How to get maximum user connection settings in SQL Server?

To know current actual connection settings in SQL Server?

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

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

How to Rename a database in SQL Server?

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

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

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

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

How to get currently running query in SQL Server?

How to get long-running Query in SQL Server?

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

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

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

How to know memory usage in SQL Server?

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

How to reset of login in SQL Server?

How to get statistics collection date in SQL Server?

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

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

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

Searching database name by data files in SQL Server?

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

How to take the database offline immediately in SQL Server?

Schema authorization change in SQL Server?

How to get DB isolation level in SQL Server?

How to Change the transaction level in SQL Server?

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

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

How to check SQL Server restart time in SQL Server?

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

About SQL Server Versions

Find the user who executed DELETE on tables in SQL Server

Comments

How to Create Local User Account in Windows 10

How to send email using Powershell

Oracle DBA Interview Questions

Archives

Categories