SQL Server Performance

Tracking Growth - MOSS 2007

Discussion in 'SQL Server 2005 General Developer Questions' started by St3ve, Jun 19, 2008.

  1. St3ve New Member

    <P mce_keep="true">Hi All, </P><P mce_keep="true">We have a MOSS 2007 and a number of distributed WSS3 Installations and I'm trying to track how the data is growing. I have a daily job which cycles around all the databases (Approx 340 in total distributed over 17 sites) which pulls back the ServerName, ContentDB Name, SiteURL, RecycleBin Size, Site Size (Inc BLOB Data). </P><P mce_keep="true">I can see how this is growing if I create a SSRS report, but one of my axis is un-readable due to the number of dates in the record set, so I'm trying to create a query/report that will&nbsp;show this, but on a monthly basis. My existing report looks like this:</P><P mce_keep="true"><IMG style="WIDTH: 800px; HEIGHT: 353px" height=353 src="http://i55.photobucket.com/albums/g145/St3veMax/SSRSReport.jpg" width=800 align=left mce_src="http://i55.photobucket.com/albums/g145/St3veMax/SSRSReport.jpg"></P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">&nbsp;</P><P mce_keep="true">So far I have the following query:</P><FONT color=#0000ff size=2><P>WITH</FONT><FONT size=2> CTE </FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>ServerName</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> ContentDB</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> Total</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> Y</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> M</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> D</FONT><FONT color=#808080 size=2>)</P></FONT><FONT color=#0000ff size=2><P>AS</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>(</P></FONT><FONT size=2><P></FONT><FONT color=#0000ff size=2>SELECT</P></FONT><FONT size=2>ServerName </FONT><FONT color=#0000ff size=2>AS</FONT><FONT size=2> [Server Name]</FONT></FONT><FONT size=2> <P></FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>ContentDB</P></FONT><FONT color=#808080 size=2>,</FONT><FONT color=#ff00ff size=2>SUM</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>Site</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#808080 size=2>+</FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2>SUM</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>RecycleBin</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>AS</FONT><FONT size=2> [Total]</FONT></FONT><FONT size=2> <P></FONT><FONT color=#808080 size=2>,</FONT><FONT color=#ff00ff size=2>YEAR</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>InsertionDate</FONT><FONT color=#808080 size=2>)</P></FONT><FONT size=2><P></FONT><FONT color=#808080 size=2>,</FONT><FONT color=#ff00ff size=2>MONTH</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>InsertionDate</FONT><FONT color=#808080 size=2>)</P></FONT><FONT size=2><P></FONT><FONT color=#808080 size=2>,</FONT><FONT color=#ff00ff size=2>DAY</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>InsertionDate</FONT><FONT color=#808080 size=2>)</P></FONT><FONT size=2></FONT><FONT color=#0000ff size=2>FROM</FONT><FONT size=2> </FONT></FONT><FONT size=2><P>DBAdmin</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>MSX</FONT><FONT color=#808080 size=2>.</FONT><FONT size=2>tbl_SharePointUsage </P></FONT><FONT color=#0000ff size=2>WHERE</FONT><FONT size=2> </FONT></FONT><FONT size=2><P>SiteURL </FONT><FONT color=#808080 size=2>&lt;&gt;</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>''</P></FONT><FONT size=2><P></FONT><FONT color=#808080 size=2>AND</P></FONT><FONT size=2><P>Site </FONT><FONT color=#808080 size=2>&lt;&gt;</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'0.00'</P></FONT><FONT size=2><P></FONT><FONT color=#808080 size=2>AND</P></FONT><FONT size=2><P>ServerName </FONT><FONT color=#808080 size=2>=</FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2>'&lt;ServerName&gt;'</P></FONT><FONT size=2><P></FONT><FONT color=#0000ff size=2>GROUP</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>BY</P></FONT><FONT size=2>ServerName</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> ContentDB</FONT><FONT color=#808080 size=2>,</FONT><FONT size=2> InsertionDate</FONT></FONT><FONT size=2> <P></FONT><FONT color=#808080 size=2>)</P></FONT><FONT color=#0000ff size=2><P>SELECT</P></FONT><FONT size=2><P>Y </P></FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>M </FONT></FONT><FONT size=2><P></FONT><FONT color=#808080 size=2>,</FONT><FONT color=#ff00ff size=2>SUM</FONT><FONT color=#808080 size=2>(</FONT><FONT size=2>Total</FONT><FONT color=#808080 size=2>)</FONT><FONT size=2> </P></FONT><FONT color=#0000ff size=2>FROM</FONT><FONT size=2> </FONT></FONT><FONT size=2><P>CTE </P></FONT><FONT color=#0000ff size=2><P>GROUP</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>BY</P></FONT><FONT size=2><P>Y</P><P></FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>M</P></FONT><FONT color=#0000ff size=2><P>ORDER</FONT><FONT size=2> </FONT><FONT color=#0000ff size=2>BY</P></FONT><FONT size=2><P>Y </FONT><FONT color=#0000ff size=2>ASC</P></FONT><FONT size=2><P></FONT><FONT color=#808080 size=2>,</FONT><FONT size=2>M </FONT><FONT color=#0000ff size=2>ASC</FONT></P><P><FONT color=#0000ff size=2>The results I'm getting are as follows:</FONT></P><P>2007&nbsp; 11&nbsp; 5710948.00<BR>2007&nbsp; 12&nbsp; 16997857.00<BR>2008&nbsp; 1&nbsp;&nbsp;&nbsp; 19409679.00<BR>2008&nbsp; 2&nbsp;&nbsp; &nbsp;22635180.00<BR>2008&nbsp; 3&nbsp;&nbsp;&nbsp; 25452520.00<BR>2008 &nbsp;4&nbsp;&nbsp;&nbsp; 26053560.00<BR>2008 &nbsp;5&nbsp;&nbsp;&nbsp; 29210482.00<BR>2008&nbsp; 6&nbsp;&nbsp;&nbsp; 18570304.00&nbsp;</P><P><FONT color=#0000ff size=2>The trouble with the above is that it's Sum'ing the entire month's values, but I only want the last day's value from each month.</FONT></P><P><FONT color=#0000ff size=2>I'm having one of those moments where I cant see the wood for the tree's!!!</FONT></P><P><FONT color=#0000ff size=2></FONT><FONT color=#0000ff size=2>Any help would be appreciated!</FONT></P><P><FONT color=#0000ff size=2>Steve</P></FONT>

Share This Page