Finding Size Information for the Content of your WSS 3.0 Webs/Lists

Today I had to hack out a T-SQL query to determine information regarding the size of indiviual lists and libraries inside of a single site collection (one content DB). I used this query and then pulled it into an Excel workbook and used pivot tables to aggregate the data.

USE [WSS_Content]
GO

SELECT
    [dbo].[Webs].[FullUrl]
   ,[dbo].[Lists].[tp_Title] AS "ListName"
   ,[dbo].[Docs].[DirName]
      ,[dbo].[Docs].[LeafName]
      ,[dbo].[Docs].[Size]
      ,[dbo].[Docs].[MetaInfoSize]
      ,[dbo].[Docs].[Version]
      ,[dbo].[Docs].[TimeCreated]
      ,[dbo].[Docs].[TimeLastModified]
      ,[dbo].[Docs].[MetaInfoTimeLastModified]
      ,[dbo].[Docs].[CheckoutUserId]
      ,[dbo].[Docs].[CheckoutDate]
      ,[dbo].[Docs].[ExtensionForFile]
  
  FROM [WSS_Content].[dbo].[Docs]
  INNER JOIN [WSS_Content].[dbo].[Webs] ON [dbo].[Webs].[Id] = [dbo].[Docs].[WebId]
  INNER JOIN [WSS_Content].[dbo].[Lists] ON [dbo].[Lists].[tp_ID] = [dbo].[Docs].[ListId]
  
  WHERE [dbo].[Docs].[Size] > 0     
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.stp')   
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.aspx')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.xfp')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.dwp')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%template%')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.inf')
  AND ([dbo].[Docs].[LeafName] NOT LIKE '%.css')

PowerShell Script to Monitor the Status of a SQL Job

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$server = new-object "Microsoft.SqlServer.Management.Smo.Server" "localhost"
$job = $server.JobServer.Jobs["Test Job"]
$now = Get-Date
do
{
 Start-Sleep -Seconds 1
 $job | select Name,CurrentRunStatus,LastRunDate
 $job.Refresh()
}
while($job.LastRunDate -lt $now)
$job | select Name,CurrentRunStatus,LastRunDate