SQL-common-scripts-for-administrator.html.raw

<p>The list below contains some scripts that is commonly used when I do monitoring / administering SQL server instances.</p><p>1/ To check if there is any blocking in the instance. It will returns the blocker and the time it blocks other commands</p><blockquote><table class="highlight tab-size js-file-line-container" data-tab-size="8" style="tab-size: 8; color: rgb(36, 41, 46); font-family: -apple-system, BlinkMacSystemFont, &quot;Segoe UI&quot;, Helvetica, Arial, sans-serif, &quot;Apple Color Emoji&quot;, &quot;Segoe UI Emoji&quot;, &quot;Segoe UI Symbol&quot;; font-size: 14px; background-color: rgb(255, 255, 255);"><tbody><tr><td id="file-sql-get-all-locks-sql-LC3" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;"><span class="pl-k" style="color: rgb(215, 58, 73);">select</span> <span class="pl-c1" style="color: rgb(0, 92, 197);">handle</span>.<span class="pl-c1" style="color: rgb(0, 92, 197);">text</span> <span class="pl-k" style="color: rgb(215, 58, 73);">as</span> sql, <span class="pl-k" style="color: rgb(215, 58, 73);">*</span> <span class="pl-k" style="color: rgb(215, 58, 73);">from</span></td></tr><tr><td id="file-sql-get-all-locks-sql-L4" class="blob-num js-line-number" data-line-number="4" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC4" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">(</td></tr><tr><td id="file-sql-get-all-locks-sql-L5" class="blob-num js-line-number" data-line-number="5" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC5" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">	<span class="pl-k" style="color: rgb(215, 58, 73);">select</span> DB_NAME(dbid) <span class="pl-k" style="color: rgb(215, 58, 73);">as</span> dbname, <span class="pl-k" style="color: rgb(215, 58, 73);">*</span></td></tr><tr><td id="file-sql-get-all-locks-sql-L6" class="blob-num js-line-number" data-line-number="6" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC6" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">	<span class="pl-k" style="color: rgb(215, 58, 73);">from</span> <span class="pl-c1" style="color: rgb(0, 92, 197);">master</span>.<span class="pl-c1" style="color: rgb(0, 92, 197);">dbo</span>.sysprocesses</td></tr><tr><td id="file-sql-get-all-locks-sql-L7" class="blob-num js-line-number" data-line-number="7" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC7" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">	<span class="pl-k" style="color: rgb(215, 58, 73);">where</span> blocked <span class="pl-k" style="color: rgb(215, 58, 73);">&lt;&gt;</span> <span class="pl-c1" style="color: rgb(0, 92, 197);">0</span></td></tr><tr><td id="file-sql-get-all-locks-sql-L8" class="blob-num js-line-number" data-line-number="8" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC8" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">
</td></tr><tr><td id="file-sql-get-all-locks-sql-L9" class="blob-num js-line-number" data-line-number="9" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC9" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">	<span class="pl-k" style="color: rgb(215, 58, 73);">union</span></td></tr><tr><td id="file-sql-get-all-locks-sql-L10" class="blob-num js-line-number" data-line-number="10" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC10" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">
</td></tr><tr><td id="file-sql-get-all-locks-sql-L11" class="blob-num js-line-number" data-line-number="11" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC11" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">	<span class="pl-k" style="color: rgb(215, 58, 73);">select</span> DB_NAME(dbid) <span class="pl-k" style="color: rgb(215, 58, 73);">as</span> dbname, <span class="pl-k" style="color: rgb(215, 58, 73);">*</span></td></tr><tr><td id="file-sql-get-all-locks-sql-L12" class="blob-num js-line-number" data-line-number="12" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC12" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">	<span class="pl-k" style="color: rgb(215, 58, 73);">from</span> <span class="pl-c1" style="color: rgb(0, 92, 197);">master</span>.<span class="pl-c1" style="color: rgb(0, 92, 197);">dbo</span>.sysprocesses</td></tr><tr><td id="file-sql-get-all-locks-sql-L13" class="blob-num js-line-number" data-line-number="13" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC13" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">	<span class="pl-k" style="color: rgb(215, 58, 73);">where</span> spid <span class="pl-k" style="color: rgb(215, 58, 73);">in</span> (</td></tr><tr><td id="file-sql-get-all-locks-sql-L14" class="blob-num js-line-number" data-line-number="14" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC14" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">		<span class="pl-k" style="color: rgb(215, 58, 73);">select</span> blocked</td></tr><tr><td id="file-sql-get-all-locks-sql-L15" class="blob-num js-line-number" data-line-number="15" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC15" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">		<span class="pl-k" style="color: rgb(215, 58, 73);">from</span> <span class="pl-c1" style="color: rgb(0, 92, 197);">master</span>.<span class="pl-c1" style="color: rgb(0, 92, 197);">dbo</span>.sysprocesses</td></tr><tr><td id="file-sql-get-all-locks-sql-L16" class="blob-num js-line-number" data-line-number="16" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC16" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">		<span class="pl-k" style="color: rgb(215, 58, 73);">where</span> blocked <span class="pl-k" style="color: rgb(215, 58, 73);">&lt;&gt;</span> <span class="pl-c1" style="color: rgb(0, 92, 197);">0</span></td></tr><tr><td id="file-sql-get-all-locks-sql-L17" class="blob-num js-line-number" data-line-number="17" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC17" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">	)</td></tr><tr><td id="file-sql-get-all-locks-sql-L18" class="blob-num js-line-number" data-line-number="18" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC18" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">) p</td></tr><tr><td id="file-sql-get-all-locks-sql-L19" class="blob-num js-line-number" data-line-number="19" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC19" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;">CROSS APPLY <span class="pl-c1" style="color: rgb(0, 92, 197);">sys</span>.<span class="pl-c1" style="color: rgb(0, 92, 197);">dm_exec_sql_text</span>(<span class="pl-c1" style="color: rgb(0, 92, 197);">p</span>.<span class="pl-c1" style="color: rgb(0, 92, 197);">sql_handle</span>) <span class="pl-k" style="color: rgb(215, 58, 73);">AS</span> handle</td></tr><tr><td id="file-sql-get-all-locks-sql-L20" class="blob-num js-line-number" data-line-number="20" style="padding-right: 10px; padding-left: 10px; color: rgba(27, 31, 35, 0.3); cursor: pointer; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; line-height: 20px; min-width: 50px; text-align: right; user-select: none; vertical-align: top; white-space: nowrap; width: 50px;"></td><td id="file-sql-get-all-locks-sql-LC20" class="blob-code blob-code-inner js-file-line" style="padding-right: 10px; padding-left: 10px; line-height: 20px; position: relative; vertical-align: top; font-family: SFMono-Regular, Consolas, &quot;Liberation Mono&quot;, Menlo, Courier, monospace; font-size: 12px; overflow: visible; white-space: pre; overflow-wrap: normal;"><span class="pl-k" style="color: rgb(215, 58, 73);">order by</span> <span class="pl-c1" style="color: rgb(0, 92, 197);">p</span>.<span class="pl-c1" style="color: rgb(0, 92, 197);">waittime</span> <span class="pl-k" style="color: rgb(215, 58, 73);">desc</span></td></tr></tbody></table></blockquote><p>2/ Check the started time of a job</p><p><br></p><p><br></p>