SQL Server 2005 - Backup, Integrity Check and Index Optimization

Question

SQL Server 2005 - Backup, Integrity Check and Index Optimization

Ola Hallengren

Hall of Fame

Points: 3970
More actions
February 24, 2008 at 5:21 pm

#183278

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

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

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

idoncov SSC Journeyman Points: 77 More actions · Answer 1

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.

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 2

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

http://ola.hallengren.com

Cory E. SSCertifiable Points: 7076 More actions · Answer 3

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

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 4

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.

http://ola.hallengren.com/sql-server-backup.html

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

http://ola.hallengren.com

Cory E. SSCertifiable Points: 7076 More actions · Answer 5

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

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 6

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

http://ola.hallengren.com

junkymail999 SSC Veteran Points: 215 More actions · Answer 7

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.

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 8

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

http://ola.hallengren.com

cxyzhang SSC Veteran Points: 254 More actions · Answer 9

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

Thanks.

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 10

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.

http://ola.hallengren.com/scripts/MaintenanceSolution.sql

Ola Hallengren

http://ola.hallengren.com

cppp SSC Rookie Points: 25 More actions · Answer 11

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!

MatthieuQ SSC Enthusiast Points: 178 More actions · Answer 12

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,

Matthieu

junkymail999 SSC Veteran Points: 215 More actions · Answer 13

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?

thanks,

Ola Hallengren Hall of Fame Points: 3970 More actions · Answer 14

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

http://ola.hallengren.com/sql-server-backup.html

Ola Hallengren

http://ola.hallengren.com