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
0
votes
0answers
24 views

Restoring database created with Ola's tools from Azure blob

I'm exploring using Ola Hallengren's excellent backup tools to backup to an azure blob. Is there a simple way to do a restore of the latest backups on a fresh MSSQL 2017 database directly from azure ...
3
votes
1answer
176 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 ...
0
votes
1answer
40 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 ...
1
vote
1answer
59 views

LOG backup Always ON using Ola Hallengren database backup script does not carry out a backup

I am trying to run a LOG backup on the primary node of an Always ON SQL Server 2012 environment. The secondary server is only used for replication. no read or other access permitted. (licensing of ...
0
votes
0answers
75 views

I can not Ola Hallengren database backup script to Data Domain make works

Ola Hallengren has a nice SQL Maintenance scripts that I installed to our dev SQL server. The backup script works very well to a local drive, however it does not work to Data Domain. At all. Here is ...
2
votes
1answer
221 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 ...
2
votes
2answers
109 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,...
3
votes
1answer
186 views

How to handle failed Index Optimization (Ola Hallengren) step in SQL Azure Elastic Job Agent?

I have created a SQL maintenance job using Azure Elastic Job Agent with the following step: EXEC jobs.sp_add_jobstep @job_name = @jobName, @step_name = 'Optimize indexes and statistics', @command=N' ...
4
votes
1answer
185 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 ...
0
votes
1answer
100 views

Ola Hallengren for Managed instance

How can we configure Ola Hallengren's solution on a managed instance? (We want output file for each run, storing it is an issue) If we want backup retention longer than 35 days for the database in ...
1
vote
2answers
77 views

Logging to file

I am using the ola-hallengren maintenance scripts. I am logging to table and file. I want to change the location of the file logs. Can I do this? Many thanks
0
votes
1answer
38 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 ...
2
votes
1answer
262 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 ...
0
votes
0answers
37 views

Ola Hallengren - Parallel Backup Jobs failing on SINGLE_USER DB - 'Database is in transition' [952]

I'm using Ola's DatabasesInParallel option for "multi-threaded" execution. Last night a db was temporarily placed into SINGLE_USER for an application-based archive process and it caused all 5 threads ...
0
votes
0answers
34 views

Does OLA Scripts support striping for backup to URL?

I am testing backups to blob using OLA scripts. However, I am unable to stripe the backups into multiple files. I tried using 'Numberoffiles' parameter. Please advise. I am using a shared access ...
0
votes
0answers
21 views

Index Optimize with PartitionLevel='N' fails on SQL Express Edition 2017

If I'm running the IndexOptimize SP with @PartitionLevel='N' the SP fails with Error: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "partitions.partition_number" could not be bound. ...
1
vote
1answer
113 views

DBCC check on Always On Secondary Replica

What is the best way to schedule DBCC check on all the nodes in Always On availability group. Can we just schedule the same job on all the nodes at the same time including all the secondaries. In my ...
0
votes
2answers
110 views

Database Maintenance Job and Backup Scheduling

I would request to have expert advise on scheduling maintenance and backup jobs. Below was the scenario prior to my change: Full backup of database scheduled to run at 12:30AM everyday. Differential ...
-1
votes
1answer
42 views

Running Backup on single DB when scheduled job (USER_DATABASES) has failed

I'm using Ola Hallengren's sp to take backup of database. FULL DIFF and LOG backup is created with respective schedules (agent job) and has been working great. Due to not enough free disk space on ...
1
vote
2answers
62 views

Ola Hallengren IntegrityCheck Excludes TempDB

According to SQL Server Maintenance Solution - SQL Server Integrity Check, the SYSTEM_DATABASES value includes only master, msdb, and model. Why is TempDB excluded, when it too can have hardware ...
4
votes
2answers
265 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-...
1
vote
2answers
138 views

SQL Server Backup failing for just 1 DB (“The system cannot find the path specified.”)

For the last few mornings, I have had a backup of our ReportServerTempDB fail. We are not using SSRS on this server yet, but will in the next month, so the DB is the default 8mb .mdf and 8mb .ldf (i.e....
-2
votes
1answer
41 views

Database Backup to URL fails with Device I/O Error [closed]

I'm using Ola's Maintenance Solution to backup my databases to a URL. Most of my databases backup just fine but I have one 2 TB database that continues to fail with a device I/O error. Has anyone ...
2
votes
1answer
174 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 ...
1
vote
1answer
73 views

Exclude database form AG Backup

Is it possible to exclude a database from an availability group backup? My backup jobs looks like this: EXECUTE [dbo].[DatabaseBackup] @Databases = 'AVAILABILITY_GROUP_DATABASES' @URL= xxx, @...
0
votes
1answer
77 views

Ola scripts = log shipping and transaction logs

We use the Ola Hallengren scripts for full/diff/tran log backups. Ola's site indicates that log shipping is compatible unless we are using transaction logs. Why is that?
0
votes
1answer
314 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 ...
0
votes
1answer
100 views

DatabaseIntegrityCheck fails intermittently

Have been running the Ola Hallengren scripts on our new SQL Server 2016 Enterprise server for about 6 weeks or so now. The database Integrity script only works intermittently on some instances. ...
1
vote
1answer
97 views

Ola Hallengren - Backupdatabase Task

When running Ola's full SQL Server Maintenance Solution or the individual databasebackup script I get the following error: MSG 207, Level 16, State 1, Procedure DatabaseBackup, Line 1545 Invalid ...
0
votes
1answer
66 views

Reason for @@TRANCOUNT check?

we're using the SQL functions from Ola Hallengren since many years. Sometime between 2015 and 2018 the check for open transactions was added in IndexOptimize. So now, after upgrade to the current ...
1
vote
1answer
60 views

how to integrate ola hallengren solution with log shipping?

I need to set up log-shipping from my SQLPROD2 to server2 but I am facing the following problems: 1) I already use ola hallegren backup solution, and it provides the 15 min log backups for all ...
0
votes
1answer
38 views

Custom Deletion for Ola Hallengren Scripts

I am trying to find a way to delete backups being taken by Ola's native SQL backups. I am trying to keep only 2 weeks of transaction logs, 5 weeks of diffs, ,14 weeks of full backups, 13 monthly full ...
1
vote
1answer
62 views

Custom backup deletion Ola Scripts

I am trying to find a way to delete backups being taken by Ola's native SQL backups. I am trying to keep only 14 days of transaction logs, 1 month of diffs, and 12 months of full weekend backups. Is ...
0
votes
1answer
21 views

Index Maintenance with Ola starting from day x (7 or 9) till the end of month

I want to run the index maintenance from Ola daily, but in my company the firsts business days every month I can't, cause heavy application usage during the night. So I want to run Ola's script only ...
0
votes
1answer
126 views

The value for the parameter @MirrorDirectory is not supported error with Ola Hallengren Backup script

I am using Ola Hallengren's Maintenance Solution, and it's been working great for our environment. Quick question regarding the User Database backups s proc. Currently, when I'm taking backups, the ...
1
vote
2answers
261 views

Trouble with Ola Hallengren's @DatabasesInParallel parameter

I am testing out this new parameter @DatabasesInParallel and it seems to only back up 1 databases at a time but it doesn't specificy this as a limitation in the documentation so I am wondering if I ...
1
vote
1answer
197 views

SQL Server hangs on index rebuild, works well after SQL service restart

We have a SQL Server 2014 enterprise cluster. Server 1 and Server 2. SQL Server is limited to 64GB of memory. We are using Ola Hallengren scripts for index rebuilding and optimizations once per week ...
1
vote
0answers
120 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 ...
1
vote
2answers
143 views

SQL Maintenance Tasks on Azure [Ola Hallengren Scripts]

I am trying to implement the SQL maintenance scripts from Ola on my Azure elastic pool databases. I did the steps as provided in this link. I could create the job, but I always get this error after ...
0
votes
1answer
51 views

Excluding Read only Log backups using ola-hallengren script

I know that Full backups are excluded from readable secondaries, but how do I exclude log backups from read-only secondaries? The is_read_only column in sys.databases does not get set on ...
1
vote
2answers
460 views

DBCC CheckDb on Secondary Aynchronous Replica

Can I run Ola Hallengren 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 is ...
-1
votes
1answer
91 views

ola-hallengren cleanup will not remove AOAG old DB backups if server fails over

ola-hallengren cleanup will not remove AOAG old DB backups if server fails over anyone else experience this?
0
votes
1answer
170 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 ...
3
votes
1answer
279 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 ...
0
votes
1answer
100 views

Index Maintenance - Reorganize After BIG SQL Server Shrink

I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database. EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @...
4
votes
2answers
1k 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 ...
0
votes
1answer
182 views

Ola Hallengren Index Maintenance with compressed indexes

If the Ola Hallengren index maintenance solution is installed onto a server which already has compression applied to several of the indexes, will the script respect the current compression level when ...
0
votes
2answers
95 views

Optionally exclude certain partitions using OLA's scripts

One scenario. I have been using OLA's scripts for a long time and love them. I have a very large database - 20+ TB with hundreds of partitioned tables. I purge old data by dropping (merging the old ...
1
vote
1answer
153 views

Index Maintenance for DW- DB ( ETL process )

I'm planning to set up a t-sql job to perform index maintenance in a DW DB (Stores data through an ETL process) currently overnight and eventually will need to be run more frequently as the business ...
0
votes
2answers
177 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 ...