Search results matching tags 'Maintenance' and 'metadata'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Maintenance,metadata&orTags=0Search results matching tags 'Maintenance' and 'metadata'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Using the catalog views : clearing out a &quot;botched&quot; schemahttp://sqlblog.com/blogs/aaron_bertrand/archive/2011/07/07/using-the-catalog-views-clearing-out-a-botched-schema.aspxThu, 07 Jul 2011 15:16:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:36703AaronBertrand<p>A user on StackOverflow had populated a custom schema with a bunch of tables, and needed to replace them with a new set of tables due to various changes. But he couldn't just drop them all in any arbitrary order, because there were foreign keys in place. His workaround was to just run a "drop all tables" script multiple times until there were no more errors. As you can imagine, on a complicated enough schema, this can be really tedious. Even on a small schema, this just doesn't feel like the right way.<br><br>Let's assume the following case:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">USE </font><font color="black">[tempdb]</font><font color="gray">;<br></font><font><font><font><font color="blue">GO</font></font></font></font><br><font color="black"><br></font><font color="blue">CREATE SCHEMA </font><font color="black">[blat] </font><font color="blue">AUTHORIZATION </font><font color="black">[dbo]</font><font color="gray">;<br></font><font><font><font><font color="blue">GO</font></font></font></font><br><font color="black"><br></font><font color="blue">CREATE TABLE </font><font color="black">[blat].A<br></font><font color="gray">(<br>&nbsp;&nbsp; </font><font color="black">id </font><font color="blue">INT PRIMARY KEY<br></font><font color="gray">);<br><br></font><font color="blue">CREATE TABLE </font><font color="black">[blat].B<br></font><font color="gray">(<br>&nbsp;&nbsp; </font><font color="black">id </font><font color="blue">INT PRIMARY KEY FOREIGN KEY <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES </font><font color="black">[blat].A</font><font color="gray">(</font><font color="black">id</font><font color="gray">)<br>);<br><br></font><font color="blue">CREATE TABLE </font><font color="black">[blat].C<br></font><font color="gray">(<br>&nbsp;&nbsp; </font><font color="black">id </font><font color="blue">INT FOREIGN KEY <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES </font><font color="black">[blat].B</font><font color="gray">(</font><font color="black">id</font><font color="gray">)<br>);<br><br></font><font color="green">-- let's also create a table in another schema<br>-- that points at tables in this schema:<br><br></font><font color="blue">CREATE TABLE </font><font color="black">[dbo].D<br></font><font color="gray">(<br>&nbsp;&nbsp; </font><font color="black">id1 </font><font color="blue">INT FOREIGN KEY <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES </font><font color="black">[blat].A</font><font color="gray">(</font><font color="black">id</font><font color="gray">),<br>&nbsp;&nbsp; </font><font color="black">id2 </font><font color="blue">INT FOREIGN KEY <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES </font><font color="black">[blat].B</font><font color="gray">(</font><font color="black">id</font><font color="gray">)<br>);<br></font><font><font color="blue">GO</font></font>
</pre></td></tr></table></blockquote>
<p>Initially if you try this:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">DROP TABLE </font><font color="black">[blat].A</font><font color="gray">;</font>
</pre></td></tr></table></blockquote>
You get this error message:
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<div style="color:red;padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;">
Msg 3726, Level 16, State 1, Line 1<br>Could not drop object 'blat.A' because it is referenced by a FOREIGN KEY constraint. <br></div>
</td></tr></table></blockquote>
<p>So I put together a quick script that would allow you to drop all of the tables in the schema, by first removing all of the foreign key constraints. In this case order within each set of statements is unimportant, so we can use lazy string concatenation instead of more complicated FOR XML queries that allow you to dictate order:</p>
<blockquote>
<table bgcolor="#eeeeee" cellpadding="0" cellspacing="0">
<tr>
<td>
<pre style="padding:10px 20px;font-size:12px;font-family:consolas,lucida console,courier new,courier;-moz-background-inline-policy:continuous;"><font color="blue">DECLARE <br>&nbsp;&nbsp; </font><font color="#434343">@schema_to_empty </font><font color="blue">SYSNAME </font><font color="gray">=</font><font><font color="#434343"></font></font><font color="blue"> </font><font color="red">N'blat'</font><font color="gray">,<br>&nbsp;&nbsp; </font><font color="#434343">@sql </font><font color="blue"> NVARCHAR</font><font color="gray">(</font><font color="magenta">MAX</font><font color="gray">) </font><font color="gray">=</font><font><font color="#434343"></font></font><font color="blue"> </font><font color="red">N''</font><font color="gray">;<br><br></font><font color="green">-- drop all references to tables in the blat schema,<br>-- even FKs on tables in other schemas.<br><br></font><font color="blue">SELECT </font><font color="#434343">@sql </font><font color="gray">+=</font><font color="blue"> </font><font color="red">N'<br> ALTER TABLE '<br>&nbsp;&nbsp; </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font><font color="magenta">OBJECT_SCHEMA_NAME</font></font><font color="gray">(</font><font color="black">k.parent_object_id</font><font color="gray">))<br>&nbsp;&nbsp; + </font><font color="red">'.' </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="magenta">OBJECT_NAME</font><font color="gray">(</font><font color="black">k.parent_object_id</font><font color="gray">))<br>&nbsp;&nbsp; + </font><font color="red">' DROP CONSTRAINT ' </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="black">k.[name]</font><font color="gray">) + </font><font color="red">';'<br></font><font color="blue">FROM </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">foreign_keys</font></font><font color="black"> </font><font color="blue">AS </font><font color="black">k<br></font><font color="blue">INNER JOIN </font><font><font color="green">sys</font></font><font color="black">.</font><font><font color="green">tables</font></font><font color="black"> </font><font color="blue">AS </font><font color="black">t<br></font><font color="blue">ON </font><font color="black">k.referenced_object_id </font><font color="gray">=</font><font color="black"> </font><font color="black">t.[object_id]<br></font><font color="blue">WHERE </font><font color="black">t.[schema_id] </font><font color="gray">=</font><font color="black"> </font><font><font color="magenta">SCHEMA_ID</font></font><font color="gray">(</font><font color="#434343">@schema_to_empty</font><font color="gray">);<br><br></font><font color="green">-- then drop all the tables.<br><br></font><font color="blue">SELECT </font><font color="#434343">@sql </font><font color="gray">+=</font><font color="blue"> </font><font color="red">N'<br> DROP TABLE ' <br>&nbsp;&nbsp; </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="#434343">@schema_to_empty</font><font color="gray">) + </font><font color="red">'.'<br>&nbsp;&nbsp; </font><font color="gray">+ </font><font color="magenta">QUOTENAME</font><font color="gray">(</font><font color="black">[name]</font><font color="gray">) + </font><font color="red">';'<br>&nbsp;&nbsp; </font><font color="blue">FROM </font><font><font color="green">sys</font></font><font color="black">.</font><font color="black"><font color="green">tables</font><br>&nbsp;&nbsp; </font><font color="blue">WHERE </font><font color="black">[schema_id]</font><font color="black"> </font><font color="gray">=</font><font color="blue"> </font><font><font color="magenta">SCHEMA_ID</font></font><font color="gray">(</font><font color="#434343">@schema_to_empty</font><font color="gray">);<br><br></font><font><font color="green">-- if the output is &lt; 8K, you can inspect it using PRINT:<br></font></font><br><font color="blue">PRINT </font><font color="#434343">@sql</font><font color="gray">;<br><br></font><font color="green">-- in case it's too big for PRINT (&gt; 8K), but still less than 64K<br>-- you can run this in grid mode, click on the output, and copy the<br>-- script from the new window that is created:<br><br>-- SELECT CONVERT(XML, @sql);<br><br></font><font><font color="green">-- or you can just trust me and run it, MWAHAHAHAHA!:<br><br></font></font><font color="green">-- EXEC sp_executeSQL @sql;</font>
</pre></td></tr></table></blockquote>
<p>You may also want / need to clear out other objects, such as views that reference objects in this schema, or triggers on tables in other schemas that reference objects in this schema. Those bring about other complications I'll address in a future post, if there is enough interest. </p>
<p>In the meantime, wouldn't it be great if we had CREATE OR REPLACE functionality that would, in addition to preventing us from having to drop all the objects first or write complex existence checks and alter commands, also sever these dependencies for us? You can vote for this functionality at <a href="http://connect.microsoft.com/SQLServer/feedback/details/127219/create-or-replace" title="http://connect.microsoft.com/SQLServer/feedback/details/127219/create-or-replace" target="_blank">Connect #127219</a>. </p>
<p>It would also be nice to have a DROP SCHEMA command that did the same type of thing as I manually do above. Instead of just complaining that the schema is not empty, I'd love to see a DROP SCHEMA ... WITH FORCE option, or something similar, where it took care of all the objects for you - much like DROP DATABASE does. Of course this would be easy to implement in a simple case, but not when schemas have cross-dependencies and references that are embedded in module definitions rather than straightforward DRI and similar constructs.</p><p>&nbsp; <br></p>Backup Meta-Datahttp://sqlblog.com/blogs/buck_woody/archive/2010/03/30/backup-meta-data.aspxTue, 30 Mar 2010 14:46:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:23870BuckWoody<P>I'm working on a PowerShell script to show me the trending durations of my backup activities. The first thing I need is the data, so I looked at the Standard Reports in SQL Server Management Studio, and found a report that suited my needs, so I pulled out the script that it runs and modified it to this T-SQL Script. </P>
<P>A few words here - you need to be in the MSDB database for this to run, and you can add a WHERE clause to limit to a database, timeframe, type of backup, whatever. For that matter, I won't use all of the data in this query in my PowerShell script, but it gives me lots of avenues to graph:</P>
<P><FONT color=#009900>SELECT distinct t1.name AS 'DatabaseName'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,(datediff( ss,<SPAN style="mso-spacerun:yes;">&nbsp; </SPAN>t3.backup_start_date, t3.backup_finish_date)) AS 'DurationInSeconds'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,t3.user_name AS 'UserResponsible'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,t3.name AS backup_name</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,t3.description</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,t3.backup_start_date</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,t3.backup_finish_date</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,CASE WHEN t3.type = 'D' THEN 'Database'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>WHEN t3.type = 'L' THEN 'Log'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>WHEN t3.type = 'F' THEN 'FileOrFilegroup'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>WHEN t3.type = 'G' THEN 'DifferentialFile'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>WHEN t3.type = 'P' THEN 'Partial'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>WHEN t3.type = 'Q' THEN 'DifferentialPartial'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>END AS 'BackupType'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,t3.backup_size AS 'BackupSizeKB' </FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,t6.physical_device_name</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,CASE WHEN t6.device_type = 2 THEN 'Disk'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>WHEN t6.device_type = 102 THEN 'Disk'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>WHEN t6.device_type = 5 THEN 'Tape'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>WHEN t6.device_type = 105 THEN 'Tape'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>END AS 'DeviceType'</FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>,t3.recovery_model<SPAN style="mso-spacerun:yes;">&nbsp; </SPAN></FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>FROM sys.databases t1 </FONT></P>
<P style="MARGIN:0in 0in 0in 0.375in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>INNER JOIN backupset t3 </FONT></P>
<P style="MARGIN:0in 0in 0in 0.75in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>ON (t3.database_name = t1.name )<SPAN style="mso-spacerun:yes;">&nbsp; </SPAN></FONT></P>
<P style="MARGIN:0in 0in 0in 0.375in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>LEFT OUTER JOIN backupmediaset t5 </FONT></P>
<P style="MARGIN:0in 0in 0in 0.75in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>ON ( t3.media_set_id = t5.media_set_id ) </FONT></P>
<P style="MARGIN:0in 0in 0in 0.375in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>LEFT OUTER JOIN backupmediafamily t6 </FONT></P>
<P style="MARGIN:0in 0in 0in 0.75in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>ON ( t6.media_set_id = t5.media_set_id ) </FONT></P>
<P style="MARGIN:0in;FONT-FAMILY:Calibri;FONT-SIZE:11pt;"><FONT color=#009900>ORDER BY backup_start_date DESC</FONT></P>
<P>I'll munge this into my Excel PowerShell chart script tomorrow.</P>
<P style="MARGIN:0in 0in 0pt;mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;" class=MsoNormal><FONT face=Calibri><B><I style="mso-bidi-font-style:normal;"><SPAN style="COLOR:maroon;FONT-SIZE:10pt;mso-bidi-font-size:11.0pt;mso-bidi-font-family:Calibri;mso-ansi-language:EN;">Script Disclaimer, for people who need to be told this sort of thing: </SPAN></I></B><B><I style="mso-bidi-font-style:normal;"><SPAN style="COLOR:maroon;FONT-SIZE:10pt;mso-bidi-font-family:Calibri;mso-ansi-language:EN;"><o:p></o:p></SPAN></I></B></FONT></P>
<P style="MARGIN:0in 0in 0pt;" class=MsoNormal><I style="mso-bidi-font-style:normal;"><SPAN style="COLOR:maroon;FONT-SIZE:10pt;mso-bidi-font-family:Calibri;mso-ansi-language:EN;"><FONT face=Calibri>Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea&nbsp;during the operation of this script, see a physician immediately.</FONT></SPAN></I></P>Tracking SQL Server Timehttp://sqlblog.com/blogs/buck_woody/archive/2010/01/25/tracking-sql-server-time.aspxMon, 25 Jan 2010 14:10:28 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:21420BuckWoody<p>In the past few blog posts I’ve showed you how to use several methods to track things in SQL Server. You can use the “tags” to the right of this post here at this site to list things like PowerShell, Performance Tuning and so on. Now that you’re armed with these tools, what should you track?</p> <p>Well, one of the items I track is <em>time</em>. I track the time it takes for lots of things, but they fall into three general buckets:</p> <ol> <li>Queries – Normally I track the five longest running queries with their query plans. </li> <li>Maintenance – From how long each backup takes to index reorgs and rebuilds, I want to know how long these things take.</li> <li>Jobs – Most all of us have SQL Server Agent Jobs, and developing a schedule of how long they are running is very useful.</li> </ol> <p>For each of these, I track the minimum, maximum and average times. I look for outliers – things that suddenly change and so on.</p> <p>There are a lot of uses for this information. From performance tuning to developing a recovery plan, all of these actions need to be taken into account.</p> <p>I think I’ll write up an article sometime on how I do this – it’s a little long for a blog post.</p>