InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 05:08:40 GMT20http://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI have been using this solution quite some time now, its super fantastic.Regards,AnishWed, 13 Mar 2013 04:10:27 GMTAnish-1093520http://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxAfter a nice conversation with Ola I think I understand his concept about the script and the reasons for the logic in some points. It does make sense and if you wanted to add notifications to certain aspects it would be easy to modify.In my opinion it would depend on what you are comfortable with as far as relying on the script to make the correct decision depending on the situation or if you would like to be involved when something unplanned occurs so you can investigate.Overall I can't believe he did such a nice job with the script and I look forward to trying to use if all works out.LeeThu, 17 Mar 2011 09:53:46 GMTLeelandhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxInteresting, Never thought about testing that. Thanks for the info.Wed, 16 Mar 2011 08:48:36 GMTmcliffordDBAhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspx[quote][b]mcliffordDBA (3/16/2011)[/b][hr]Any luck with your testing?[/quote][quote][b]mcliffordDBA (3/15/2011)[/b][hr]Yes, setting your database to simple will break the chain. You can only take log backups when you database is in Full or Bulk Logged. You will need to set the database(s) back to Full or Bull Logged, take your full backup and start the transaction Log backup process. I would have to say the script is functioning as expected[/quote]Hi there...I wasn't able to reply last night (no internet connection). I do understand the concepts of backups and log chain and that is what I was trying to point out in my question.I know the normal process...full backup...diff / log. I wanted to continue testing to make sure what I think is happening is actually correct before I attempt to carry this one. Ola contacted me and I am attempting at the moment to create the scenario for him to have a discussion.The point I was trying to make was if you had your db set up in full recovery mode...had a full backup...and then subsequent differentials and / or log backups. What happens when the log chain is broken by putting the database into SIMPLE recovery mode.My event was supposed to highlight the change happens without your knowledge (i.e. you aren't the one doing it).So you would think the database backups are continuing on the hour and at least in my intitial test of walking through the debugger it appeared for some reason it wasn't catching and it would totally skip over the database...which is something I wouldn't personally want.I had taken some of the code out that related to the multiple backup software solutions that were coded in the script since I am not using any of them.I re-deployed the original script from Ola and am about to work up a test case to see if I screwed something up.I plan on sending it to Ola when I am done so he can see if it is correct or not.Thanks for checking up :)Wed, 16 Mar 2011 08:05:49 GMTLeelandhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxAny luck with your testing?Wed, 16 Mar 2011 07:58:22 GMTmcliffordDBAhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxYes, setting your database to simple will break the chain. You can only take log backups when you database is in Full or Bulk Logged. You will need to set the database(s) back to Full or Bull Logged, take your full backup and start the transaction Log backup process. I would have to say the script is functioning as expectedTue, 15 Mar 2011 15:43:23 GMTmcliffordDBAhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspx[quote][b]mcliffordDBA (3/15/2011)[/b][hr]I don't know if this answers your question or not but I run 3 different jobs. The databases I want to be part of my transaction log shipping is set to Full. Everything else simple. Basic Logic Sunday(Midnight) - run full backups during a weekly maintenance. --Every hour TLog. Monday - Saturday (Midnight)- Diff Backups --Every hour TLog. 1st job - Full Backups (Sunday Only)2nd Job - Diff Backups (Mon-Fri)3rd Job - TLog/shipping (At the top of the hour - after maintenance windows)[/quote]Hate the session expired deal :) had a nice reply and it was lost.Anyways here is a quick summary of what I wrote.I had a test db in full recovery...ran a full backup with the stored procedure...then ran several log backups...I switched the database to simple recovery mode to simulate some 'event' that would break the log chain...then attempted to run the log backup again...it ran with no error and listed the following...DateTime: 2011-03-15 16:09:53Database: [DB_UTILS]Status: ONLINEMirroring role: NoneStandby: NoUpdateability: READ_WRITEUser access: MULTI_USERIs accessible: YesRecovery model: SIMPLEDifferential base LSN: 137000000672800036Last log backup LSN: NULLDateTime: 2011-03-15 16:09:53This segment of code gets skipped because it the DB is in SIMPLE mode[code="sql"] IF @ChangeBackupType = 'Y' BEGIN IF @CurrentBackupType = 'LOG' AND DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') &lt;&gt; 'SIMPLE' AND @CurrentLogLSN IS NULL AND @CurrentDatabase &lt;&gt; 'master' BEGIN SET @CurrentBackupType = 'DIFF' END IF @CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL AND @CurrentDatabase &lt;&gt; 'master' BEGIN SET @CurrentBackupType = 'FULL' END END[/code]and also here because it is it is a LOG backup but the database is in SIMPLE mode[code="sql"] IF DATABASEPROPERTYEX(@CurrentDatabase,'Status') = 'ONLINE' AND NOT (DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0) AND DATABASEPROPERTYEX(@CurrentDatabase,'IsInStandBy') = 0 AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') = 'SIMPLE' OR @CurrentLogLSN IS NULL)) AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL) AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabase = 'master')[/code]the whole backup process is skipped and you loop through.No backup occurs...again I am wondering if I am the one doing something wrong here.I am running the debugger on a SQL 2008 R2 server for testing.Tue, 15 Mar 2011 15:34:10 GMTLeelandhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI don't know if this answers your question or not but I run 3 different jobs. The databases I want to be part of my transaction log shipping is set to Full. Everything else simple. Basic Logic Sunday(Midnight) - run full backups during a weekly maintenance. --Every hour TLog. Monday - Saturday (Midnight)- Diff Backups --Every hour TLog. 1st job - Full Backups (Sunday Only)2nd Job - Diff Backups (Mon-Fri)3rd Job - TLog/shipping (At the top of the hour - after maintenance windows)Tue, 15 Mar 2011 14:19:33 GMTmcliffordDBAhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxCan someone please let me know if this is a bug or if I am just dreaming...I started looking at this process and running through it...and tried it out. Given the following scenario let me know if this is valid or not.you have a DB in full recovery mode and are using this process to do your backups...The parameters I had set were as follows...SET @Databases = 'DB_UTILS'SET @Directory = 'E:\Backup'SET @BackupType = 'LOG'SET @Verify = 'Y'SET @CheckSum = 'Y'SET @NumberOfFiles = 1SET @CopyOnly = 'N'SET @ChangeBackupType = 'N'SET @CheckSum = 'N'SET @NumberOfFiles = 1SET @Execute = 'Y' I set it to a situation where you have a full backup plan running on a DB...the DB is in full recovery mode and has a full backup already and you are just motoring along with log backups.SOMEONE or SOMETHING switches the db to simple mode for what ever reason.Now your log backup job comes along and fires off with basic inputs...listed above...and I didn't have the 'ChangeBackupType' enabled since on a VLDB it would be bad to fire a full during the day due to impact to the system...I instead would want to be notified to check something out and then make a decision.Again, unless I am missing something there is a little fault in the logic...[code="sql"] IF DATABASEPROPERTYEX(@CurrentDatabase,'Status') = 'ONLINE' AND NOT (DATABASEPROPERTYEX(@CurrentDatabase,'UserAccess') = 'SINGLE_USER' AND @CurrentIsDatabaseAccessible = 0) AND DATABASEPROPERTYEX(@CurrentDatabase,'IsInStandBy') = 0 AND NOT (@CurrentBackupType = 'LOG' AND (DATABASEPROPERTYEX(@CurrentDatabase,'Recovery') = 'SIMPLE' OR @CurrentLogLSN IS NULL)) AND NOT (@CurrentBackupType = 'DIFF' AND @CurrentDifferentialLSN IS NULL) AND NOT (@CurrentBackupType IN('DIFF','LOG') AND @CurrentDatabase = 'master')[/code]This segment will eval that it is now a log backup but the recovery is simple and totally pass over the backup steps and then jumps to this block...without so much as a peep...[code="sql"] -- Update that the database is completed UPDATE @tmpDatabases SET Completed = 1 WHERE ID = @CurrentID[/code]looking at my directory no file is written and I am none the wiser that it didn't run.I again might be taking things out the intended context but this kind of occurrence while rare does happen and I just wanted to comment and see if someone can verify this for me. LeeTue, 15 Mar 2011 14:09:30 GMTLeelandhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxjust tested this wonderful scripts and definately one of the must in job schedules.One thing I would like to know that as soon the script completed it created the job. But in job the step comes as :-sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d ...................and this will fail with incorrect servername and the only option I found is replace $(ESCAPE_SQUOTE(SRVR)) with real sql server name. Am I taking this script incorrectly or do I need to modify it somewhere to reflect the correct servername while running the main script to create these jobs.edit :- Sorry just checked that this blog is quiet old. But one thing cant understand that this script is on Ola blog from thu 20jan 2011, and the discussion seems to be started here on this website from 2/25/2008 12:21:14 AM.Thu, 27 Jan 2011 03:53:16 GMTcrazy4sqlhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxGreat set of scripts! But...I am having one issue and I want to see if anyone else is having the same problem. The problem I am having is with the IndexOptmize Procedure. For my FragmenationMedium_LOB and Medium_NonLob (between 10%-30%) I run the index reorganize. But for some reason my ldf file is growing out of control. My understanding of the reorganize feature is it doesn't have that kind of an impact on your log file. Before anyone hammers me, it is not the transaction log that is growing. Yes, after this runs I do have a large transaction log file, but the one right after that one is ok. It is just the ldf that is growing. Database mdf file is around 25 gigDatabase ldf file will start at 1.5 gig and grow to almost 40 gig in 4 or 5 days. I am growing the file in 500MB inc. RecoveryModel: FullAlso I am logging the output of this script and it has never reached a rebuild point over 30% its always a reorganize. Here are my options set in the IndexOptimize Script. @Databases nvarchar(max),@FragmentationHigh_LOB nvarchar(max) = 'INDEX_REBUILD_OFFLINE',@FragmentationHigh_NonLOB nvarchar(max) = 'INDEX_REBUILD_OFFLINE',@FragmentationMedium_LOB nvarchar(max) = 'INDEX_REORGANIZE_STATISTICS_UPDATE',@FragmentationMedium_NonLOB nvarchar(max) = 'INDEX_REORGANIZE_STATISTICS_UPDATE',@FragmentationLow_LOB nvarchar(max) = 'NOTHING',@FragmentationLow_NonLOB nvarchar(max) = 'NOTHING',@FragmentationLevel1 int = 10,@FragmentationLevel2 int = 30,@PageCountLevel int = 1000,@SortInTempdb nvarchar(max) = 'N',@MaxDOP int = NULL,@FillFactor int = NULL,@LOBCompaction nvarchar(max) = 'N',@StatisticsSample int = NULL,@PartitionLevel nvarchar(max) = 'N',@TimeLimit int = NULL,@Execute nvarchar(max) = 'Y'Fri, 05 Nov 2010 07:29:30 GMTmcliffordDBAhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxDated files are fine when you want everything in seperate files, each log backup etc. If you want the log backups appended into one file or device with no init then it all goes wrong at midnight when the date changes! You also have to ensure that there are cleanup tasks as dated files won't automatically overwrite each day. I don't have the disk space for much more than a day's backups on each server - we copy and tape backup to keep older copies so there is no justification for having the large quantities of disk space required for keeping more than 24 hours worth on line.Wed, 18 Aug 2010 01:34:30 GMTP Joneshttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxYou might also want to try out the option @ChangeBackupType = 'Y'. Then the log backup job will see that there is a new database and perform a full backup instead for that database. The next time the log backup runs it can perform a log backup. This way new a database gets into the backup procedure very fast.Tue, 17 Aug 2010 10:34:30 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxSome input on this. The advantage with backing up to new files with date and time in the file name and the way DatabaseBackup works, is that you're guaranteed to always have the latest full, the latest differential and all transaction log backups since the latest full or differential backup on disk.Ola Hallengren[url=http://ola.hallengren.com]http://ola.hallengren.com[/url]Tue, 17 Aug 2010 10:28:42 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI've been using the backup script for some time and it works a treat. It is especially good for Sharepoint database servers where databases can suddenly appear as if by magic as the content grows. No need to work out names or set up jobs or keep checking for new databases, they just get included in the backup by default. I've added a couple of extra parameters to the databasebackup proc. and added the code for them.@Init is Y or N as to whether to backup WITH INIT or WITH NOINIT (for log files)and @DAYFILES is Y or N for whether to use a dated file name or just the same file name each time - server space is the criteria here as backups are copied off to tape daily. I found the original dated files ate up disk space too quickly.Tue, 17 Aug 2010 05:57:00 GMTP Joneshttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxAwesome. Two thumbs up for you, Ola. Finally someone post it. I have been using three scripts on agentjob I wrote to do these three things. Better late than never for those who are still missing the boat. I have not run yours yet personally. Anybody find a kink, please post for discussion.Fri, 13 Nov 2009 13:23:04 GMTjswong05http://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI was experiencing downtime on my website yesterday, but it's working again now. I'm sorry for the inconvenience.The script was first published on SQL Server Central 23 Feb 2008.Ola Hallengren[url=http://ola.hallengren.com]http://ola.hallengren.com[/url]Sat, 05 Sep 2009 09:09:52 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxYou 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[url=http://ola.hallengren.com/sql-server-backup.html]http://ola.hallengren.com/sql-server-backup.html[/url]Ola Hallengren[url=http://ola.hallengren.com]http://ola.hallengren.com[/url]Sat, 05 Sep 2009 09:01:34 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI 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,Fri, 04 Sep 2009 08:58:41 GMTjunkymail999http://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxHello, 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, MatthieuFri, 04 Sep 2009 02:24:10 GMTMatthieuQhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxola, 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!Wed, 26 Aug 2009 00:24:24 GMTcppphttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI 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.[url=http://ola.hallengren.com/scripts/MaintenanceSolution.sql]http://ola.hallengren.com/scripts/MaintenanceSolution.sql[/url]Ola Hallengren[url=http://ola.hallengren.com]http://ola.hallengren.com[/url]Sun, 23 Aug 2009 15:30:52 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI am wondering how your logging works. Where can I view the logs?Thanks.Sun, 23 Aug 2009 14:34:19 GMTcxyzhanghttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxMy 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[url]http://ola.hallengren.com[/url]Sun, 16 Nov 2008 09:22:52 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxthanks 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.Sun, 16 Nov 2008 08:51:58 GMTjunkymail999http://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxThanks. This bug fix has now been added to the new version. I didn't think about database snapshots, when I developed it.Ola Hallengren[url]http://ola.hallengren.com[/url]Fri, 06 Jun 2008 13:10:06 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxThanks 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:[code] INSERT INTO @Database02 (DatabaseName, DatabaseStatus) SELECT [name], 1 FROM sys.databases WHERE database_id &gt; 4 [b]AND source_database_id IS NULL[/b][/code]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!Fri, 06 Jun 2008 12:53:30 GMTCory E.http://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxThanks, 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.[url]http://ola.hallengren.com/sql-server-backup.html[/url]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[url]http://ola.hallengren.com[/url]Fri, 06 Jun 2008 12:47:49 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI 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?Fri, 06 Jun 2008 11:33:45 GMTCory E.http://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxThe 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:59Command: ALTER INDEX [IX_Address_StateProvinceID] ON [AdventureWorks].[Person].[Address] REORGANIZEComment: IndexType: 2, LOB: 0, PageCount: 1001, Fragmentation: 7.40741DateTime: 2008-03-05 21:59:10Ola Hallengren[url]http://ola.hallengren.com[/url]Tue, 22 Apr 2008 11:11:52 GMTOla Hallengrenhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxI 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.Mon, 21 Apr 2008 15:54:41 GMTidoncovhttp://www.sqlservercentral.com/Forums/Topic459547-1207-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Backup/62380/">SQL Server 2005 - Backup, Integrity Check and Index Optimization</A>[/B]Sun, 24 Feb 2008 17:21:14 GMTOla Hallengren