Problem: SQL Server Service will not start due to expiration of evaluation period.

Windows Small Business Server 2008 (SBS 2008) out of the box needs optimizations that, without regular maintenance and supervision, will consume all of your disk space in several places.

In this article, we will cover the tuning and optimization of WSUS in SBS 2008 to free up disk space consumption.  Some of the tips below should be repeat every several months to reclaim disk space.  Here is a summary of topics:

1. IIS Logging for WSUS
2. SQL Server Database “SBSMONITORING”
3. SBSMonitoring_log.LDF size reduction
4. (Coming) Updates Synchronization settings in “Windows Server Update Services” manager
5. Reducing the size of Windows\winsxs Folder
6. Cleanup the Licensing Logs for SBS 2008

IIS logging for WSUS is a hidden disk eating culprit, so we’ll address this one first.

These files are typically found in: “%SystemDrive%\inetpub\logs\LogFiles\W3SVC1372222313″  My suggestion is to disable logging for WSUS and delete these log files.  To do so:

1.  Open IIS Manager
2. Navigate to “Sites” and then ”WSUS Administration”
3. Click the ”Logging” icon
4. On the right hand column, click “Disable”
5. Close IIS Manager
6. Open Explorer and navigate to “%SystemDrive%\inetpub\logs\LogFiles\W3SVC1372222313″
7. Delete the contents of this directory to reclaim disk space

Another disk eating culprit is the “SBSMONITORING” SQL Database.

This database will eventually grow to 4GB where it reaches its limit.  You may notice “sqlservr.exe *32″ process takes a lot of processor time when the database grows large and may also notice that Status & Alerts in Windows SBS Console take a long time.

1.  Log on with a user that has administrative privileges.
2. Start SQL Server Management Studio. (Express edition works) in elevated mode by right clicking and then clicking “Run as Administrator”
3. Attach to SBSMONITORING instance (replace SERVERNAME with the name of your server):
   Server Type: Database Engine Server
   Name: SERVERNAME\SBSMONITORING Authentication: Windows Authentication
4. Once connected, from the Menu, click: File -> New -> Query with Current Connection. This creates a new query window.
5. To prevent your database from outgrowing it’s 4GB size limitation, Copy the commands below into the query window, then press F5 to execute:USE SBSMonitoring UPDATE [SBSMonitoring].[dbo].[Settings] SET [Value] = 30 WHERE [Name] = ‘CleanupPeriod’ EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase] CREATE NONCLUSTERED INDEX [_SBS_BLOG_index_WMICollectedData_5_K4_K1_K5_2] ON [dbo].[WMICollectedData] ([WMIPropertyID] ASC,[ID] ASC, [WMIInstanceID] ASC) INCLUDE ( [DateCollected]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] CREATE NONCLUSTERED INDEX [_SBS_BLOG_index_Reports_5_K2_K3_1_4] ON [dbo].[Reports] ([ConfigurationID] ASC, [DateGenerated] ASC) INCLUDE ([ID], [Data]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] CREATE NONCLUSTERED INDEX [_SBS_BLOG_index_WMICollectedData_5_K2D_K4_K5] ON [dbo].[WMICollectedData] ([DateCollected] DESC, [WMIPropertyID] ASC, [WMIInstanceID] ASC) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] CREATE INDEX [_SBS_BLOG_index_Alerts_8_7_] ON [SBSMonitoring].[dbo].[Alerts] ([DefinitionID], [ComputerID]) INCLUDE ([DateOccured]) CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_5_1_4] ON [dbo].[WMICollectedData]([WMIInstanceID], [ID], [WMIPropertyID]) CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_4_1_5_2_3] ON [dbo].[WMICollectedData]([WMIPropertyID], [ID], [WMIInstanceID], [DateCollected], [StatusID]) CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_5_1_3_4] ON [dbo].[WMICollectedData]([WMIInstanceID], [ID], [StatusID], [WMIPropertyID]) CREATE STATISTICS [_SBS_BLOG_dta_stat_WMICollectedData_5_4_2] ON [dbo].[WMICollectedData]([WMIInstanceID], [WMIPropertyID], [DateCollected]) CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_2_1_5] ON [dbo].[WMICollectedData]([DateCollected], [ID], [WMIInstanceID]) CREATE STATISTICS [_SBS_BLOG_stat_WMICollectedData_1_3_5_2] ON [dbo].[WMICollectedData]([ID], [StatusID], [WMIInstanceID], [DateCollected])

An optional optimization that you may use at your own risk is to add indexes.

Although this has worked well for me, I must say that this is tinkering with the original product design.  Regarding “sqlservr.exe *32 high CPU usage in SBS 2008″
This is the bit that will help optimize the database to take less processing resources by indexing some of the monitoring database tables.  Replace the code in your query window with the code below and press F5:

USE SBSMONITORING
GO
CREATE NONCLUSTERED INDEX [IDX_Alerts_GetAlertsPerID] ON [dbo].[Alerts] (    [DefinitionID] ASC,    [ComputerID] ASC,    [DateOccured] ASC,    [IsSet] ASC,    [ID] ASC )
GO
CREATE NONCLUSTERED INDEX [IDX_WMICollectedData_GetSecurityProductPerComputer] ON [dbo].[WMICollectedData] (    [WMIInstanceID] ASC,    [WMIPropertyID] ASC,    [DateCollected] ASC,   [ID] ASC )
GO

create index IDX_WMIObjectProperties_ObjectID on WMIObjectProperties(ObjectID,ID)
Go

create index IDX_WMIObjectInstances_ObjectID on WMIObjectInstances(ObjectID,ID)

SBSMonitoring_log.LDF size reduction

 The SBSMonitoring_log.LDF file should be 1GB or less, but has been known to grow over 30GB in some instances.  To reduce the size of this space eating monster, do the following:

Open SQL Server Management Studio Express by “RunAs” Administrator
Connect to the “[servername]\SBSMonitoring” database, use Windows Authentication
Expand Databases and then right click on “SBS Monitoring” and choose Tasks, Shrink, file.  In the window that opens, choose “Log” from the “File Type” drop down, click OK. 

Reducing the size of Windows\winsxs Folder

The winsxs folder is a PIG.  Open a command prompt in the administrative context
Run “compcln.exe”
After completion, you may notice up to 1.7GB of recovered space, but you won’t be able to roll back SP2.

Cleanup Licensing Log

The licensing log will take up a lot of space over time.  To reclaim that space:
Open a command prompt in the administrative context
Run: del “%systemroot%\system32\winevt\logs\Microsoft-Windows-Server Infrastructure Licensing*%4Debug.etl.*”

References:

• Index entries found at : http://social.technet.microsoft.com/Forums/en/smallbusinessserver/thread/d195baac-da8b-4387-9079-c55d5e1879b4
• WinSXS cleanup found at:
  http://social.technet.microsoft.com/Forums/en-US/3bcacb59-61f8-457d-bd62-b1230c98aec0/sbs-2008-sp1-winsxs-growing
• Cleanup Licensing Log found at:
  http://blogs.technet.com/b/sbs/archive/2010/03/02/recovering-disk-space-on-the-c-drive-in-small-business-server-2008.aspx