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" } } |