SQL/ Syndicated/ Tips and Tricks

Rock Paper SharePoint Lizard SQL

, , , 1 Comment

SharePoint is a fine tool for document management and sharing.

However SharePoint likes to play by its own rules when it comes to how it stores and organizes the information it relies upon to serve out those mad skillz to the denizens of the cube farms and board rooms.  It does so with the bravado that it knows better how to handle data than the relational database system in which it stores this data.

Yep, SharePoint knows more about being SharePoint than SQL Server does and it knows more about hosting SharePoint data than SQL Server does, because Sharepoint does some wicked stuff to ensure that SharePoint can be SharePoint without SQL Server interfering in things.  (Note there are transparent sarcasm tags all over that statement.  You just can’t see ’em because SharePoint doesn’t want you to maaaaan.)

I’m not here to tell you what’s right and wrong about an application telling the database layer how to do its job.  What I am here to do is tell you how to cope with just one of the very minor things that can be done to remove a bit of weekly maintenance that you’ll find yourself doing if you actively monitor (and give a damn) about how the files in your databases are structured, sized, and grow.

On a weekly basis I have a SharePoint database that wants to add a new data file.  Why?  That is a very good question for which I’ve not been able to come up with an answer. Could it be because SharePoint doesn’t know how to tell SQL to partition data? Probably not.  Could be because SharePoint doesn’t understand that a 512mb data file is not considered large? We all have issues judging size so I’m not going to throw stones there.  Perhaps SharePoint doesn’t know that a data file can have more than one table? I’d hope that’s not true.

I’m really tired of going in after the fact and sizing this new data file out properly. It creates a data file that is far too small and it’s growth settings mean instant and widespread external fragmentation. I did a little research. By looking at the file creation time I determined that SharePoint creates this new file weekly on Sundays at 2:00am.  Therefore I built this little routine that looks for the most recent data file for the database in question. If the most recent data file doesn’t fit the standards of the parameters passed into the stored procedure it will adjust the size and growth properties for the file.

Oh, yeah… I forgot to mention that SharePoint creates this file to grow at a percentage factor… see this post on why that is the opposite of a best practice.

Please steal this code and test it in a non-production instance of SharePoint first. As always I must state that because this works for me doesn’t mean it will work for you. Also, change that database name to the one appropriate for your environment. It’s full of GUIDs… (don’t get me started.)

USE [WebAnalyticsServiceApplication_ReportingDB_GUID];
GO
 
CREATE SCHEMA [dba] AUTHORIZATION dbo;
GO
 
CREATE PROCEDURE [dba].usp_fix_poor_sharepoint_file_settings (@set_size INT, @set_filegrowth INT)  AS
DECLARE @file_name sysname;
DECLARE @sql NVARCHAR(3000);
DECLARE @size INT;
DECLARE @filegrowth INT;
DECLARE @is_pct_growth BIT;
 
--FOR DEBUG-----------------
--DECLARE @set_size INT;
--DECLARE @set_filegrowth INT;
 
--SELECT @set_size = 512	--MB
--SELECT @set_filegrowth = 64
-----------------------------
 
SELECT top 1 @file_name = NAME FROM sys.[database_files] AS DF WHERE [type_desc] = 'ROWS' ORDER BY [file_id] desc; 
 
SELECT @size = (size * 8 / 1024) FROM sys.[database_files] AS DF WHERE [name] = @file_name;
SELECT @filegrowth = [growth] FROM sys.[database_files] AS DF WHERE [name] = @file_name;
SELECT @is_pct_growth = [is_percent_growth] FROM sys.[database_files] AS DF WHERE [name] = @file_name;
 
--Check variable values:
--SELECT @file_name AS [logical_name], @size AS [file_size], @filegrowth AS [growth], @is_pct_growth AS [is_pct_growth];
 
--Fix File Size and Autogrowth
IF @size < @set_size AND (@filegrowth <> @set_filegrowth OR @is_pct_growth = 1)
	BEGIN
		SELECT @sql = 'ALTER DATABASE [' + db_name() + '] MODIFY FILE ( NAME = N' + '''' + @file_name + '''' + ', SIZE = ' + CAST(@set_size AS NVARCHAR(4)) + 'MB, FILEGROWTH = ' + CAST(@set_filegrowth AS NVARCHAR(3)) + 'MB )'
	END
 
--Size is fine, but autogrowth is not.  Fix it.
IF @size !< @set_size AND (@filegrowth <> @set_filegrowth OR @is_pct_growth = 1)
	BEGIN
		SELECT @sql = 'ALTER DATABASE [' + db_name() + '] MODIFY FILE ( NAME = N' + '''' + @file_name + '''' + ', FILEGROWTH = ' + CAST(@set_filegrowth AS NVARCHAR(3)) + 'MB )'
	END
 
EXEC [sys].[sp_executesql] @sql;
GO

In my environment 512mb seems to be the sweet spot for the file size.  It never grows beyond it in a week’s time and if it did I’ve proactively adjusted the growth factor to accommodate it.

This code creates a stored procedure you’ll then call from a SQL Server Agent job weekly (in my case) after the file is created. Be sure to set the database context appropriately in your Agent job kids. Rookie mistake waiting to happen.

Speaking of the Agent job, here is the code I run in mine. Yours may vary.

EXEC [dba].[usp_fix_poor_sharepoint_file_settings] @set_size = 512, @set_filegrowth = 64

 
This will set the most-recent data file’s size to 512mb and autogrowth at a fixed 64mb rather than a percentage as is the case with SharePoint out of the box (again, right for my environment. Review your history for what is best for your installation.)

I’m a strong believer in operationalizing things that you find you spend time on repetitively.  Did it take me longer to build, test, and implement it rather than go in and fix it one more time – two, or perhaps three more times?  Sure it did.  But I would have to do it 52 times per year.  You tell me if it would make sense to keep doing it repetitively each Monday morning (including when I’m on vacation.)

Rock Paper SharePoint Lizard SQL…

SQL just beat Sharepoint. A little bit.