When-WillSQLComplete

1.0

When-WillSQLComplete.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
 <#PSScriptInfo
 
.VERSION 1.0
 
.GUID e113cd95-5822-4114-89c8-8293422b655a
 
.AUTHOR Rob Sewell
 
.DESCRIPTION Runs some t-sql to gather some information about requests from the sys.dm_exec_requests dmv to estimate the amount of time remaining for a statement which can be filtered by BACKUP,RESTORE,INDEX,DBCC,STATS commands
       
.COMPANYNAME
 
.COPYRIGHT
 
.TAGS SQL, Queries, Commands, Time, When Will SQL Complete
 
.LICENSEURI
 
.PROJECTURI
 
.ICONURI
 
.EXTERNALMODULEDEPENDENCIES
 
.REQUIREDSCRIPTS
 
.EXTERNALSCRIPTDEPENDENCIES
 
.RELEASENOTES
 
#>
<#
.Synopsis
A quick function to estimate the completion time of a SQL Statement
.DESCRIPTION
Runs some t-sql to gather some information about requests from the sys.dm_exec_requests dmv to estimate the
amount of time remaining for a statement which can be filtered by BACKUP,RESTORE,INDEX,DBCC,STATS commands
 
.PARAMETER Server
The SQL Server to query
 
.PARAMETER CommandType
 
The type of command to filter for Backup, Restore, Index,DBCC,Stats
.PARAMETER OGV
 
Sends Results to Out-GridView
 
.EXAMPLE
When-WillThisSQLComplete -Server Fade2black
 
Returns SPID, Login Name,Domain, NTUserName,Database, %,START_TIME,STATUS,COMMAND,EST_COMP for all processes on Fade2Black
.EXAMPLE
When-WillThisSQLComplete -Server SQLServer1 -Commandtype Backup
 
Returns SPID, Login Name,Domain, NTUserName,Database, %,START_TIME,STATUS,COMMAND,EST_COMP for all processes where the command
includes Backup on SQLServer1
 
.EXAMPLE
When-WillThisSQLComplete -Server Fade2black -OGV
 
Returns SPID, Login Name,Domain, NTUserName,Database, %,START_TIME,STATUS,COMMAND,EST_COMP for all processes on Fade2Black
using Out-GridView
.NOTES
AUTHOR : Rob Sewell http://sqldbawithabeard.com
 
#>
function When-WillSQLComplete
{
param([string]$Server,
[ValidateSet("Backup", "Restore", "Index","DBCC","Stats")]
[string]$Commandtype,
[switch]$OGV
)
$BaseQuery = @"
USE MASTER
GO
SELECT
    DER.SESSION_ID as SPID,
    RTRIM(SP.Loginame) as 'Login Name',
    RTRIM(SP.nt_domain) as Domain,
    RTRIM(SP.nt_username) as NTUserName,
    '[' + CAST(DER.DATABASE_ID AS VARCHAR(10)) + '] ' + DB_NAME(DER.DATABASE_ID) AS [Database],
    DER.PERCENT_COMPLETE as '%', DER.START_TIME, DER.STATUS, DER.COMMAND,
    DATEADD(MS, DER.ESTIMATED_COMPLETION_TIME, GETDATE()) AS EST_COMP,
    DER.CPU_TIME
FROM SYS.DM_EXEC_REQUESTS DER
left join
sys.sysprocesses SP
on DER.Session_id = SP.spid
--Apply this Where Clause Filter if you need to check specific events
--such as Backups, Restores, Index et al.
 
"@
$BackupCMD = @"
WHERE COMMAND LIKE '%BACKUP%'
"@
$RestoreCMD = @"
WHERE COMMAND LIKE '%RESTORE%'
"@
$IndexCMD = @"
WHERE COMMAND LIKE '%INDEX%'
"@
$DBCCCMD = @"
WHERE COMMAND LIKE '%DBCC%'
"@
$StatsCMD = @"
WHERE COMMAND LIKE 'UPDATE STAT%'
"@
switch ($Commandtype)
{
Backup   {$query = $BaseQuery + $BackupCMD }
Restore  {$query = $BaseQuery + $RestoreCMD}
Index    {$query = $BaseQuery + $IndexCMD}
DBCC     {$query = $BaseQuery + $DBCCCMD}
Stats    {$query = $BaseQuery + $StatsCMD}
default  {$query = $BaseQuery }
}
try
{
$results = Invoke-Sqlcmd -ServerInstance $Server -Database master -Query $query
}
catch
{
Write-Warning "FAILED to gather information from $Server"
}
If($results)
{
if($OGV)
{
$results | Out-GridView
}
else
{
$results | Format-Table -AutoSize -Wrap
}
}
else
{
Write-Output "There were no results for the $Commandtype queries on $Server"
}
}