Skip to main content
Share Your Experience: Take the 2024 Developer Survey

Questions tagged [ola-hallengren]

Refers to the set of SQL Server Management Scripts created and maintained by Ola Hallengren. Questions tagged with this tag should be about how to use the scripts or about how to resolve an issue with the script.

Filter by
Sorted by
Tagged with
15 votes
3 answers
889 views

Transaction Log Backups Serial or Parallel?

We happen to be using SQL Server 2012 Standard Edition. I also happen to use Ola Hallengren's scripts to provide an easy, more flexible framework for doing backups and maintenance. This question isn'...
Chris Aldrich's user avatar
12 votes
3 answers
18k views

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

I am having trouble understanding what exactly to expect from the CleanupTime option in the Ola Hallengren Server Maintenance Solution. I'm finding some related questions, and elaborate answers, but ...
Wouter's user avatar
  • 821
12 votes
3 answers
2k views

Queries and updates extremely slow after IndexOptimize

Database SQL Server 2017 Enterprise CU16 14.0.3076.1 We recently tried switching from the default Index Rebuild maintenance jobs to the Ola Hallengren IndexOptimize. The default Index Rebuild jobs ...
Martin Bergström's user avatar
11 votes
4 answers
2k views

Alternatives to Network Backup

In our environment we have some servers that are in an Always On Availability Group, and some that are standalone. We normally backup to a network share, but we have recently observed that as the ...
SQL_NoExpert's user avatar
  • 1,061
10 votes
5 answers
5k views

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

Will you please help me understand pros and cons of using Ola’s solution over maintenance plan? I have prepared a presentation based on SQL Pass (http://www.pass.org/DownloadFile.aspx?File=ebae1b31) ...
Pat's user avatar
  • 337
8 votes
1 answer
943 views

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

I have an Index which has page level locking disabled and now I have index fragmentation issues for that index. I was aware that I won't be able to Reorganize the index, but I believed that I would ...
user9516827's user avatar
  • 1,315
8 votes
2 answers
4k views

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

We usually use Ola Hallengren's solution that takes care of Update Statistics which is inside the Index optimize job that we have. We have it scheduled for every Sunday midnight in all servers (1500+) ...
Ramakant Dadhichi's user avatar
7 votes
2 answers
898 views

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

When using Ola Hallengren's SQL Server Maintenance Solution, the job output log is going to a local directory on the SQL server. I was thinking of placing these logs on a fileserver to allow access to ...
cspell's user avatar
  • 501
7 votes
2 answers
6k views

IndexOptimize - Configuration

We recently switched to Ola Hallengren's maintenance script and automated the deployment of MaintenanceSolution.sql to our customers's SQL Server instance. We need to set these parameters for the job ...
William's user avatar
  • 73
7 votes
3 answers
5k views

(Ola Hallengren) Deleting log backups older than last FULL backup

When I deploy Ola Hallengren's backup script into my servers, I always set the @CleanupTime parameter for log backups to zero. This way, every time the log backup job runs, it checks for log backup ...
Guillermo Garcia's user avatar
7 votes
1 answer
2k views

Ola Hallengren Index Script not Reindexing

First, I realize a similar question was asked and the poster had page count set to 1000 for a 679 page index; not what's going on. I have Ola's script set as @Databases nvarchar(max) ,@...
jbkelly's user avatar
  • 93
7 votes
3 answers
3k views

Ola Hallengren index maintenance - long periods of time between commands?

I run the Ola Hallengren scripts on all of my servers for index and statistics maintenance. When I look through the command log table, I notice long periods of time between a command ending, and the ...
Erik Darling's user avatar
  • 41.2k
7 votes
2 answers
11k views

How to maintain Index and Statistics on AWS RDS?

In the process of setting up the SQL Server Index and Statistics Maintenance code on our AWS RDS instance, I've managed to set up the scripts and can run it from SSMS. Please advice on how to setup ...
Andy Felton's user avatar
6 votes
2 answers
1k views

Ola Hallengren with database mirroring

I am about to setup Ola Hallengrens database maintenance plan. We have our database mirrored and I was just wanting to know if I need to run the script on both my Sql Server instances or only the ...
Peuge's user avatar
  • 209
6 votes
2 answers
1k views

FillFactor: IndexOptimize script by Ola Hallengren

We are using Ola Hallengren's IndexOptimize script for index rebuild on SQL Server 2008 and 2012. These maintenance scripts are very good and have worked for our DBs for months now. However, very ...
Nancy's user avatar
  • 211
6 votes
5 answers
5k views

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

I'm using Ola Hallengren IndexOptimize script against a SQL 2016 database that is 7 TB in size, with over 300,000 tables. I only have a 6 hour window each night to manage indexes. I'm using the ...
johna's user avatar
  • 61
6 votes
2 answers
10k views

Ola Hallengren backup failed

I have been using on my SQL Server 2008R2 server Ola Hallengren solutions for almost two years. Last night backups failed, both System databases and user databases with error below. Example of ...
SQL_DBA_Bini's user avatar
6 votes
4 answers
38k views

The system cannot find the file specified

I'm running into a problem when trying to run any of the jobs created by Ola's script: The process could not be created for step 1 of job 0x19DAD4A7019FB04D9C984027FCA2F4E3 (reason: The system ...
UIowaJeremy's user avatar
5 votes
2 answers
3k views

Ola Hallengren Index maintenance on servers with Availability Groups

I'm configuring the Ola Hallengren scripts for Index maintenance on all servers in may Availability Group cluster which has several AG's and databases which are not in AG's. If I select the "...
Phil Holmes's user avatar
5 votes
3 answers
14k views

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

I am recently seeing the Unknown Netowrk Error 59 which saves the full back ups to a file share on some of the servers . The back ups happens at different times. Ola Halengren scripts are being used ...
SQL_NoExpert's user avatar
  • 1,061
5 votes
1 answer
447 views

Ola Hallengren: encrypt, but verify?

I've been using Ola Hallengren's scripts for a few years now, and a recent upgrade to MSSQL2017 has allowed me to start testing encrypted back-ups. Before anyone asks: yes, master keys and encryption ...
Laurens Bakker's user avatar
5 votes
1 answer
1k views

Excluding Read only databases using Ola Hallengren script

Is there a way to exclude Standby or Read only databases from Index maintenance and also from backups using Ola Hallengren scripts.
David's user avatar
  • 51
5 votes
3 answers
2k views

Backup to local and MIRROR TO Azure Files returns ERROR

I have an Azure VM with SQL Server 2014, with a local drive (X:) for storing backups. Using Ola Hallengren's scripts. I added an Azure Files share and tried to do a mirror backup, but I get the ...
nulldotzero's user avatar
5 votes
1 answer
880 views

Specify different log file locations with Hallengren scripts

I appreciate Ola Hallengren scripts. Being new to SQL Server, they've made my life so much easier. Thank you Mr. Hallengren. I have 5 user databases that I need to back up at separate times, so ...
Sherrie's user avatar
  • 55
5 votes
1 answer
4k views

Getting a proxy account to backup to a share using Ola Hallengrens backup script

I have a SQL Server 2012 Standard install where the Engine and Agent are running under the default NT Service accounts Engine - NT Service\MSSQLSERVER Agent - NT Service\SQLSERVERAGENT I am trying ...
Lawrage's user avatar
  • 446
5 votes
1 answer
812 views

SQL Server mirrored backup using cluster shared volumes

I have SQL Server 2014 cluster with 2 nodes and a third server with iSCSI target server configured on it, all of them are virtual Servers with Windows Server 2012 R2. Both nodes of cluster are iSCSI ...
cesarlinux's user avatar
5 votes
1 answer
991 views

How is Index rebuild affected by IN_ROW_DATA vs LOB DATA

I am using Ola Hallengren's scripts to maintain my databases. On a few of them there are indices that don't seem to be benefiting from the maintenance. I know that his scripts have a lower threshold ...
Slothy's user avatar
  • 61
5 votes
1 answer
550 views

Why is the lease locked for a some full sql backups to Azure Blob using Ola Hallengren backup scripts?

I'm using SQL Server 2012 and working on backing up to Azure Blob using Ola Hallengren backup scripts. Some backups are lease locked (ex. model) while others are not (ex. msdb). It's inconsistent, it ...
AP1's user avatar
  • 51
4 votes
1 answer
1k views

Fragmentation Level for Heaps

I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and ...
Learning_DBAdmin's user avatar
4 votes
2 answers
382 views

DIFFs promoted to FULLs

We use Ola Hallengren's DatabaseBackup stored procedure to backup a load of SharePoint databases on a SQL Server 2012 instance into Azure blob storage. We have been doing this for quite a while ...
Oleksandr's user avatar
4 votes
1 answer
692 views

Server Integrity Check CheckCommands question

I am trying to figure out what is the best CheckCommands option for ServerIntegrity check. Should I leave the job defaults as is? EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES'...
Mike's user avatar
  • 43
4 votes
2 answers
1k views

SQL Server Index and Statistics Maintenance performance question

I tried to use IndexOptimize stored procedure on a database with a very high number of indexes (> 250.000). The initial step where stored procedure collects data what needs to be processed takes hours,...
Joerg Rueschenschmidt's user avatar
4 votes
1 answer
2k views

Will Ola's default IndexOptimizer job update my stats?

I just installed Ola Hallengren's MaintenanceSolution.sql maintenance scripts. After looking at the output of the log generated by the IndexOptimize - USER_DATABASES job, I'm concerned that it's not ...
poke's user avatar
  • 527
4 votes
1 answer
3k views

Scripting a Maintenance Plan in SQL Server Express

How would I run each aspect of Ola Halengren's sql maintenance plan on SQL Express? It's my understanding that each job should be run in a specific order. I'm sure that a .bat or cmd file can do ...
Dave Morse's user avatar
4 votes
3 answers
2k views

Deploying Ola Hallengrens scripts - Index Optimization

Never before I used Ola Hallengren Scripts . I am planning to deploy the Ola Hallengren scripts for Index Optimization for the first time. I need to deploy them in PROD so I want to be more careful. ...
Learning_Learning's user avatar
4 votes
2 answers
764 views

Ola Hallengreen Backup Retry

Is there a way to do an automatic backup re-try utilizing Ola Hallengren's back up scripts for just one database back up if there is a failure? Looking for a way to complete the back up without re-...
Jessica's user avatar
  • 41
4 votes
2 answers
2k views

How Ola Hallengrens Index maintenance scripts handles columnstore indexes?

I'd like to know how Ola's script handles columnstore indexes. Couln't find any information about that, only in the version history it was mentioned that script does have a support. My experience is ...
VeePee's user avatar
  • 43
4 votes
1 answer
526 views

BACKUP WITH CHECKSUM works but DBCC CHECKDB detects problems

A server I administer recently experienced a power outage that caused issues with the SSRS ReportServerTempDB. After the outage, the nightly job that runs Ola's DatabaseIntegriyCheck started reporting ...
poke's user avatar
  • 527
4 votes
1 answer
2k views

Availability Group backup preference not changing to primary

I have an availability group setup and I'm using Ola Hallengren backup scripts. At setup of the Availability group, the default option of Prefer Secondary was set, where it should have been changed to ...
Dennis Finke's user avatar
4 votes
1 answer
6k views

Hallengren @DatabasesInParallel sql_message_id 50000 in sysjobhistory table

Just implemented Ola's Parallel execution for backups, integrity checks and index maintenance. Everything appears to be running great but I am seeing sql_message_id 50000 in the sysjobhistory table ...
Jeff Gerew's user avatar
4 votes
2 answers
653 views

Is it good practice to rebuild index on FileTable?

We are using Ola Hallengren scripts, both to backup our databases and for index maintenance. We have a FileTable in our database and from time to time, REBUILDING its PK index takes close to three (3) ...
EricLanglois's user avatar
4 votes
3 answers
5k views

Ola Hallengren solution, backups not cleaning up

I have about 30 SQL Server 2005-2014 instances, all of which use the excellent Ola Hallengran maintenance script to produce my full/diff/log backups. This has worked for me for years with no issues, ...
Peter's user avatar
  • 1,490
4 votes
1 answer
1k views

Why is my DB size increasing when rebuildling an index using sort in tempDB in SQL Server?

I am running Ola Hallengren's SQL Maintenance scripts using the following command: EXECUTE [dbo].[IndexOptimize] @Databases = 'MyDatabase' , @FragmentationLow = NULL , @FragmentationMedium = '...
Woody's user avatar
  • 155
4 votes
0 answers
832 views

Allow developers to run database maintenance

We use the database solution by Ola Hallengren. We're trying to give our developers a way to run the maintenance stored procedures after they do an ETL. Our developers have locked down permissions. We'...
Elijah W. Gagne's user avatar
3 votes
2 answers
2k views

DBCC CheckDb on Secondary Asynchronous Replica

Can I run Ola Hallengren's CheckDB job on a Secondary Asynchronous Replica (DR) that is readable during business hours? Currently the Asynchronous Replica is not readable... First question is if it ...
SQL_NoExpert's user avatar
  • 1,061
3 votes
3 answers
716 views

Does Index Rebuild Reduces physical read from disk

Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index ...
SQL_NoExpert's user avatar
  • 1,061
3 votes
2 answers
465 views

Can a single database be excluded from an AG backup?

I want to backup all the databases in an availability group except one. Using the @AvailabilityGroups option in Ola's Maintenance Solution scripts, can a single database in an Availability Group be ...
Phil Holmes's user avatar
3 votes
4 answers
2k views

How do I restore multiple transaction log backups after restoring a full backup SQLSERVER 2019

I am creating my DR strategy, and my full back-up recovers fine. The full backups are taken with a modified version of the Ola Hallengren scripts. The file timestamp of the full backup shows 07/26/...
Thomas's user avatar
  • 37
3 votes
1 answer
10k views

Table Fragmentation

If I execute the following: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index', indexstats.alloc_unit_type_desc, indexstats.avg_fragmentation_in_percent as ...
Round's user avatar
  • 123
3 votes
1 answer
9k views

Ola Hallengren backup systax when excluding a database [closed]

I would like to backup most of the databases except the database DBA. I use below syntax in the job step: EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', '-DBA' @Directory = N'G:...
Anne's user avatar
  • 101

1
2 3 4 5 6