Skip to main content

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
1 vote
1 answer
1k views

Copy contents of network drive to local folder [closed]

I have an automated third party backup places files on a network drive. I've mapped this drive to a local folder on the DB server using the following PowerShell script $pass ="xxxx"|ConvertTo-...
Asher's user avatar
  • 303
1 vote
2 answers
759 views

How to take backups on a defined directory instead of creating sub-directories

I am using Ola-Hallengren scripts and now I have a situation in which I require to take backups without creating sub-directories for each DB. I need to define a centralized path like C:\Backups for ...
Sourabh's user avatar
  • 13
1 vote
1 answer
1k views

sqlcmd issues with -E and -S flags

Consider the following command, which I've wrapped for readability: sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'C:\...
PaulB's user avatar
  • 11
1 vote
1 answer
1k views

Backing Up an AG with Ola Hallengren

I'm trying to understand the labyrinth that is the rules about AG backups.I've got Ola Hallengren's scripts installed on machines TST01/TST02. 02 is the preferred replica for backups. I have an AG ...
Lee M's user avatar
  • 388
1 vote
2 answers
1k views

Set retention for SQL backup to URL

I am using Ola Hallengrens DatabaseBackup script. I am storing my backups in Azure Blob storage using the @url parameter. Everything is working fine, however I am looking for a way to set a retention ...
Peuge's user avatar
  • 209
1 vote
2 answers
410 views

Proper way to setup SQL Agent account

I have an AlwaysOn setup in which the jobs are running on domain service account which have been given access to a file share for backups. The jobs are not able to run. This is the error message ...
buddinggeek's user avatar
1 vote
1 answer
4k views

Ola Hallengren's Backup solution error message after moving files using cmdexec stops job, this disagrees with faq

I am using Ola Hallengren's Maintenance script, and I am having an issue which seems documented in the FAQ. For context, there was an issue where the existing SQL Server backups had to be moved to ...
ConstantineK's user avatar
1 vote
2 answers
320 views

Do user database settings affect maintenance job times for system databases?

I have an SQL Server 2008 and for maintenance solutions I use Ola Hallengren scripts. Recently, out of 15 databases I have on this server, I made two changes in three of them (not system databases, ...
xrpapak's user avatar
  • 51
1 vote
0 answers
38 views

DST Adjustments to Ola Hallengren's backup jobs not working as intended [closed]

I have been using Ola Hallengren's backup/maintenance scripts for a few years but one thing that usually plagued me once a year was our backup location going near capacity after spring forward DST. I ...
Matt's user avatar
  • 365
1 vote
1 answer
56 views

CleanUp Time not working for DIFF Backups

We have a database server where the test database is "refreshed" with the backup of prod database every day at 22:00. The first backup after the restore is a differential backup, because we ...
Guenter's user avatar
  • 13
1 vote
2 answers
4k views

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Data source name not found and no default driver specified

I recently upgraded from SQL Server 2017 Express to SQL Server 2019 Express and installed Microsoft SQL Server ODBC Driver 18. However, a sqlcmd script to back up all databases, using Ola Hallengren's ...
Mike Pace's user avatar
1 vote
1 answer
554 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 ...
SQL_NoExpert's user avatar
  • 1,061
1 vote
1 answer
452 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. ...
Cell-o's user avatar
  • 1,088
1 vote
0 answers
161 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 ...
BCM's user avatar
  • 733
1 vote
1 answer
504 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 ...
Lee M's user avatar
  • 388
1 vote
0 answers
102 views

update statistics in ola.hallengren maintenance solution [closed]

Is there a reason the default IndexOptimize job not include: @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y' We will have to remember to change t incude above two lines to the job. Is there ...
Anne's user avatar
  • 101
1 vote
0 answers
479 views

OLA Output File Cleanup - Better Options

Does anyone have a way to change the Ola Hallengren "Output File Cleanup" job that doesn't require CmdExec? My company is trying to eliminate CmdExec where ever possible. I was thinking a PowerShell ...
JonDBA5420's user avatar
1 vote
0 answers
2k views

Ola Hallengren Backup - stuck at 100% complete

Full Backup started on 4/21 12 AM. While backing up one of the database, it is stuck. sp_WhoIsActive shows following information wait_info: (1208ms)PREEMPTIVE_OS_WAITFORSINGLEOBJEC CPU - 188,528,...
Santhoshkumar KB's user avatar
1 vote
0 answers
595 views

HowTo: [Output File Cleanup] workaround for UNC targets

Hallengren job [Output File Cleanup] silently fails when the output file directory is a network file share. Question: Is there a better, cleaner way to resolve the issue detailed below when the job ...
M S's user avatar
  • 11
1 vote
0 answers
223 views

Customize - Ola Hallengren script

Trying to incorporate Date wise folder in the script. Following is the original code INSERT INTO @CurrentDirectories (ID, DirectoryPath, Mirror, DirectoryNumber, CreateCompleted, CleanupCompleted) ...
user3540063's user avatar
1 vote
0 answers
814 views

Modifying Ola Hallengrens index maintenance script for deadlocks [closed]

In a SQL Server 2012 Enterprise database without a true maintenance window, I would like to run Ola Hallengrens script to optimize indices. Testing has shown that while indices are reorganized and ...
HugoRune's user avatar
  • 361
0 votes
3 answers
1k views

Problem generating restore scripts for multiple ola backups

I am using Ola for maintenance solution. I have 300 databases I want restore those databases using scripts rather than going through one by one. I prepare the following script to generate restore ...
SQL_DBA_Bini's user avatar
0 votes
2 answers
2k views

Amazon RDS Ola Hallengren

Very new to SQL on AWS RDS so. Need to create maintenance jobs on an AWS RDS SQL2016 instance. I know parts of Ola Hallengren's life saver maintenance script works, Index, stats and integrity. ...
Stockburn's user avatar
  • 419
0 votes
1 answer
5k views

How do you skip databases with Ola.Hallengren's scripts?

I have a few databases in which the frontend application manages certain maintenance tasks. In this particular instance, I'm running the INDEX OPTIMIZE job every night and every so often it clashes ...
AKDiscer's user avatar
  • 1,385
0 votes
1 answer
3k views

SQL Server Backups in Parallel

Currently I am using ola hallengren backup and maintenance scripts. I have a question: I have 5 big databases in one server. It's taking approximate 10+ hours everyday to complete full backups. ...
ven's user avatar
  • 13
0 votes
1 answer
110 views

Whether to run integrity check before or after backups (Ola H FAQ)?

Going through Ola H FAQ's, https://ola.hallengren.com/frequently-asked-questions.html, I see that I also recommend that you perform the full backup after the integrity check. Isn't the opposite true?...
variable's user avatar
  • 3,554
0 votes
1 answer
712 views

Cannot Backup to Network Share using Ola Hallengren's Scripts

SQL Version: Microsoft SQL Server 2016(SP3)(KB5003279) - 13.0.6300.2 (X64) OS Version: Windows Server 2012 R2 Standard I am trying to setup the backups and database maintenance jobs using Ola ...
Linger's user avatar
  • 115
0 votes
1 answer
539 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 ...
krdba's user avatar
  • 3
0 votes
1 answer
1k 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 ...
RDSago's user avatar
  • 1
0 votes
2 answers
340 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, ...
Norman Henderson's user avatar
0 votes
1 answer
1k views

Can not backup multiple (not all) databases Ola Hallengren [closed]

This error is quite easy to replicate. So before I send a bug to Ola, I'd like to check if maybe I'm doing it wrong. Version is 2018-06-27 20:44:12 I created 3 databases. DB1, DB2 and DB3. I'd like ...
stack_henk's user avatar
0 votes
2 answers
2k views

Rebuild Index Causing Data File Growth Issue

I have a database which is 200 GB in size and I have scheduled a Rebuild Index maintenance plan for all databases every Saturday at 2:00 AM. The job ran for 8 hours and then it failed. I knew while ...
Sunder Bolla's user avatar
0 votes
2 answers
163 views

IndexOptimize procedure doesn't obey @MaxNumberOfPages when only updating statistics

I'd like to use EXECUTE [master].dbo.IndexOptimize @Databases = 'mydatabase', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @...
Endrju's user avatar
  • 126
0 votes
1 answer
728 views

Ola Hallengren Transaction Log Backups of System Databases

I am attempting to backup the transaction logs of master, model in addition to the msdb. MSDB transaction log backs up without any issues, but the master and model do not. Here is the script that ...
Bill's user avatar
  • 13
0 votes
2 answers
103 views

Dealing with varying fill factors in a SQL Server 2014 environment

I've recently become a DBA of sorts for our company's ERP system. I've been making small changes to the server & improvements have been steady. One area I'm concerned with is the index fill factor....
thatstevecena's user avatar
0 votes
1 answer
407 views

Using Ola Hallengren's succeeds to take full backups but fails to take a differential

I take weekly Full backups (all my databases are in Full recovery model) on Sundays and then schedule the Diffs to run nightly (except on Sundays). Looking through my logs I see this: Cannot perform ...
user263099's user avatar
0 votes
1 answer
174 views

ola hallengren @databasesinparallel parameter on Azure SQLServer

I have a serverless azure sql server instance with about 50 databases and I am trying to get the @databasesinparallel parameter to work to reindex all databases at the same time rather than one at a ...
Justin Gunderson's user avatar
0 votes
2 answers
802 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 ...
Ken's user avatar
  • 71
0 votes
1 answer
422 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(...
abi ram's user avatar
0 votes
1 answer
678 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?
Jared Barnard's user avatar
0 votes
1 answer
651 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&...
Pixxel Wizzard's user avatar
0 votes
1 answer
127 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.
Anil Gupta's user avatar
0 votes
1 answer
440 views

How do I get more control over backup file retention using Ola Hallengren's DatabaseBackup solution?

I've noticed some unexpected behavior using the database backup job in Ola's Maintenance Solution. On the test instance I'm working on, I have the user database backup job scheduled to run at 2:00am ...
Jeff Edmunds's user avatar
0 votes
1 answer
73 views

What rights do you need for Ola's integrity check?

I'm configuring a new server and Ola's integrity check is working for user databases (I have restored a database of around 50 GB for tests) and is failing for system databases. If I look in the logs ...
Sacha K's user avatar
  • 211
0 votes
1 answer
2k views

sp_DatabaseRestore msg 50000

I used Ola's script to backup my database (SQL Server 2017) and now I'm trying to restore it using Brent's sp_DatabaseRestore. Currently trying to do a Full restore only. Here is my code, written ...
Riddle-Master's user avatar
0 votes
1 answer
79 views

Cleanup of AG databases on secondaries nodes

Consider a scenario where you have an AG running across 3 nodes and your backups are being backed up locally. Each node have all the default jobs deployed and scheduled to run at same time. As the ...
Tiago's user avatar
  • 279
0 votes
1 answer
288 views

Ola Hallengren - DatabaseBackup SQL Version - Backup to Azure requirements

I see that the DatabaseBackup requires SQL Server 2012 SP1 CU2 (11.03339). I have a Server using SP1 (11.0.3000), and I have verified Backing up to azure blob storage does work. Is there a reason the ...
Justin's user avatar
  • 145
0 votes
1 answer
555 views

Using Ola Hallengren’s Maintenance in SQL Server 2016 with TDE Enabled databases and compression

I’m using Ola Hallengren’s maintenance and backup solution and looking into leveraging TDE compression as I have all the databases in instance using TDE impression. Is there a way of getting benefit ...
Im88's user avatar
  • 326
0 votes
1 answer
133 views

Find all stats that were sampled

I am using Ola's scripts for stats updates and for one particular index I want to do full scans when updating stats (to solve for the Ascending Key problem in conjunction with trace flag 2389). We ...
Chris Woods's user avatar
  • 1,771
0 votes
1 answer
48 views

Preferred seconday backup in AG - what about the primary ? Shows no backups taken

I did setup the Ola Scripts sticking to https://www.brentozar.com/archive/2015/06/how-to-configure-alwayson-ag-backups-with-ola-hallengrens-scripts/ Options are: @Databases = 'USER_DATABASES', @...
Thomas D's user avatar