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