SharePoint & SQL - Tidying up a messy shared development SQL instance

Came across a bit of a messy SQL instance for our SharePoint dev. environments recently.

Here is a bit of optimisation... (DISCLAIMER: This is mainly for my reference, consult someone in the know before attempting ANY of it!)

General notes:

• Create one tempdb DB file for each CPU core available
• Provided the server is dedicated to one SQL instance, I assign the maximum memory usage as the total physical memory less 2GB for the OS (So, an 8GB machine would have up to 6GB assignable to SQL)

The script below will do the following:

• Set all databases to simple recovery (we do full backups and are not interested in transactional backups)
• Shrink all database and log files down to 1MB (where possible)
• Set the autogrowth of each database and log file to 512MB
• Set the size of each database and log file to 512MB (where possible)

Following execution of the script, any outliers to the 512MB size / autogrowth should be manually configured. Content and UsageApplication DBs are a good example of this and should be sized accordingly. I am currently trying 5GB for UsageApplicaton DBs (you could alternatively reduce the retention in central admin) and 1GB for Content DBs (we don't deal with large amounts of content in our line of work).

File sizes and autogrowth rates can be set by right-clicking a DB > Properties > Files

After everything is configured, stop the SQL service and defrag the relevant disks for good measure. This should result in a relatively healthy / happy SQL environment and keep your SharePoint development systems running nicely.



DECLARE @Databases TABLE ([ID] INT IDENTITY, [name] VARCHAR(MAX))INSERT INTO @Databases ([name])
    SELECT [name]
    FROM sys.databasesDECLARE @DatabaseID INT
SELECT @DatabaseID = MIN([ID])
    FROM @DatabasesDECLARE @DatabaseFiles TABLE ([ID] INT IDENTITY, [name] VARCHAR(MAX), [filename] VARCHAR(MAX))WHILE @DatabaseID IS NOT NULL
    BEGIN
    DECLARE @DatabaseName VARCHAR(MAX)
    SELECT @DatabaseName = [name]
        FROM @Databases
        WHERE [ID] = @DatabaseID
    INSERT INTO @DatabaseFiles ([name], [filename])
        EXEC('use [' + @DatabaseName + ']; select ''' + @DatabaseName + ''', [name] from sys.database_files')
    EXEC('alter database [' + @DatabaseName + '] set recovery simple;')
    DELETE
        FROM @Databases
        WHERE [ID] = @DatabaseID
    SELECT @DatabaseID = MIN([ID])
        FROM @Databases
    END
DECLARE @DatabaseFileID INT
SELECT @DatabaseFileID = MIN([ID])
    FROM @DatabaseFilesWHILE @DatabaseFileID IS NOT NULL
    BEGIN
    DECLARE @DatabaseFileName VARCHAR(MAX)
    SELECT @DatabaseName = [name], @DatabaseFileName = [filename]
        FROM @DatabaseFiles
        WHERE [ID] = @DatabaseFileID
    EXEC('use [' + @DatabaseName + ']; dbcc SHRINKFILE ([' + @DatabaseFileName + '], 1);')
    EXEC('ALTER DATABASE [' + @DatabaseName + '] MODIFY FILE ( NAME = ''' + @DatabaseFileName + ''', FILEGROWTH = 524288KB)')
    EXEC('ALTER DATABASE [' + @DatabaseName + '] MODIFY FILE ( NAME = ''' + @DatabaseFileName + ''', SIZE = 524288KB)')
    DELETE
        FROM @DatabaseFiles
        WHERE [ID] = @DatabaseFileID
    SELECT @DatabaseFileID = MIN([ID])
        FROM @DatabaseFiles
    END