25 votes
Accepted

Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

The @CleanupTime is always specified for a specific backup job. For example, if you create a Full backup job, a Differential backup job and a Transaction Log backup job, then the @CleanupTime always ...
  • 15.4k
15 votes

Alternatives to Network Backup

There are ways to tune backups by messing with different knobs like MAXTRANSFERSIZE or BUFFERCOUNT, or striping the file (which you've noted you're already doing). The problem is that touching those ...
  • 34.4k
14 votes
Accepted

What are pros and cons of Ola’s scripts vs using maintenance plan?

I have written here Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient. To add more, Ola'...
  • 61.4k
14 votes
Accepted

Index fragmentation problem after disabling page-level locking for an Index

The answer to your query is buried in some documentation: Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options. The ...
  • 27.7k
13 votes

FillFactor: IndexOptimize script by Ola Hallengren

Ola's script will only change Fill Factor if you assign a value to the @FillFactor variable. IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @...
  • 34.4k
11 votes

Ola Hallengren Index maintenance on servers with Availability Groups

Yes, the script is intelligent enough. This is explicitly stated in the FAQ: How should I configure IndexOptimize for an Availability Group? Indexes can only be rebuilt or reorganized and ...
  • 15.5k
11 votes
Accepted

Fragmentation Level for Heaps

Heaps have a few special challenges that you can't experience with clustered indexes: Forwarded Records Captive Pages I'd suggest running sp_BlitzIndex against your database to find out if either of ...
  • 34.4k
11 votes
Accepted

Queries and updates extremely slow after IndexOptimize

I suspect you've got a different sample rate defined between your two maintenance approaches. I believe Ola's scripts use default sampling unless you specify the @StatisticsSample parameter, which it ...
10 votes
Accepted

Alternatives to Network Backup

The alternative you mentioned seems to be the best choice. What you can do is a 2 step process : Take native sql server backups with compression using Ola's backup solution locally. Use Robocopy to ...
  • 61.4k
9 votes
Accepted

Ola Hallengren Index Script not Reindexing

Ah! It's the image column. [column11] [image] NULL, Online only works on tables without blobs. Guidelines for Performing Online Index Operations
9 votes

Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

I upvoted @hot2use's answer as it covers this question in detail, but I did want to share an easy way to test this stuff. It might help you (as it helped me) to fully understand how the script works ...
9 votes
Accepted

Server Integrity Check CheckCommands question

If you run the base CHECKDB command, you don't need to run the others. The other commands make up what is run by the full command. See: CHECKDB From Every Angle: Complete description of all ...
  • 34.4k
9 votes

Alternatives to Network Backup

A couple of potential solutions: Going from full-only to a weekly full backup and nightly differential can be an easy solution. There are a number of performance-related parameters that you can tweak ...
  • 666
9 votes

How can I configure Ola Hallengren IndexOptimize script to run against a subset of tables

Don't defrag your indexes every night. You don't need to anymore on modern hardware (especially with solid-state storage with zero seek times). For daily maintenance, you only need to update modified ...
8 votes

DBCC CheckDb on Secondary Asynchronous Replica

Can I run Ola Hallengren CheckDb job on a Secondary Asynchronous Replica (DR) that is readable during business hours? Sure, assuming the routine doesn't try to update any command or history tables in ...
8 votes
Accepted

Table Fragmentation

Ola Hallengren's index optimize script does not perform Heap defragmentation. In other words table rebuilds are not happening when running the procedure and that is expected. You could run ALTER ...
7 votes

Ola's maintenance solution is not processing sysjob* tables

You need an additional parameter to tell Ola's script to consider Microsoft-shipped objects, since those job-related tables in msdb are marked as is_ms_shipped: @MSShippedObjects = N'Y' (This ...
7 votes
Accepted

How to change job output log location, when using Ola Hallengren's scripts

I’m going to reword your questions a little: Q: Should I log Agent jobs directly to a remote file server? No, because if you lose network connectivity or the file share goes down, your Agent job ...
  • 41.5k
7 votes

Filter Schema In Index Optimize Script

Yes, if you look at the documentation, you see that you can use the @indexes parameter to specify which indexes to defragment. You can use % wildcards in your object names and you can prefix them with ...
  • 15.5k
7 votes
Accepted

Ola Hallengren backup systax when excluding a database

it has to be @Databases = 'USER_DATABASES, -Db1' See https://ola.hallengren.com/sql-server-backup.html Assuming you want to backup all User Databases except one named DBA, your statement should ...
  • 61.4k
7 votes
Accepted

Scripting a Maintenance Plan in SQL Server Express

You've pretty much self answered here - you won't be able to rely on SQL Server to do the scheduling for you. The most common method is to use Windows Task Scheduler, but if you have any other job ...
7 votes
Accepted

IndexOptimize - Configuration

Why don't "statistics" options appear in the job's command? Because you updated the default parameter in the procedure call. This means that if you call the procedure without these parameters, E.G. ...
6 votes

Transaction Log Backups Serial or Parallel?

In general, always run your T-log backups in serial; many of my instances have a couple of dozen databases, and several that are very active, and the transaction log backups only take a few seconds ...
6 votes
Accepted

Transaction Log Backups Serial or Parallel?

Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel? I would suggest to setup one job that would backup the ...
  • 18.4k
6 votes
Accepted

How often should we use Update Statistcs.(More frequent or Not)

How often should we have it scheduled? You will need to decide this for yourself. It will depend on the modification (insert update delete)of your columns that are related to that statistics in ...
  • 12.3k
6 votes
Accepted

Index Optimize cause DB size increasement

Yes that is normal. See: Disk Space Requirements for Index DDL Operations When a new index structure is created, disk space for both the old (source) and new (target) structures is required in ...
  • 15.5k
6 votes

Ola Maintenance Solution and striped Azure URL backups - possible?

I have added support for striping of backups to Azure Blob Storage. https://ola.hallengren.com/versions.html Here is how it works: EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES' , @URL = '...
6 votes
Accepted

Full Back up over to Network failed due to unknown network error 59

Aaron's answer provides great advice on how to work around this issue. I thought I'd add an answer to provide some more technical details. According to Microsoft's err.exe utility, error 59 is ...
  • 67.6k
6 votes

Can a single database be excluded from an AG backup?

Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES, ALL_DATABASES, and AVAILABILITY_GROUP_DATABASES are supported. The hyphen character (-) is used to exclude databases, and the ...
  • 3,022
6 votes

What are pros and cons of Ola’s scripts vs using maintenance plan?

One of the major advantages of a script based solution is ease of deployment - something that is clearly relevant in your case, as you have 150+ servers. Trying to rollout several maintenance plans (i....
  • 468

Only top scored, non community-wiki answers of a minimum length are eligible