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
2 votes
5 answers
600 views

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

I have configured Ola Hallengren's backup and integrity check scripts. However I want advice whether it is required to setup the SQL Server Index and Statistics Maintenance scripts, because my SQL ...
user avatar
  • 2,116
3 votes
1 answer
59 views

Cross-domain backup using Ola Hallengren's scripts

I have a backup repository on a different domain than the one where I have SQL Server. Is there any way I can define the credentials to access the repository on the backup itself? (domains are ...
user avatar
  • 31
-2 votes
1 answer
39 views

Is there dbo for shrinking the Transaction Log?

I'm looking for a System Stored Procedure to shrink our log file. Our database is in Simple mode but the log grows out of control, so once a week, I would like to call upon the procedure to shrink ...
user avatar
  • 1
0 votes
0 answers
39 views

Ola Hallengren Stored procs updated by windows patching?

we use OH maintenance solution on all of our SQL instances, no issues. On 01/23/2022, one of our SQL servers that is automatically patched with Windows patches went thru it's patching & rebooting ...
user avatar
-1 votes
1 answer
85 views

ola hallengren on Azure SQL

I'm new to the use of the Ola Hallengren optimize index scripts on Azure SQL, but with out of the box settings I notice that some indices with 80-90% defrag % en > 80.000 page counts are simply ...
user avatar
0 votes
1 answer
148 views

DatabaseBackup backs up the same database twice?

I use the Ola Hallengren DatabaseBackup procedure to run backups. I have two jobs configured so the backups can run parallel as I have one large database that takes 45 minutes to back up and many ...
user avatar
  • 51
0 votes
1 answer
48 views

Order of execution in Ola Index maintenance and Stats collection

I was thinking of performing The index maintenance and stats collection jobs in the below order for all databases in an Instance. step 1.Perform all index maintenance activity for all required Indexes(...
user avatar
0 votes
1 answer
441 views

Ola Hallengren maintenance solution

When I am executing the below query: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @...
user avatar
  • 41
0 votes
0 answers
42 views

Availability Group Backups For Ola H.'s Software

Using the Ola Hallengren's DatabaseBackup software on a SQL Server 2016 AG. Trying to use copy-only and tran log backups on secondary node, with Backup Preference set to 'Prefer Secondary'. Given ...
user avatar
1 vote
1 answer
211 views

Ola hallengren SQL Server Index and Statistics Maintenance solution

We are using the Ola hallengren SQL Server Index and Statistics Maintenance solution from the past 6 months in our production system. Script is only used for Update Statistics not Index maintenance. ...
user avatar
0 votes
0 answers
25 views

Clean up of old trn files

I've started using the backup solution from Ola's scripts and I've come across the below problem. I am getting my transaction log backups on my primary replica, and my full backups on a secondary, ...
user avatar
0 votes
1 answer
47 views

RESOURCE_SEMAPHORE wait during Full and transaction log backup done by Ola Hallengren's solution

We have an MSSQL instance 2017 on version 14.0.3370 and there are issues with RESOURCE_SEMAPHORE waits when full or transaction backup job runs. DBs hosts over 9000 small dbs with used space up to ...
user avatar
0 votes
0 answers
32 views

Failover an Availability Group during a backup

This is a scenario I hope won't happen, but if I was to perform a manual failover on an availability group halfway through a database backup will this cause the backup to fail or would it continue? I'...
user avatar
0 votes
1 answer
72 views

Ola Hallengren's IndexOptimize for Updating Statistics exclude schema

I am using Ola Hallengren's IndexOptimize for Updating Statistics only. Can I exclude a schema from the UpdateStatistics?
user avatar
1 vote
1 answer
99 views

DBCC Check DB fails due to operating system error 665

We have a SQL Server 2017 Standard version where recently the CHECKDB is failing with the operating system error 665. The fragmentation level of the disk drive is 0% as it is automatically ...
user avatar
  • 1,007
1 vote
1 answer
94 views

Schedule suggestion and index rebuild / database integrity progress for Ola Hallengren’s Maintenance Script

I have to configure Ola Hallengren's Maintenance Scripts for my SQL Server 2017. SQL Database size about total 400GB.Total 10 Dbs. But I have some questions. 1- I will use these schedules like below. ...
user avatar
  • 1,048
0 votes
3 answers
72 views

Index Maintenance on busy OLTP database

I have a database where constant read writes happening all the time. Searches are being done with wild card entries. The server is on SQL 2016 standard edition. There is no budget for enterprise nor ...
user avatar
  • 1,007
1 vote
2 answers
233 views

Ola Hallengren scripts: after a job failure, how to skip the databases that worked and backup only the remaining ones?

In our organization we have more than 150 databases in one instance and full backup should run for every day. Our backup script should be in such a way that if backup job fails while taking 100th ...
user avatar
1 vote
0 answers
50 views

Ola IndexOptimize Deadlocking On Self But No Error Record In CommandLog Table

I have seen this a couple times. There is a job running IndexOptimize that completes successfully, but running sp_BlitzLock will show a deadlock for one specific table. The deadlock command is ...
user avatar
  • 713
0 votes
1 answer
209 views

Ola Hallengren DataBaseBackup to URL results in un-equal file sizes causing IO Device Error

we have been using Ola's Maintenance Solution and Database backup direct to URL since we migrated our system to Azure IAAS. We have multi terabyte DB's (approx 40% Filestream data) and therefore ...
user avatar
  • 3
0 votes
0 answers
58 views

Backup cleanup Ola Hallengren on AG fails after failover

We run an Always On Availability Group in SQL Server 2017 Standard Edition. Backup is managed with Ola Hallengren Maintenance Solution with the following settings: EXECUTE [dbo].[DatabaseBackup] @...
user avatar
2 votes
2 answers
401 views

Improve performance of index reorganize on volatile table

This is occurring on SQL Server 2016 (Enterprise). Example Table structure: CREATE TABLE [dbo].[Foo]( [FooId] [bigint] IDENTITY(1,1) NOT NULL, [FirstFkId] [bigint] NULL, [SecondFkId] [...
user avatar
1 vote
1 answer
77 views

Questions on updating statistics and index maintenance jobs

Since my question Index and statistics optimization scripts duration and log bloat problem. Looking for good strategy? has been closed because it was not focused enough I've three questions: For ...
user avatar
0 votes
1 answer
298 views

How to ensure backups are successfully persisted to S3 when using Ola Hallengren solution on AWS EC2 Instances

Improving Existing Backup Approach By Using Ola Hallengren Backup Solution Deploying a new backup approach to eliminate dependency on a vendor tool (Cloudberry) that I've leveraged before. In my ...
user avatar
0 votes
1 answer
85 views

ola-hallengren running full backups and log backups at the same time-- restore issues?

I recently ran across an issue trying to restore a db and log files. DB restored without issues. About 10 log files in, I received a message stating the LSN was out of sync and could not finish. I ...
user avatar
0 votes
1 answer
234 views

Ola Hallengren's SQL Server Maintenance Solution & CMD Syntax

I'm using the free version of SQL Server Management Studio so I have to use CMD to call the SQL scripts. The official page gives an example of the CMD syntax used to call the "DatabaseBackup&...
user avatar
0 votes
1 answer
57 views

Error log folder location and OLA's SQL maintenance solution

Looking at one of our dev sql servers, it has a -e startup parameter to define the error log folder at this location : E:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG. However, only E:\DATA\MSSQL13....
user avatar
4 votes
2 answers
219 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 ...
user avatar
1 vote
1 answer
60 views

Excluding log shipped DB from current database maintenance plan (Ola's solution)

The Senior DBA and I were discussing our most recent rollout of log shipping to 25+ servers and one of the issues we had ran into during our pilot was the log chain breaking randomly. We narrowed down ...
user avatar
0 votes
1 answer
143 views

@TimeLimit setting in Ola-Hallengren's IndexOptimize Running Much Longer Than The Value Set

We start our indexing at 10:00 PM every evening and we want to allow it run until 4:30 AM, the next day. Therefore, we set the @timelimit variable / parameter to 23400 seconds. The issue is that the ...
user avatar
  • 1
1 vote
1 answer
173 views

Any potential cons associated with enabling backup checksums when performing backups?

I noticed that Ola Hallengren's backup sp has backup checksums disabled by default. This made me curious to know if there are any potential cons (i.e. increased CPU, IO, long backup times, etc) ...
user avatar
0 votes
0 answers
221 views

Ola Backup scripts, How do you specify a different filename for the mirrored set?

I'm using the backup script below as a test. I want to use a different file name for the mirror. The format should be @FileName = '{ServerName}-DEFAULT-{DatabaseName}-{BackupType}-MIRROR_{FileNumber}....
user avatar
1 vote
1 answer
323 views

Ola Hallengren's backups scripts, cleanup only mode

Is it possible to run Ola Hallengren's backup scripts in cleanup only mode? I have a couple of servers where the cleanup time was set too long, and I now have to clean up hundreds of databases worth ...
user avatar
  • 365
1 vote
1 answer
246 views

How can I capture error thrown by SET_LOCKTIMEOUT in ola Hallengren

A stats only job for Ola Hallengren was blocked for the 180 seconds as specified below. The job threw an error and emailed me and the other DBA. However, upon review of the CommandLog table and the ...
user avatar
  • 362
1 vote
1 answer
136 views

If dbo.DatabaseIntegrityCheck fails, what happens, i.e. how am I notified?

I'm new to Ola Hallengren's MaintenanceSolution (which looks fantastic), and DBA-ing generally. I've created a job to run dbo.DatabaseIntegrityCheck (weekly against ALL_DATABASES). If there are errors ...
user avatar
  • 1,258
0 votes
0 answers
50 views

update stats on specific statistic not working?

I am using Ola Hallengren's maintenance solution and have come across something really strange. I grabbed the command from the log table as below: UPDATE STATISTICS [dbo].[APIActivityLog] [...
user avatar
-1 votes
1 answer
77 views

Ola Hallengren Database Backup keeps failing (for both SYSTEM and USER databases) [closed]

Both USER and SYSTEM backup database jobs have been running fine until some weeks back when I checked my alerts (bearing in mind that this is on DEV where I do my own testing etc). I am just confused ...
user avatar
0 votes
2 answers
216 views

Ola Hallengren MaintenanceSolution - Differential - the value for @ModificationLevel is not supported

I just installed this great tool, mostly it's working fine, just that if I include for a differential backup the parameter @ModificationLevel = 50 (or anything else) the action fails: Msg 50000, ...
user avatar
0 votes
1 answer
515 views

Trouble with differential backup - full backup not being recognized

I have recently implemented backups using https://ola.hallengren.com/. Backups EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES, -%Archive', @Directory = N'H:\sqlbackup', @BackupType = '...
user avatar
  • 1,005
2 votes
2 answers
374 views

dbcc checkdb runs for interminably long time, 50% of the time

I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database. The integrity check part is ...
user avatar
  • 21
6 votes
4 answers
2k 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 ...
user avatar
  • 61
1 vote
1 answer
54 views

Ola Scripts installation in "Multi-server" Jobs Folder [closed]

I am installing the Ola scripts on a Central Management Server for multi-server administration. How can I get the jobs to show up under "Multi-server" Jobs Folder instead of the "Local Jobs" folder?
user avatar
  • 11
0 votes
1 answer
93 views

DBCC CHECKDB running longer

We are using Ola's Maintenance solution and part of that we have job DatabaseIntegrityCheck - User Database and System Databases, sometimes job for some of the databases it's running longer like in ...
user avatar
  • 11
2 votes
1 answer
165 views

ola-hallengren backup solution with encryption is not encrypting

Installed OH's scripts and they run great. sqlserver 2017 standard edition in a testing/dev environment. testing before rolling out to production. I did a test of a restoration of an encrypted, or so ...
user avatar
-2 votes
1 answer
306 views

Using Ola Hallengren's backup solution - Can't exclude databases [closed]

I am using the newest version of Ola's scripts. When backing-up databases, I'm experiencing an issue with excluding certain databases. The website states, if you want to backup all User database ...
user avatar
  • 21
1 vote
1 answer
210 views

Ola Hallengren SQL Server Backup - CleanupTime

When the @CleanupTime parameter is used in the [dbo].[DatabaseBackup] procedure, are the targeted old backup files deleted after each successful database backup? Or are all the backups processed ...
user avatar
0 votes
0 answers
191 views

Ola Hallengren’s SQL Server Maintenance Solution using a different user

I am looking at using Ola Hallengren’s SQL Server Maintenance Solution to create a backup plan. However, I cannot figure out how to run the Agent Job (T-SQL script) as a different user as the ...
user avatar
  • 11
1 vote
1 answer
47 views

How to control reindexing that results in high log send queue

During reindexing using Ola's IndexOptimize stored procedure, the log send queues for remote asynchronous replicas can grow quite large if there are a lot of large index defrags, requiring gigantic ...
user avatar
  • 7,713
0 votes
1 answer
393 views

msdb system table fragmentation

I have a Index Maintenance job for msdb database to rebuild those fragmented index in msdb database. But I still found some backup related table in msdb database (e.g. backupmediafamily, backupset, ...
user avatar
0 votes
1 answer
91 views

Index / Check DB Script question

I'm using Ola Hallengren's maintenance scripts and I wanted to see if I could create 2 tables so that it logs integrity checks and index optimizations to different tables.
user avatar

1
2 3 4 5