This’ll be a quick one, but I just wanted to throw you guys another useful script real quick.  Now, whether or not you decide to make this work in PS or not is up to you.  Most of my post-install stuff is  still in T-SQL so that’s how I’m giving this to you.  Anyway, it’s something that gets overlooked quite often so I thought I’d just throw it up here real quick.

There are 2 things I like to do right away when I install SQL.  One is to set the job history max rows and the max rows per job, and the other is to set the number of error logs SQL will keep before deleting them. 

So this first one configs the job logging.  I like to keep a lot of logs for my jobs.  I’ve found that more often than not the default setting isn’t adequate if you want a real picture of what’s happening with your jobs.  So I increase it right off the bat.

 USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=100000,
       @jobhistory_max_rows_per_job=10000
GO

This 2nd script is what I use to set how many of the logs get stored.  Of course, you could also use a simple PS script to save the logs off to a different folder so you have them on hand if you like, but that’s up to you.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 30
GO

The thing I really like about both of these scripts is that they work on SQL2K and up.  I’ve tested these all the way up to R2 and they’re both just fine.  So if you don’t have some standardized setup scripts you run on all your boxes when you install them, then let these be your first.  The more things are the same in your shop the easier it’ll be to admin things.  You’ll know what’s happening on your boxes and you have fewer things to remember.  And scripting becomes much easier too because if things run the same way and are named the same thing, then you know exactly what to look for in your scripts. 

Have fun.