Download Ola Hallengren Script

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need. You can also download the objects as separate scripts. The SQL Server Maintenance Solution is available on GitHub.

To fully understand the agent job created by the installation script, we need to go through the parameters that IndexOptimize stored procedure uses. The are total of 27 parameters that can be used to configure the procedure. To get full details on the procedure parameters, run a following query:

Download Zip 🔥 https://urluso.com/2y3ChL 🔥

The common practice is to pass all necessary parameters to the stored procedure through a T-SQL, or CmdExec script. These scripts can then be included in a SQL Server Agent job, and assigned to run on a custom schedule. Creating and configuring these jobs would result in a custom, fully automated index maintenance solution.

Parameter valueDescription ALL_INDEXES Selects all indexes Database1.Schema1.Tab1.Index1 Selects the Index1 on the object Schema1.Tab1 in Database1 Database1.Schema1.% Selects all indexes in Schema1 in Database1

Parameter valueDescription INDEX_REBUILD_ONLINE Runs the online index rebuild. The procedure uses SQL Server ALTER INDEX command, combined with REBUILD WITH ONLINE = ON option INDEX_REBUILD_OFFLINE Runs the online index rebuild. The procedure uses SQL Server ALTER INDEX command, combined with REBUILD WITH ONLINE = OFF option INDEX_REORGANIZE Reorganizes the index. The procedure uses SQL Server ALTER INDEX command, with the REORGANIZE option NULL Skips index maintenance for the specified index group. This is the default value for indexes with low fragmentation

Parameter valueDescription ALL Updates both index and column statistics INDEX Updates index statistics only COLUMNS Updates column statistics only NULL Skip the statistics update.

Parameter valueDescription NONE Continues to wait for the locks SELF Aborts the rebuild operation BLOCKERS Kills the user transactions that block the rebuild operation

The only parameters that are passed to the procedure are Databases (selection is performed on all user databases) and LogToTable (operations are logged to dbo.CommandLog table). This means that all other parameters, that are not speciffied in this script use their default values. At a glance, this job performs the following actions:

This query can be used as is to run the maintenance task, but to take the full advantage of this custom solution, it is best to include the script in SQL Server Agent Job. To create the job, perform the following steps:

In the New Job Step form, specify the Step name. For the Type, select Transact-SQL script (T-SQL). In Command box, paste the custom maintenance script. Click OK to save the job step.

Some more experienced administrators prefer to design and use their own scripts for these tasks. The scripts are usually designed to fit the requirements imposed by the specific environment. After the scripts are thoroughly tested, they often get included in a maintenance plan, or SQL Server agent job to automate their execution. Such solution is usually optimal for some demanding environments like high traffic servers or databases that are still in development. Designing these solutions usually take time, and require an experienced DBA skilled in T-SQL or PowerShell scripting.

Execute the MaintenanceSolution.sql against the server. The script creates one table (dbo.CommandLog), one function, and four stored procedures in the master database. It also creates 11 pre-created SQL Server Agent jobs. Each of the created jobs will be discussed separately

SQL Server Agent jobs are the final product of the script. All jobs are created without a schedule, and these need to be set manually by the user. There are total of 11 jobs: 4 backup jobs, 2 database integrity check jobs, one index optimize job, and 4 cleanup jobs. This article will focus on the backup jobs

Kimberly Tripp has some scripts on determining the skew of the data inside the indexes. In my experience, a higher sample size helps deal with large amounts of skew. I would use default sample size and pick a specific larger sample size for your worst skewed indexes.

On one server in particular, I set up a maintenance schedule about a year and a half ago. It was doing backups nightly to another local server using a UNC Path (and several other commands). The code for the script is as follows:

This worked fine for quite a while, but stopped working about a month ago. I set it to backup locally, then added a string to xcopy it to the remote location, and a script on the remote server to cleanup old backups. Not ideal.

Obviously, SQL thinks the network location doesn't exist, so I've tried what I can to check that all the network side stuff is in order. I pulled a new copy of the script and recreated all the objects and jobs. I have checked that the other scripts (integrity checks, statistic updates, etc) work. I created a script that uses the same credentials as the backup script to run the xcopy of the local backups to the target server, so I have the proper share/NTFS credentials. That account is a domain account (AD) specifically created for SQL backups. I can backup locally (with that account), so I have database permissions. I can navigate to the share as the backup account using Windows Explorer. I can manually copy the files to the remote location using Windows Explorer using the backup account.

Hi, I am using OH's excellent scripts, however I have found and issue when it comes to backing up to Azure blob storage via URL. All the backups work fine, apart from one 1TB sized database that when compressed has a backup size of around 300GB. I have split this into 12 files backed up locally and takes around an hour. I was using OH script however they would error with the "Device has reached the block size limit" so I did some reading and used a native SQL Backup with the following:

qhI know this has already been pointed out but the largest that the block size can be is 65536. The script says 65563... the last two numbers are transposed. Here's the listing from BOL...

I will tell you that the BACKUP command DOES allow all of the settings to be used when backing up to a UNC. I've never tried it to a URL. (p.s. I don't use Ola's fine scripts. Not because there's anything wrong with them... I just built my own a long time ago and before I even knew about Ola's scripts.)

Thanks for heads up Jeff. I am using SAS token, (see my first post) where my backup script works, so I am using block blobs and not page.

In theory using Ola's script should work as I am using the same parameters (I know it is coded not to).

Has anyone been able to successfully use Ola Hallengren's maintenance scripts to only clean up old backups and not take a new backup? I have full backups running weekly, differentials running daily, and transaction log backups running every hour. I want to keep two weeks worth of backups (full, diff, and tran), so my plan was to set the @CleanupTime for the full backup to two weeks and then, as part of the job, add two steps -- one to delete any differential backups greater than two weeks old and another to delete any transaction log backups greater than two weeks old.

Perhaps I'm overthinking this--is there an approach, using Ola's scripts, that makes more sense if I'm trying to always keep the last two weeks' worth of full, differential, and transaction log backups?

Perhaps I haven't communicated my goal well enough. The gist of it is that I want to delete the differential backups for all user databases that are > 2 weeks old, without actually taking a differential backup--using Ola's script. I want to do the same thing with transaction log backups.

For setting this up, I assume the simplest method would be to add @DatabasesInParallel='Y', to my parameters list on my existing SQL Server agent job, then in SSMS perform a Script Job As > Create To... on that job so I'll have a script to create an exact copy and then just change the @job_name in the script to the new job name so that the new job gets created with the same parameters and uses the same schedule? 2351a5e196