Hi, My name is Jimmy Sam. If you need DBA help, please email to jimmy_sam001@yahoo.com for further discussion. Thanks.
mssql   AzureSQLDatabase   mssql01   mssql03   Querying the SQL Server System Catalog FAQ 
Instance: Change Server Name  change_servername  db start time  numa nodes 
Database: show_databases  Single User Mode  Rename Data File 
Error Logs: Query Error Log Location 
index: find missing index  estimated execution plan  index_rebuild  rebuild and reorgnization  table_index_size 
table: table_compression  foreignkey constraints 
session and connections: sp_who  sp_whoisactive  kill sessions for a db  kill sessions for a user 
Backup: backupRestoreStatus  backupFileLocation  defaultBackupLocation  backupUsingSSMS  queryBackupTime  generateBackupScript 
Recovery: Generate Recovery Script  point_in_time_restore_and_recovery 
logshipping:check_log_shipping 
sql agent jobsfailed job error message 
Mail and Alert:Database Mail  alter,operator,sqlagent,databasemail  sql profile and sql trace 
objects and DDL defintion:find_objects  find_sqltext 
Functions: Get Current Time 
User: fix_orphaned_user  createUserWithSysadmin  passwordHash  loginProperty  createUserExtractPermission 
Shrink: Shrink Database  list_datafile  logfile_shrink  logfile_size  tempfile_shrink 
Linked Server: Linked Server  RLS: Row Level Security  
-- to do
1): query instance service account
2): dbcc checkdb
3): validate a database
4): database instance information
5): replication
6): alwayson


top   prev   next  



SQL Server Compatibility Level

SELECT name, compatibility_level FROM master.sys.databases;
GO

USE [master]
GO
ALTER DATABASE [TR_PROD02] SET COMPATIBILITY_LEVEL = 100
GO

140 2017
130 2016
120 2014
110 2012
100 2008R2
100 2008
90  2005
80  2000

DB Recovery Model: FULL, SIMPLE

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model] FROM master.sys.databases
where recovery_model_desc = 'FULL' and name != 'model';

IF EXISTS(SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model] FROM master.sys.databases
where recovery_model_desc = 'FULL' and name != 'model')
BEGIN
... ...
END

Find Current SQL Server Properties

SELECT 
SERVERPROPERTY('MachineName') AS ComputerName,
ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') AS InstanceName,
SERVERPROPERTY('ServerName') AS SqlInstance,
CAST(serverproperty(N'Servername') AS sysname) AS [Server_Name],
'Server[@Name=' + quotename(CAST( serverproperty(N'Servername') AS sysname),'''') + ']' AS [Server_Urn],
CAST(null AS int) AS [Server_ServerType],
CAST(0x0001 AS int) AS [Server_Status],0 AS [Server_IsContainedAuthentication],
(@@microsoftversion / 0x1000000) & 0xff AS [VersionMajor],
(@@microsoftversion / 0x10000) & 0xff AS [VersionMinor],
@@microsoftversion & 0xffff AS [BuildNumber],
CAST(SERVERPROPERTY('IsSingleUser') AS bit) AS [IsSingleUser],
CAST(SERVERPROPERTY(N'Edition') AS sysname) AS [Edition],
SERVERPROPERTY ('productlevel') Service_Pack, 
CAST(SERVERPROPERTY('EngineEdition') AS int) AS [EngineEdition],
CAST(ISNULL(SERVERPROPERTY(N'IsXTPSupported'), 0) AS bit) AS [IsXTPSupported],
SERVERPROPERTY(N'ProductVersion') AS [VersionString],
CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit) AS [IsFullTextInstalled]
ORDER BY [Server_Name] ASC;

Find Current SQL Server Version

SELECT SERVERPROPERTY ('edition')      Edition, 
       SERVERPROPERTY ('productlevel') Service_Pack, 
       SERVERPROPERTY ('productversion') Numeric_Version;
GO

sqlcmd -W -E -S

set MY_HOSTNAME=XXX

sqlcmd -W -h -1 -E -S %MY_HOSTNAME%

select @@version
go

SELECT  
  SERVERPROPERTY('MachineName') AS ComputerName,
  SERVERPROPERTY('ServerName') AS InstanceName,  
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion,  
  SERVERPROPERTY('ProductLevel') AS ProductLevel;  
GO 

select 'servername' whatisthis, @@servername servername;
go

select serverproperty('ServerName');
go

select serverproperty('MachineName');
go

select serverproperty('InstanceDefaultDataPath');
go

select serverproperty('InstanceDefaultLogPath');
go


set nocount on
select name from master.sys.databases 
where name not in 
( 'master', 'tempdb', 'model', 'msdb',
  'ReportServer', 'ReportServerTempDB'
);
go

set nocount on
SELECT  
   convert(sysname,serverproperty('Machinename')) machinename,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   default_domain() domain,
   char(10)+char(13)+ 
   CASE 
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'     
     WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017' 
     ELSE 'unknown'
  END AS MajorVersion,
  SERVERPROPERTY('ProductLevel') AS ProductLevel,
  SERVERPROPERTY('Edition') AS Edition,
  SERVERPROPERTY('ProductVersion') AS ProductVersion,
  char(10)+char(13)+
  @@version, 
  char(10)+char(13)+
  replace(replace(replace(cast((SELECT  Name
        FROM master.sys.databases where name not in ( 'master', 'tempdb', 'model','msdb','ReportServer', 'ReportServerTempDB' )
        order by name 
        FOR XML raw) as nvarchar(max)),'"/><row Name="',','),'<row Name="',''), '"/>','') databases
GO

-- os physical_memory info, SQL Server (starting with 2008)
set nocount on 
select 
cast(round(total_physical_memory_kb*1.0/1024,1) as decimal(6,0)) total_memory, 
cast(round(available_physical_memory_kb*1.0/1024,0) as decimal(6,0)) avail_memory
from sys.dm_os_sys_memory;
go

-- 
-- starting with 2008
select * from sys.dm_os_windows_info;
go

set nocount on 
select 
case
when windows_release = '10.0' then 'Windows Server 2016'
when windows_release = '6.3'  then 'Windows Server 2012 R2'
when windows_release = '6.2'  then 'Windows Server 2012'
when windows_release = '6.1' then 'Windows Server 2008 R2'
when windows_release = '6.0' then 'Windows Server 2008'
end windows_version,
windows_release
from sys.dm_os_windows_info;
go

-- cpu count and sql server startup time
set nocount on select cpu_count, sqlserver_start_time from sys.dm_os_sys_info;
go

-- service account info, sql server 2008R2 SP1 and up
set nocount on select servicename,status_desc,service_account from sys.dm_server_services;
go

--
SELECT Maximum FROM master.sys.configurations WHERE configuration_id = 1544;
go

--
exec xp_fixeddrives
go

-- -- using linked server and open query
-- SELECT MachineName
-- FROM OPENQUERY([LinkedServer], 'SELECT MachineName = SERVERPROPERTY (''MachineName'')') x;
-- go

-- programming tricks
QUOTENAME(@instance_name)
EXECUTE sp_executesql @Command;
--

Find Windows Group Current Windows User Belongging To

# Find "AD group" current windows user belongging to 

EXEC xp_logininfo 'BUILTIN\Administrators';
GO
EXEC xp_logininfo 'domain\useraccount';
GO
EXEC xp_logininfo 'domain\useraccount','all';
GO

Find Windows Domain, Servername and DB Name

1):
SELECT DEFAULT_DOMAIN()[DomainName],@@servername, db_name() db_name


2):
DECLARE @Domain varchar(100), @key varchar(100)
SET @key = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\'
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key=@key,@value_name='Domain',@value=@Domain OUTPUT 
SELECT 'Server Name: '+@@servername + ' Domain Name:'+convert(varchar(100),@Domain)

rem 3):
rem EXEC Master.dbo.xp_LoginConfig 'Default Domain'

sp_who sp_who2

-- sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]
sp_who
go
exec sp_who
EXEC sp_who 'active';
EXEC sp_who 'sa';
EXEC sp_who 'interface';
EXEC sp_who '32' --specifies the process_id;

exec sp_helptext sp_who

sp_who @loginame='my_username'

drop table #temp_sp_who ;
create table #temp_sp_who 
                     (  spid            smallint,  
                        ecid            smallint,  
                        status          nchar(30),  
                        loginame        nchar(128),  
                        hostname        nchar(128),  
                        blk             char(5),  
                        dbname          nchar(128),  
                        cmd             nchar(16),
                        request_id      int);

INSERT INTO #temp_sp_who EXEC sp_who
set nocount on select * from #temp_sp_who;
select * from #temp_sp_who where dbname='amos';

set nocount on;
DECLARE @retTable TABLE 
                (  spid      smallint,  
                   ecid      smallint,  
                   status    nchar(30),  
                   loginname nchar(128),  
                   hostname nchar(128),  
                   blk char(5),  
                   dbname nchar(128),  
                   cmd nchar(16),  
                   request_id INT)INSERT INTO @retTable EXEC sp_who;
select * from @retTable where dbname='amos';

declare @ttTable table (SPID INT,
                        Status VARCHAR(255),
                        Login VARCHAR(255),
                        HostName VARCHAR(255),
                        BlkBy VARCHAR(255),
                        DBName VARCHAR(255),
                        Command VARCHAR(255),
                        CPUTime INT,
                        DiskIO INT,
                        LastBatch VARCHAR(255),
                        ProgramName VARCHAR(255),
                        SPID2 INT,
                        REQUESTID INT);

INSERT INTO @ttTable EXEC sp_who2;

select * from @ttTable where DBName is not null and DBName not in ( 'master' );

top

sp_who2
go

select count(*) from #temp_sp_who2;
go

DROP TABLE #temp_sp_who2;
go

CREATE TABLE #temp_sp_who2 
(
   SPID INT,  
   Status VARCHAR(1000) NULL,  
   Login SYSNAME NULL,  
   HostName SYSNAME NULL,  
   BlkBy SYSNAME NULL,  
   DBName SYSNAME NULL,  
   Command VARCHAR(1000) NULL,  
   CPUTime INT NULL,  
   DiskIO INT NULL,  
   LastBatch VARCHAR(1000) NULL,  
   ProgramName VARCHAR(1000) NULL,  
   SPID2 INT,
   REQUESTID INT
) 
GO

TRUNCATE TABLE #temp_sp_who2
GO

INSERT INTO #temp_sp_who2
EXEC sp_who2
GO

SELECT *
FROM #temp_sp_who2
where Status not in ( 'sleeping', 'BACKGROUND' )
order by status, Login
go

SELECT *
FROM #temp_sp_who2
where DBName not in ( 'master' )
order by status, Login
go


SELECT *
FROM #temp_sp_who2
order by Login
GO

SELECT *
FROM #temp_sp_who2
WHERE Login like '%SAN%'
GO

SELECT *
FROM #temp_sp_who2
WHERE upper(Login) like upper('%San%')
GO

TRUNCATE TABLE #temp_sp_who2
GO

DROP TABLE #temp_sp_who2
GO

Session Property

SELECT  
c.session_id as SessionId, 
most_recent_session_id as MostRecentSessionId, 
connect_time as ConnectTime,
net_transport as Transport,
 protocol_type as ProtocolType, 
protocol_version as ProtocolVersion,
c.endpoint_id as EndpointId, 
encrypt_option as EncryptOption, 
s.host_name as host_name,
s.login_name as login_name,
s.original_login_name as original_login_name,
auth_scheme as AuthScheme, 
s.program_name as program_name,
s.client_interface_name as client_interface_name,
node_affinity as NodeAffinity,
num_reads as NumReads, 
num_writes as NumWrites, 
last_read as LastRead, 
last_write as LastWrite,
net_packet_size as PacketSize, 
client_net_address as ClientNetworkAddress, 
client_tcp_port as ClientTcpPort,
local_net_address as ServerNetworkAddress, 
local_tcp_port as ServerTcpPort, 
connection_id as ConnectionId,
parent_connection_id as ParentConnectionId, most_recent_sql_handle as MostRecentSqlHandle
FROM sys.dm_exec_connections c, sys.dm_exec_sessions s
WHERE 
-- c.session_id = @@SPID and 
c.session_id=s.session_id
;



top   prev   next  

sp_whoisactive with query_plan order by CPU desc

dbcc inputbuffer (session_id or process_id)

SELECT * FROM sys.dm_exec_input_buffer (spid-session_process_id, null);
GO

EXEC sp_WhoIsActive
    @filter = '',
    @filter_type = 'session',
    @not_filter = '',
    @not_filter_type = 'session',
    @show_own_spid = 0,
    @show_system_spids = 0,
    @show_sleeping_spids = 1,
    @get_full_inner_text = 0,
    @get_plans = 2,
    @get_outer_command = 0,
    @get_transaction_info = 1,
    @get_task_info = 1,
    @get_locks = 1,
    @get_avg_time = 0,
    @get_additional_info = 1,
    @find_block_leaders = 1,
    @delta_interval = 1,
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][query_plan][locks][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][[%]',
    @sort_order = '[CPU] DESC',
    @format_output = 1,
    @destination_table = '',
    @return_schema = 0,
    @schema = NULL,
    @help = 0



top   prev   next  

Current Running sql Captured By Monitoring Script

SELECT s.loginame, db_name(s.dbid) name, s.hostname, s.program_name, 
s.sql_handle, s.stmt_start, s.stmt_end, s.spid, 
CONVERT(smallint, s.waittype) waittype, s.lastwaittype, s.ecid, 
s.waittime, CONVERT(varchar(64), s.context_info) context_info,
RTRIM(r.wait_resource) waitresource, s.blocked , r.plan_handle, 
r.statement_start_offset, r.statement_end_offset, r.start_time, 
r.query_plan_hash, t.text,
qp.query_plan
FROM master..sysprocesses AS s WITH(NOLOCK) 
LEFT OUTER JOIN sys.dm_exec_requests r WITH(NOLOCK) ON r.session_id = s.spid 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE (s.dbid<>0 AND s.cmd<>'AWAITING COMMAND' AND s.cmd NOT LIKE '%BACKUP%' 
AND s.cmd NOT LIKE '%RESTORE%' AND s.cmd NOT LIKE 'FG MONITOR%' 
AND s.hostprocess > '' AND s.spid>50 AND s.spid<>@@SPID)  
AND lastwaittype NOT IN ('BACKUPIO', 'SLEEP_BPOOL_FLUSH', 'SLEEP_TASK', 'WAITFOR');



blocking session

1):
select count(*) cnt from master.sys.dm_exec_requests a 
where (a.blocking_session_id <> 0 and a.blocking_session_id is not null) 
and status not in ( 'background' )

2):
select blocking_session_id, session_id
-- ,  a.* from master.sys.dm_exec_requests a 
where (a.blocking_session_id <> 0 and a.blocking_session_id is not null) 
and status not in ( 'background' )
order by a.blocking_session_id;
go

3):
SELECT  r.session_id
      , r.command
      , CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
      , CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GETDATE()), 20) AS [ETA Completion Time]
      , CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
      , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
      , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
      , CONVERT(VARCHAR(1000), (SELECT  SUBSTRING(text, r.statement_start_offset / 2, CASE WHEN r.statement_end_offset = -1 THEN 1000
                                                                                           ELSE (r.statement_end_offset - r.statement_start_offset) / 2
                                                                                      END)
                                FROM    sys.dm_exec_sql_text(sql_handle)
                               ))
FROM    master.sys.dm_exec_requests r
WHERE   command IN ('SELECT', 'SELECT INTO', 'INSERT', 'UPDATE', 'DELETE','RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE HEADERON', 'RESTORE HEADERONLY')
and r.session_id <> @@SPID;

4):
dbcc inputbuffer (58 ) 

5):
sp_who2

sp_who

sp_who my_loginame

-- blocking session ordered by
SELECT
s.spid, 
BlockingSPID = s.blocked, 
DatabaseName = DB_NAME(s.dbid),
s.program_name, 
s.loginame, 
ObjectName = OBJECT_NAME(objectid,s.dbid), 
Definition = CAST(text AS VARCHAR(MAX))
INTO        #Processes
FROM      sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50;

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow)
AS
(
     SELECT
      s.SPID, s.BlockingSPID, s.Definition,
      ROW_NUMBER() OVER(ORDER BY s.SPID),
      0 AS LevelRow
    FROM
      #Processes s
      JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
    WHERE
      s.BlockingSPID = 0
    UNION ALL
    SELECT
      r.SPID,  r.BlockingSPID, r.Definition,
      d.RowNo,
      d.LevelRow + 1
    FROM
      #Processes r
     JOIN Blocking d ON r.BlockingSPID = d.SPID
    WHERE
      r.BlockingSPID > 0
)
SELECT * FROM Blocking
ORDER BY RowNo, LevelRow;

-- -----------------------------------------------------------------------------------------------------
-- Blocking Session Tree Shape
-- -----------------------------------------------------------------------------------------------------
SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

6): concatenated text
with total_blocking_session as
(select count(*) cnt from master.sys.dm_exec_requests a 
where (a.blocking_session_id <> 0 and a.blocking_session_id is not null) 
and status not in ( 'background' ))
select 
'blocking_session_id: ' + convert(sysname,isnull(a.blocking_session_id,0)) + ' ' +
'blocked_session_id: ' + convert(sysname,isnull(a.session_id,0)) + ' ' +
'blocking_session_info: ' + isnull(e.host_name,N'') + ' ' + isnull(e.login_name,'') + ' ' + isnull(e.program_name, ' ')  + ' ' + isnull(d.command, ' ') + ' ' +
'blocked_session_info: ' + isnull(c.host_name,N'') + ' ' + isnull(c.login_name,'')  + ' ' + isnull(c.program_name,'') + ' ' + isnull(a.command,'')  + ' ' + 
'blocking_session_sql_text: ' + isnull(f.text,'')  + ' ' + 
'blocked_session_sql_text: ' + isnull(b.text,'') blocked_sql_text, 
total_blocking_session.cnt + 1 cnt
from master.sys.dm_exec_requests a 
outer apply sys.dm_exec_sql_text(a.sql_handle) AS b
left join master.sys.dm_exec_sessions AS c on c.session_id = a.session_id 
left outer join master.sys.dm_exec_requests AS d on a.blocking_session_id = d.session_id
left outer join master.sys.dm_exec_sessions AS e on d.session_id = e.session_id 
outer apply sys.dm_exec_sql_text(isnull(d.sql_handle,null)) AS f,
total_blocking_session total_blocking_session
where 
a.sql_handle is not null 
and a.session_id <> @@SPID
and a.status not in ( 'background' ) 
and a.blocking_session_id is not null and a.blocking_session_id <> 0;

7): comma seperated
with total_blocking_session as
(select count(*) cnt from master.sys.dm_exec_requests a 
where (a.blocking_session_id <> 0 and a.blocking_session_id is not null) 
and status not in ( 'background' ))
select 
a.blocking_session_id,
a.session_id,
e.host_name blocking_host_name, e.login_name blocking_login_name,e.program_name blocking_program_name,
d.command blocking_command,
c.host_name blocked_host_name, c.login_name blocked_login_name,c.program_name blocked_program_name, a.command blocked_command
, b.text blocked_sql_text
,f.text blocking_sql_text 
,total_blocking_session.cnt + 1 as cnt
from master.sys.dm_exec_requests a 
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS b
left join master.sys.dm_exec_sessions AS c on c.session_id = a.session_id 
left outer join master.sys.dm_exec_requests AS d on a.blocking_session_id = d.session_id
left outer join master.sys.dm_exec_sessions AS e on d.session_id = e.session_id 
CROSS APPLY sys.dm_exec_sql_text(d.sql_handle) AS f,
total_blocking_session total_blocking_session
where a.sql_handle is not null and a.session_id <> @@SPID
and a.status not in ( 'background' ) 
and a.blocking_session_id is not null and a.blocking_session_id <> 0;



top   prev   next  

sp_whoisactive source code

sp_whoisactive source code


top   prev   next  

Find Missing Index

-- Find Missing Index Per Query
;WITH  
 XMLNAMESPACES 
     (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'   
             ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)       
SELECT top 5 ECP.[usecounts]    AS [UsageCounts] 
      ,ECP.[refcounts]    AS [RefencedCounts] 
      ,ECP.[objtype]      AS [ObjectType] 
      ,ECP.[cacheobjtype] AS [CacheObjectType] 
      ,EST.[dbid]         AS [DatabaseID] 
      ,EST.[objectid]     AS [ObjectID] 
      ,EST.[text]         AS [Statement]       
      ,EQP.[query_plan]   AS [QueryPlan] 
FROM sys.dm_exec_cached_plans AS ECP 
     CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST 
     CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP 
WHERE ECP.[usecounts] > 1  -- Plan should be used more then one time (= no AdHoc queries) 
      AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0 
ORDER BY ECP.[usecounts] DESC

-- Find Missing Index Per Table
EXEC sp_MSforeachdb '
use [?]
SELECT 
TOP 25  @@servername,
db_name(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,''''),'', '',''_''),''['',''''),'']'','''')  + ''_IDX_#MYCOMPANY'' + '']'' 
+ '' 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 
'

save xml content as myfile.sqlplan

<ShowPlanXML>

</ShowPlanXML>

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select object_name(object_id) table_name, name, type_desc, is_disabled from sys.indexes where name like ''%#ncl'''
EXECUTE master.sys.sp_MSforeachdb "USE [?]; select  object_name(object_id) table_name, name, type_desc, is_disabled from sys.indexes where name like '%#ncl'"

select * from sys.indexes where name like '%#xxx';

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; select db_name(); select * from sys.indexes where name like ''%#xxx'''
EXECUTE master.sys.sp_MSforeachdb "USE [?]; select db_name(); select * from sys.indexes where name like '%#xxx'"

SET Showplan and Set Statistics 
-- new
SET SHOWPLAN_XML ON
SET STATISTICS XML ON
SET STATISTICS IO ON
SET STATISTICS TIME ON
-- depreciated
SET SHOWPLAN_TEXT ON
SET STATISTICS PROFILE ON
SET SHOWPLAN_ALL ON



top   prev   next  

Estimated Execution Plan

-- need declare the type, don't need to supply the value.
(@P1 datetime,@P2 int,@P3 varchar(40),@P4 text)
(@1 varchar(8000)) 
SELECT *  
FROM [mytable]  
WHERE [mycol1]=@1;

-- SSMS: Display Estimated Execution Plan With Variables
declare @COL1 smallint;
declare @COL2 uniqueidentifier;

UPDATE MYTABLE  
   SET COL2 = @COL2
WHERE COL1 = @COL1;

SET STATISTICS IO ON
SET STATISTICS TIME ON; 

SET SHOWPLAN_XML ON

save the following as a .sqlplan file
<ShowPlanXML ...>
... ....
</ShowPlanXML>


top   prev   next  

Numa Nodes

-- one row per numa nodes
select *
from sys.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Node'
and counter_name = 'Page life expectancy';



top   prev   next  

sp_whoisactive source code

sp_whoisactive source code


top   prev   next  

Kill Sessions For A Specific DB

Kill <n> with statusonly will tell you about how far through the rollback is.

kill 53 with statusonly

-- sp_databases
-- sp_who2

declare @database_name sysname;
-- set @database_name='MYDB'
set @database_name='MYDB1'

SELECT  'kill ' + convert(varchar,spid) cmd FROM master..sysprocesses
WHERE   dbid = DB_ID(@database_name) AND spid != @@SPID order by spid;

-- session connect to current database
SELECT  a.loginame,a.hostname,db_name(a.dbid) db_name,a.program_name,
a.cmd, a.status,a.cpu,a.physical_io, a.memusage,a.last_batch 
FROM    master..sysprocesses a 
WHERE   
spid != @@SPID
AND dbid = DB_ID(db_name());

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_RemoveActiveConnections]
    @database_name SYSNAME
AS 
    BEGIN
        DECLARE @spid INT
        DECLARE @query NVARCHAR(255)
        DECLARE processes CURSOR
        FOR
            SELECT  spid
            FROM    master..sysprocesses
            WHERE   dbid = DB_ID(@database_name)
                    AND spid != @@SPID
        -- DECLARE @database_name sysname;
        -- @database_name = 'mydbname';
        OPEN processes
        FETCH NEXT FROM processes INTO @spid
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                PRINT 'KILLing ' + RTRIM(@spid)
                SET @query = 'KILL ' + RTRIM(@spid)
                EXEC(@query)
                FETCH NEXT FROM processes INTO @spid
            END
        CLOSE processes
        DEALLOCATE processes
    END
GO


top   prev   next  

Kill Sessions For A Specific User

-- sp_who2

declare  @login_name sysname;
-- set @login_name='MYLOGIN'
set @login_name=N'MYLOGIN1'

SELECT  N'kill ' + convert(nvarchar,spid) cmd FROM master..sysprocesses
WHERE   loginame = @login_name AND spid != @@SPID order by spid;

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_KillUserSessions] @login_name sysname
AS
    BEGIN
        DECLARE @spid INT;
        DECLARE @query NVARCHAR(255);
        DECLARE processes CURSOR
        FOR
            SELECT  spid
            FROM    master.sys.sysprocesses
            WHERE   loginame = @login_name
                    AND spid != @@SPID;
        -- DECLARE @login_name sysname;
        -- @login_name = 'myusername';
        OPEN processes;
        FETCH NEXT FROM processes INTO @spid;
        WHILE @@FETCH_STATUS = 0
            BEGIN
                PRINT 'KILLing ' + RTRIM(@spid);
                SET @query = 'KILL ' + RTRIM(@spid);
                EXEC(@query);
                FETCH NEXT FROM processes INTO @spid;
            END;
        CLOSE processes;
        DEALLOCATE processes;
    END;
GO



top   prev   next  

SQL Server Agent Job Failed Job Error Message

SELECT top 10 
        sh.run_status,
        sh.step_id,
        sj.name + ' ' + sh.step_name,
        sh.run_date,
        STUFF(STUFF(RIGHT(REPLICATE('0', 6) +  
          CAST(sh.run_time as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
        STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + 
        CAST(sh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 
            'run_duration (DD:HH:MM:SS)  ',
	sh.message
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobhistory sh
ON sj.job_id = sh.job_id
where name like '%BK%' 
-- and sh.run_status <> 1 
order by sh.instance_id desc,sh.run_date desc, sh.run_time desc;
go

select	j.name
	,js.step_name
	,js.command
	,jh.message
	,jh.run_date
	,jh.run_time
	,jh.run_duration
from	msdb..sysjobs j
join	msdb..sysjobsteps js
		on js.job_id = j.job_id
join	msdb..sysjobhistory jh
		on jh.job_id = js.job_id
		and jh.step_id = js.step_id
where	
jh.run_status <> 1 
-- and j.[name] = N'!BK_USERDB_FULL'
; 

select @@version
go

select name from msdb..sysjobs 
where name like '%BK%' 
order by 1
go

select * from msdb..sysjobs 
where name like '%BK%' 
and enabled = 1
order by 1
go

select top 10 j.name, h.* from msdb..sysjobhistory h, msdb..sysjobs j 
where 
h.job_id = h.job_id and j.name = N'BK_DAILY_DATABASE.Subplan_1' 
order by h.instance_id;

select top 3	j.name
	,js.step_name
	,js.command
	,jh.message
	,jh.run_date
	,jh.run_time
	,jh.run_duration
from	msdb..sysjobs j
join	msdb..sysjobsteps js
		on js.job_id = j.job_id
join	msdb..sysjobhistory jh
		on jh.job_id = js.job_id
		and jh.step_id = js.step_id
where	
jh.run_status <> 1 and
-- and jh.run_date > dateadd(dd,-1,getdate()) 
-- and 
-- j.name = N'BK_DAILY_DATABASE.Subplan_1'
j.name like '%BK_TRANS%LOG_DATABASE%'
and jh.run_date > cast(convert(char(8),getdate(),112) as int) -1 
order by jh.run_date desc, jh.run_time desc;
go



top   prev   next  

SQL Server (Active) Session Eg: Backup Or Restore Status

-- Backup And Restoration Status
SELECT  r.session_id
      , r.command
      , CONVERT(NUMERIC(6, 2), r.percent_complete) AS [Percent Complete]
      , CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GETDATE()), 20) AS [ETA Completion Time]
      , CONVERT(NUMERIC(10, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]
      , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [ETA Min]
      , CONVERT(NUMERIC(10, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [ETA Hours]
      , CONVERT(VARCHAR(1000), (SELECT  SUBSTRING(text, r.statement_start_offset / 2, CASE WHEN r.statement_end_offset = -1 THEN 1000
                                                                                           ELSE (r.statement_end_offset - r.statement_start_offset) / 2
                                                                                      END)
                                FROM    sys.dm_exec_sql_text(sql_handle)
                               ))
FROM    master.sys.dm_exec_requests r
WHERE   command IN ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE HEADERON', 'RESTORE HEADERONLY')



top   prev   next  

Default Backup Location

Using SSMS to Change Default Backup Location:
Database Settings --> Data and Log locations 

Default Backup Location
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer 

EXEC  master.dbo.xp_instance_regread 
 N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer',N'BackupDirectory'

-- Query Default Backup Location
DECLARE @BackupDirectory VARCHAR(100) 
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', 
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer', 
@value_name='BackupDirectory', 
@BackupDirectory=@BackupDirectory OUTPUT 
SELECT @BackupDirectory 

-- Change Default Backup Location
mssqltips:Change Default Backup Location
EXEC master..xp_regwrite 
@rootkey='HKEY_LOCAL_MACHINE', 
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLServer', 
@value_name='BackupDirectory', 
@type='REG_SZ', 
@value='C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Backup'



top   prev   next  

Backup SSMS and Script

mssqltips:BACKUP DB Using SSMS and Scripting out TSQL
BACKUP DATABASE [YourDB] TO  DISK = N'SomePath\YourDB.bak' 
WITH FORMAT, INIT,  NAME = N'YourDB Full Database Backup', 
SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

BACKUP DATABASE [AdventureWorks] 
   TO  DISK = N'C:\SQL_Backup\AdventureWorks_full_20080522.BAK' 
   WITH  DESCRIPTION = N'Full backup of the AdventureWorks database', 
   NOFORMAT, 
   INIT,  
   NAME = N'AdventureWorks-Full Database Backup', 
   SKIP, 
   NOREWIND, 
   NOUNLOAD,  
   STATS = 10, 
   CHECKSUM
GO
DECLARE @backupSetId AS INT
SELECT @backupSetId = position 
FROM msdb..backupset 
WHERE database_name=N'AdventureWorks' 
   AND backup_set_id=(SELECT MAX(backup_set_id) 
               FROM msdb..backupset 
               WHERE database_name=N'AdventureWorks' )
IF @backupSetId IS NULL 
BEGIN 
   RAISERROR(N'Verify failed. Backup information for database ''AdventureWorks'' not found.', 16, 1) 
END
RESTORE VERIFYONLY 
   FROM  DISK = N'C:\SQL_Backup\AdventureWorks_full_20080522.BAK' 
   WITH  FILE = @backupSetId,  
   NOUNLOAD,  
   NOREWIND
GO


top   prev   next  

Query SQL Server Last Backup Time

-- query db last backup time
SELECT CONVERT(VARCHAR(15), @@SERVERNAME) servername, sdb.Name db_name, cast(MAX(backup_finish_date)AS date) LastBackUpTime 
FROM master.sys.sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name and bus.type = 'D'
GROUP BY sdb.Name;
GO

-- view db last backup time
create view [dbo].[vw_dblastbackup] as SELECT CONVERT(VARCHAR(15), @@SERVERNAME) servername, sdb.Name db_name, 
cast(MAX(backup_finish_date)AS date) LastBackUpTime 
FROM master.sys.sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name and bus.type = 'D'
GROUP BY sdb.Name;
GO


top   prev   next  

Query SQL Server Backupset File Location

msdb.dbo.backupfile: logical datafile name, need to restore datafile to different location
msdb.dbo.backupfilegroup
msdb.dbo.backupmediafamily
msdb.dbo.backupmediaset
msdb.dbo.backupset

select  top 50 a.server_name, a.database_name, backup_finish_date, a.backup_size,
CASE a.[type] -- Let's decode the three main types of backup here
 WHEN 'D' THEN 'Full'
 WHEN 'I' THEN 'Differential'
 WHEN 'L' THEN 'Transaction Log'
 ELSE a.[type]
END as BackupType
 ,b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
where backup_finish_date !> GETDATE()
-- and a.database_name Like 'master%'
-- and a.type <> 'L' 
order by a.backup_finish_date desc;
go

------------------------------------------------------------------------------------------- 
--Most Recent Database Backup for Each Database 
------------------------------------------------------------------------------------------- 
SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name  
ORDER BY  
   msdb.dbo.backupset.database_name;
GO

-- Database Backups for all databases For Previous Week  GETDATE() - 7
SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset 
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date;
go

------------------------------------------------------------------------------------------- 
--Most Recent Database Backup for Each Database - Detailed 
------------------------------------------------------------------------------------------- 

SELECT  
   A.[Server],  
   A.last_db_backup_date,  
   B.backup_start_date,  
   B.expiration_date, 
   B.backup_size,  
   B.logical_device_name,  
   B.physical_device_name,   
   B.backupset_name, 
   B.description 
FROM 
   ( 
   SELECT   
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
       msdb.dbo.backupset.database_name,  
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
   FROM    msdb.dbo.backupmediafamily  
       INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
   WHERE   msdb..backupset.type = 'D' 
   GROUP BY 
       msdb.dbo.backupset.database_name  
   ) AS A    
   LEFT JOIN  
   ( 
   SELECT   
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   msdb.dbo.backupset.backup_start_date,  
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.expiration_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.logical_device_name,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.name AS backupset_name, 
   msdb.dbo.backupset.description 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset 
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
   ) AS B 
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] 
  AND A.[last_db_backup_date] = B.[backup_finish_date] 
ORDER BY  
   A.database_name;
GO

------------------------------------------------------------------------------------------- 
--Databases Missing a Data (aka Full) Back-Up Within Past 24 Hours 
------------------------------------------------------------------------------------------- 
--Databases with data backup over 24 hours old 
SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name, 
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date, 
   DATEDIFF(hh, MAX(msdb.dbo.backupset.backup_finish_date), GETDATE()) AS [Backup Age (Hours)] 
FROM    msdb.dbo.backupset 
WHERE     msdb.dbo.backupset.type = 'D'  
GROUP BY msdb.dbo.backupset.database_name 
HAVING      (MAX(msdb.dbo.backupset.backup_finish_date) < DATEADD(hh, - 24, GETDATE()))  
UNION  
--Databases without any backup history 
SELECT      
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,  
   master.dbo.sysdatabases.NAME AS database_name,  
   NULL AS [Last Data Backup Date],  
   9999 AS [Backup Age (Hours)]  
FROM 
   master.dbo.sysdatabases LEFT JOIN msdb.dbo.backupset 
       ON master.dbo.sysdatabases.name  = msdb.dbo.backupset.database_name 
WHERE msdb.dbo.backupset.database_name IS NULL AND master.dbo.sysdatabases.name <> 'tempdb' 
ORDER BY  
   msdb.dbo.backupset.database_name;
GO

-- Full Back-Up Within Past 24 Hours 
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'D'
ORDER BY backup_set_id DESC
GO

-- Transaction Log Back-Up Within Past 24 Hours 
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'L'
ORDER BY backup_set_id DESC
GO

-- Differential Back-Up Within Past 24 Hours 
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'I'
ORDER BY backup_set_id DESC
GO

-- File\File Group Backups
SELECT database_name, backup_start_date, type, *
FROM msdb.dbo.backupset
WHERE backup_start_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND Type = 'F'
ORDER BY backup_set_id DESC
GO

select a.backup_set_id, a.server_name, a.database_name, 
a.name, a.user_name, a.position, a.software_major_version, 
a.backup_start_date, backup_finish_date, a.backup_size, 
a.recovery_model, b.physical_device_name
from msdb.dbo.backupset a join msdb.dbo.backupmediafamily b
  on a.media_set_id = b.media_set_id
-- where a.database_name = 'DatabaseName'
order by a.backup_finish_date desc
Go

SELECT  DatabaseName = x.database_name,
        LastBackupFileName = x.physical_device_name,
        LastBackupDatetime = x.backup_start_date
FROM (  SELECT  bs.database_name,
                bs.backup_start_date,
                bmf.physical_device_name,
                  Ordinal = ROW_NUMBER() OVER( PARTITION BY bs.database_name 
                                               ORDER BY bs.backup_start_date DESC )
          FROM  msdb.dbo.backupmediafamily bmf
                  JOIN msdb.dbo.backupmediaset bms ON bmf.media_set_id = bms.media_set_id
                  JOIN msdb.dbo.backupset bs ON bms.media_set_id = bs.media_set_id
          WHERE   bs.[type] = 'D'
                  AND bs.is_copy_only = 0 ) x
WHERE x.Ordinal = 1
ORDER BY DatabaseName;
Go

SELECT          database_name,
                physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
-- WHERE database_name = 'YourDB'
ORDER BY backup_finish_date DESC
GO

wraithnath



top   prev   next  

point in time restore and recovery:

ALTER DATABASE AdventureWorks SET RECOVERY FULL
GO

Back up the tail of the log
BACKUP LOG <database_name> TO <backup_device>   
   WITH NORECOVERY, NO_TRUNCATE; 

-- Multiple File Restore
RESTORE DATABASE [MYDB] FROM
DISK = 'F:\MYDIR\MY_BACKUP_FILE_1.bak',
DISK = 'F:\MYDIR\MY_BACKUP_FILE_2.bak',
DISK = 'F:\MYDIR\MY_BACKUP_FILE_3.bak',
DISK = 'F:\MYDIR\MY_BACKUP_FILE_4.bak'
-- WITH  NOUNLOAD,  REPLACE,  STATS = 10 
WITH  NORECOVERY 
GO

RESTORE DATABASE AdventureWorks  FROM AdventureWorksBackups  WITH FILE=3, NORECOVERY;  
-- or RESTORE DATABASE AdventureWorks  FROM DISK = N'D:\xxx.bak', NORECOVERY;  

RESTORE LOG AdventureWorks  FROM AdventureWorksBackups  WITH FILE=4, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
-- or RESTORE LOG AdventureWorks  FROM DISK = N'D:\yyy.trn', NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  
  
RESTORE LOG AdventureWorks  FROM AdventureWorksBackups  WITH FILE=5, NORECOVERY, STOPAT = 'Apr 15, 2020 12:00 AM';  

RESTORE LOG [AdventureWorks] 
   FROM DISK = N'D:\zzz.trn'
   WITH STOPAT = '2016-12-21 11:12:18.797', -- Insert Your Time
   STANDBY = N'D:\yyy.trn'
GO

rem RESTORE LOG [test] 
rem FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Test_Transaction' 
rem WITH FILE = 3, 
rem NOUNLOAD , 
rem STATS = 10, RECOVERY , 
rem STOPAT = N'8/21/2005 3:20:00 PM'

rem RESTORE LOG AdventureWorks FROM DISK = 'c:\adventureworks_log.bak'   
rem WITH STOPATMARK = 'lsn:15000000040000037'  
rem GO

rem RESTORE LOG AdventureWorks FROM DISK = 'c:\adventureworks_log.bak'   
rem WITH STOPBEFOREMARK = 'lsn:15000000040000037'  
rem GO

RESTORE DATABASE AdventureWorks WITH RECOVERY;   
GO  

Restore a SQL Server Database to a Point in Time (Full Recovery Model)
1 
2 

Recover to a Log Sequence Number 
1 

msdbgmarkhistory
1 

restore headeronly
1 

RESTORE HEADERONLY   
FROM DISK = N'C:\AdventureWorks-FullBackup.bak'   
WITH NOUNLOAD;  
GO 

restore filelist only
1 
RESTORE FILELISTONLY FROM AdventureWorksBackups   
--   WITH FILE=2;  
GO

1 
RESTORE VERIFYONLY

Generate a point-in-time restore automatically SQL 2005 version
1 
2 
3 



top   prev   next  

Generate Backup Script

-- backup everyday

backup

DECLARE @LFCR AS CHAR(2) = CHAR(13) + CHAR(10)
select 
'backup database [' + name + ']' + @LFCR + 
'TO DISK = N''D:\DBBACKUPS\Backup\' + name +  '.bak'' WITH COMPRESSION,FORMAT, INIT,'+ @LFCR + 
'NAME = N'''+ name + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'+ @LFCR + 
'GO' + @LFCR
as cmd 
 from sys.databases where name not in ( 'model', 'tempdb' ) order by database_id;

-- -----------------------------------------------------------------------------------

DECLARE @LFCR AS CHAR(2) = CHAR(13) + CHAR(10)
select 
'backup database [' + name + ']' + @LFCR + 
'TO DISK = N''D:\DBBACKUPS\Backup\' + name +  N'_D' + substring (convert (varchar (10), getdate(),112),7,2) + 
'.bak'' WITH COMPRESSION,FORMAT, INIT,'+ @LFCR + 
'NAME = N'''+ name + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'+ @LFCR + 
'GO' + @LFCR
as cmd 
 from sys.databases where name not in ( 'model', 'tempdb' ) order by database_id;

-- -----------------------------------------------------------------------------------


-- cf1: select name from sys.databases where name not in ( 'model' );

d:
cd dbbackups

BACKUP DATABASE [MyDB1] 
TO  DISK = N'D:\DBBACKUPS\Backup\MyDB1.bak' WITH NOFORMAT, INIT,  
NAME = N'MyDB1-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO



top   prev   next  

Generate Recovery Script

/*
Generate Point-in-time restore script, given only DBName and Point-in-time timestamp
The script WILL NOT do any changes to your databases, it will only generate sql code 
that you need to manually execute.
  
Created by Henning Frettem, www.thefirstsql.com
*/
SET NOCOUNT ON
GO
  
DECLARE
  @DBName varchar(200),
  @PointInTime datetime,
  @cPointInTime varchar(19),
  @Filename varchar(200),
  @tab varchar(1) = char(9),
  @cr varchar(2) = char(13)+char(10),
  @Full_BackupStartDate datetime,
  @Diff_BackupStartDate datetime,
  @Log_BackupStartDate datetime,
  @SQL varchar(max) = ''

  
-- SET @DBName  = 'MyDatabase'
-- SET @PointInTime = '2017-01-01 17:10:00'

set @cPointInTime=convert(char(19),getdate(),20) 
set @DBName=db_name()
set @PointInTime=@cPointInTime
print '-- ' + @cPointInTime
print '-- ' + @DBName

  
BEGIN TRY
  --> Performing some checks
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset 
      WHERE database_name = @DBName)
       RAISERROR(N'The database has never been backed up', 16, 1)
  IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset 
          WHERE database_name = @DBName AND type = 'D' AND backup_start_date <= @PointInTime)
    RAISERROR(N'No full backup exists prior to the specified PointInTime', 16, 1)
  ELSE IF NOT EXISTS (SELECT 1 FROM msdb.dbo.backupset 
         WHERE database_name = @DBName AND type = 'L' AND backup_start_date > @PointInTime)
    RAISERROR(N'No backup of the log exist after the specified PointInTime, do a tail log backup first', 16, 1)
  
  --> Getting the filename and the date of the last full backup prior to the PointInTime
  SELECT TOP 1 
      @Filename = b.physical_device_name,
      @Full_BackupStartDate = backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date <= @PointInTime
  ORDER BY a.backup_start_date DESC
  
  SET @SQL = 'USE master' + @cr + 
    'GO' + @cr + @cr +
    'ALTER DATABASE ' + QUOTENAME(@DBName) + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + @cr + 
    'GO' + @cr + @cr
   
  PRINT @SQL
  SET @SQL = ''
  
  --> Restore the last full backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', REPLACE, STATS=5, NORECOVERY' + @cr + 'GO' + @cr,
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'D'
    AND a.backup_start_date = @Full_BackupStartDate
  ORDER BY a.backup_start_date DESC  
 
  PRINT @SQL
  SET @SQL = ''
    
  --> Restore the last differential backup if it exists
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE DATABASE ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO',
    @Diff_BackupStartDate = a.backup_start_date
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'I'
    AND a.backup_start_date > @Full_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date DESC
   
  PRINT @SQL
  SET @SQL = ''
    
  IF @Diff_BackupStartDate IS NULL
    SET @Diff_BackupStartDate = @Full_BackupStartDate
      
  --> Generate all log restores except the last one
  SELECT
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', NORECOVERY' + @cr + 'GO' + @cr,
    @Log_BackupStartDate = a.backup_start_date   
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @Diff_BackupStartDate
    AND a.backup_start_date < @PointInTime
  ORDER BY a.backup_start_date
  
  PRINT @SQL
  SET @SQL = ''
 
  --> Generate last log restore script with the stopat command and recovery
  SELECT TOP 1
    @SQL = @SQL + 'RESTORE LOG ' + QUOTENAME(@DBName) + ' FROM DISK = ''' + b.physical_device_name + ''' 
        WITH FILE=' + CAST(a.Position as varchar(10)) + ', RECOVERY, STOPAT = ''' + CONVERT(varchar(20), 
            @PointInTime, 120) + '''' + @cr + 'GO' + @cr
  FROM msdb.dbo.backupset a
    INNER JOIN msdb.dbo.backupmediafamily b
      ON a.media_set_id = b.media_set_id
  WHERE a.database_name = @DBName
    AND a.type = 'L'
    AND a.backup_start_date > @PointInTime
  ORDER BY a.backup_start_date ASC
  PRINT @SQL
END TRY
BEGIN CATCH
  PRINT ERROR_MESSAGE()
END CATCH
GO



top   prev   next  

Single User Mode

-- EXEC sp_RemoveActiveConnections @database_name='mydb'
-- kill 9999
USE MASTER
GO
ALTER DATABASE [mydb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
-- DROP DATABASE [mydb]
-- GO
ALTER DATABASE [mydb] SET MULTI_USER;
Go


top   prev   next  

Get Current Time and Format

-- get est/edt time from different time zone
select  dateadd(hour, -4, getutcdate());

-- Quick Delay after Truncate
WAITFOR DELAY '00:00:01'
GO

-- GET DATE TIME FORMAT
SELECT GETDATE() 
GO

-- GET DATE TIME FORMAT
SELECT SYSDATETIME(),SYSDATETIMEOFFSET(),SYSUTCDATETIME(),CURRENT_TIMESTAMP,GETDATE(),GETUTCDATE();  
date and time

-- Yesterday
select cast(convert(char(8),getdate(),112) as int) -1 as yesterday;

-- for a day dd
select substring (convert (varchar (10), getdate(),112),7,2)

-- for yyyymmdd
select substring (convert (varchar (10), getdate(),112),1,8)

select substring (convert (varchar (10), getdate(),112),1,10)
go
-- for yyyymmdd hhmmss
select substring (convert (varchar (19), getdate(),120),1,19)

select substring (convert (varchar (20), getdate(),120),1,20)
go


BEGIN
	declare @ts Datetime
	declare @time_str1 varchar(30)
	declare @time_str2 varchar(30)
	declare @time_str varchar(30)
	declare @filename varchar(100)
	-- get current_timestamp : Apr 20 2017  5:31PM
	SELECT @ts=CURRENT_TIMESTAMP
	-- after 120 convert 2017-04-20 17:31:22
	-- after left 20, 2017-04-20 17:31:22
	set @time_str1 = LEFT(CONVERT(VARCHAR, @ts, 120), 20)
	-- after replace _ and :  2017-04-20_173122
	set @time_str = replace( replace( @time_str1, ' ', '_'), ':', '')
	-- filename format: T:\dbbackups\mydb_2017-04-20_173122.bak
	set @filename = 'D:\dbbackups\mydb_'+@time_str+'.bak'
	print @ts
	set @time_str2 = CONVERT(VARCHAR, @ts, 120)
	print @time_str2 
	print @time_str1
	print @time_str
    print @filename
END


top

top   prev   next  

Change Server Name

rename sql server instance name 
-- query server name
use master
go
select @@servername;
go
sp_helpserver;
go

-- change server name, printing
use master
go
begin 
declare @my_servername nvarchar(256); 
select @my_servername=@@servername;
print 'exec sp_dropserver ' + @my_servername + ';';
declare @my_hostname nvarchar(256);
-- select @my_hostname=host_name();
select @my_hostname=convert(sysname,serverproperty('Machinename'));
-- select @my_hostname=convert(sysname,serverproperty('servername'));
print 'exec sp_addserver ' + @my_hostname + ', local;';
END;
go

-- change server name, executing
use master
go
begin 
declare @my_servername nvarchar(256); 
select @my_servername=@@servername;
exec sp_dropserver @my_servername;
declare @my_hostname nvarchar(256);
-- select @my_hostname=host_name();
select @my_hostname=convert(sysname,serverproperty('Machinename'));
-- select @my_hostname=convert(sysname,serverproperty('servername'));
exec sp_addserver @my_hostname, local;
END;
go

-- sp_dropserver 'server', @droplogins
sp_dropserver 

sp_dropserver [ @server = ] 'server'   [ , [ @droplogins = ] { 'droplogins' | NULL} ]  
sp_dropserver 'old_servername', 'droplogins'; 
sp_dropserver 'ACCOUNTS', 'droplogins';  
-- removes the remote server ACCOUNTS and all associated remote logins from the local instance of SQL Server

-- list current remote logins and linked servers
SELECT ss.server_id 
          ,ss.name 
          ,'Server ' = Case ss.Server_id 
                            when 0 then 'Current Server' 
                            else 'Remote Server' 
                            end 
          ,ss.product 
          ,ss.provider 
          ,ss.catalog 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,'RPC Out Enabled'    = case ss.is_rpc_out_enabled 
                                   when 1 then 'True' 
                                   else 'False' 
                                   end 
           ,'Data Access Enabled' = case ss.is_data_access_enabled 
                                    when 1 then 'True' 
                                    else 'False' 
                                    end 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id;


top   prev   next  

Rename Data File

select d.name db_name, f.name file_lname,  f.physical_name file_pname
from sys.master_files f, sys.databases d
where  d.database_id = f.database_id;

-- cf: select @@VERSION
-- cf: select name, physical_name from sys.master_files
-- cf: select * from sys.master_files

-- -- Changing logical file names
-- ALTER DATABASE DB1 MODIFY FILE (NAME = DB1, NEWNAME = NEW_DB1);
-- ALTER DATABASE DB1 MODIFY FILE (NAME = DB1_log, NEWNAME = NEW_DB1_log);
-- newer_db declare @MYDB as nvarchar(255) = N'MYDB1'
declare @MYDB as nvarchar(255) 

-- set @MYDB = N'MYDB1'
set @MYDB = db_name();

select 'alter database ' + d.name + ' modify file ( name = ''' + f.name + ''', newname = ''new_'+ f.name + ''');' 
from sys.master_files f, sys.databases d
where d.name = @MYDB and d.database_id = f.database_id;

-- -- Changing file locations or physical file names
-- newer_db declare @MYDB as nvarchar(255) = N'MYDB1'
declare @MYDB as nvarchar(255) 

-- set @MYDB = N'MYDB1'
set @MYDB = db_name();

select 'alter database ' + d.name + ' modify file ( name = ''' + f.name + ''', filename = '''+ f.physical_name + ''');' 
from sys.master_files f, sys.databases d
where d.name = @MYDB and d.database_id = f.database_id;

-- alter database DB1 modify file ( name = 'DB1', filename = 'D:\MSSQL\DATA\DB1_01.mdf');
-- alter database DB1 modify file ( name = 'DB1_log', filename = 'E:\MSSQL\Data\DB1_log_01.LDF');

-- take database off line
-- move OS files
-- take database on line



top   prev   next  

logshipping Applying Check

View the Log Shipping Report -->
Right-click the server instance in Object Explorer, 
point to Reports, 
and point to Standard Reports.
Click Transaction Log Shipping Status Report.

select primary_server, primary_database, last_backup_date, last_backup_file from msdb.dbo.log_shipping_monitor_primary;

select secondary_server, secondary_database, last_copied_date, last_copied_file from msdb.dbo.log_shipping_monitor_secondary;

select secondary_server, secondary_database, last_restored_date, last_restored_file from msdb.dbo.log_shipping_monitor_secondary;

sp_help_log_shipping_monitor_secondary 
sp_change_log_shipping_secondary_database 
sp_add_log_shipping_secondary_database 


-- log restored
select 
@@servername primary_server
,secondary_server
,secondary_database
,isnull(threshold_alert, 14421) threshold_alert
,restore_threshold
,last_restored_date_utc
,getutcdate() current_utc_time
,datediff(minute, last_restored_date_utc, getutcdate()) restore_latency
,isnull(last_restored_latency,0) last_restored_latency
from msdb.dbo.log_shipping_monitor_secondary
order by secondary_server;


top   prev   next  

dba(database) mail

GUI
SSMS --> Management --> Database Mail --> Configure Database Mail,Profile,Account

Script
1): Enable Database Mail Stored Procedure
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure 
go

2): Add email account and mail server
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'MyMailAccount',
    @description = 'Mail account for Database Mail',
    @email_address = 'support_db1@quickdbasupport.com',
    @display_name = 'MyAccount',
    @username='support@@quickdbasupport.com',
    @password='support_pass1',
    @mailserver_name = 'mail.quickdbasupport.com'

3): Add email profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'MyMailProfile',
       @description = 'Profile used for database mail'

4): Add mail account to email profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyMailProfile',
    @account_name = 'MyMailAccount',
    @sequence_number = 1

5): to grant the Database Mail profile access to the msdb public database role 
and to make the profile the default Database Mail profile.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MyMailProfile',
    @principal_name = 'public',
    @is_default = 1 ;

6): Test This Email
declare @body1 varchar(100)
set @body1 = 'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='support@quickdbasupport.com',
    @subject = 'My Mail Test',
    @body = @body1,
    @body_format = 'HTML' ;

7): Verify through gui
SSMS --> Management --> Database Mail --> Configure Database Mail,Profile,Account

8): view Database Mail Logs
SELECT * FROM msdb.dbo.sysmail_event_log


top


top   prev   next  

Alert,Operator,sqlagent,DatabaseMail

 sqlmail,dbamail operator,alert,dbamail (profile),sqlagent
restarting SQL Agent
Warning: Restarting SQL Agent will cancel any executing jobs. 

ssms --> Right Click/select "SQL Server Agent" --> (Select ) Properties --> Alert System

Add New Operator: Sql Server Agent --> Operator --> New Operator

Add New Alert: Sql Server Agent --> Alerts --> New Alert



top   prev   next  

Shrink Database

DBCC SHRINKDATABASE ( database_name [ , target_percent ][ , { NOTRUNCATE | TRUNCATEONLY } ])
GO
DBCC SHRINKDATABASE (mydb, 10);
GO

-- light but may not working
USE [mydb]
GO
DBCC SHRINKFILE (N'my_db_log' , 0, TRUNCATEONLY)
GO

-- fileid=2 trying log file
EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0, TRUNCATEONLY)'

-- heavy but working
USE [mydb]
GO
DBCC SHRINKFILE (N'my_db_log' , 0)
GO

-- fileid=2 trying log file
EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0)'

-- one time truncate backup log
backup log mydb to disk='NUL:';
go
-- one time truncate backup log 1): pre-2012
backup log mydb with truncate_only;
go

-- flip database recovery mode to simple mode from full-recovery mode or bulk-log mode




top   prev   next  

sql profiler and sql trace

https://www.google.com/search?q=sql+profiler+trace&ie=utf-8&oe=utf-8

https://www.mssqltips.com/sql-server-tip-category/83/profiler-and-trace/

http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-sql-server-2008-profiler-complete/

http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

http://sqlconcept.com/tools/default-trace-audit-documentation-and-faq/

http://sqlconcept.com/tools/default-trace-audit-documentation-and-faq/screenshots/

Introducing SQL Trace
https://technet.microsoft.com/en-us/library/ms191006(v=sql.105).aspx

How to: Set a Trace Filter
https://technet.microsoft.com/en-us/library/ms188627(v=sql.105).aspx



top   prev   next  

CREATE LOGIN with sysadmin

USE [master]
GO
CREATE LOGIN [MYDOMAIN\MYUSER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [MYDOMAIN\MYUSER]
GO
EXEC sp_addsrvrolemember 'MYDOMAIN\MYUSER', 'sysadmin';
GO



top   prev   next  

Rebuild And Reorganize Indexes

ALTER INDEX myindex ON mytable REORGANIZE ;   
ALTER INDEX ALL     ON myschema.mytable REORGANIZE ;     

alter index myindex on mytable rebuild;
alter index all on mytable rebuild;
ALTER INDEX ALL ON myschema.mytable REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'mydb'), 
      OBJECT_ID(N'mytable'), NULL, NULL) AS a  
    JOIN sys.indexes AS b 
      ON a.object_id = b.object_id AND a.index_id = b.index_id;   
GO  

# cluster index and non-cluster index conversion
option 1):
CREATE UNIQUE CLUSTERED INDEX [PK_Customer] on Customer(CustomerID) WITH DROP_EXISTING;

option 2):
ALTER TABLE dbo.YourTable DROP CONSTRAINT PK_YourTable;
ALTER TABLE dbo.YourTable ADD CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED (YourPKField)



top   prev   next  

sp_configure and Configuration

sp_configure
go

sp_configure 'show advanced options', 1
go
reconfigure
go

select * from sys.configurations;
go

-- for wait type: CXPACKET
sp_Configure "max degree of parallelism"
go
sp_Configure "max degree of parallelism",1
go
reconfigure
go
sp_Configure "max degree of parallelism"
go




top   prev   next  

Query Error Log Location

REM query location
SELECT SERVERPROPERTY('ErrorLogFileName')

serverproperty 

D:\....\MSSQL12.MSSQLSERVER\MSSQL\Log\ERRORLOG


-- logging both success and failed login into ERRORLOG
SQL Server Properties > Security > Login Auditing select "Both failed and successful logins"

Maximum number of error log files: 6 and 99

In Object Explorer, 
Expand Management Node 
and then right click SQL Server Logs 
and click Configure as shown in the snippet below.

-- --------------
USE [master]
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'NumErrorLogs'
    ,REG_DWORD
    ,99
GO

EXEC master.dbo.sp_cycle_errorlog
GO

EXECUTE master.dbo.sp_cycle_errorlog 
GO

exec msdb.dbo.sp_cycle_agent_errorlog 
GO

cycling


sp_readerrorlog: the first 4 parameters

xp_readerrrorlog: all 7 parameters
1. log file to read: 0 = ERRORLOG, 1 = ERRORLOG.1 
2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1: 
4. Search string 2: 
5. Search start time
6. Search end time
7. N'asc' = ascending, N'desc' = descending

-- Reads current SQL Server error log
EXEC xp_ReadErrorLog 0, 1

-- Reads current SQL Server Agent error log
EXEC xp_ReadErrorLog 0, 2

-- Reads SQL Server error log from ERRORLOG.1 file
EXEC xp_ReadErrorLog 1

-- read in descending order
exec xp_readerrorlog 0,1,null,null,null,null,'DESC'
go

-- Reads current SQL Server error between 01-Nov-2012 and 30-Nov-2012
EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130'

-- Reads current SQL Server error log between 01-Nov-2012 and 30-Nov-2012 and sorts in descending order
EXEC xp_ReadErrorLog 0, 1, NULL, NULL, '20121101', '20121130', 'DESC'

-- find sql server database instance startup time
exec sp_readerrorlog 0,1,'Copyright (c)'
GO

exec xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 
GO

-- Reads current SQL Server error log with text 'Failed'
EXEC xp_ReadErrorLog 0, 1, 'Failed'

-- Reads current SQL Server error log with text Failed AND 'Login'
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login'


-- Reads current SQL Server error log with text Failed AND 'Login'
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login',null,null,'desc'



-- Reads current SQL Server error log with text Failed AND Login from 01-Nov-2012
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', NULL

-- Reads current SQL Server error log with text Failed AND Login between 01-Nov-2012 and 30-Nov-2012
EXEC xp_ReadErrorLog 0, 1, 'Failed', 'Login', '20121101', '20121130'

exec xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc' 
GO



top   prev   next  

Create Operator

USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name=N'JobAlerts', 
		@enabled=1, 
		@weekday_pager_start_time=0, 
		@weekday_pager_end_time=235900, 
		@saturday_pager_start_time=0, 
		@saturday_pager_end_time=235900, 
		@sunday_pager_start_time=0, 
		@sunday_pager_end_time=235900, 
		@pager_days=127, 
		@email_address=N'mydbateam@xxx.com;myithelpdesk@xxx.com', 
		@category_name=N'[Uncategorized]'
GO



top   prev   next  

DB Start Time

net statistics srv
net statistics srv|findstr /i since
hostname

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
go
select login_time from sys.dm_exec_sessions where session_id = 1;
go
select start_time from sys.traces where is_default = 1  
go 
SELECT crdate FROM sysdatabases WHERE name='tempdb'  
go  
SELECT create_date FROM sys.databases WHERE name = 'tempdb' 
go

ssms --> Management > SQL Server Logs

ssms --> Reports > Server Reports > Server Dashboard

-- backup and  restoration progress
SELECT db_name(database_id) db_name, percent_complete,estimated_completion_time FROM sys.dm_exec_requests;



top   prev   next  

Foreign Key Constraints

;
WITH    fk_cte
          AS ( SELECT   OBJECT_NAME(fk.referenced_object_id) pk_table ,
                        c2.name pk_column ,
                        kc.name pk_index_name ,
                        OBJECT_NAME(fk.parent_object_id) fk_table ,
                        c.name fk_column ,
                        fk.name fk_name ,
                        CASE WHEN i.object_id IS NOT NULL THEN 1 ELSE 0 END does_fk_has_index ,
                        i.is_primary_key is_fk_a_pk_also ,
                        i.is_unique is_index_on_fk_unique, 
						fk.parent_object_id
                        -- fk.*
               FROM     sys.foreign_keys fk
                        INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
                        INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
                        LEFT  JOIN sys.columns c2 ON c2.object_id = fk.referenced_object_id AND c2.column_id = fkc.referenced_column_id
                        LEFT JOIN sys.key_constraints kc ON kc.parent_object_id = fk.referenced_object_id AND kc.type = 'PK'
                        LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                        LEFT JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
             )
    SELECT  fk_cte.* FROM    fk_cte
       LEFT JOIN sys.dm_db_partition_stats ps on ps.object_id = fk_cte.parent_object_id and ps.index_id <= 1
    WHERE   does_fk_has_index = 0 -- and fk_table = 'LineItems'
    ORDER BY used_page_count desc;



top   prev   next  

Severity Alert

error and severity level 

SELECT * FROM master.sys.sysmessages ORDER BY severity;

SELECT * FROM master.sys.sysmessages where severity = 17 and msglangid=1033  ORDER BY severity, error;

1): 
msg 0, severity 17: Severity 17, Insufficient Resource
msg 35264, severity 0: AG Data Movement - Suspended
msg 35265, severity 0: AG Data Movement - Resumed
msg  1480, severity 0: AG Role Change

Severity Information:
0 Messages with Level 0 are purely informational. 
  A PRINT statement produces a message on severity level 0. 
  These messages do not set @@error. 
  Most query tools prints only the text part of a level 0 message.

1-9 These levels, too, are for informational messages/warnings. 
    Query Analyzer and SQL Management Studio prints the message number, 
    the level and the state, but not the procedure and line number for these messages.

10 This level does not really exist. 
   It appears that SQL Server internally converts level 10 to level 0, 
   both for its own messages when you use level 10 in RAISERROR.

11-16 These levels indicate a regular programming error of some sort. 
      But it is not the case that level 16 is more serious than level 11.
11  Specified Database Object Not Found
12  Unused
13  User Transaction Syntax Error
14  Insufficient Permission
15  Syntax Error in SQL Statements
16  Miscellaneous User Error

Deadlock, for instance is level 13

17-25 Messages with any of these severity levels indicate some sort of resource problem 
     (for instance running out of disk space), 
     or internal error in SQL Server, 
     or a problem with the operating system or hardware. 
     The higher the severity, the more serious problems.

19-25 To use level 19 or higher in RAISERROR you must use the WITH LOG option, and you must have sysadmin rights.

20-25 Errors with these severity levels are so fatal, that they always terminate the connection.



-- 1): alert
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity_17_Insufficient_Resource', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@notification_message=N'Insufficient  Resource (Disk Space)', 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'AG Data Movement - Resumed', 
		@message_id=35265, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

-- 2): Add Operator
-- Operator (Notification) will list all alerts in SSMS
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'OPERATOR_NAME', 
		@enabled=1, 
		@weekday_pager_start_time=0, 
		@weekday_pager_end_time=235900, 
		@saturday_pager_start_time=0, 
		@saturday_pager_end_time=235900, 
		@sunday_pager_start_time=0, 
		@sunday_pager_end_time=235900, 
		@pager_days=127, 
		@email_address=N'OPERATOR_NAME@ccc.com', 
		@category_name=N'[Uncategorized]'
GO

-- 3):
USE [msdb]
GO
EXEC msdb.dbo.sp_update_operator @name=N'OPERATOR_NAME', 
		@enabled=1, 
		@weekday_pager_start_time=0, 
		@weekday_pager_end_time=235900, 
		@saturday_pager_start_time=0, 
		@saturday_pager_end_time=235900, 
		@sunday_pager_start_time=0, 
		@sunday_pager_end_time=235900, 
		@pager_days=127, 
		@email_address=N'OPERATOR_NAME@ccc.com', 
		@pager_address=N'', 
		@netsend_address=N''
GO


-- EXEC msdb.dbo.sp_delete_notification @alert_name=N'Serverity_17_Insufficient_Resource', @operator_name=N'OPERATOR_NAME'
-- GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Serverity_17_Insufficient_Resource', @operator_name=N'OPERATOR_NAME', @notification_method = 1
GO



top   prev   next  

RLS: Row Level Security

1): step 1: filter function
CREATE FUNCTION RLSSecurityFilter.fn_LoginsDataFilter(@loginId AS varchar(20))  
RETURNS TABLE  
WITH SCHEMABINDING  
AS  
RETURN SELECT 1 AS result   
WHERE 
USER_NAME()='MANAGER'
OR
@loginId= USER_NAME()
GO

2): step 2: add policy
CREATE SECURITY POLICY [LoginDataFilterPolicy]   
ADD FILTER PREDICATE [rls].[fn_LoginsDataFilter]([loginId])   
ON [dbo].[logins];  

3): step 3: test
3a):
EXECUTE AS USER = 'JSmith';  
EXEC sp_set_session_context @key=N'loginId', @value='JSmith';  
SELECT * FROM logins;  
GO  

3b):
select USER_NAME()
select loginId, loginTime from dbo.logins;

Other:
SELECT USER_NAME(), CURRENT_USER

Row-Level Security (RLS)
Dynamic Data Masking (DDM)
Transparent Data Encryption (TDE)
Transport Layer Encryption (TLS)
SQL Server Audit
Temporal Tables
Always Encrypted (AE)
Authentication
Azure vault
Azure Active Directory
SQL Threat detection



top   prev   next  

Linked Server

select * from openquery(MY_LINKED_SERVERNAME, 'select * from myschema.mytable where rownum =1' );

select * from OPENROWSET('OraOLEDB.Oracle','srv';'user';'pass', 'select * from table');

sp_testlinkedserver [ @servername ] = servername  

error 7302 

OraOLEDB.Oracle for Linked server 7302
--
1): EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1

2a): 
Linked servers -- Providers -- OraOLEDB.Oracle -- Provider Options 1): Enable -- Allow inprocess

2b):
\Server Objects\Linked Servers\Providers, right click on the OraOLEDB.Oracle provider, 
select properties and check the "Allow inprocess" option. 
Recreate your linked server and test again.


Home  
top