Don’t you love looking at your old scripts?
I’ve rewritten all of our maintenance jobs, including the jobs in task manager that handle SBSMonitoring. I’ve rid myself of Windows Internal Database, but was still using the script I wrote for it. Not any more!
I decided to use and abuse Ola Hallengren’s scripts, so what I’m using for SBSMonitoring is:
Download and install CommandExecute.sql, DatabaseIntegrityCheck.sql, and IndexOptimize.sql. Then, create the following .sql file:
Declare @4wks datetime
set @4wks = DateAdd(hh, DateDiff(hh, 0, GETDATE()) – 672, 0)EXECUTE [dbo].[IndexOptimize] @Databases = ‘ALL_DATABASES’, @OnlyModifiedStatistics = ‘Y’
EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = ‘ALL_DATABASES’exec msdb.dbo.sp_delete_backuphistory @oldest_date = @4wks
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = ‘CommandLog’))
BEGIN
DELETE FROM [dbo].[CommandLog] WHERE DATEDIFF(dd,StartTime,GETDATE()) > 30
ENDIF @@SERVERNAME = ‘MYSBSSERVER\SBSMONITORING’
BEGIN
EXECUTE [SBSMonitoring].[dbo].[CleanupDatabase]
END
This can be called with sqlcmd in Task Scheduler. (“sqlcmd -E -SmyServer -i c:\path\to\scriptname.sql”)
I’ve also started using Microsoft’s script to reindex SUSDB. I migrated that into SQL Server, so it’s an actual job, but SBS doesn’t support migrating the SBSMonitoring database out of the SBSMonitoring 2005 Express instance that’s installed by default, hence the scripty maintenance.