Some Useful SQL commands
-------------------------
-- get database names
SELECT name,* FROM master.dbo.sysdatabases
Job History
-----------
USE msdb ;
GO
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
-- name of the job
where j.name='Populate_TTC_Task_Details'
-- only failed jobs
AND h.run_status=0
ORDER BY h.run_date desc
Full Job history
------------------
USE msdb ;
GO
EXEC dbo.sp_help_jobhistory
@job_name = N'Populate_TTC_Task_Details' ;
GO
Job names
----------
select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
order by s.name
-- find guid from backups
use [msdb]
SELECT * FROM log_shipping_primary_databases
"D:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqllogship.exe" -Backup E842CE8C-9F88-4483-B3AE-96BA97F62E25 -server ServerName
Most CPU Intensive Queries
--------------------------
--- https://www.red-gate.com/simple-talk/blogs/how-to-find-cpu-intensive-queries/
SELECT
-- using statement_start_offset and
-- statement_end_offset we get the query text
-- from inside the entire batch
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)
as [Text],
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,
-- converting microseconds to seconds
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
-- Retrieve the query text
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
-- Retrieve the query plan
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC -- CPU time
Longest Running queries
-----------------------
SELECT st.text,
qp.query_plan,
total_worker_time,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
Permission
----------
USE (your database)
EXEC sp_addrolemember 'db_datareader', '(your user name)'
Server Info
-----------
SELECT
SERVERPROPERTY('MachineName') AS [ServerName],
SERVERPROPERTY('ServerName') AS [ServerInstanceName],
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
Left(@@Version, Charindex('-', @@version) - 2) As VersionName
Change Recovery Model
---------------------
USE [master]
GO
ALTER DATABASE [Mydb] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Get Recovery Info
-----------------
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name in ('Mydb')
Shrink Files
---------------
USE [Mydb]
GO
DBCC SHRINKFILE (N'mydb_log' , 0, TRUNCATEONLY)
GO
Update Column uppercase
------------------------
update [MyDB].[myTable].[HostNameCountry]
set hostname=upper(hostname)
database Size
--------------
IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
DROP TABLE #space
CREATE TABLE #space (
database_id INT PRIMARY KEY
, data_used_size DECIMAL(18,2)
, log_used_size DECIMAL(18,2)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT '
USE [' + d.name + ']
INSERT INTO #space (database_id, data_used_size, log_used_size)
SELECT
DB_ID()
, SUM(CASE WHEN [type] = 0 THEN space_used END)
, SUM(CASE WHEN [type] = 1 THEN space_used END)
FROM (
SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
FROM sys.database_files s
GROUP BY s.[type]
) t;'
FROM sys.databases d
WHERE d.[state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
d.database_id
, d.name
, d.state_desc
, d.recovery_model_desc
, t.total_size
, t.data_size
, s.data_used_size
, t.log_size
, s.log_used_size
, bu.full_last_date
, bu.full_size
, bu.log_last_date
, bu.log_size
FROM (
SELECT
database_id
, log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
FROM sys.master_files
GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
SELECT
database_name
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_finish_date
, backup_size =
CAST(CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0 AS DECIMAL(18,2))
, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC
Get size of db from back up file
--------------------------------
-- give the path of backup file
RESTORE FILELISTONLY FROM DISK = N'C:\DB_Backup\Mydb.bak'
Link Server Query
----------------
-- get the details of the linkservers
sp_linkedservers
-- query from link server
-- below is the example
select * from openquery(LInkServerName, 'select * from XXXX.XXV_V')
Session Precheck / any active sessions for DB
---------------------------------------------
BEGIN TRANSACTION
SET NOCOUNT ON
BEGIN
DECLARE @V_COUNT INTEGER
SELECT @V_COUNT = count(*) FROM MASTER..SYSPROCESSES P, MASTER..SYSDATABASES D WHERE P.DBID = D.DBID AND D.DBID = DB_ID('#DBNAME#') and P.SPID <> @@spid and P.SPID > 50;
IF (@V_COUNT > 0)
BEGIN
SELECT SPID,hostname FROM MASTER..SYSPROCESSES P, MASTER..SYSDATABASES D
WHERE P.DBID = D.DBID AND D.DBID = DB_ID('#DBNAME#') and P.SPID <> @@spid;
RAISERROR('',16,1)
ROLLBACK TRANSACTION
RETURN
END
END
Add user
---------
USE [master]
GO
CREATE LOGIN [NEXA\username] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [Mydb]
GO
CREATE USER [NEXA\username] FOR LOGIN [NEXA\username]
GO
USE [Mydb]
GO
EXEC sp_addrolemember N'db_owner', N'NEXA\username'
GO
Check Performance and kill running Scripts
------------------------------------------
sp_who2
kill 74
Shrink DB
------------------
USE [MyDB]
GO
/****** Object: StoredProcedure [mydb].[Nexa_Shrink_MyDBTrasactionLog] Script Date: 9/27/2016 12:00:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MyDB].[Nexa_Shrink_MyDBTrasactionLog]
AS
BEGIN
-- Declare required variables.
DECLARE @vcDBMirroringStatus VARCHAR (100);
DECLARE @vcMirroringStatus VARCHAR (100);
DECLARE @intDBId INT;
-- Set the values for variables.
SET @vcMirroringStatus = 'SYNCHRONIZED' -- This is the syncronized status
SET @intDBId = DB_ID();-- This is the digite mirroring DB id.
-- Get the Digite DB mirroring status
SET @vcDBMirroringStatus = (select mirroring_state_desc from sys.database_mirroring where database_id = @intDBId)
-- If Digite DB mirroring status is 'SYNCHRONIZED' only shrink the DB log
IF @vcDBMirroringStatus = @vcMirroringStatus
BEGIN
CHECKPOINT
BACKUP LOG MyDb to disk = 'C:\ShrinkDBLogs\Mydb1.trn'
DBCC SHRINKFILE (Mydb_log, 15360)
END
END
-------------
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
---------------------------------
-------------------------
-- get database names
SELECT name,* FROM master.dbo.sysdatabases
Job History
-----------
USE msdb ;
GO
SELECT j.name JobName,h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
-- name of the job
where j.name='Populate_TTC_Task_Details'
-- only failed jobs
AND h.run_status=0
ORDER BY h.run_date desc
Full Job history
------------------
USE msdb ;
GO
EXEC dbo.sp_help_jobhistory
@job_name = N'Populate_TTC_Task_Details' ;
GO
Job names
----------
select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
order by s.name
-- find guid from backups
use [msdb]
SELECT * FROM log_shipping_primary_databases
"D:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqllogship.exe" -Backup E842CE8C-9F88-4483-B3AE-96BA97F62E25 -server ServerName
Most CPU Intensive Queries
--------------------------
--- https://www.red-gate.com/simple-talk/blogs/how-to-find-cpu-intensive-queries/
SELECT
-- using statement_start_offset and
-- statement_end_offset we get the query text
-- from inside the entire batch
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)
as [Text],
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,
-- converting microseconds to seconds
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
-- Retrieve the query text
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
-- Retrieve the query plan
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC -- CPU time
Longest Running queries
-----------------------
SELECT st.text,
qp.query_plan,
total_worker_time,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
Permission
----------
USE (your database)
EXEC sp_addrolemember 'db_datareader', '(your user name)'
Server Info
-----------
SELECT
SERVERPROPERTY('MachineName') AS [ServerName],
SERVERPROPERTY('ServerName') AS [ServerInstanceName],
SERVERPROPERTY('InstanceName') AS [Instance],
SERVERPROPERTY('Edition') AS [Edition],
SERVERPROPERTY('ProductVersion') AS [ProductVersion],
Left(@@Version, Charindex('-', @@version) - 2) As VersionName
Change Recovery Model
---------------------
USE [master]
GO
ALTER DATABASE [Mydb] SET RECOVERY SIMPLE WITH NO_WAIT
GO
Get Recovery Info
-----------------
SELECT name, recovery_model_desc
FROM sys.databases
WHERE name in ('Mydb')
Shrink Files
---------------
USE [Mydb]
GO
DBCC SHRINKFILE (N'mydb_log' , 0, TRUNCATEONLY)
GO
Update Column uppercase
------------------------
update [MyDB].[myTable].[HostNameCountry]
set hostname=upper(hostname)
database Size
--------------
IF OBJECT_ID('tempdb.dbo.#space') IS NOT NULL
DROP TABLE #space
CREATE TABLE #space (
database_id INT PRIMARY KEY
, data_used_size DECIMAL(18,2)
, log_used_size DECIMAL(18,2)
)
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
SELECT '
USE [' + d.name + ']
INSERT INTO #space (database_id, data_used_size, log_used_size)
SELECT
DB_ID()
, SUM(CASE WHEN [type] = 0 THEN space_used END)
, SUM(CASE WHEN [type] = 1 THEN space_used END)
FROM (
SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
FROM sys.database_files s
GROUP BY s.[type]
) t;'
FROM sys.databases d
WHERE d.[state] = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
EXEC sys.sp_executesql @SQL
SELECT
d.database_id
, d.name
, d.state_desc
, d.recovery_model_desc
, t.total_size
, t.data_size
, s.data_used_size
, t.log_size
, s.log_used_size
, bu.full_last_date
, bu.full_size
, bu.log_last_date
, bu.log_size
FROM (
SELECT
database_id
, log_size = CAST(SUM(CASE WHEN [type] = 1 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, data_size = CAST(SUM(CASE WHEN [type] = 0 THEN size END) * 8. / 1024 AS DECIMAL(18,2))
, total_size = CAST(SUM(size) * 8. / 1024 AS DECIMAL(18,2))
FROM sys.master_files
GROUP BY database_id
) t
JOIN sys.databases d ON d.database_id = t.database_id
LEFT JOIN #space s ON d.database_id = s.database_id
LEFT JOIN (
SELECT
database_name
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_finish_date
, backup_size =
CAST(CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0 AS DECIMAL(18,2))
, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) bu ON d.name = bu.database_name
ORDER BY t.total_size DESC
Get size of db from back up file
--------------------------------
-- give the path of backup file
RESTORE FILELISTONLY FROM DISK = N'C:\DB_Backup\Mydb.bak'
Link Server Query
----------------
-- get the details of the linkservers
sp_linkedservers
-- query from link server
-- below is the example
select * from openquery(LInkServerName, 'select * from XXXX.XXV_V')
Session Precheck / any active sessions for DB
---------------------------------------------
BEGIN TRANSACTION
SET NOCOUNT ON
BEGIN
DECLARE @V_COUNT INTEGER
SELECT @V_COUNT = count(*) FROM MASTER..SYSPROCESSES P, MASTER..SYSDATABASES D WHERE P.DBID = D.DBID AND D.DBID = DB_ID('#DBNAME#') and P.SPID <> @@spid and P.SPID > 50;
IF (@V_COUNT > 0)
BEGIN
SELECT SPID,hostname FROM MASTER..SYSPROCESSES P, MASTER..SYSDATABASES D
WHERE P.DBID = D.DBID AND D.DBID = DB_ID('#DBNAME#') and P.SPID <> @@spid;
RAISERROR('',16,1)
ROLLBACK TRANSACTION
RETURN
END
END
Add user
---------
USE [master]
GO
CREATE LOGIN [NEXA\username] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE [Mydb]
GO
CREATE USER [NEXA\username] FOR LOGIN [NEXA\username]
GO
USE [Mydb]
GO
EXEC sp_addrolemember N'db_owner', N'NEXA\username'
GO
Check Performance and kill running Scripts
------------------------------------------
sp_who2
kill 74
Shrink DB
------------------
USE [MyDB]
GO
/****** Object: StoredProcedure [mydb].[Nexa_Shrink_MyDBTrasactionLog] Script Date: 9/27/2016 12:00:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [MyDB].[Nexa_Shrink_MyDBTrasactionLog]
AS
BEGIN
-- Declare required variables.
DECLARE @vcDBMirroringStatus VARCHAR (100);
DECLARE @vcMirroringStatus VARCHAR (100);
DECLARE @intDBId INT;
-- Set the values for variables.
SET @vcMirroringStatus = 'SYNCHRONIZED' -- This is the syncronized status
SET @intDBId = DB_ID();-- This is the digite mirroring DB id.
-- Get the Digite DB mirroring status
SET @vcDBMirroringStatus = (select mirroring_state_desc from sys.database_mirroring where database_id = @intDBId)
-- If Digite DB mirroring status is 'SYNCHRONIZED' only shrink the DB log
IF @vcDBMirroringStatus = @vcMirroringStatus
BEGIN
CHECKPOINT
BACKUP LOG MyDb to disk = 'C:\ShrinkDBLogs\Mydb1.trn'
DBCC SHRINKFILE (Mydb_log, 15360)
END
END
-------------
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
---------------------------------
