SQL Server 2005 - Backup, Integrity Check and Index Optimization

  • Comments posted to this topic are about the item SQL Server 2005 - Backup, Integrity Check and Index Optimization

  • I was wondering if you would tell me why a function was written to execute a sql command instead of calling the exec sql command directly.

  • The background for the design decision to execute all commands through a CommandExecute stored procedure is that I would like to have a consistent error handling and logging. Information about all commands are logged with start time, command text, command output and end time. Here's an example of how a command is logged.

    DateTime: 2008-03-05 21:58:59

    Command: ALTER INDEX [IX_Address_StateProvinceID] ON [AdventureWorks].[Person].[Address] REORGANIZE

    Comment: IndexType: 2, LOB: 0, PageCount: 1001, Fragmentation: 7.40741

    DateTime: 2008-03-05 21:59:10

    Ola Hallengren


  • I have started testing and implementing this process, and so far, I like it! The issue I currently am having is "The statement BACKUP LOG is not allowed while the recovery model is SIMPLE". I know the issue is you cannot do log backups on DB's that are in SIMPLE recovery model, what is the best way to handle this?

    -- Cory

  • Thanks, Cory. You need to change one line of code in [DatabaseBackup] to have it dynamically skip databases that are in Simple recovery model, when doing log backups.


    IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE' AND NOT (@BackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabase,'recovery') = 'SIMPLE')

    This is also how it will work in the next version.

    Another possibility is to exclude these databases by name.

    @databases = 'USER_DATABASES,-Db1,-Db2'

    Ola Hallengren


  • Thanks for getting back to timely with the solution -

    Since we are talking about next version things, I had to make one other modification to not backup snapshots:

    in the DATABASESELECT, I had to add to the "insert into @database02..." the following:

    INSERT INTO @Database02 (DatabaseName, DatabaseStatus)

    SELECT [name], 1

    FROM sys.databases

    WHERE database_id > 4

    AND source_database_id IS NULL

    I had thought about doing the "-DB" part, but I wanted this to be as "set it and forget it" as possible...if a DB was set to simple, it just doesnt do the backup, and vice versa. THANKS!

    -- Cory

  • Thanks. This bug fix has now been added to the new version. I didn't think about database snapshots, when I developed it.

    Ola Hallengren


  • thanks for the script. It is beyond the best effort.

    one question : I usually backup Log on hourly basis for production databases. If I use your script, every thing works fine except the log is not being truncated. Can we make any change to the script so I do not have to maintain TLog manually if I choose to use your script.

  • My script is using the normal BACKUP LOG command. When this command is performed the transaction log is truncated. (It is however not shrinked.)

    Ola Hallengren


  • I am wondering how your logging works. Where can I view the logs?


  • I prefer to use SQL Server Agent output files. This way the output from the stored procedures are redirected to text files. I have an installation script that creates the objects and jobs with output files configured.


    Ola Hallengren


  • ola, just wanted to give you big thumbs up for the 3-in-1 DB maintenance script. within our sql environment it's working beyond our expectations.

    keep up the good work!

  • Hello,

    I was wondering why the publication date of the article is 0001/01/01 🙂

    Anyway, I'm trying to watch the documentation on your website. It seems that it is no more available. Can you place it in the forum please ?

    Thanks by advance,


  • I would like to use your script as it is far better than some thing I can write. Thanks for your contribution to DBA world. I would prefer to clean up backup files or tlog backups after 6 days. How could I accomplish this using your script ? Is it possible to add or update for this purpose with your next update?


  • You can use the parameter @CleanupTime to delete old backup files. It's in hours so you have to set it to 144 (24 * 6) to keep backup files for 6 days. Please see this example for the transaction log backups.

    EXECUTE dbo.DatabaseBackup

    @databases = 'USER_DATABASES',

    @Directory = 'C:\Backup',

    @BackupType = 'LOG',

    @verify = 'Y',

    @CleanupTime = 144


    Ola Hallengren


Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply