SQL Server 2005 - Backup, Integrity Check and Index Optimization

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ola Hallengren » SQL Server 2005 - Backup, Integrity Check and...

16 posts, Page 1 of 2

1 2 »»

SQL Server 2005 - Backup, Integrity Check and Index Optimization

Rate Topic

Display Mode

Topic Options

Author

Message

Ola Hallengren

Posted Sunday, February 24, 2008 5:21 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 01, 2009 7:40 AM
Points: 147, Visits: 1,222

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

Post #459547

idoncov

Posted Monday, April 21, 2008 3:54 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 18, 2008 11:16 AM
Points: 1, Visits: 23

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.

Post #488271

Ola Hallengren

Posted Tuesday, April 22, 2008 11:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 01, 2009 7:40 AM
Points: 147, Visits: 1,222

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

Post #488777

Cory E.

Posted Friday, June 06, 2008 11:33 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 594, Visits: 9,495

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 Ellingson

Post #513106

Ola Hallengren

Posted Friday, June 06, 2008 12:47 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 01, 2009 7:40 AM
Points: 147, Visits: 1,222

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://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html#1119100

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'

Please feel free to contact me.
http://blog.ola.hallengren.com/About.html

Ola Hallengren
http://ola.hallengren.com

Post #513151

Cory E.

Posted Friday, June 06, 2008 12:53 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 594, Visits: 9,495

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 Ellingson

Post #513160

Ola Hallengren

Posted Friday, June 06, 2008 1:10 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 01, 2009 7:40 AM
Points: 147, Visits: 1,222

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

Post #513169

junkymail999

Posted Sunday, November 16, 2008 8:51 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 04, 2009 8:56 AM
Points: 11, Visits: 53

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.

Post #603339

Ola Hallengren

Posted Sunday, November 16, 2008 9:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, November 01, 2009 7:40 AM
Points: 147, Visits: 1,222

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

I'm not sure that I understand what you mean. Maybe you could send me an e-mail with some more information.
http://blog.ola.hallengren.com/About.html

Ola Hallengren
http://ola.hallengren.com

Post #603345

cxyzhang

Posted Sunday, August 23, 2009 2:34 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 03, 2009 10:43 AM
Points: 36, Visits: 14

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

Thanks.

Post #775722

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions