Details and manual monitoring of the Content DBs

So our Ad-Hoc environment started growing a bit faster than we had originally anticipated.  We knew we would have some disparity between site's content size, so we set our content DB max site limits a bit lower than normal (hey, we have 50 of them, so we thought we'd be safe).  The thought was that way we could help balance the sizing by adjusting the max counts on the DB's to reflect the physical size based on content.

It quickly became way to much of a pain to manually collate the size details with the site details based on content DB...  So here's a little script I wrote up (ok, modified from my original Site Details script) that pulls all the goodness you could want about a content DB direct from the DB itself... <Insert canned warnings about how MS does not recommend querying the DB directly here>

 

/*
ContentDBReport.sql
written by Josef Nielsen
October 2007 

NOTE: You must create a linked server if you use multiple SQL server to house you content DBs
*/  

BEGIN
DECLARE @ts1 varchar(1000), @ConfigDB VARCHAR(128) 

-- Set your Config DB Name here if it is different
SET @ConfigDB = 'SharePoint_Config'

-- This creates a temp table to hold the list of content DBs referenced by the Config DB
CREATE TABLE [#TempDbList]
(
DBname VARCHAR(128),
DBInstance VARCHAR(128),
DBServer VARCHAR(128),
MaxSites INT,
WarnSites INT
)

-- Populate the temp table with content DBs
SET @ts1 = 'INSERT INTO #TempDbList
SELECT [DbName].[Name] AS ''DatabaseName'',
[Instance].[Name] AS ''DatabaseInstance'',
[Server].[Name] AS ''DatabaseServer'',
CONVERT(XML, [DbName].[properties]).value (''(/object/sFld/text())[1]'', ''int'') AS ''MaxSites'',
CONVERT(XML, [DbName].[properties]).value (''(/object/sFld/text())[2]'', ''int'') AS ''WarnSites''
FROM '+'['+@ConfigDB+']'+'.[dbo].[Objects] AS [DbName]
LEFT JOIN '+'['+@ConfigDB+']'+'.[dbo].[Objects] AS [Instance]
ON [DbName].[ParentId] = [Instance].[ID]
LEFT JOIN '+'['+@ConfigDB+']'+'.[dbo].[Objects] AS [Server]
ON [Instance].[ParentId] = [Server].[Id]
WHERE [DbName].[Properties] LIKE ''%SPContentDatabase%''
AND [DbName].[Properties] NOT LIKE ''%WebApplication%'''

EXEC (@ts1)

DECLARE @ts2 VARCHAR(1000)

--This creates a temp table to hold the end results of the Site Collection lists from all Content DBs
CREATE TABLE [#TempContentDbList]
(
WebApp VARCHAR(128),
DBServer VARCHAR(128),
DBName VARCHAR(128),
DBSites int,
DBWarnSites int,
DBMaxSites int,
DBSize float
)

-- Create a cursor to walk through each content DB
DECLARE DB_cursor CURSOR
FOR
SELECT [DBServer], [DBInstance], [DBName], [MaxSites], [WarnSites]
FROM [#TempDbList] 

OPEN DB_Cursor

-- Declare Variables to populate by Cursor
DECLARE @vDBServer VARCHAR(128)
DECLARE @vDBInstance VARCHAR(128)
DECLARE @vDBName VARCHAR(128)
DECLARE @vMaxSites INT
DECLARE @vWarnSites INT
DECLARE @DBv1 VARCHAR(5000) 

FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName, @vMaxSites, @vWarnSites
WHILE @@FETCH_STATUS = 0
BEGIN 

-- Add a backslash for DBServers that are not default instances
DECLARE @slash VARCHAR(5)
IF @vDBInstance = ''
SET @slash = ''
ELSE
SET @slash = '\'

-- Script to insert Content DB details to the temp site summery table
SET @DBv1 = 'INSERT INTO [#TempContentDbList]
SELECT [ConfigObjects].[Name] AS ''WebApp'',
(SELECT ''' + @vDBServer+@slash+@vDBInstance + ''') AS ''SQL Server'',
(SELECT ''' + @vDBName + ''') AS ''Content DB Name'',
(SELECT COUNT([Webs].[Title])) AS ''Current Site Count'',
(SELECT ' + CONVERT(VARCHAR(20), @vMaxSites) + ') AS ''Max Site Count'',
(SELECT ' + CONVERT(VARCHAR(50),@vWarnSites) + ') AS ''Site Size'',
(SELECT round(sum(convert(float,[size])*8/1024),2) AS ''DB Size in MB''
FROM
[sys].[master_files]
WHERE
[state] = 0
AND [data_space_id] = 1
AND db_name([database_id]) = ''' + @vDBName + '''
GROUP BY [database_id]) AS ''Site Size''
FROM
['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].[dbo].[sites] AS [Sites] WITH (NOLOCK)
LEFT JOIN ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].[dbo].[webs] AS [Webs] WITH (NOLOCK) ON [Webs].[siteID] = [Sites].[Id]
LEFT JOIN '+'['+@ConfigDB+']'+'.[dbo].[SiteMap] AS [ConfigSiteMap] WITH (NOLOCK) ON [ConfigSiteMap].[Id] = [Sites].[Id]
LEFT JOIN '+'['+@ConfigDB+']'+'.[dbo].[Objects] AS [ConfigObjects] WITH (NOLOCK) ON [ConfigSiteMap].[ApplicationID] = [ConfigObjects].[Id]
WHERE
[Webs].[ParentWebId] IS NULL
GROUP BY [ConfigObjects].[Name]' 

EXEC (@DBv1)

FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName, @vMaxSites, @vWarnSites

END

CLOSE DB_cursor
DEALLOCATE DB_Cursor

END

-- Cursor is closed and released, and now we select the results of the scan
SELECT * FROM #TempContentDbList ORDER BY [WebApp], [DBName]

GO

-- Clean up to get rid of those temp tables
DROP TABLE [#TempDbList]
DROP TABLE [#TempContentDbList]