clear
#Install-Module ImportExcel
#output file location: c:\powershell\Report_PROD_.xlsx
#Input File C:\powershell\sqlservers.txt
function run_tsqlscript {
[CmdletBinding()]
param (
[parameter(Position=0,Mandatory=$true,HelpMessage="Provide SQL Script to be executed")]
$SQL)
$outputarray = @();
$objectarray = @();
$objectarray = Import-Csv C:\powershell\sqlservers.txt
foreach($object in $objectarray)
{
[Array]$outputarrary += Invoke-Sqlcmd -Query $SQL -ServerInstance $object.Servername
}
$outputarrary | Export-Excel -Path c:\powershell\Report_PROD_.xlsx -AutoSize -ClearSheet
}
run_tsqlscript
--Script:1 Data and Log File usage
--Run the below code to see available space in data and log files.
--You can use the following script as an input to powershell and create report for all your sql instances.
SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
Declare @dbname varchar(max)
DECLARE @command nvarchar(4000);
DECLARE db_names CURSOR FOR SELECT name FROM sys.databases;
open db_names;
fetch next from db_names into @dbname;
while @@FETCH_STATUS = 0
Begin
Set @command = N'Use [' + @dbname +'];
declare @sqlinstancename varchar(max)
declare @sqldbname varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max),
sqldbname varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('''+'MachineName'+'''))
select @sqldbname='''+@dbname+'''
insert into @servername (sqlinstancename,sqldbname)
select @sqlinstancename, @sqldbname;
SELECT SQLServerVersion.sqlinstancename as [SQLInstanceName], SQLServerVersion.sqldbname as [SQLDatabaseName], dbfilegp.name as [FileGroupNmae],
dbfile.name AS [File Name] , physical_name AS [Physical Name], size/128000.0 AS [Total Size in GB],
size/128000.0 - CAST(FILEPROPERTY(dbfile.name, '''+'spaceused'+''') AS int)/128000.0 AS [Available Space In GB], Growth/128 as [AutoGrowth_MB],
is_percent_growth as [Percentage_Growth], dbfilegp.is_default as [Default_FileGroup]
FROM sys.database_files as [dbfile]
WITH (NOLOCK)
CROSS APPLY @servername as SQLServerVersion
full join sys.filegroups as [dbfilegp]
on dbfilegp.data_space_id=dbfile.data_space_id';
fetch next from db_names into @dbname
execute (@command);
--print N'Executed: ' + @command
End
close db_names;
deallocate db_names;
Go
--Script:2 Data and Log Files with Auto_Growth not enabled
--You can use the following script as an input to powershell and create report for all your sql instances.
SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
Declare @dbname varchar(max)
DECLARE @command nvarchar(4000);
DECLARE db_names CURSOR FOR SELECT name FROM sys.databases;
open db_names;
fetch next from db_names into @dbname;
while @@FETCH_STATUS = 0
Begin
Set @command = N'Use [' + @dbname +'];
declare @sqlinstancename varchar(max)
declare @sqldbname varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max),
sqldbname varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('''+'MachineName'+'''))
select @sqldbname='''+@dbname+'''
insert into @servername (sqlinstancename,sqldbname)
select @sqlinstancename, @sqldbname;
SELECT SQLServerVersion.sqlinstancename as [SQLInstanceName], SQLServerVersion.sqldbname as [SQLDatabaseName], dbfilegp.name as [FileGroupNmae],
dbfile.name AS [File Name] , physical_name AS [Physical Name], size/128000.0 AS [Total Size in GB],
size/128000.0 - CAST(FILEPROPERTY(dbfile.name, '''+'spaceused'+''') AS int)/128000.0 AS [Available Space In GB], Growth/128 as [AutoGrowth_MB],
is_percent_growth as [Percentage_Growth], dbfilegp.is_default as [Default_FileGroup]
FROM sys.database_files as [dbfile]
WITH (NOLOCK)
CROSS APPLY @servername as SQLServerVersion
full join sys.filegroups as [dbfilegp]
on dbfilegp.data_space_id=dbfile.data_space_id
where is_percent_growth = 0 and Growth = 0';
fetch next from db_names into @dbname
execute (@command);
--print N'Executed: ' + @command
End
close db_names;
deallocate db_names;
Go
--Script:3 Verify SQL Server ErrorLogs
--You can use the following script as an input to powershell and create report for all your sql instances.
print ' '
print ' '
print '==================='
print '== SQL Errorlogs =='
print '==================='
print ' '
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
declare
@FirstLog smallint,
@LastLog smallint,
@BufferRecords smallint,
@SQL varchar(2000),
@Output varchar(2000)
select
@FirstLog = 0, --> Defaults to current logfile
@LastLog = 0, --> Defaults to logfile immediately preceding current
@BufferRecords = 4 --> Used to give a frame of reference to the error message
---------------------------------------------------------------------------------------------------
-- Date Created: July 11, 2005
-- Description: This procedure combines and parses the SQL Error Logs. Entries of special
-- interest are marked which allows the operator to quickly scan the output for
-- errors. Entries of interest are removed from the output. Entries before and
-- after the errors are included to give a frame of reference.
---------------------------------------------------------------------------------------------------
-- Date Revised: December 7, 2006
-- Reason: I converted this procedure to support SQL Server 2005
---------------------------------------------------------------------------------------------------
-- Date Revised: October 10, 2019
-- Reason: Updated to add SQL Instance name to output column, so it can be run using PowerShell
---------------------------------------------------------------------------------------------------
set nocount on
declare @count smallint,
@alert char(5)
select @count = 0,
@SQL = '',
@output = '',
@alert = '---->'
---------------------------------------------------------------------
-- Validate input parameters --
---------------------------------------------------------------------
IF (@FirstLog > @LastLog)
BEGIN
select @lastLog = @FirstLog + 1
END
---------------------------------------------------------------------
-- M A I N P R O C E S S I N G --
---------------------------------------------------------------------
-- --
-- Create work tables --
-- --
-- Import SQL Error Logs --
-- --
-- Remove unwanted entries --
-- --
-- Mark items of interest --
-- --
-- Generate report --
-- --
---------------------------------------------------------------------
---------------------------------------------------------------------
-- Create work tables --
---------------------------------------------------------------------
--IF (object_id('tempdb..#ERRORLOG') IS NOT NULL)
-- drop table #ERRORLOG
CREATE TABLE #ERRORLOG
(
LogID int identity primary key clustered,
LogDate datetime NULL,
ProcessInfo varchar(12) NULL,
Alert char(5) default ' ',
LogEntry varchar(900) NULL,
Row smallint NULL
)
-- Add indexes
create index [IX_ERRORLOG_Alert] on dbo.[#ERRORLOG](Alert) with fillfactor = 98 on [PRIMARY]
create index [IX_ERRORLOG_LogEntry] on dbo.[#ERRORLOG](LogEntry) with fillfactor = 98 on [PRIMARY]
---------------------------------------------------------------------
-- Import SQL Error Logs --
---------------------------------------------------------------------
select @count = @FirstLog
WHILE (@Count <= @LastLog)
BEGIN
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')
select @output = '-- Processing: ERRORLOG' + case(@count) when 0 then ' ' else '.' + convert(char(3), @count) end + ' --'
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',@output)
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','','------------------------------')
insert into #ERRORLOG (ProcessInfo, Alert, LogEntry) values (' ','',' ')
select @SQL = 'exec master..sp_readerrorlog ' + convert(varchar(3), @count)
insert into #errorlog (LogDate, ProcessInfo, LogEntry)
execute (@SQL)
select @count = @count + 1
END
---------------------------------------------------------------------
-- Remove unwanted entries --
---------------------------------------------------------------------
delete
from #ERRORLOG
where LogEntry like '%Bypassing recovery for database%'
or LogEntry like '%Setting database option ANSI_WARNINGS%'
or LogEntry like '%Log backed up with following information:%'
or LogEntry like '%Login succeeded for user%'
or LogEntry like '%Database differential changes%'
or LogEntry like '%The certificate was successfully loaded%'
or LogEntry like '%this is an informational message only%'
or LogEntry like '%logging sql server messages in file%'
or LogEntry like '%Recovery complete.%'
or LogEntry like '%Starting up database ''ERROR_LOGGING''%'
or LogEntry like '%Database backed up: Database: %'
or LogEntry like '%Log backed up: Database: %'
or LogEntry like '%allocate%'
---------------------------------------------------------------------
-- Mark items of interest --
---------------------------------------------------------------------
update #ERRORLOG
set Alert = @alert
where (LogEntry like '%err%'
or LogEntry like '%exception%'
or LogEntry like '%violation%'
or LogEntry like '%warn%'
or LogEntry like '%kill%'
or LogEntry like '%dead%'
or LogEntry like '%encounter%'
or LogEntry like '%cannot%'
or LogEntry like '%could%'
or LogEntry like '%fail%'
or LogEntry like '%full%'
or LogEntry like '%not%'
or LogEntry like '%terminate%'
or LogEntry like '%bypass%'
or LogEntry like '%recover%'
or LogEntry like '%roll%'
or LogEntry like '%upgrade%'
or LogEntry like '%victim%'
or LogEntry like '%stop%'
or LogEntry like '%shut%'
or LogEntry like '%timed out%'
or LogEntry like '%truncate%'
or LogEntry like '%terminat%')
and (ProcessInfo <> ' ' or ProcessInfo IS NULL)
---------------------------------------------------------------------
-- Generate report --
---------------------------------------------------------------------
BEGIN
-- Show entries of interest + buffer records
select SQLServerVersion.sqlinstancename as [SQLInstanceName],
A.LogID,
'Date' = isnull(convert(varchar(19), A.LogDate, 120), ''),
A.ProcessInfo,
A.Alert,
'Descripton' = left(A.logEntry, 250)
from #ERRORLOG A
join (select LogID from #ERRORLOG where Alert = @alert) B on (A.LogID >= (B.LogID - @BufferRecords))
and (A.LogID <= (B.LogID + @BufferRecords))
CROSS APPLY @servername as SQLServerVersion
-- Show informational records
union
select SQLServerVersion.sqlinstancename as [SQLInstanceName],
A.LogID,
'Date' = isnull(convert(varchar(19), A.LogDate, 120), ''),
A.ProcessInfo,
A.Alert,
'Descripton' = left(A.logEntry, 250)
from #ERRORLOG A
full join @servername as SQLServerVersion
on a.ProcessInfo <> SQLServerVersion.sqlinstancename
where ProcessInfo = ''
order by A.LogID
END
Drop TABLE #ERRORLOG
--Script:4 Find Lead Blocker
--You can use the following script as an input to powershell and create report for all your sql instances.
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
SELECT
SQLServerName.sqlinstancename as [SQLInstanceName],
spid
,sp.STATUS
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(CHAR(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(sp.dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
CROSS APPLY @servername as SQLServerName
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
--Script:5 Find Queries Running in your SQL Server, for SQL Instances 2005 and above
--You can use the following script as an input to powershell and create report for all your sql instances.
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
select SQLServerName.sqlinstancename as [SQLInstanceName], DB_NAME(pr.dbid) as [DB_Name], pr.spid as [SPID], pr.status as [Status], pr.cmd as [CMD],
pr.blocked as [Blocked_By], pr.waittime as [WaitTime], pr.lastwaittype as [LastWaitType],
pl.query_plan as [Query_Plan], t.text as [Query_Text], pr.cpu as [CPU], pr.physical_io as [Physical_IO], pr.memusage as [Memory_Usage], pr.login_time as [Login_Time],
pr.last_batch as [Last_Batch], pr.open_tran as [Open_Tran], pr.hostname as [HostName], pr.program_name as [Program_Name],
pr.loginame as [Login_Name], pr.net_library as [Net_Library], pr.nt_domain as [Net_Domain], pr.nt_username as [NT_UserName], pr.hostprocess as [HostProcess]
from sys.sysprocesses pr
join sys.dm_exec_requests er on pr.spid = er.session_id
CROSS APPLY @servername as SQLServerName
cross apply sys.dm_exec_sql_text(er.plan_handle) t
cross apply sys.dm_exec_query_plan(er.plan_handle) pl
where pr.status IN ('suspended', 'runnable', 'sleeping') and pr.spid >10 and pr.hostname <> 'VYNIT01'
and er.session_id <> @@SPID
order by pr.blocked desc
--Script:6 Find SQL Server Wait Types
--You can use the following script as an input to powershell and create report for all your sql instances.
Declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
Insert into @servername (sqlinstancename)
select @sqlinstancename;
select TOP 5 SQLServerName.sqlinstancename as [SQLInstanceName], wst.wait_type, wst.waiting_tasks_count,
wst.wait_time_ms, wst.max_wait_time_ms, wst.signal_wait_time_ms, str((( 100.0 * [wait_time_ms]) / SUM ([wait_time_ms]) OVER()), 5, 2) AS [Percentage]
from sys.dm_os_wait_stats wst
CROSS APPLY @servername as SQLServerName
where wait_type not in
(select wait_type
from sys.dm_os_waiting_tasks wt
join sys.dm_exec_sessions s on s.session_id = wt.session_id
where is_user_process = 0)
and [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
N'CHKPT', N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC', N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',N'XE_LIVE_TARGET_TVF',
N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
order by wait_time_ms desc
--Script:7 SQL Server Last Full Backup, time within 7 days
--You can use the following script as an input to powershell and create report for all your sql instances.
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
select distinct SQLServerName.sqlinstancename as [SQLInstanceName], sdb.name as [DatabaseName], bus.backup_start_date as [Full_DB_Backup_Start], bus.backup_finish_date as [Full_DB_Backup_Finish]
from sys.databases sdb
left outer join msdb.dbo.backupset bus on sdb.name=bus.database_name
CROSS APPLY @servername as SQLServerName
where sdb.name <> 'tempdb' and bus.type='D' and bus.backup_finish_date > getdate () -7
group by SQLServerName.sqlinstancename, sdb.name, bus.backup_start_date, bus.backup_finish_date
order by bus.backup_finish_date desc
--Script:8 SQL Server latest log Backup, time within 24 hours
--You can use the following script as an input to powershell and create report for all your sql instances.
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
select distinct SQLServerName.sqlinstancename as [SQLInstanceName], sdb.name as [DatabaseName], bus.backup_start_date as [Log_DB_Backup_Start], bus.backup_finish_date as [Log_DB_Backup_Finish]
from sys.databases sdb
left outer join msdb.dbo.backupset bus on sdb.name=bus.database_name
CROSS APPLY @servername as SQLServerName
where sdb.name <> 'tempdb' and bus.type='L' and bus.backup_finish_date > getdate () -1 and sdb.recovery_model_desc='FULL'
group by SQLServerName.sqlinstancename, sdb.name, bus.backup_start_date, bus.backup_finish_date
order by bus.backup_finish_date desc
--Script:9 SQL Server latest Differentical Backup, time within 7 days
--You can use the following script as an input to powershell and create report for all your sql instances.
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
select distinct SQLServerName.sqlinstancename as [SQLInstanceName], sdb.name as [DatabaseName], bus.backup_start_date as [Differential_DB_Backup_Start], bus.backup_finish_date as [Differential_DB_Backup_Finish]
from sys.databases sdb
left outer join msdb.dbo.backupset bus on sdb.name=bus.database_name
CROSS APPLY @servername as SQLServerName
where sdb.name <> 'tempdb' and bus.type='I' and bus.backup_finish_date > getdate () -7
group by SQLServerName.sqlinstancename, sdb.name, bus.backup_start_date, bus.backup_finish_date
order by bus.backup_finish_date desc
--Script:10 SQL Server Job History
--You can use the following script as an input to powershell and create report for all your sql instances.
select distinct jh.server as [SQLInstanceName],
j.Name as "Job Name", --j.job_id,
case j.enabled
when 1 then 'Enable'
when 0 then 'Disable'
end as "Job Status", jh.run_date as [Last_Run_Date(YY-MM-DD)] ,
case jh.run_status
when 0 then 'Failed'
when 1 then 'Successful'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as Job_Execution_Status
from msdb.dbo.sysJobHistory jh, msdb.dbo.sysJobs j
where j.job_id = jh.job_id and jh.run_date =
(select max(hi.run_date) from msdb.dbo.sysJobHistory hi where jh.job_id = hi.job_id )-- to get latest date
--Script:11 SQL Server data and log file IO Stall, since last sql server reboot
--You can use the following script as an input to powershell and create report for all your sql instances.
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
SELECT SQLServerName.sqlinstancename as [SQLInstanceName], DB_NAME(fs.database_id) AS [Database Name], mf.physical_name, mf.state_desc, io_stall_read_ms, num_of_reads,
CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null) AS fs
INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
CROSS APPLY @servername as SQLServerName
ORDER BY avg_io_stall_ms DESC OPTION (RECOMPILE);
--Script:12 sql server UPDATESTATS info.
--You can use the following script as an input to powershell and create report for all your sql instances.
SET ARITHABORT ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
Declare @dbname varchar(max)
DECLARE @command nvarchar(4000);
DECLARE db_names CURSOR FOR SELECT name FROM sys.databases;
open db_names;
fetch next from db_names into @dbname;
while @@FETCH_STATUS = 0
Begin
Set @command = N'Use [' + @dbname +'];
declare @sqlinstancename varchar(max)
declare @sqldbname varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max),
sqldbname varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('''+'MachineName'+'''))
select @sqldbname='''+@dbname+'''
insert into @servername (sqlinstancename,sqldbname)
select @sqlinstancename, @sqldbname;
SELECT SQLServerName.sqlinstancename as [SQLInstanceName], SQLServerName.sqldbname as [SQLDatabaseName],
obj.name AS ObjectName, obj.object_id, stat.name AS StatisticsName, stat.stats_id, last_updated, modification_counter
FROM sys.objects AS obj
JOIN sys.stats AS stat
ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
CROSS APPLY @servername as SQLServerName
WHERE obj.type='''+'U'+''' and sp.modification_counter>1000
order by modification_counter desc';
fetch next from db_names into @dbname
execute (@command);
--print N'Executed: ' + @command
End
close db_names;
deallocate db_names;
Go
--Script:14 Find Queries Running in your SQL Server, for SQL Instances 2016 SP1 and above
--enable query profiling globally under SQL Server 2016 SP1 or above (not needed in SQL Server 2019)
--DBCC TRACEON (7412, -1);
--You can use the following script as an input to powershell and create report for all your sql instances.
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
select SQLServerName.sqlinstancename as [SQLInstanceName], DB_NAME(pr.dbid) as [DB_Name], pr.spid as [SPID], pr.status as [Status], pr.cmd as [CMD],
pr.blocked as [Blocked_By], pr.waittime as [WaitTime], pr.lastwaittype as [LastWaitType],
pl.query_plan as [Query_Plan], t.text as [Query_Text],pr.cpu as [CPU], pr.physical_io as [Physical_IO], pr.memusage as [Memory_Usage], pr.login_time as [Login_Time],
pr.last_batch as [Last_Batch], po.physical_operator_name, po.row_count, po.estimate_row_count, pr.open_tran as [Open_Tran], pr.hostname as [HostName],pr.program_name as [Program_Name],
pr.loginame as [Login_Name], pr.net_library as [Net_Library], pr.nt_domain as [Net_Domain], pr.nt_username as [NT_UserName], pr.hostprocess as [HostProcess]
from sys.sysprocesses pr
join sys.dm_exec_requests er on pr.spid = er.session_id
join sys.dm_exec_query_profiles as po on po.session_id = pr.spid
cross apply @servername as SQLServerName
cross apply sys.dm_exec_sql_text(er.plan_handle) t
cross apply sys.dm_exec_query_plan(er.plan_handle) pl
where pr.status IN ('suspended', 'runnable', 'sleeping') and pr.spid >10 and pr.hostname <> 'VYNIT01'
and er.session_id <> @@SPID
order by pr.blocked desc
--Script:15 Find Queries Running in your SQL Server, for SQL Instances 2019 and above
--Alter database scoped configuration set last_query_plan_stats = ON
--You can use the following script as an input to powershell and create report for all your sql instances.
declare @sqlinstancename varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('MachineName'))
insert into @servername (sqlinstancename)
select @sqlinstancename;
select SQLServerName.sqlinstancename as [SQLInstanceName], DB_NAME(pr.dbid) as [DB_Name], pr.spid as [SPID], pr.status as [Status], pr.cmd as [CMD],
pr.blocked as [Blocked_By], pr.waittime as [WaitTime], pr.lastwaittype as [LastWaitType],
pl.query_plan as [Current_Plan], t2.query_plan as [Prev_Actual_Plan], t.text as [Query_Text],pr.cpu as [CPU], pr.physical_io as [Physical_IO], pr.memusage as [Memory_Usage], pr.login_time as [Login_Time],
pr.last_batch as [Last_Batch], po.physical_operator_name, po.row_count, po.estimate_row_count, pr.open_tran as [Open_Tran], pr.hostname as [HostName],pr.program_name as [Program_Name],
pr.loginame as [Login_Name], pr.net_library as [Net_Library], pr.nt_domain as [Net_Domain], pr.nt_username as [NT_UserName], pr.hostprocess as [HostProcess]
from sys.sysprocesses pr
join sys.dm_exec_requests er on pr.spid = er.session_id
join sys.dm_exec_query_profiles as po on po.session_id = pr.spid
CROSS APPLY @servername as SQLServerName
cross apply sys.dm_exec_sql_text(er.plan_handle) t
cross apply sys.dm_exec_query_plan_stats(er.plan_handle) as t2
cross apply sys.dm_exec_query_plan(er.plan_handle) pl
where pr.status IN ('suspended', 'runnable', 'sleeping') and pr.spid >10 and pr.hostname <> 'VYNIT01'
and er.session_id <> @@SPID
order by pr.blocked desc
--Script:16 SQL Server 2019 and Above. Find DB and Object name having Page_latch Wait type.
--You can use the following script as an input to powershell and create report for all your sql instances.
SET ANSI_NULLS ON;
SET ARITHABORT ON;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
Declare @dbname varchar(max)
DECLARE @command nvarchar(4000);
DECLARE db_names CURSOR FOR SELECT name FROM sys.databases;
open db_names;
fetch next from db_names into @dbname;
while @@FETCH_STATUS = 0
Begin
Set @command = N'Use [' + @dbname +'];
declare @sqlinstancename varchar(max)
declare @sqldbname varchar(max)
Declare @servername Table
(
sqlinstancename varchar(max),
sqldbname varchar(max)
)
SELECT @sqlinstancename=convert(varchar(max),SERVERPROPERTY('''+'MachineName'+'''))
select @sqldbname='''+@dbname+'''
insert into @servername (sqlinstancename,sqldbname)
select @sqlinstancename, @sqldbname;
select SQLServerVersion.sqlinstancename as [SQLInstanceName], SQLServerVersion.sqldbname as [SQLDatabaseName],
object_name(page_info.object_id) AS [OBJECT_NAME], page_info.*
from sys.dm_exec_requests as d
cross apply sys.fn_PageResCracker(d.page_resource) as r
cross apply sys.dm_db_page_info(r.db_id, r.file_id, r.page_id, '''+'DETAILED'+''') as page_info
cross apply @servername as SQLServerVersion';
fetch next from db_names into @dbname
execute (@command);
--print N'Executed: ' + @command
End
close db_names;
deallocate db_names;
Go
Comments