SQL DBA hands-on queries used in day-to-day activities

Some TSQL hands-on queries used by SQL DBA in day-to-day activities.

    1.To get SQL Server version installed in your server?

   SELECT @@VERSION AS SQLVersion;

    2.How to get when your SQL server was last restarted?

  SELECT sqlserver_start_time from sys.dm_os_sys_info;

    3.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)

    4.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;

     5.How to get session details in SQL Server?

              select * from sys.dm_exec_sessions;

       exec sp_who2;

     6.How to get blocking query in SQL Server?

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

      exec  sp_who2;

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

SELECT name, database_id, create_date,*

FROM sys.databases where state_desc = 'offline';

    8. 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>=DATEADD(day,-1,GETDATE())

ORDER BY s.database_name,backup_start_date DESC, backup_finish_date

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

select object_name(object_id),name "Index" from sys.indexes where object_id in (select object_id from sys.objects where name in 

('Table1','Table2')) ;

exec sp_helpindex 'account';

   10. how to get 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('dbo.table1');

11.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'

12.   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;

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

14.   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'

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


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

17.   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%'


18.   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 command.:

    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

Monitor tempdb growth:

select *

from sys.dm_db_session_space_usage spu

join sys.dm_exec_sessions s on s.session_id = spu.session_id

join sys.dm_exec_requests r on s.session_id = r.session_id

cross apply sys.dm_exec_sql_text(sql_handle) t

order by internal_objects_alloc_page_count desc


19.   How to get un used 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

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

21.   How check 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



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

23.   How to get maximum user connection setting in SQL Server?

           select * from sys.configurations

           where name ='user connections'

24.   To know current actual connection settings in SQL Server?

          select * from sys.dm_os_performance_counters

          where counter_name ='User Connections';

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

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

27.   How to Rename a database in SQL Server?

       USE master

      GO

     ALTER DATABASE <DB Name>

     SET SINGLE_USER

     WITH ROLLBACK IMMEDIATE

     GO

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

    GO

    ALTER DATABASE DBNewName

   SET MULTI_USER

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

29.   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)

30.   How get SQL Text using session id in SQL Server?

       DBCC INPUTBUFFER (session id)

31.   How to get expensive queries in database in SQL Server?

query-1

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

query-2

SELECT TOP 20 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.TEXT)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,

qs.total_logical_writes, qs.last_logical_writes,

qs.total_worker_time,

qs.last_worker_time,

qs.total_elapsed_time/1000000 total_elapsed_time_in_S,

qs.last_elapsed_time/1000000 last_elapsed_time_in_S,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC -- logical reads

-- ORDER BY qs.total_logical_writes DESC -- logical writes

-- ORDER BY qs.total_worker_time DESC -- CPU time

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

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

34.   How to get SQL Txt 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

35.   How to Move Data files or log files to different physical location in SQL Server?

 Step: 1
ALTER DATABASE <DB Name> MODIFY FILE ( NAME = <logical Name>, FILENAME = 'E:\MSSQL.1\MSSQL\Data\datafile1.mdf');

ALTER DATABASE <Database Name> MODIFY FILE ( NAME = <Logical log file name>, FILENAME = 'F:\MSSQL.1\MSSQL\Log\Logfile.ldf');

Step 2:

ALTER DATABASE<Database Name > SET OFFLINE WITH ROLLBACK IMMEDIATE;

Step 3:

Copy data file/log file to new location.

Step 4:

ALTER DATABASE <Database Name > SET ONLINE;

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

37.   How to know memory usage in SQL Server?

a.         select * from sys.dm_os_process_memory;

b.       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;

38.   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;

39.   How to reset of login in SQL Server?

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

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

41.   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;

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

43.   How to get list of objects pinned to memory in SQL Server?

SELECT * FROM sysobjects WHERE objectproperty(id, 'TableIsPinned') = 1

44.   How to list all partition tables?

select distinct t.name

from sys.partitions p

inner join sys.tables t

on p.object_id = t.object_id

where p.partition_number <> 1

45.   How see all partitions of a particular table in SQL Server?

  SELECT * FROM sys.dm_db_partition_stats  

      WHERE object_id = OBJECT_ID('Partition Table name’);

46.   How to verify partition function in SQL Server?

   SELECT PF.[name], RV.boundary_id, RV.[value]

FROM sys.partition_range_values AS RV

 JOIN sys.partition_functions AS PF

  ON RV.function_id = PF.function_id

WHERE PF.[name] = '<Function Name>'

47.    How to see partitions and range values in SQL Server?

SELECT psch.name as PartitionScheme,

prng.value AS ParitionValue,

prng.boundary_id AS BoundaryID

FROM sys.partition_functions AS pfun

INNER JOIN sys.partition_schemes psch ON pfun.function_id = psch.function_id

 INNER JOIN sys.partition_range_values prng ON prng.function_id=pfun.function_id

WHERE pfun.name = 'RangePF1'

48.   To check data of a particular partition in SQL Server?

SELECT * FROM score_general_data

WHERE $PARTITION.F_SG(Hash_Part) = 4 ;

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

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

50.   Bulk insert syntax in SQL Server.

BULK

INSERT JKH_OMG_Main_Acct_Tagging

FROM 'C:\Users\454048\Desktop\John\OMG2_main_account_tagging_20170521\main_file.txt' --location with filename

WITH

(

FIRSTROW=2,----starting form 2nd row.

FIELDTERMINATOR = '\t',

ROWTERMINATOR = '\n'

)

GO

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

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

53.   How to take database offline immediately in SQL Server?

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

54.   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;

55.   How to get DB isolation level  in SQL Server?

EXEC('DBCC USEROPTIONS WITH NO_INFOMSGS')

56.   How to Change transaction level in SQL Server?

ALTER DATABASE <DB Namne> SET READ_COMMITTED_SNAPSHOT ON

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

select is_read_committed_snapshot_on, name

   from DBSWERMDB60.master.sys.databases

where name not in ('master','model','tempdb','msdb')

and is_read_committed_snapshot_on <>1

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

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

59. How to check SQL Server restart time in SQL Server?

select * from sys.dm_os_sys_info

60. How to generate a script to take multiple databases off line in SQL Server?

select 'alter database ' +name+ ' set offline with rollback immediate;' from sys.databases where name not in ('master','msdb','tempdb','model');