Search results matching tags 'DBA', 'Metadata', and 'Maintenance'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=DBA,Metadata,Maintenance&orTags=0Search results matching tags 'DBA', 'Metadata', and 'Maintenance'en-USCommunityServer 2.1 SP2 (Build: 61129.1)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>