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.
270
questions
0
votes
1
answer
28
views
Ola Hallengren Maintenance Solution Upgrade
I’m relatively new to SQL Server and my company is currently using an older version (2014) of the Ola Hallengren Maintenance Solution. We’re looking to upgrade to a version that supports SQL Server ...
0
votes
0
answers
38
views
Index and Statistics Maintenance Scheduling Questions
I'm working on setting up a maintenance plans for some SQL Server instances. I'm trying to find a good/safe staring point to start the maintenance plan on and I can adjust and make appropriate changes ...
- 1
-2
votes
0
answers
28
views
backups, filestream, maxfilesize
Seems the calculations to compute the number of files based on @maxFileSize when backing up multiple TB of filestream data are not working. Does anyone have a work around other than explicitly ...
0
votes
2
answers
88
views
script to change the schedule of a job, it needs to find the schedule associated with the job
using Ola's backup strategy,
there is a job
databaseBackup - system databases full, as you can see on the picture below.
this job is scheduled to run once a week, on a sunday morning.
that is all good,...
- 16.4k
-1
votes
1
answer
50
views
Ola Hallengren: not accepting two databases in IndexOptimize scripts [closed]
In Ola's documentation there is this procedure:
USER_DATABASES, -Db1 All user databases, except Db1 If you want make
in all database except in DB1.
But the second parameter is marked with red ...
-4
votes
1
answer
80
views
Reorganize or Rebuild Spatial Indexes
Will there ever be a way to exclude or only include spatial indexes from the IndexOptimize tool?
- 125
0
votes
1
answer
60
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',
@...
- 1
0
votes
0
answers
65
views
Backup Directory Path Ola Maintenance Script: platform agnostic path on Windows q:/backup/folder
We are using configuration management, specifically Chef, to install SQL server on many new VMs. Part of the cookbook specifies the default SQL directories including the backup directory. In order to ...
0
votes
0
answers
171
views
SQL Backups with SQL 2022 Contained Availability Group
We have a SQL 2022 Always On Availabilty Group with Contained databases; a new feature in this version of SQL. The master and msdb databases are now also in the Avalability Group. We like this setup, ...
0
votes
0
answers
42
views
Automatic backup deletion?
I backup servers to a network share, all is well. But one backup must be kept so I move it to a new folder and rename it, this should keep the backup indefinitely but I'm wondering if it got auto ...
- 101
1
vote
0
answers
45
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 ...
- 365
0
votes
1
answer
201
views
What are recommended values for block size, buffercount and maxtransfer size for Ola backups to Azure?
I have SQL servers running on onprem and Azure VM hardware.
The backups write to a file share located on a network VM.
I am wanting to change the backup destination to Azure blob storage.
For this I ...
- 3,451
0
votes
1
answer
48
views
SQL Server Backup Solution - Cleaning Up Dropped Databases
I'm just wondering how people who use Olas (fantastic) SQL Server Backup Solution deal with dropped databases.
The CleanupTime and CleanupMode parameters only operate on databases that currently exist ...
0
votes
2
answers
106
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 ...
- 231
1
vote
2
answers
193
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 ...
0
votes
0
answers
68
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 | ...
- 11
2
votes
1
answer
175
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 ...
- 23
1
vote
1
answer
76
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 ...
- 13
1
vote
1
answer
368
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 ...
0
votes
1
answer
240
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 ...
- 101
2
votes
2
answers
631
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 ...
0
votes
1
answer
150
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?...
- 3,451
-1
votes
1
answer
94
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 = ...
-2
votes
1
answer
405
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 ...
- 1
1
vote
1
answer
106
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'...
- 69
1
vote
1
answer
304
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 ...
- 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 ...
- 41
2
votes
0
answers
309
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 ...
- 21
0
votes
2
answers
198
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',
@...
- 204
0
votes
1
answer
472
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 ...
0
votes
1
answer
59
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....
- 3
2
votes
1
answer
594
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 ...
- 23
-1
votes
1
answer
640
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 ...
0
votes
0
answers
177
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, ...
- 117
-1
votes
1
answer
134
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 ...
- 99
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/...
- 37
1
vote
2
answers
5k
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 ...
- 11
0
votes
1
answer
775
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 ...
- 115
0
votes
1
answer
179
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 ...
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 ...
- 3,451
3
votes
1
answer
334
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 ...
- 31
-2
votes
1
answer
551
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 ...
- 1
0
votes
0
answers
129
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 ...
-1
votes
1
answer
527
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 ...
- 11
0
votes
2
answers
862
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 ...
- 181
0
votes
1
answer
490
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(...
- 1
1
vote
1
answer
3k
views
Ola Hallengren maintenance solution
When I am executing the below query:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@...
- 51
1
vote
1
answer
753
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. ...
- 11
0
votes
1
answer
92
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 ...
- 21
0
votes
1
answer
761
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?
