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
2 answers
93 views

How to prevent backup messages to be printed

In one of my servers, there are many databases which I take backup from the with Ola's script. Sometimes it happens that the backup job failed. But because many messages had been printed in job ...
Meyssam Toluie's user avatar
1 vote
2 answers
75 views

Full Database Backup failing

I have been using the Ola Hallengren script for a number of years and it works great. We recently set up a new SQL server and I installed the latest script. When I run the Full Database backup I get ...
Charles Spencer's user avatar
0 votes
0 answers
60 views

Why bulk of pages/extents are unallocated in sys.dm_db_file_page_usage

Could someone help shed some light on a strange sys.dm_db_file_page_usage result of a large database? sys.dm_db_file_space_usage result: |total_page_count | allocated_extent_page_count | ...
Mary W's user avatar
  • 11
2 votes
1 answer
80 views

IndexOptimize Question

I have a quick question: Is it safe to run IndexOptimize for stats update under SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid blocking as below? USE [DBA] GO SET TRANSACTION ISOLATION ...
Nauman Aziz's user avatar
1 vote
1 answer
44 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
1 answer
125 views

Questions about the @DatabasesInParallel parameter for the Ola Hallengren Index and Statistics Maintenance scripts?

I've used Ola Hallengren's Maintenance Solution for a while, but I've never used the @DatabasesInParallel parameter. I am considering using this on the Index and Statistics Maintenance jobs where I ...
Wayne Cochran's user avatar
0 votes
1 answer
100 views

Reducing Fragmentation on a clustered columstore index

I have a database with a clustered columnstore index on it. The table has 1,685,123,846 records in it. I use the ola hallengre script monthly to rebuild the index, but it takes so long to run before ...
Ross Dargan's user avatar
2 votes
2 answers
222 views

What happens if you stop the IndexOptimize job, because it is running long and causing blocking?

When the dbo.IndexOptimize job is running for long time and causing blocking, if we stop the job, does it cause Rollback of everything that was reorged or rebuilt? Or it just stop where it is and ...
veena ranganath's user avatar
0 votes
1 answer
68 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,541
-1 votes
1 answer
52 views

ola-hallengren- rebuilding indexes for individual index

Could you please explain how the parameter @Indexes='' should be used when we are maintaining a single index from a table? EXECUTE dbo.IndexOptimize @Databases = 'AdventureWorks', @FragmentationLow = ...
vivekanand Inamadar's user avatar
-2 votes
1 answer
207 views

Database Integrity check fails as follows

(I have used the Ola Hallengren scripts for quite a while)....but if there is an open query I end up with the following: Msg 5030, Sev 16, State 12, Line 1 : The database could not be exclusively ...
Ian Cope's user avatar
1 vote
1 answer
68 views

Should statistics be updated manually?

I'm currently considering using Ola Hallengren's Index Maintenance solution to update statistics once a day. What is the benefit of this solution if a database has AUTO_UPDATE_STATISTICS set to on? I'...
Marcus's user avatar
  • 59
1 vote
1 answer
173 views

Ola Hallengren's Maintenance Solution script doesn't dump transactional log file to Destination

At one of our client sites, I implemented a high availability (HA) solution. However, during the backup process, I encountered an issue related to backup environment configuration. We had configured ...
kedar's user avatar
  • 23
3 votes
1 answer
2k views

Update statistics on a 4 billion record table

On a SQL Server 2016 SP3 database, we have several (under 10) tables with billions of records. It's a 24/7 system with limited maintenance windows so it's essential that user impact is limited while ...
Rolann's user avatar
  • 41
2 votes
0 answers
182 views

Setting up Database Integrity Checks

I'm relatively new to SQL and, unfortunately, am definitely not at the level of a DBA - However, it's become my job to look into some SQL issues... There's a Database Integrity Check set to run for ...
Yeldur's user avatar
  • 21
0 votes
2 answers
137 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
318 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
53 views

Strategy to backup up databases based on their status obtained from another Table

I'm looking for a solution to backing up databases based on their status stored in a DB Table (Inactive databases stay online as well) i.e. Get the status of active DBs using SELECT dbName from DBMain....
MSKH's user avatar
  • 3
2 votes
1 answer
408 views

What permissions are needed for a user running Ola Hallengren's IndexOptimize stored procedure?

I'm planning to orchestrate my index maintenance with other jobs in my Azure SQL Database (serverless) using Azure Data Factory. The job will be run by the Managed Identity of my ADF service, and the ...
Widforss's user avatar
-1 votes
1 answer
436 views

Using maintenance script to Reindex specific tables in a database

Is there a way I can use Maintenance solution to reindex specific tables in a database. I am dividing tables in my db to be large and small. Need to reindex large tables only on the weekend and others ...
user2819187's user avatar
0 votes
0 answers
127 views

Commandlog table record is missing the errormessage for errornumber 2508

We have deployed ola hallangren maintenance scripts in our environment. We noticed a record in commandlog table with text similar to 'DBCC CHECKDB (databasename) WITH all_errormsgs, no_infomsgs, ...
deepak's user avatar
  • 107
-1 votes
1 answer
104 views

Any possibility that Ola Hallengren could add a job name prefix option to the script that creates all the maintenance jobs? [closed]

When creating the jobs, they sort together for most of them, but some of the jobs are alphabetically far away from the other Ola jobs on servers with hundreds of jobs. I would find it easier to see ...
EricI's user avatar
  • 99
3 votes
4 answers
1k 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
1 vote
2 answers
3k 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
0 votes
1 answer
644 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
162 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
2 votes
6 answers
3k 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 ...
variable's user avatar
  • 3,541
3 votes
1 answer
276 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 ...
Lithium's user avatar
  • 31
-2 votes
1 answer
474 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 ...
Tracy's user avatar
  • 1
0 votes
0 answers
95 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 ...
Lori Halsey's user avatar
-1 votes
1 answer
486 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 ...
Ties Blom's user avatar
0 votes
1 answer
728 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
370 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
2k views

Ola Hallengren maintenance solution

When I am executing the below query: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @...
Hany Helmy's user avatar
1 vote
1 answer
706 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. ...
Arun Kumar's user avatar
0 votes
1 answer
84 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 ...
Pajusisko's user avatar
0 votes
1 answer
590 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
1 vote
1 answer
476 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,049
1 vote
1 answer
411 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
0 votes
4 answers
201 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 ...
SQL_NoExpert's user avatar
  • 1,049
1 vote
2 answers
745 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 ...
Sattenapalli David's user avatar
1 vote
0 answers
145 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
  • 723
0 votes
1 answer
509 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
2 votes
2 answers
1k 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] [...
Preben Huybrechts's user avatar
1 vote
1 answer
256 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 ...
user1673665's user avatar
0 votes
2 answers
731 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 ...
sheldonhull's user avatar
0 votes
1 answer
214 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 ...
Tim Shelton's user avatar
0 votes
1 answer
614 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
234 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....
user2368632's user avatar
  • 1,087
4 votes
2 answers
373 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

1
2 3 4 5 6