27 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 ...
John K. N.'s user avatar
  • 17.8k
15 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 ...
Josh Darnell's user avatar
  • 29.3k
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 ...
Erik Darling's user avatar
  • 40.8k
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'...
Kin Shah's user avatar
  • 62.2k
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 @...
Erik Darling's user avatar
  • 40.8k
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 ...
Tom V's user avatar
  • 15.7k
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 ...
Erik Darling's user avatar
  • 40.8k
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 ...
John Eisbrener's user avatar
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 ...
Kin Shah's user avatar
  • 62.2k
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 ...
Scott Hodgin - Retired's user avatar
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 ...
Erik Darling's user avatar
  • 40.8k
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 ...
Patrick's user avatar
  • 678
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 ...
Randolph West's user avatar
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 ...
Sean Gallardy's user avatar
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 ...
Randi Vertongen's user avatar
8 votes

Is there any purpose of Ola Hallengren's SQL Server Index and Statistics Maintenance scripts on sql server running on SAN storage?

The "index optimize" job created by Ola's install script focuses on defragmentation, not statistics update. It will do: Nothing if the fragmentation level is below 5% REORG if it is between ...
Tibor Karaszi's user avatar
7 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 ...
SqlWorldWide's user avatar
  • 13.2k
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 ...
Brent Ozar's user avatar
7 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 = '...
Ola Hallengren's user avatar
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 ...
Tom V's user avatar
  • 15.7k
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 ...
Kin Shah's user avatar
  • 62.2k
7 votes
Accepted

DBCC CheckDb on Secondary Asynchronous Replica

This method is not safe because it is almost meaningless. It is a quote of P. Randal who is author/architect of DBCC CHECKDB and SQL Server storage subsystem. Any of the answers where DBCC CHECKDB ...
Alex S's user avatar
  • 122
7 votes
Accepted

SQL Server Index and Statistics Maintenance performance question

Can somebody share with me his experience in using IndexOptimize for a database with a very high number of indexes ? You are right, Ola's script first gets data from all the index related DMV's and ...
Randi Vertongen's user avatar
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 ...
LowlyDBA - John M's user avatar
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. ...
Randi Vertongen's user avatar
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 ...
Anti-weakpasswords's user avatar
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 ...
Shanky's user avatar
  • 19k
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 ...
Tom V's user avatar
  • 15.7k
6 votes

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

If you are having network reliability issues: speak to your network/SAN team (and/or use a different file share that doesn't exhibit the problem) Back up the file, temporarily, to the local SQL ...
Aaron Bertrand's user avatar
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 ...
Hannah Vernon's user avatar
  • 70.1k

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