tag:blogger.com,1999:blog-9256553942007395442024-03-13T05:57:10.943-07:00SQLismLogging and Blogging SQL Server Experiences.SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.comBlogger110125tag:blogger.com,1999:blog-925655394200739544.post-47217864049879205632020-07-16T11:44:00.005-07:002020-07-16T11:45:47.911-07:00Reorganize all indexes in a Database in SQL Server.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; box-sizing: border-box; color: #212121; font-family: "open sans", sans-serif; font-size: 16px;">
<span style="box-sizing: border-box; color: #333333; font-size: 15px;">Index Maintenance is one of the major task for a DBA. If rate of Index fragmentation increased then index de-fragmentation is become required. So we can use index reorganize option to rebuild all the indexes. Use Index reorganize option when rate of index fragmentation is b/w 10% to 40%.</span></div>
<div style="background-color: white; box-sizing: border-box; color: #212121; font-family: "open sans", sans-serif; font-size: 16px;">
</div>
<div style="background-color: white; box-sizing: border-box; color: #212121; font-family: "open sans", sans-serif; font-size: 16px;">
<div style="box-sizing: border-box; color: #333333; font-size: 15px; outline: 0px;">
<strong style="box-sizing: border-box; outline: 0px;">Syntax:</strong></div>
<div style="box-sizing: border-box;">
</div>
<div style="box-sizing: border-box; color: #333333; font-size: 15px; outline: 0px;">
</div>
<div style="box-sizing: border-box; color: #333333; font-size: 15px; outline: 0px;">
Alter Index All On Table_Name Reorganize.</div>
<div style="box-sizing: border-box;">
</div>
<div style="box-sizing: border-box; color: #333333; font-size: 15px; outline: 0px;">
<strong style="box-sizing: border-box; outline: 0px;"></strong></div>
<div style="box-sizing: border-box; color: #333333; font-size: 15px; outline: 0px;">
<strong style="box-sizing: border-box; outline: 0px;">Example</strong>:</div>
<div style="box-sizing: border-box;">
</div>
<div style="box-sizing: border-box; color: #333333; font-size: 15px; outline: 0px;">
</div>
<div style="box-sizing: border-box; color: #333333; font-size: 15px; outline: 0px;">
<div id="pastingspan1" style="box-sizing: border-box; font-size: 16px !important; outline: 0px;">
ALTER INDEX ALL ON geographicals Reorganize</div>
<div id="pastingspan1" style="box-sizing: border-box; font-size: 16px !important; outline: 0px;">
GO</div>
<div style="box-sizing: border-box; font-size: 16px !important;">
</div>
<div style="box-sizing: border-box; font-size: 16px !important;">
<img alt="" class="" data-src="/UploadFile/BlogImages/07112016015725AM/32 - Copy.PNG" src="https://www.c-sharpcorner.com/UploadFile/BlogImages/07112016015725AM/32%20-%20Copy.PNG" style="box-sizing: border-box; max-width: 100%;" /> </div>
<div style="box-sizing: border-box; font-size: 16px !important;">
<div style="box-sizing: border-box; outline: 0px;">
<strong style="box-sizing: border-box; outline: 0px;">Rebuild Index for All tablex in database:</strong></div>
<div style="box-sizing: border-box;">
</div>
<div style="box-sizing: border-box; outline: 0px;">
</div>
<div style="box-sizing: border-box; font-family: "Open Sans", sans-serif; outline: 0px;">
<div id="pastingspan1" style="box-sizing: border-box; outline: 0px;">
Exec sp_msforeachtable 'ALTER INDEX ALL ON ? <span style="box-sizing: border-box; font-family: "roboto" , sans-serif;">Reorganize</span>'</div>
<div id="pastingspan1" style="box-sizing: border-box; outline: 0px;">
GO</div>
<div id="pastingspan1" style="box-sizing: border-box; outline: 0px;">
<br /></div>
<div id="pastingspan1" style="box-sizing: border-box; outline: 0px;">
<br /></div>
<div id="pastingspan1" style="box-sizing: border-box; outline: 0px;">
<br /></div>
<div id="pastingspan1" style="box-sizing: border-box; outline: 0px;">
<br /></div>
<div id="pastingspan1" style="box-sizing: border-box; outline: 0px;">
<a href="https://www.c-sharpcorner.com/blogs/reorganize-all-index-in-sql-server">Reference</a>: </div>
</div>
</div>
</div>
</div>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-13994308049940061452020-03-02T14:48:00.005-08:002020-03-02T14:50:08.785-08:00SQL SERVER – Drop All the Foreign Key Constraint in Database – Create All the Foreign Key Constraint in Database<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<br />
<br />
<span style="background-color: white; color: black; display: inline; float: none; font-family: "times new roman"; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
</span><span style="background-color: white; color: black; display: inline; float: none; font-family: "times new roman"; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
</span><br />
<pre class="SQLCode" style="-webkit-text-stroke-width: 0px; color: black; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><span class="SQLKeyword" style="color: #0000aa;">SET</span> <span class="SQLKeyword" style="color: #0000aa;">NOCOUNT</span> <span class="SQLKeyword" style="color: #0000aa;">ON</span>
<span class="SQLKeyword" style="color: #0000aa;">DECLARE</span> @table <span class="SQLKeyword" style="color: #0000aa;">TABLE</span> <span class="SQLOperator" style="color: #777777;">(</span>
RowId <span class="SQLKeyword" style="color: #0000aa;">INT</span> <span class="SQLKeyword" style="color: #0000aa;">PRIMARY</span> <span class="SQLKeyword" style="color: #0000aa;">KEY</span> <span class="SQLFunction" style="color: #aa00aa;">IDENTITY</span><span class="SQLOperator" style="color: #777777;">(</span>1<span class="SQLOperator" style="color: #777777;">,</span> 1<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">,</span>ForeignKeyConstraintName <span class="SQLKeyword" style="color: #0000aa;">NVARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>200<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">,</span>ForeignKeyConstraintTableSchema <span class="SQLKeyword" style="color: #0000aa;">NVARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>200<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">,</span>ForeignKeyConstraintTableName <span class="SQLKeyword" style="color: #0000aa;">NVARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>200<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">,</span>ForeignKeyConstraintColumnName <span class="SQLKeyword" style="color: #0000aa;">NVARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>200<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">,</span>PrimaryKeyConstraintName <span class="SQLKeyword" style="color: #0000aa;">NVARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>200<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">,</span>PrimaryKeyConstraintTableSchema <span class="SQLKeyword" style="color: #0000aa;">NVARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>200<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">,</span>PrimaryKeyConstraintTableName <span class="SQLKeyword" style="color: #0000aa;">NVARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>200<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">,</span>PrimaryKeyConstraintColumnName <span class="SQLKeyword" style="color: #0000aa;">NVARCHAR</span><span class="SQLOperator" style="color: #777777;">(</span>200<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLKeyword" style="color: #0000aa;">INSERT INTO</span> @table <span class="SQLOperator" style="color: #777777;">(</span>
ForeignKeyConstraintName
<span class="SQLOperator" style="color: #777777;">,</span>ForeignKeyConstraintTableSchema
<span class="SQLOperator" style="color: #777777;">,</span>ForeignKeyConstraintTableName
<span class="SQLOperator" style="color: #777777;">,</span>ForeignKeyConstraintColumnName
<span class="SQLOperator" style="color: #777777;">)</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> U<span class="SQLOperator" style="color: #777777;">.</span>CONSTRAINT_NAME
<span class="SQLOperator" style="color: #777777;">,</span>U<span class="SQLOperator" style="color: #777777;">.</span>TABLE_SCHEMA
<span class="SQLOperator" style="color: #777777;">,</span>U<span class="SQLOperator" style="color: #777777;">.</span>TABLE_NAME
<span class="SQLOperator" style="color: #777777;">,</span>U<span class="SQLOperator" style="color: #777777;">.</span>COLUMN_NAME
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> INFORMATION_SCHEMA<span class="SQLOperator" style="color: #777777;">.</span>KEY_COLUMN_USAGE U
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> INFORMATION_SCHEMA<span class="SQLOperator" style="color: #777777;">.</span>TABLE_CONSTRAINTS C <span class="SQLKeyword" style="color: #0000aa;">ON</span> U<span class="SQLOperator" style="color: #777777;">.</span>CONSTRAINT_NAME <span class="SQLOperator" style="color: #777777;">=</span> C<span class="SQLOperator" style="color: #777777;">.</span>CONSTRAINT_NAME
<span class="SQLKeyword" style="color: #0000aa;">WHERE</span> C<span class="SQLOperator" style="color: #777777;">.</span>CONSTRAINT_TYPE <span class="SQLOperator" style="color: #777777;">=</span> <span class="SQLString" style="color: #aa0000;">'FOREIGN KEY'</span>
<span class="SQLKeyword" style="color: #0000aa;">UPDATE</span> @table
<span class="SQLKeyword" style="color: #0000aa;">SET</span> PrimaryKeyConstraintName <span class="SQLOperator" style="color: #777777;">=</span> UNIQUE_CONSTRAINT_NAME
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> @table T
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> INFORMATION_SCHEMA<span class="SQLOperator" style="color: #777777;">.</span>REFERENTIAL_CONSTRAINTS R <span class="SQLKeyword" style="color: #0000aa;">ON</span> T<span class="SQLOperator" style="color: #777777;">.</span>ForeignKeyConstraintName <span class="SQLOperator" style="color: #777777;">=</span> R<span class="SQLOperator" style="color: #777777;">.</span>CONSTRAINT_NAME
<span class="SQLKeyword" style="color: #0000aa;">UPDATE</span> @table
<span class="SQLKeyword" style="color: #0000aa;">SET</span> PrimaryKeyConstraintTableSchema <span class="SQLOperator" style="color: #777777;">=</span> TABLE_SCHEMA
<span class="SQLOperator" style="color: #777777;">,</span>PrimaryKeyConstraintTableName <span class="SQLOperator" style="color: #777777;">=</span> TABLE_NAME
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> @table T
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> INFORMATION_SCHEMA<span class="SQLOperator" style="color: #777777;">.</span>TABLE_CONSTRAINTS C <span class="SQLKeyword" style="color: #0000aa;">ON</span> T<span class="SQLOperator" style="color: #777777;">.</span>PrimaryKeyConstraintName <span class="SQLOperator" style="color: #777777;">=</span> C<span class="SQLOperator" style="color: #777777;">.</span>CONSTRAINT_NAME
<span class="SQLKeyword" style="color: #0000aa;">UPDATE</span> @table
<span class="SQLKeyword" style="color: #0000aa;">SET</span> PrimaryKeyConstraintColumnName <span class="SQLOperator" style="color: #777777;">=</span> COLUMN_NAME
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> @table T
<span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> INFORMATION_SCHEMA<span class="SQLOperator" style="color: #777777;">.</span>KEY_COLUMN_USAGE U <span class="SQLKeyword" style="color: #0000aa;">ON</span> T<span class="SQLOperator" style="color: #777777;">.</span>PrimaryKeyConstraintName <span class="SQLOperator" style="color: #777777;">=</span> U<span class="SQLOperator" style="color: #777777;">.</span>CONSTRAINT_NAME
<span class="SQLComment" style="color: #00aa00;">--SELECT * FROM @table</span>
<span class="SQLComment" style="color: #00aa00;">--DROP CONSTRAINT:</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLString" style="color: #aa0000;">'
ALTER TABLE ['</span> <span class="SQLOperator" style="color: #777777;">+</span> ForeignKeyConstraintTableSchema <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">'].['</span> <span class="SQLOperator" style="color: #777777;">+</span> ForeignKeyConstraintTableName <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">']
DROP CONSTRAINT '</span> <span class="SQLOperator" style="color: #777777;">+</span> ForeignKeyConstraintName <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">'
GO'</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> @table
<span class="SQLComment" style="color: #00aa00;">--ADD CONSTRAINT:</span>
<span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLString" style="color: #aa0000;">'
ALTER TABLE ['</span> <span class="SQLOperator" style="color: #777777;">+</span> ForeignKeyConstraintTableSchema <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">'].['</span> <span class="SQLOperator" style="color: #777777;">+</span> ForeignKeyConstraintTableName <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">']
ADD CONSTRAINT '</span> <span class="SQLOperator" style="color: #777777;">+</span> ForeignKeyConstraintName <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">' FOREIGN KEY('</span> <span class="SQLOperator" style="color: #777777;">+</span> ForeignKeyConstraintColumnName <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">') REFERENCES ['</span> <span class="SQLOperator" style="color: #777777;">+</span> PrimaryKeyConstraintTableSchema <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">'].['</span> <span class="SQLOperator" style="color: #777777;">+</span> PrimaryKeyConstraintTableName <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">']('</span> <span class="SQLOperator" style="color: #777777;">+</span> PrimaryKeyConstraintColumnName <span class="SQLOperator" style="color: #777777;">+</span> <span class="SQLString" style="color: #aa0000;">')
GO'</span>
<span class="SQLKeyword" style="color: #0000aa;">FROM</span> @table
<span class="SQLKeyword" style="color: #0000aa;">GO</span>
Reference:<a href="https://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/">https://blog.sqlauthority.com/2014/04/11/sql-server-drop-all-the-foreign-key-constraint-in-database-create-all-the-foreign-key-constraint-in-database/</a></pre>
</div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-79576658065589761332020-02-27T12:35:00.002-08:002020-02-27T12:35:42.641-08:00List all Orphan users in a SQL Server database<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">CREATE</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">TABLE</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> ##ORPHANUSER </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">(</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /> DBNAME </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">VARCHAR</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">(</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">100</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">)</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">,</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">USERNAME </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">VARCHAR</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">(</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">100</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">)</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">,</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">CREATEDATE </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">VARCHAR</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">(</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">100</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">)</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">,</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">USERTYPE </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">VARCHAR</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">(</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">100</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">)</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">)</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /></span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">EXEC</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> SP_MSFOREACHDB </span><span class="SQLString" style="-webkit-text-stroke-width: 0px; color: #aa0000; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">' USE [?] <br />INSERT INTO ##ORPHANUSER <br />SELECT DB_NAME() DBNAME, NAME,CREATEDATE, <br />(CASE <br />WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ''SQL LOGIN'' <br />WHEN ISNTGROUP = 1 THEN ''NT GROUP'' <br />WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ''NT LOGIN'' <br />END) [LOGIN TYPE] FROM sys.sysusers <br />WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND <br />SID NOT IN (SELECT SID FROM sys.syslogins)'</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /></span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">SELECT</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">*</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /></span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">FROM</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> ##ORPHANUSER<br /></span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">DROP</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">TABLE</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> ##ORPHANUSER</span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-60515540175102373832020-02-25T11:27:00.001-08:002020-02-25T11:27:47.742-08:00Find text in SQL Server Agent Jobs<div dir="ltr" style="text-align: left;" trbidi="on">
<br /><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">USE</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> MSDB</span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">GO</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /></span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">SELECT</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> Job</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">name </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">AS</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> JobName<br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">,</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">Job</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">enabled </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">AS</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> ActiveStatus<br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">,</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">JobStep</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">step_name </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">AS</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> JobStepName<br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">,</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">JobStep</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">command </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">AS</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> JobCommand</span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">FROM</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> sysjobs Job</span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">INNER JOIN</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> sysjobsteps JobStep </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">ON</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> Job</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">job_id </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">=</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> JobStep</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">job_id</span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">WHERE</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> JobStep</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">command </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">LIKE</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLString" style="-webkit-text-stroke-width: 0px; color: #aa0000; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">'%YourTextHere%'</span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-84946306069733427572020-02-25T11:19:00.000-08:002020-02-25T11:19:16.967-08:00List tables on their dependency order based on foreign keys for Data Migration<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: Times New Roman; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: Times New Roman; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
</span><pre class="SQLCode" style="-webkit-text-stroke-width: 0px; color: black; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><span class="SQLKeyword" style="color: #0000aa;"><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike>
<div style="-webkit-text-stroke-width: 0px; border-bottom-color: currentColor; border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: currentColor; border-left-style: none; border-left-width: 0px; border-right-color: currentColor; border-right-style: none; border-right-width: 0px; border-top-color: currentColor; border-top-style: none; border-top-width: 0px; box-sizing: inherit; clear: both; color: #242729; font-family: inherit; font-size-adjust: none; font-size: 100%; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; letter-spacing: normal; line-height: inherit; margin-bottom: 1em; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; vertical-align: baseline; white-space: normal; word-spacing: 0px;">
If you want to perform bulk insert operation on multiple tables.
The tables have foreign key relationships between themselves.
If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.</div>
<span style="-webkit-text-stroke-width: 0px; background-color: white; color: #242729; display: inline !important; float: none; font-family: Arial,"Helvetica Neue",Helvetica,sans-serif; font-size-adjust: none; font-size: 15px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; letter-spacing: normal; line-height: 1.3; orphans: 2; overflow-wrap: break-word; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
</span><h3 style="-webkit-text-stroke-width: 0px; border-bottom-color: currentColor; border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: currentColor; border-left-style: none; border-left-width: 0px; border-right-color: currentColor; border-right-style: none; border-right-width: 0px; border-top-color: currentColor; border-top-style: none; border-top-width: 0px; box-sizing: inherit; color: #242729; font-family: inherit; font-size-adjust: none; font-size: 17px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: 400; letter-spacing: normal; line-height: 1.3; margin-bottom: 1em; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; overflow-wrap: break-word; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; vertical-align: baseline; white-space: normal; word-spacing: 0px;">
Requirement</h3>
<span style="-webkit-text-stroke-width: 0px; background-color: white; color: #242729; display: inline !important; float: none; font-family: Arial,"Helvetica Neue",Helvetica,sans-serif; font-size-adjust: none; font-size: 15px; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; letter-spacing: normal; line-height: 1.3; orphans: 2; overflow-wrap: break-word; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
</span><div style="-webkit-text-stroke-width: 0px; border-bottom-color: currentColor; border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: currentColor; border-left-style: none; border-left-width: 0px; border-right-color: currentColor; border-right-style: none; border-right-width: 0px; border-top-color: currentColor; border-top-style: none; border-top-width: 0px; box-sizing: inherit; clear: both; color: #242729; font-family: inherit; font-size-adjust: none; font-size: 100%; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; letter-spacing: normal; line-height: inherit; margin-bottom: 1em; margin-left: 0px; margin-right: 0px; margin-top: 0px; orphans: 2; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; vertical-align: baseline; white-space: normal; word-spacing: 0px;">
Produce a list of tables within a database ordered according to their dependencies.
Tables with no dependencies (no foreign keys) will be 1st.
Tables with dependencies only in the 1st set of tables will be 2nd.
Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd.
and so on...</div>
<b></b>
<b></b>
<b></b>
<b></b>
<b></b>
<b>WITH</b></span><b> cte <span class="SQLOperator" style="color: #777777;">(</span></b>
<b> lvl</b>
<b> <span class="SQLOperator" style="color: #777777;">,</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span></b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>name</b>
<b> <span class="SQLOperator" style="color: #777777;">,</span><span class="SQLFunction" style="color: #aa00aa;">schema_Name</span></b>
<b> <span class="SQLOperator" style="color: #777777;">)</span></b>
<b><span class="SQLKeyword" style="color: #0000aa;">AS</span> <span class="SQLOperator" style="color: #777777;">(</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">SELECT</span> 1</b>
<b> <span class="SQLOperator" style="color: #777777;">,</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span></b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span>name</b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>sys<span class="SQLOperator" style="color: #777777;">.</span>schemas<span class="SQLOperator" style="color: #777777;">.</span>name <span class="SQLKeyword" style="color: #0000aa;">AS</span> <span class="SQLFunction" style="color: #aa00aa;">schema_Name</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">FROM</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables</b>
<b> <span class="SQLKeyword" style="color: #0000aa;">INNER JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>schemas <span class="SQLKeyword" style="color: #0000aa;">ON</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">schema_id</span> <span class="SQLOperator" style="color: #777777;">=</span> sys<span class="SQLOperator" style="color: #777777;">.</span>schemas<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">schema_id</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">WHERE</span> type_desc <span class="SQLOperator" style="color: #777777;">=</span> <span class="SQLString" style="color: #aa0000;">'USER_TABLE'</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">AND</span> is_ms_shipped <span class="SQLOperator" style="color: #777777;">=</span> 0</b>
<b> </b>
<b> <span class="SQLKeyword" style="color: #0000aa;">UNION ALL</span></b>
<b> </b>
<b> <span class="SQLKeyword" style="color: #0000aa;">SELECT</span> cte<span class="SQLOperator" style="color: #777777;">.</span>lvl <span class="SQLOperator" style="color: #777777;">+</span> 1</b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>t<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span></b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>t<span class="SQLOperator" style="color: #777777;">.</span>name</b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>S<span class="SQLOperator" style="color: #777777;">.</span>name <span class="SQLKeyword" style="color: #0000aa;">AS</span> <span class="SQLFunction" style="color: #aa00aa;">schema_Name</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">FROM</span> cte</b>
<b> <span class="SQLKeyword" style="color: #0000aa;">JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>tables <span class="SQLKeyword" style="color: #0000aa;">AS</span> t <span class="SQLKeyword" style="color: #0000aa;">ON</span> <span class="SQLOperator" style="color: #777777;">EXISTS</span> <span class="SQLOperator" style="color: #777777;">(</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLKeyword" style="color: #0000aa;">NULL</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">FROM</span> sys<span class="SQLOperator" style="color: #777777;">.</span>foreign_keys <span class="SQLKeyword" style="color: #0000aa;">AS</span> fk</b>
<b> <span class="SQLKeyword" style="color: #0000aa;">WHERE</span> fk<span class="SQLOperator" style="color: #777777;">.</span>parent_object_id <span class="SQLOperator" style="color: #777777;">=</span> t<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">AND</span> fk<span class="SQLOperator" style="color: #777777;">.</span>referenced_object_id <span class="SQLOperator" style="color: #777777;">=</span> cte<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span></b>
<b> <span class="SQLOperator" style="color: #777777;">)</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">JOIN</span> sys<span class="SQLOperator" style="color: #777777;">.</span>schemas <span class="SQLKeyword" style="color: #0000aa;">AS</span> S <span class="SQLKeyword" style="color: #0000aa;">ON</span> t<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">schema_id</span> <span class="SQLOperator" style="color: #777777;">=</span> S<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">schema_id</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">AND</span> t<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span> <span class="SQLOperator" style="color: #777777;"><></span> cte<span class="SQLOperator" style="color: #777777;">.</span><span class="SQLFunction" style="color: #aa00aa;">object_id</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">AND</span> cte<span class="SQLOperator" style="color: #777777;">.</span>lvl <span class="SQLOperator" style="color: #777777;"><</span> 30</b>
<b> <span class="SQLKeyword" style="color: #0000aa;">WHERE</span> t<span class="SQLOperator" style="color: #777777;">.</span>type_desc <span class="SQLOperator" style="color: #777777;">=</span> <span class="SQLString" style="color: #aa0000;">'USER_TABLE'</span></b>
<b> <span class="SQLKeyword" style="color: #0000aa;">AND</span> t<span class="SQLOperator" style="color: #777777;">.</span>is_ms_shipped <span class="SQLOperator" style="color: #777777;">=</span> 0</b>
<b> <span class="SQLOperator" style="color: #777777;">)</span></b>
<b><span class="SQLKeyword" style="color: #0000aa;">SELECT</span> <span class="SQLFunction" style="color: #aa00aa;">schema_Name</span></b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>name</b>
<b> <span class="SQLOperator" style="color: #777777;">,</span><span class="SQLFunction" style="color: #aa00aa;">MAX</span><span class="SQLOperator" style="color: #777777;">(</span>lvl<span class="SQLOperator" style="color: #777777;">)</span> <span class="SQLKeyword" style="color: #0000aa;">AS</span> dependency_level</b>
<b><span class="SQLKeyword" style="color: #0000aa;">FROM</span> cte</b>
<b><span class="SQLKeyword" style="color: #0000aa;">WHERE</span> <span class="SQLFunction" style="color: #aa00aa;">schema_Name</span> <span class="SQLOperator" style="color: #777777;">LIKE</span> <span class="SQLString" style="color: #aa0000;">'%ORCA%'</span></b>
<b><span class="SQLKeyword" style="color: #0000aa;">GROUP</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> <span class="SQLFunction" style="color: #aa00aa;">schema_Name</span></b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>name</b>
<b><span class="SQLKeyword" style="color: #0000aa;">ORDER</span> <span class="SQLKeyword" style="color: #0000aa;">BY</span> dependency_level</b>
<b> <span class="SQLOperator" style="color: #777777;">,</span><span class="SQLFunction" style="color: #aa00aa;">schema_Name</span></b>
<b> <span class="SQLOperator" style="color: #777777;">,</span>name<span class="SQLOperator" style="color: #777777;">;
<a href="https://stackoverflow.com/questions/40388903/how-to-list-tables-in-their-dependency-order-based-on-foreign-keys">Reference</a></span></b>
</pre>
<span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: Times New Roman; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px;">
</span></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-5658556417880090062020-02-19T16:42:00.000-08:002020-02-19T16:42:32.203-08:00Calculate disk space occupied by each schema in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
<span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">SELECT</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">SCHEMA_NAME</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">(</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">so</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">schema_id</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">)</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">AS</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> SchemaName<br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">,</span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">SUM</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">(</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">ps</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">reserved_page_count</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">)</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">*</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> 8.0 </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">/</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> 1024 </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">AS</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> SizeInMB</span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">FROM</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> sys</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">dm_db_partition_stats ps</span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">JOIN</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> sys</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">indexes i </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">ON</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> i</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">object_id</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">=</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> ps</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">object_id</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /> </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">AND</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> i</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">index_id </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">=</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> ps</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">index_id</span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">JOIN</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> sys</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">objects so </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">ON</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> i</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">object_id</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">=</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> so</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">object_id</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /></span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">WHERE</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> so</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">type </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">=</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLString" style="-webkit-text-stroke-width: 0px; color: #aa0000; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">'U'</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /></span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">GROUP</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">BY</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> so</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">schema_id</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /></span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">ORDER</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">BY</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"> OBJECT_SCHEMA_NAME</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">(</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">so</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">.</span><span class="SQLFunction" style="-webkit-text-stroke-width: 0px; color: #aa00aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">schema_id</span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">)</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;"><br /> </span><span class="SQLOperator" style="-webkit-text-stroke-width: 0px; color: #777777; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">,</span><span style="-webkit-text-stroke-width: 0px; background-color: white; color: black; display: inline !important; float: none; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: bold; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">SizeInMB </span><span class="SQLKeyword" style="-webkit-text-stroke-width: 0px; color: #0000aa; font-family: monospace; font-size: 13px; font-style: normal; font-variant: normal; font-weight: 700; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; white-space: pre; word-spacing: 0px;">DESC</span><b></b><i></i><u></u><sub></sub><sup></sup><strike></strike></div>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-5895233377551438932018-08-22T08:49:00.000-07:002018-08-22T08:49:20.189-07:00List out all MDF and LDF File locations of SQL Sever Databases<div dir="ltr" style="text-align: left;" trbidi="on">
SELECT<br />
db.name AS DBName,<br />
type_desc AS FileType,<br />
Physical_Name AS Location<br />
FROM<br />
sys.master_files mf<br />
INNER JOIN<br />
sys.databases db ON db.database_id = mf.database_id</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-23640113392333087992018-08-22T08:42:00.001-07:002018-08-22T08:42:57.793-07:00Move User Databases<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="background-color: #f9f9f9; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 14px; white-space: pre;">USE master;
GO
-- Return the logical file name.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2012')
AND type_desc = N'LOG';
GO
ALTER DATABASE AdventureWorks2012 SET OFFLINE;
GO
-- Physically move the file to a new location.
-- In the following statement, modify the path specified in FILENAME to
-- the new location of the file on your server.
ALTER DATABASE AdventureWorks2012
MODIFY FILE ( NAME = AdventureWorks2012_Log,
FILENAME = 'C:\NewLoc\AdventureWorks2012_Log.ldf');
GO
ALTER DATABASE AdventureWorks2012 SET ONLINE;
GO
--Verify the new location.
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'AdventureWorks2012')
AND type_desc = N'LOG';</span></div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-87431121146651514612018-08-22T08:25:00.000-07:002018-08-22T08:25:22.060-07:00Space occupied by each Schema in SQL Server database<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="white-space: pre;"> </span>SELECT SCHEMA_NAME(so.schema_id) AS SchemaName<br />
,SUM(ps.reserved_page_count) * 8.0 / 1024 AS SizeInMB<br />
FROM sys.dm_db_partition_stats ps<br />
JOIN sys.indexes i<br />
ON i.object_id = ps.object_id<br />
AND i.index_id = ps.index_id<br />
<span style="white-space: pre;"> </span>JOIN<span style="white-space: pre;"> </span>sys.objects<span style="white-space: pre;"> </span>so<br />
<span style="white-space: pre;"> </span> ON<span style="white-space: pre;"> </span>i.object_id<span style="white-space: pre;"> </span>=<span style="white-space: pre;"> </span>so.object_id<br />
WHERE so.type<span style="white-space: pre;"> </span>=<span style="white-space: pre;"> </span>'U'<br />
GROUP BY<span style="white-space: pre;"> </span>so.schema_id<br />
ORDER BY<span style="white-space: pre;"> </span>OBJECT_SCHEMA_NAME(so.schema_id), SizeInMB DESC<br />
<div>
<br /></div>
</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-88186459421171960682017-12-04T10:07:00.002-08:002017-12-04T10:08:26.275-08:00How to Change the SQL Server Agent Log File Path<div dir="ltr" style="text-align: left;" trbidi="on">
SQL Server Agent Error Log: Log File Viewer displays log information from many different components. When Log File Viewer is open, use the Select logs pane to select the logs you want to display. Each log displays columns appropriate to that kind of log. The logs that are available depend on how Log File Viewer is opened.<br />
Solution<br />
<br />
<br />
To modify location and name of SQLServer Agent log file. We use following<br />
<br />
SP_GET_SQLAGENT_PROPERTIES<br />
SP_SET_SQLAGENT_PROPERTIES<br />
<br />
The first stored procedure is used to retrieve the SQL Server Agent properties and the second stored procedure is used to set/change the properties for the SQL Agent service.<br />
<br />
I strongly suggest testing any undocumented stored procedures in a lab environment first, before changing your production servers.<br />
<br />
Steps to move the SQL Agent log file<br />
Step 1<br />
<br />
First check the existing location of the SQL Server Agent log file. Run the below undocumented stored procedure to get the current location. This stored procedure will let us know the SQL Agent properties of a particular server. This stored procedure can be found in the msdb database.<br />
<br />
USE MASTER<br />
GO<br />
EXEC msdb..sp_get_sqlagent_properties<br />
GO<br />
We can see below the different settings that are returned when we run this command. We are interested in the errorlog_file column for the change we need to make. Here we can see the current location is the C drive.<br />
<br />
Find the current location of sql server agent log file<br />
Step 2<br />
<br />
Now we will change the location of the file from the C drive to the G drive.<br />
<br />
First we need to create the new destination folders where we want to put the SQLAGENT.OUT file. So, I created the folder "Microsoft SQL Server\MSSQL.1\MSSQL\LOG" on the G drive. If you do not create these folders you will have issues when the SQL Server Agent service restarts.<br />
<br />
After the new folder has been created, run the below stored procedure to change the location.<br />
<br />
USE MASTER<br />
GO<br />
EXEC msdb.dbo.sp_set_sqlagent_properties<br />
@errorlog_file=N'G:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLAGENT.OUT'<br />
GO<br />
<br />
Change the agent logfile location to different path<br />
Step 3<br />
<br />
Now we will verify whether the SQL Server Agent log file path has changed or not. We will be run the same command as in step 1 to get the SQL Server agent properties. We can see in the below screenshot that the path for the SQL Server Agent log file has been changed to the G drive.<br />
<br />
Verify the change of agent log file location<br />
Note, this change will not go into effect until you restart your the SQL Agent service. You can verify this by checking the new location to see if the file SQLAGENT.OUT exists or not.<br />
<br />
Step 4<br />
<br />
Now restart your SQL Server Agent service to bring the changes into effect. If you have any issues or the SQL Server Agent service does not start (you might get an error like below) then you should check the path you have set in step 2. The path has to be valid to successfully start this service.<br />
<br />
<br />
<br />
<br />
<a href="https://www.mssqltips.com/sqlservertip/3093/how-to-change-the-sql-server-agent-log-file-path/">Reference</a></div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-47302543739891557242017-10-05T14:22:00.002-07:002017-10-05T14:23:20.276-07:00What Is Filling Up The Transaction Log<div dir="ltr" style="text-align: left;" trbidi="on">
SELECT db.[name] AS [Database Name] ,<br />
db.recovery_model_desc AS [Recovery Model] ,<br />
db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,<br />
ls.cntr_value AS [Log Size (KB)] ,<br />
lu.cntr_value AS [Log Used (KB)] ,<br />
CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)<br />
AS DECIMAL(18,2)) * 100 AS [Log Used %] ,<br />
db.[compatibility_level] AS [DB Compatibility Level] ,<br />
db.page_verify_option_desc AS [Page Verify Option]<br />
FROM sys.databases AS db<br />
INNER JOIN sys.dm_os_performance_counters AS lu<br />
ON db.name = lu.instance_name<br />
INNER JOIN sys.dm_os_performance_counters AS ls<br />
ON db.name = ls.instance_name<br />
WHERE lu.counter_name LIKE 'Log File(s) Used Size (KB)%'<br />
AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;<br />
<br />
<br />
Reference: http://www.sqlservercentral.com/scripts/DMV/71997/</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-30378093991435067672017-06-07T09:44:00.000-07:002017-06-07T09:44:29.894-07:00T SQL query to find when was the database backed up lastly on SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
Select<span class="Apple-tab-span" style="white-space: pre;"> </span>database_name As [Database Name]<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>, [name] As [Backup Name]<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>, Case<span class="Apple-tab-span" style="white-space: pre;"> </span>When Type = 'D' Then 'Full Backup'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>When Type = 'I' Then 'Differential Backup'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>When Type = 'L' Then 'Log Backup'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Else 'File or filegroup or partial'<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>End As [Backup Type]<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>, Recovery_Model<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>, [backup_start_date] As [Time of the SQL Backup Job]<br />
FROM<span class="Apple-tab-span" style="white-space: pre;"> </span>[msdb].[dbo].[backupset]</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-43691004563284440822017-05-11T08:17:00.002-07:002017-05-11T08:17:38.692-07:00List of all user databases in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
SELECT *<br />
FROM master.sys.databases<br />
WHERE Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-67941886334746157212017-02-27T15:45:00.000-08:002017-02-27T15:46:06.611-08:00Find what script is being run by specific SPID number<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
DECLARE @sqltext VARBINARY(128)<br />
SELECT @sqltext = sql_handle<br />
FROM sys.sysprocesses<br />
WHERE spid = 53<br />
SELECT TEXT<br />
FROM sys.dm_exec_sql_text(@sqltext)<br />
GO</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-56680816946419415192017-02-27T15:40:00.000-08:002017-02-27T15:40:08.565-08:00SQL Script to find all Date/time columns in database<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
select<br />
so.name table_name<br />
,sc.name column_name<br />
,st.name data_type<br />
from sysobjects so<br />
inner join syscolumns sc on (so.id = sc.id)<br />
inner join systypes st on (st.type = sc.type)<br />
where so.type = 'U'<br />
and st.name IN ('DATETIME', 'DATE', 'TIME')<br />
<div>
<br /></div>
</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-45291318899205806452017-02-17T10:25:00.000-08:002017-02-17T10:25:25.450-08:00Find last modified date of stored procedure or function in SQL server<div dir="ltr" style="text-align: left;" trbidi="on">
SELECT name, create_date, modify_date<br />
FROM sys.objects<br />
WHERE type = 'P' order by modify_date desc</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-90805166726528851072017-02-07T15:27:00.000-08:002017-02-07T15:28:25.507-08:00Generate Script of All indexes in a SQL Server database<div dir="ltr" style="text-align: left;" trbidi="on">
SELECT ' CREATE ' +<br />
CASE<br />
WHEN I.is_unique = 1 THEN ' UNIQUE '<br />
ELSE ''<br />
END +<br />
I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +<br />
I.name + ' ON ' +<br />
SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +<br />
KeyColumns + ' ) ' +<br />
ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +<br />
ISNULL(' WHERE ' + I.filter_definition, '') + ' WITH ( ' +<br />
CASE<br />
WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '<br />
ELSE ' PAD_INDEX = OFF '<br />
END + ',' +<br />
'FILLFACTOR = ' + CONVERT(<br />
CHAR(5),<br />
CASE<br />
WHEN I.fill_factor = 0 THEN 100<br />
ELSE I.fill_factor<br />
END<br />
) + ',' +<br />
-- default value<br />
'SORT_IN_TEMPDB = OFF ' + ',' +<br />
CASE<br />
WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '<br />
ELSE ' IGNORE_DUP_KEY = OFF '<br />
END + ',' +<br />
CASE<br />
WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '<br />
ELSE ' STATISTICS_NORECOMPUTE = ON '<br />
END + ',' +<br />
' ONLINE = OFF ' + ',' +<br />
CASE<br />
WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '<br />
ELSE ' ALLOW_ROW_LOCKS = OFF '<br />
END + ',' +<br />
CASE<br />
WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '<br />
ELSE ' ALLOW_PAGE_LOCKS = OFF '<br />
END + ' ) ON [' +<br />
DS.name + ' ] ' + CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]<br />
FROM sys.indexes I<br />
JOIN sys.tables T<br />
ON T.object_id = I.object_id<br />
JOIN sys.sysindexes SI<br />
ON I.object_id = SI.id<br />
AND I.index_id = SI.indid<br />
JOIN (<br />
SELECT *<br />
FROM (<br />
SELECT IC2.object_id,<br />
IC2.index_id,<br />
STUFF(<br />
(<br />
SELECT ' , ' + C.name + CASE<br />
WHEN MAX(CONVERT(INT, IC1.is_descending_key))<br />
= 1 THEN<br />
' DESC '<br />
ELSE<br />
' ASC '<br />
END<br />
FROM sys.index_columns IC1<br />
JOIN sys.columns C<br />
ON C.object_id = IC1.object_id<br />
AND C.column_id = IC1.column_id<br />
AND IC1.is_included_column =<br />
0<br />
WHERE IC1.object_id = IC2.object_id<br />
AND IC1.index_id = IC2.index_id<br />
GROUP BY<br />
IC1.object_id,<br />
C.name,<br />
index_id<br />
ORDER BY<br />
MAX(IC1.key_ordinal)<br />
FOR XML PATH('')<br />
),<br />
1,<br />
2,<br />
''<br />
) KeyColumns<br />
FROM sys.index_columns IC2<br />
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables<br />
GROUP BY<br />
IC2.object_id,<br />
IC2.index_id<br />
) tmp3<br />
)tmp4<br />
ON I.object_id = tmp4.object_id<br />
AND I.Index_id = tmp4.index_id<br />
JOIN sys.stats ST<br />
ON ST.object_id = I.object_id<br />
AND ST.stats_id = I.index_id<br />
JOIN sys.data_spaces DS<br />
ON I.data_space_id = DS.data_space_id<br />
JOIN sys.filegroups FG<br />
ON I.data_space_id = FG.data_space_id<br />
LEFT JOIN (<br />
SELECT *<br />
FROM (<br />
SELECT IC2.object_id,<br />
IC2.index_id,<br />
STUFF(<br />
(<br />
SELECT ' , ' + C.name<br />
FROM sys.index_columns IC1<br />
JOIN sys.columns C<br />
ON C.object_id = IC1.object_id<br />
AND C.column_id = IC1.column_id<br />
AND IC1.is_included_column =<br />
1<br />
WHERE IC1.object_id = IC2.object_id<br />
AND IC1.index_id = IC2.index_id<br />
GROUP BY<br />
IC1.object_id,<br />
C.name,<br />
index_id<br />
FOR XML PATH('')<br />
),<br />
1,<br />
2,<br />
''<br />
) IncludedColumns<br />
FROM sys.index_columns IC2<br />
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables<br />
GROUP BY<br />
IC2.object_id,<br />
IC2.index_id<br />
) tmp1<br />
WHERE IncludedColumns IS NOT NULL<br />
) tmp2<br />
ON tmp2.object_id = I.object_id<br />
AND tmp2.index_id = I.index_id<br />
WHERE I.is_primary_key = 0<br />
AND I.is_unique_constraint = 0<br />
--AND I.Object_id = object_id('Person.Address') --Comment for all tables<br />
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes </div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-91258061753771108832017-01-25T08:54:00.000-08:002017-01-25T08:54:41.300-08:00Difference between Detaching database and bringing database Offline<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: Verdana, sans-serif; font-size: large;">SQL Server Offline and Detach Database</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: large;">Detach Database/Attach Database:</span><span style="font-family: Verdana, sans-serif; font-size: x-small;"> The data and transaction log files of a database can be detached and then reattached to the same or another instance of SQL Server. Detaching and attaching a database is useful if you want to change the database to a different instance of SQL Server on the same computer or to move the database.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Detaching a database removes it from the instance of SQL Server but leaves the database intact within its data files and transaction log files. These files can then be used to attach the database to any instance of SQL Server, including the server from which the database was detached.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">you can attach a copied or detached SQL Server database. When you attach a SQL Server 2005 database that contains full-text catalog files onto a SQL Server 2016 server instance, the catalog files are attached from their previous location along with the other database files, the same as in SQL Server When you attach a database, all data files (MDF and NDF files) must be available. If any data file has a different path from when the database was first created or last attached, you must specify the current path of the file.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<div style="line-height: 18px; padding-bottom: 15px;">
<span style="font-family: Verdana, sans-serif; font-size: x-small;">The requirement for attaching log files depends partly on whether the database is read-write or read-only, as follows:</span></div>
<ul>
<li style="list-style-image: none;"><div style="line-height: 18px; padding-bottom: 15px;">
<span style="font-family: Verdana, sans-serif; font-size: x-small;">For a read-write database, you can usually attach a log file in a new location. However, in some cases, reattaching a database requires its existing log files. Therefore, it is important to always keep all the detached log files until the database has been successfully attached without them.</span></div>
<div style="line-height: 18px; padding-bottom: 15px;">
<span style="font-family: Verdana, sans-serif; font-size: x-small;">If a read-write database has a single log file and you do not specify a new location for the log file, the attach operation looks in the old location for the file. If it is found, the old log file is used, regardless of whether the database was shut down cleanly. However, if the old log file is not found and if the database was shut down cleanly and has no active log chain, the attach operation attempts to build a new log file for the database.</span></div>
</li>
<li style="list-style-image: none;"><div style="line-height: 18px; padding-bottom: 15px;">
<span style="font-family: Verdana, sans-serif; font-size: x-small;">If the primary data file being attached is read-only, the Database Engine assumes that the database is read-only. For a read-only database, the log file or files must be available at the location specified in the primary file of the database. A new log file cannot be built because SQL Server cannot update the log location stored in the primary file.</span></div>
</li>
</ul>
<span style="font-family: Verdana, sans-serif; font-size: large;">Database Offline:</span><span style="font-family: Verdana, sans-serif; font-size: x-small;"> </span><span style="font-family: "Segoe UI", "Lucida Grande", Verdana, Arial, Helvetica, sans-serif; font-size: 13px;">Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Use OFFLINE and ONLINE</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">1) If you are trying to make the database temporary unavailable for a period of time, you could take the database OFFLINE, and make it available by bringing it ONLINE whenever it is or you are ready.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">2a) If you want to move the database or log files to different physical location or changing the database and/or log physical file name, AND keep them within the same SQL Server instance.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">- Before you take the database offline, you need to know the logical name of the file. The name field is the logical name for the physical file name that you are about to change the path. Record the logical name for later use.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">SELECT name, type, type_desc, physical_name, state, state_desc</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">FROM sys.master_files</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">WHERE database_id = DB_ID('YourDatabase')</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">- Take the database OFFLINE.</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">ALTER DATABASE YourDatabase SET OFFLINE</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">WITH ROLLBACK IMMEDIATE;</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">-Move the database and/or log files to different location.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">- Update the database and/or log files path registered on its SQL Server instance. Execute the T-SQL command for each database and log files,</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">ALTER DATABASE YourDatabase </span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">MODIFY FILE (NAME = YourFileLogicalName,</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">FILENAME = 'Your Database or Log new physical path')</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Bring the database ONLINE.</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">ALTER DATABASE YourDatabase SET ONLINE;</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">If you just want to change the logical name, you dont have to take the database OFFLINE. You could change it on SSMS or T-SQL.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Right click on the database > Properties > Select Page > Change it under Logical Name column > OK.</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">ALTER DATABASE YourDatabase </span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">MODIFY FILE (NAME = YourFileLogicalName,</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">NEWNAME = YourFileNewLogicalName)</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">2b) If you are relocating a file during part of the scheduled disk maintenance process, you may need to change the file path registered on its SQL Server instance before taking the database OFFLINE. This assure the server has the right path for the database and log files when the instance restart, after being shut down for maintenance.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">- When the database and/or log file path is successfully updated on the instance, a message will show; 'The file "YourDatabaseFile" has been modified in the system catalog. The new path will be used the next time the database is started.'</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">Use DETACH and ATTACH,</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">1) If you want to move the database and log files to a different SQL Server instance or another server.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">- Rollback active transaction and gain exclusive access. Detach the database. (optional: update statistics) </span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">ALTER DATABASE YourDatabase SET SINGLE_USER</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">WITH ROLLBACK IMMEDIATE;</span><br />
<br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">EXEC sp_detach_db 'YourDatabase', 'true'</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">- Move database and log files to different location.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;">- Attach the database on different instance on same/another server. </span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">USE master;</span><br />
<br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">CREATE DATABASE YourDatabase</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">ON (FILENAME = 'the physical path of your database file')</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">LOG ON (FILENAME = 'the physical path of your log file')</span><br />
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">FOR ATTACH;</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">- If the database has previously set to single user, you may want to set it to multiple user access option.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="color: blue; font-family: Verdana, sans-serif; font-size: x-small;">ALTER DATABASE YourDatabase SET MULTI_USER</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;">2) Even though you can choose to detach and attach the database back on the same SQL Server instance for scenario like changing physical path or file name, it is recommended to use the OFFLINE and ONLINE methods due to the restriction and limitation on DETACH. When you detach a database, you remove the database from the instance. It is required to remove the database from any participation of replication, mirroring and snapshot.</span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><a href="http://www.travisgan.com/2012/06/sql-server-offline-and-detach-database.html">Reference: </a></span><br />
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<span style="font-family: Verdana, sans-serif; font-size: x-small;"><br /></span>
<br /></div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-17479535261734598702017-01-24T13:53:00.001-08:002017-01-24T13:53:45.893-08:00Query to List all the partitioned tables in SQL Server database<div dir="ltr" style="text-align: left;" trbidi="on">
select object_schema_name(i.object_id) as [schema],<br />
object_name(i.object_id) as [object],<br />
i.name as [index],<br />
s.name as [partition_scheme]<br />
from sys.indexes i<br />
join sys.partition_schemes s on i.data_space_id = s.data_space_id order by [schema]</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-63533890757391209102016-12-06T08:33:00.000-08:002016-12-06T08:33:07.456-08:00Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator<div dir="ltr" style="text-align: left;" trbidi="on">
Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator<br />
<br />
<br />
<span style="color: blue;">DBCC CHECKDB('databaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS </span><br />
<br />
<br />
--Resulted In<br />
Msg 8909, Level 16, State 1, Line 5<br />
Table error: Object ID 0, index ID 12341, page ID (1:5880). The PageId in the page header = (9728:16777220).<br />
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.<br />
<br />
<br />
DBCC results for 'TableName'.<br />
Msg 8928, Level 16, State 1, Line 5<br />
Object ID 871674153, index ID 0: Page (1:5880) could not be processed. See other errors for details.<br />
<br />
<br />
There are 20993 rows in 584 pages for object 'TableName'.<br />
CHECKTABLE found 0 allocation errors and 8 consistency errors in table 'TableName' (object ID 871674153).<br />
Msg 8909, Level 16, State 1, Line 5<br />
Table error: Object ID 1109413712, index ID 24940, page ID (1:5883). The PageId in the page header = (25198:1632843825).<br />
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 1109413712)' (object ID 1109413712).<br />
<br />
<br />
Although its not an ideal solution, you can use DBCC CHECKTABLE which in our case fixed the issue:<br />
<br />
--Put the database into single user mode<br />
<span style="color: blue;">ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH NO_WAIT</span><br />
--Check the erors and fix any issues found (that you can)<br />
<span style="color: blue;">DBCC CHECKTABLE ('Orders', REPAIR_REBUILD)</span><br />
--Put the database back into multiuser mode<br />
<span style="color: blue;">ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT</span><br />
<div>
<br /></div>
<div>
<br /></div>
<div>
<a href="https://www.blogger.com/A%20quick%20Google%20suggests%20a%20physical%20disk%20drive%20error%20and%20having%20a%20quick%20look%20at%20the%20issues%20it%20wasn't%20pretty.%20Running:%20-%20See%20more%20at:%20http://thesitedoctor.co.uk/blog/sql-server-warning-fatal-error-823-occurred-at-date-time-note-the-error-and-time-and-contact-your-system-administrator/#sthash.sg8lspsp.dpuf.">Reference:</a></div>
</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-19764017479294743552016-11-21T09:38:00.002-08:002016-11-21T09:40:12.657-08:00 T-SQL code to get the active Connections for each Database.<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif; font-size: 14px;">--Method 1</span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> SELECT </span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> DB_NAME(dbid) as DBName, </span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> COUNT(dbid) as NoOfConnections,</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> loginame as LoginName</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">FROM</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> sys.sysprocesses</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">WHERE </span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> dbid > 0</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">GROUP BY </span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> dbid, loginame</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"><br /></span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"><br /></span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">--Method 2</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">EXEC sp_who2</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"><br /></span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"><br /></span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">--Method 3</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">SELECT spid,</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> sp.[status],</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> loginame [Login],</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> hostname, </span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> blocked BlkBy,</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> sd.name DBName, </span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> cmd Command,</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> cpu CPUTime,</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> physical_io DiskIO,</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> last_batch LastBatch,</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;"> [program_name] ProgramName </span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">FROM master.dbo.sysprocesses sp </span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid</span></span></div>
<div style="background-color: white; border: none; list-style-type: none; margin-bottom: 1em; outline: 0px; padding: 0px;">
<span style="color: #333333; font-family: "segoe ui" , "lucida grande" , "verdana" , "arial" , "helvetica" , sans-serif;"><span style="font-size: 14px;">ORDER BY DBName,spid</span></span></div>
</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-85650343657583074572016-11-16T13:49:00.001-08:002016-11-16T13:49:29.603-08:00Delete all Tables based on creation date <div dir="ltr" style="text-align: left;" trbidi="on">
<br />
--Delete all Tables based on creation date<br />
<br />
<br />
<br />
DECLARE @tname VARCHAR(100)<br />
DECLARE @sql VARCHAR(max)<br />
<br />
DECLARE db_cursor CURSOR FOR<br />
SELECT name AS tname<br />
FROM sys.objects<br />
WHERE create_date < GETDATE() - 1-- Days old<br />
<br />
OPEN db_cursor <br />
FETCH NEXT FROM db_cursor INTO @tname <br />
<br />
WHILE @@FETCH_STATUS = 0 <br />
BEGIN <br />
SET @sql = 'DROP TABLE ' + @tname<br />
--EXEC (@sql) -- For Executing<br />
PRINT @sql --For Printing<br />
<br />
FETCH NEXT FROM db_cursor INTO @tname <br />
END <br />
<br />
CLOSE db_cursor <br />
DEALLOCATE db_cursor<br />
<div>
<br /></div>
</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-74779294212400678252016-11-04T09:11:00.000-07:002016-11-04T09:11:43.813-07:00Simplest way to Truncate/Delete all tables in a given database.<div dir="ltr" style="text-align: left;" trbidi="on">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:"Segoe UI";
panose-1:2 11 5 2 4 2 4 2 2 3;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin-top:0in;
margin-right:0in;
margin-bottom:8.0pt;
margin-left:0in;
line-height:107%;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{font-family:"Calibri",sans-serif;}
.MsoPapDefault
{margin-bottom:8.0pt;
line-height:107%;}
/* Page Definitions */
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
-->
</style>
<br />
<div class="WordSection1">
<div class="MsoNormal">
Simplest way to Truncate all tables in a given database.<o:p></o:p></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 2pt; margin-top: 2pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">Use databasename</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 2pt; margin-top: 2pt;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">EXEC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">sp_MSForEachTable</span><span style="color: blue;"> </span><span style="color: red;">'Truncate TABLE ?'</span> <span style="color: green;">--- For Truncating all tables</span></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 2pt; margin-top: 2pt;">
<br /></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 2pt; margin-top: 2pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"><br /></span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 2pt; margin-top: 2pt;">
<span style="font-family: Consolas; font-size: 9.5pt;"><br /></span></div>
<div class="MsoNormal">
Simplest way to Truncate all tables in a given database.<o:p></o:p></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: 2pt; margin-top: 2pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.6667px;">Use databasename</span></div>
<div class="MsoNormal" style="line-height: normal; margin-bottom: .0001pt; margin-bottom: 0in; text-autospace: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;">EXEC</span><span style="font-family: Consolas; font-size: 9.5pt;"> <span style="color: maroon;">sp_MSforeachtable</span><span style="color: blue;"> </span>@command1
<span style="color: grey;">=</span> "DROP TABLE ?" <span style="color: green;">--- For Deleting all tables</span></span></div>
</div>
</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-76362572681318351902016-10-25T14:37:00.000-07:002016-11-04T09:08:09.624-07:00How to Stop SQL Server Agent Job while it is actively running?<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="color: red; font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif; font-size: large;">How to Stop SQL Server Agent Job while it is actively running?</span><br />
<span style="background-color: white; color: #333333;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><br /></span></span>
<span style="background-color: white; color: #333333;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Try running sp_who2 and find the SPID and kill the process using KILL SPID with status only so that it will show the rollbackup completed as well.</span></span><br />
<span style="background-color: white; color: #333333;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><br /></span></span>
<span style="background-color: white; color: #333333;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><br /></span></span>
<br />
<h1 class="entry-title" style="background-color: white; box-sizing: border-box; font-weight: normal; line-height: 1.1; margin: 20px 0px 10px; word-wrap: break-word;">
<span style="color: red; font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif; font-size: large;">Difference between KILL and KILL WITH STATUSONLY – Estimate Rollback completion time</span></h1>
<div>
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><br /></span></div>
<div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">While I was talking to a customer during a replication scenario, customer asked me a question about understanding the estimated completion time for a rollback.</span></span></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><o:p style="box-sizing: border-box;"></o:p></span></span></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;">I was explaining that we have KILL WITH STATUSONLY but it will just report the progress only if the SPID was <span style="box-sizing: border-box; font-weight: 700;">killed</span> already and it will not kill the SPID also KILL WITH STATUSONLY will not help to track the progress of ROLLBACK issued by ROLLBACK TRAN.</span></span> <span style="box-sizing: border-box;"><span style="box-sizing: border-box;">This was something new to him that KILL WITH STATUSONLY will not kill the SPID instead it only reports the progress. <o:p style="box-sizing: border-box;"></o:p></span></span></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">I thought of sharing this in a blog so that it will be useful for others who overlooked the KILL command.<o:p style="box-sizing: border-box;"></o:p></span></span></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Here is what BOL says:<o:p style="box-sizing: border-box;"></o:p></span></span></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<i style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">KILL WITH STATUSONLY <span style="box-sizing: border-box; font-weight: 700;">does not terminate or roll back the session</span> ID or UOW; the command only displays the current progress of the rollback.<o:p style="box-sizing: border-box;"></o:p></span></span></span></i></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; text-decoration: underline;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><span style="box-sizing: border-box;"></span></span></span></span></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; text-decoration: underline;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Lab:<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><span style="box-sizing: border-box;"></span></span></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Step 1: I ran a long running batch from SPID 52 <o:p style="box-sizing: border-box;"></o:p></span></span></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><span style="box-sizing: border-box;">Step 2: Issued a KILL using the command </span><span style="box-sizing: border-box; color: blue; line-height: 15.3333px;">KILL</span><span style="box-sizing: border-box; line-height: 15.3333px;"> 52<o:p style="box-sizing: border-box;"></o:p></span></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><span style="box-sizing: border-box;">Step 3: Here is what I observed from </span><span style="box-sizing: border-box; color: maroon; line-height: 15.3333px;">SP_WHO2</span><span style="box-sizing: border-box; line-height: 15.3333px;"> 52</span><span style="box-sizing: border-box;"><span style="box-sizing: border-box;">:</span></span></span></div>
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="background-color: white; border-collapse: collapse; border-spacing: 0px; box-sizing: border-box; color: #333333; margin: auto auto auto 4.65pt; width: 588px;"><tbody style="box-sizing: border-box;">
<tr style="box-sizing: border-box; height: 15pt;"><td style="background-color: transparent; border: 1pt solid windowtext; box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 48pt;" valign="bottom" width="64"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">SPID<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 109pt;" valign="bottom" width="145"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Status<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 35.2pt;" valign="bottom" width="47"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Login<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 59.05pt;" valign="bottom" width="79"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">HostName<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 36.35pt;" valign="bottom" width="48"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">BlkBy<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 53.55pt;" valign="bottom" width="71"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">DBName<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 100pt;" valign="bottom" width="133"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Command<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td></tr>
<tr style="box-sizing: border-box; height: 15pt;"><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: 1pt solid windowtext; border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 48pt;" valign="bottom" width="64"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">52<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 109pt;" valign="bottom" width="145"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">SUSPENDED<span style="box-sizing: border-box;"> </span><o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 35.2pt;" valign="bottom" width="47"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">abc<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 59.05pt;" valign="bottom" width="79"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">def<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 36.35pt;" valign="bottom" width="48"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">.<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 53.55pt;" valign="bottom" width="71"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">microsoft<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 15pt; padding: 0in 5.4pt; width: 100pt;" valign="bottom" width="133"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">KILLED/ROLLBACK<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td></tr>
</tbody></table>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><span style="box-sizing: border-box;">Step 4: Here is what I got when I ran </span><span style="box-sizing: border-box; color: blue; line-height: 15.3333px;">KILL</span><span style="box-sizing: border-box; line-height: 15.3333px;"> 52 <span style="box-sizing: border-box; color: blue;">WITH</span> STATUSONLY:</span><o:p style="box-sizing: border-box;"></o:p></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><i style="box-sizing: border-box;"><span style="background: silver; box-sizing: border-box; line-height: 15.3333px;">spid 52: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.</span></i><i style="box-sizing: border-box;"><span style="box-sizing: border-box; line-height: 15.3333px;"><o:p style="box-sizing: border-box;"></o:p></span></i></span></div>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><span style="box-sizing: border-box;">Step 5: I tried to run the same statement from a different SPID 53 inside an explicit transaction and issued a ROLLBACK TRAN and this is the status when I ran</span><span style="box-sizing: border-box; color: maroon; line-height: 15.3333px;">SP_WHO2</span><span style="box-sizing: border-box; line-height: 15.3333px;"> 53:<o:p style="box-sizing: border-box;"></o:p></span></span></div>
<table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="background-color: white; border-collapse: collapse; border-spacing: 0px; box-sizing: border-box; color: #333333; margin: auto auto auto 4.65pt; width: 631px;"><tbody style="box-sizing: border-box;">
<tr style="box-sizing: border-box; height: 13.95pt;"><td style="background-color: transparent; border: 1pt solid windowtext; box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 51.5pt;" valign="bottom" width="69"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">SPID<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 117pt;" valign="bottom" width="156"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Status<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 37.8pt;" valign="bottom" width="50"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Login<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 63.4pt;" valign="bottom" width="85"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">HostName<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 39pt;" valign="bottom" width="52"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">BlkBy<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 57.45pt;" valign="bottom" width="77"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">DBName<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: 1pt solid windowtext; box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 107.3pt;" valign="bottom" width="143"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; font-weight: 700;"><span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Command<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></span></div>
</td></tr>
<tr style="box-sizing: border-box; height: 13.95pt;"><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: 1pt solid windowtext; border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 51.5pt;" valign="bottom" width="69"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">53<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 117pt;" valign="bottom" width="156"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">ROLLBACK<span style="box-sizing: border-box;"> </span><span style="box-sizing: border-box;"> </span><o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 37.8pt;" valign="bottom" width="50"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">abc<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 63.4pt;" valign="bottom" width="85"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">def<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 39pt;" valign="bottom" width="52"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">.<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 57.45pt;" valign="bottom" width="77"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">microsoft<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td><td style="background-color: transparent; border-bottom: 1pt solid windowtext; border-left: rgb(212, 208, 200); border-right: 1pt solid windowtext; border-top: rgb(212, 208, 200); box-sizing: border-box; height: 13.95pt; padding: 0in 5.4pt; width: 107.3pt;" valign="bottom" width="143"><div align="center" class="MsoNormal" style="box-sizing: border-box; margin-bottom: 10px;">
<span style="box-sizing: border-box; color: black;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">ROLLBACK TRANSAC<o:p style="box-sizing: border-box;"></o:p></span></span></span></span></div>
</td></tr>
</tbody></table>
<div style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><span style="box-sizing: border-box;">Step 6: Here is the output of </span><span style="box-sizing: border-box; color: blue; line-height: 15.3333px;">KILL</span><span style="box-sizing: border-box; line-height: 15.3333px;"> 53 <span style="box-sizing: border-box; color: blue;">WITH</span> STATUSONLY :</span><o:p style="box-sizing: border-box;"></o:p></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<i style="box-sizing: border-box;"><span style="background: silver; box-sizing: border-box; line-height: 15.3333px;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Msg 6120, Level 16, State 1, Line 1<o:p style="box-sizing: border-box;"></o:p></span></span></i></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><i style="box-sizing: border-box;"><span style="background: silver; box-sizing: border-box; line-height: 15.3333px;"><span style="box-sizing: border-box; font-weight: 700;">Status report cannot be obtained</span>. Rollback operation for Process ID 53 is not in progress.</span></i><i style="box-sizing: border-box;"><span style="box-sizing: border-box; line-height: 15.3333px;"><o:p style="box-sizing: border-box;"></o:p></span></i></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<br /></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">So KILL WITH STATUS ONLY cannot be used to track the progress of ROLLBACK issued by ROLLBACK TRAN. Hope this helps!</span></span></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><br /></span></span></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;"><br /></span></span></span></div>
<div class="MsoNormal" style="background-color: white; box-sizing: border-box; color: #333333; margin-bottom: 10px;">
<span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="font-family: "helvetica neue" , "arial" , "helvetica" , sans-serif;">Reference: <a href="https://blogs.msdn.microsoft.com/sqlsakthi/2011/02/23/difference-between-kill-and-kill-with-statusonly-estimate-rollback-completion-time/">Sakthivel Chidambaram</a></span></span></span></div>
</div>
</div>
SQLismhttp://www.blogger.com/profile/15670165704495360115noreply@blogger.com0tag:blogger.com,1999:blog-925655394200739544.post-73598337508879989822016-09-01T13:58:00.000-07:002016-10-24T08:47:01.362-07:00TempDB usage per active session<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;"><strong style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Introduction</strong></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">From time to time you find yourself needing to shrink some space out of TempDB. Shrinking database files is never my first choice but sometimes it is the best I have. Many people think that you cannot shrink TempDB in SQL 2005, but I am going to show you how.</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;"><strong style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Why would I need to shrink TempDB?</strong></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Yesterday afternoon my pager started going crazy because an Ad-Hoc query that needed some tuning filled TempDB on a server. Luckily, the user only impacted their own query so it was easy to quickly identify them and work with the right people to get the query rewritten.</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Once the immediate problem was resolved there had to be some cleanup. On this server, TempDB has 32 files (1 per processor) all on the same disk. The full database condition caused all kinds of alerts in our monitoring tools, from drive space alerts to too few growths remaining. There were 3 possible solutions to quiet the alerts:</span></div>
<div class="MsoListParagraphCxSpFirst" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-left: 0.5in; padding: 0px; text-indent: -0.25in; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; line-height: 15.3333px; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">1.</span><span style="background: transparent; border: 0px; font-stretch: normal; line-height: normal; margin: 0px; padding: 0px; vertical-align: baseline;"> </span></span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Reboot – There is never a good time to reboot a production server</span></span></div>
<div class="MsoListParagraphCxSpMiddle" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-left: 0.5in; padding: 0px; text-indent: -0.25in; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; line-height: 15.3333px; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">2.</span><span style="background: transparent; border: 0px; font-stretch: normal; line-height: normal; margin: 0px; padding: 0px; vertical-align: baseline;"> </span></span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Turn off the Alerts – Not really an option. My preference would be for increasing the sensitivity</span></span></div>
<div class="MsoListParagraphCxSpLast" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; margin-left: 0.5in; padding: 0px; text-indent: -0.25in; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; line-height: 15.3333px; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">3.</span><span style="background: transparent; border: 0px; font-stretch: normal; line-height: normal; margin: 0px; padding: 0px; vertical-align: baseline;"> </span></span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Shrink TempDB – Not a great option, but the best of the 3</span></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;"><strong style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Shrinking TempDB</strong></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">Once we had decided that we would go ahead and shrink the files in TempDB it seemed like the hard part was done, but after r</span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">unning the following command:</span></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; color: blue; margin: 0px; padding: 0px; vertical-align: baseline;">USE</span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;"> [tempdb]</span></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; color: blue; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">GO</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; color: blue; margin: 0px; padding: 0px; vertical-align: baseline;">DBCC</span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;"> SHRINKFILE<span style="background: transparent; border: 0px; color: blue; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span style="background: transparent; border: 0px; color: grey; margin: 0px; padding: 0px; vertical-align: baseline;">(</span><span style="background: transparent; border: 0px; color: red; margin: 0px; padding: 0px; vertical-align: baseline;">N’tempdev’</span> <span style="background: transparent; border: 0px; color: grey; margin: 0px; padding: 0px; vertical-align: baseline;">,</span> 5000<span style="background: transparent; border: 0px; color: grey; margin: 0px; padding: 0px; vertical-align: baseline;">)</span></span></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; color: blue; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; line-height: 15.3333px; margin: 0px; padding: 0px; vertical-align: baseline;">GO</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">I got back the following:</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">DBCC SHRINKFILE: Page 1:878039 could not be moved because it is a work file page.</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">—— ———– ———– ———– ———– ————–</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">2 1 878040 640000 4672 4672</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">(1 row(s) affected)</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">DBCC execution completed. If DBCC printed error messages, contact your system administrator.</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">“Page could not be moved because it is a work file page.”…grrr. This is a new thing in SQL 2005 caused by the caching that is done in TempDB. I am not going to try to explain here how objects are cached in TempDB, but Kalen Delaney’s Inside Sql Server Series is a great place to learn about it if you are interested (</span><a href="http://www.insidesqlserver.com/books.html" style="background: transparent; border: 0px; color: #743399; margin: 0px; padding: 0px; vertical-align: baseline;"><span style="background: transparent; border: 0px; color: purple; margin: 0px; padding: 0px; vertical-align: baseline;">http://www.insidesqlserver.com/books.html</span></a><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;">). What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files.</span></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">Trying again:</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; color: blue; margin: 0px; padding: 0px; vertical-align: baseline;">DBCC</span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;"> FREEPROCCACHE</span></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; color: blue; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">GO</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; color: blue; margin: 0px; padding: 0px; vertical-align: baseline;">USE</span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;"> [tempdb]</span></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; color: blue; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">GO</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><span style="background: transparent; border: 0px; color: blue; margin: 0px; padding: 0px; vertical-align: baseline;">DBCC</span><span style="background: transparent; border: 0px; margin: 0px; padding: 0px; vertical-align: baseline;"> SHRINKFILE<span style="background: transparent; border: 0px; color: blue; margin: 0px; padding: 0px; vertical-align: baseline;"> </span><span style="background: transparent; border: 0px; color: grey; margin: 0px; padding: 0px; vertical-align: baseline;">(</span><span style="background: transparent; border: 0px; color: red; margin: 0px; padding: 0px; vertical-align: baseline;">N’tempdev’</span> <span style="background: transparent; border: 0px; color: grey; margin: 0px; padding: 0px; vertical-align: baseline;">,</span> 5000<span style="background: transparent; border: 0px; color: grey; margin: 0px; padding: 0px; vertical-align: baseline;">)</span></span></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; color: blue; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; line-height: 15.3333px; margin: 0px; padding: 0px; vertical-align: baseline;">GO</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">This time it worked:</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">DBCC execution completed. If DBCC printed error messages, contact your system administrator.</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">—— ———– ———– ———– ———– ————–</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">2 1 640000 640000 264 264</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">(1 row(s) affected)</span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; padding: 0px; vertical-align: baseline;">
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span></div>
<div class="MsoNormal" style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 10pt; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; line-height: 12.2667px; margin: 0px; padding: 0px; vertical-align: baseline;">DBCC execution completed. If DBCC printed error messages, contact your system administrator.</span></div>
<div style="background: rgb(255, 255, 255); border: 0px; color: #333333; line-height: 24px; margin-bottom: 24px; padding: 0px; vertical-align: baseline;">
<span style="background: transparent; border: 0px; font-family: Helvetica Neue, Arial, Helvetica, sans-serif; margin: 0px; padding: 0px; vertical-align: baseline;">I think I got lucky that the shrink worked on the first try. There will certainly be times when you have to try freeing the procedure cache and shrinking multiple times to get a file to shrink, but eventually it will get the job done.</span></div>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">Tempdb is hard to shrink. Some pages cannot be moved because they are actively being used by system processes, so your chances of shrinking tempdb sink.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">I see that you already have tried clearing all the caches, which sometimes helps, but is not guaranteed to work.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">Try to identify what is using tempdb:</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">http://www.sqlservercentral.com/scripts/tempdb/72007/</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">Even if usage is low, a single non movable page is enough to make the shrink process ineffective.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">If you can't shrink it, I suggest that you plan a downtime to restart the service and let tempdb restart from its initial size.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">Lists the TempDB usage per each active session.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">It helps identifying the sessions that use the tempdb heavily with internal objects.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom of</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> an inefficient plan or a missing index.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">The only possible alternative to restarting the service, is running DBCC FREESYSTEMCACHE('ALL'), that will clear all cached objects,</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> including not only internal objects, but also cached query plans. Use it carefully on a production server.</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">;WITH task_space_usage AS (</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> -- SUM alloc/delloc pages</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> SELECT session_id,</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> request_id,</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> SUM(internal_objects_alloc_page_count) AS alloc_pages,</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> SUM(internal_objects_dealloc_page_count) AS dealloc_pages</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> FROM sys.dm_db_task_space_usage WITH (NOLOCK)</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> WHERE session_id <> @@SPID</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> GROUP BY session_id, request_id</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">)</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">SELECT TSU.session_id,</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> EST.text,</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> -- Extract statement from sql text</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ISNULL(</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> NULLIF(</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> SUBSTRING(</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> EST.text,</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ERQ.statement_start_offset / 2,</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ), ''</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ), EST.text</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ) AS [statement text],</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> EQP.query_plan</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">FROM task_space_usage AS TSU</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> ON TSU.session_id = ERQ.session_id</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"> AND TSU.request_id = ERQ.request_id</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">ORDER BY 3 DESC, 5 DESC</span><br />
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;"><br /></span>
<span style="font-family: Helvetica Neue, Arial, Helvetica, sans-serif;">Reference: http://adventuresinsql.com/2009/12/how-to-shrink-tempdb-in-sql-2005/</span><br />
<div>
<br /></div>
</div>
Unknownnoreply@blogger.com0