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 heres 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]
josef.nielsen