This week I have been looking at the usage of a large number of SharePoint content databases. By *large* I mean LARGE. Over 300 spread over a number of clustered instances.
“Why should we care?” I hear you asking, “sounds like a normal day at the SharePoint office to me…”
You’re correct, pretty normal, but I’m not spending quite so much time in the weeds of what’s beneath SharePoint recently and so coming back to some mundane DBA stuff was rather refreshing.
I needed a quick way of grabbing some data about the databases as there were some inconsistencies believed to exist in terms of sizing, recovery models, usage and other aspects of the databases. Historically cursors have been the way to get information about all the databases on a given instance. They’re simple, effective and easy to construct, but at a cost.
From a resource perspective, cursors can be quite expensive and running queries repeatedly within cursors across all databases (effectively looping as a “For…Each” through the instance) had the potential for service impact which was not acceptable to the customer.
Enter Dynamic SQL, the “new” (yeah, I know. It’s been around for years…) and “correct” way of doing this kind of thing which can be likened to passing parameters into a PowerShell function that is being repeatedly called.
By no stretch am I am SQL Server wizard but I have been around it long enough to *almost* know my way around the system tables,views and DMV constructs so knew that I could dig what I needed out of what was already in place and stuff it into a temp table. Wrapping the query as Dynamic SQL which executes against the sys.databases catalogue view allows me to wrap my query as Dynamic SQL to execute on a per database basis. Simples!
What is interesting here is that there really is no SQL magic here. It’s simple stuff. I’ve made it seemingly more complex as I want to display the information in a ready to use way so have added some clutter in the form of logic (to determine if files are database or log files for instance) and math (to make things render as MB as opposed to the OOTB 8k pages that SQL Server works in).
Enough of the narrative, let’s look at what’s what.
IF OBJECT_ID('tempdb..#spacecalc') IS NOT NULL DROP TABLE #spacecalc
CREATE TABLE #spacecalc (DatabaseName SYSNAME, FileType NVARCHAR(8), FileID NVARCHAR(8), TotalSizeMB BIGINT, UsedMB BIGINT, FreeMB BIGINT, PercentFree BIGINT, RecoveryModel NVARCHAR(8), AutoGrow NVARCHAR(12), GrowRate NVARCHAR(12))
DECLARE @SQL NVARCHAR(max)
SELECT @SQL =
'USE ' + QUOTENAME(Name) + '
INSERT INTO #spacecalc
WHEN sysfiles.groupid = 1 THEN ''Database''
WHEN sysfiles.groupid = 0 THEN ''LogFile''
((FILEPROPERTY (sysfiles.name, ''spaceused'')*8)/1024),
((sysfiles.size*8)/1024)-((FILEPROPERTY (sysfiles.name, ''spaceused'')*8)/1024),
((((sysfiles.size*8)/1024)-((FILEPROPERTY (sysfiles.name, ''spaceused'')*8)/1024) * 100) / (NULLIF(((sysfiles.size*8)/1024),0)) + 99),
WHEN ((sysfiles.growth <> 0) and (sysfiles.status = 2)) THEN ''Enabled MB''
WHEN ((sysfiles.growth <> 0) and (sysfiles.status <> 2)) THEN ''Enabled %''
WHEN sysfiles.growth = 0 THEN ''Disabled''
WHEN (sysfiles.growth < 128) THEN sysfiles.growth
FROM sys.sysfiles sysfiles
INNER JOIN sys.sysaltfiles sysaltfiles ON sysaltfiles.name = sysfiles.name
INNER JOIN sys.databases dbs ON sysaltfiles.dbid = dbs.database_id
WHERE sys.databases.database_id > 4
SELECT * FROM #spacecalc ORDER BY PercentFree ASC
I know there is no commenting in here and I’m sure there are optimisations to make, but for my needs, I’m happy.
Comments welcomed as always!
more to follow…