CodePlex novice

I have entered CodePlex.
After some looking around, my first project is published. Please take a look.

The subject is maintaining the database „msdb“ by cleaning up history data.

Reference
„SQL Server History Cleanup“ (CodePlex)

Errorlog Path

I need the path of the Errorlog directory when I define a SQL Server Agent jobstep. Using the Errorlog directory for the history (log files) of all jobsteps makes maintenance more easy and robust.
My favorite way of getting the path of the Errorlog diretory is by reading the Errorlog, while it contains a entry in the beginning about the path of the directory like
Logging SQL Server messages in file 'E:\MSSQL\MSSQL10_50.SANDY\MSSQL\Log\ERRORLOG'.
Using a (undocumented) stored procedure, I get the path by the script
DECLARE @_errorlog TABLE (
    LogDate DATETIME
    ,ProcessInfo NVARCHAR(MAX)
    ,ErrorText NVARCHAR(MAX));
INSERT INTO @_errorlog ([LogDate], [ProcessInfo], [ErrorText])
EXECUTE [master].[dbo].[sp_readerrorlog]
    @p1 = 0  -- 0 = current errorlog
    ,@p2 = 1  -- 1 = SQL Server Errorlog
    ,@p3 = N'Logging SQL Server messages in file ';

DECLARE @errorlog_directory NVARCHAR(256);
SELECT @errorlog_directory = REPLACE(REPLACE([ErrorText],N'Logging SQL Server messages in file ''',''),N'\ERRORLOG''.','')
FROM @_errorlog;

DELETE @_errorlog;

SELECT @errorlog_directory AS N'errorlog_directory';
The result is like
E:\MSSQL\MSSQL10_50.SANDY\MSSQL\Log
The (undocumented) procedure "sp_readerrorlog" takes up to four parameters

1. @p1:Generation by integer value of the Errorlog to get. 0 (zero) is current errorlog, 1 is the last archived and so on.
2. @p2: Define by integer or NULL which log to read. 1 (one) or NULL to read SQL Server Errorlog, 2 to read SQL Server Agent Log.
3. @p3: Primary search string, max. 255 characters.
4. @p4: Secondary search string, max. 255 characters.

The last part („\ERRORLOG“) of the string is filtered out as it is the name of the file.
The path of the Errorlog directory is also available through the Windows Registry. Unfortunately it is indirect so I don't like it. But it could be by the script
DECLARE @reg_key_data NVARCHAR(255);
EXECUTE [master].[dbo].[xp_instance_regread]
  N'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters'
 ,N'SQLArg1'
 ,@reg_key_data OUTPUT;
DECLARE @errorlog_directory NVARCHAR(256);
SELECT @errorlog_directory = REPLACE(REPLACE(@reg_key_data,N'-e',''),N'\ERRORLOG','');

SELECT @errorlog_directory AS N'errorlog_directory';

Reference
Greg Robidoux: „Reading the SQL Server log files using T-SQL“ (MSSQLTips)

Backup Directory

With SQL Server 2005 we were able to define a default backup directory. This is a nice thing to maintain, so that a restore can be done quickly and correct.
The directory path is stored in the Windows Registry, and is available with a (undocumented) stored procedure.
DECLARE @reg_key_data NVARCHAR(255);
EXECUTE [master].[dbo].[xp_instance_regread]
  N'HKEY_LOCAL_MACHINE'
 ,N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer'
 ,N'BackupDirectory'
 ,@reg_key_data OUTPUT;
SELECT @reg_key_data AS N'backup_directory';

(Fig. 1) SQL Server Management Studio, Facets

The script works on SQL server 2005 and 2008.
You could set the backup directory path by the (undocumented) stored procedure master.dbo.xp_instance_regwrite, but I prefer to change the value at the installation or manually using SQL Server Management Studio.
The name of the backup directory is available for read and write in SQL Server Management Studio as a facet to the database instance. Right-click on the instance in Object Explorer and select Facets. In the facet "Server" it is the second item.

Reference

Dinesh Asanka: „Working with the Registry“ (SQLServerCentral).

Secure password

A SQL Server installation requires a lot of service accounts - and then a lot of secure passwords.
I have tried various solutions, and for now I am using PC Tools Secure Password Generator on the web. It works nice, and is secured by HTTPs communication.
I still have to store the passwords in a secure and yet available place. The old school solution using paper, envelopes and a solid safe still works in some degree in smaller organisations.
In a larger organisation, or if availability with a audit is required, I have for some time used thycotic Secret Server in a local installation. It requires a software installation - with a SQL Server database, but it is secure and easy to use.

Set Recovery Model Simple

On a test installation with quite a few databases we decided to alter the Recovery Model to Simple.
This statement generated the ALTER statements.
SELECT N'ALTER DATABASE [' + [name] + '] SET RECOVERY SIMPLE WITH NO_WAIT;' AS N'DDL'
FROM [master].[sys].[databases]
WHERE [recovery_model] <> 3 AND [name] <> N'model';
The output is copied to the (same) query window in SQL Server Management Studio and executed.
The total time of delivery is measured in seconds :-)