Database Administrators
Q&A for database professionals who wish to improve their database skills
Latest Questions
0
votes
1
answers
24
views
Maintenance Solution on Contained Availability Groups
When dealing with Contained Availability Groups are you installing Ola's Maintenance Solution while connected through the listener or separately on each replica?
When dealing with Contained Availability Groups are you installing Ola's Maintenance Solution while connected through the listener or separately on each replica?
CrankBait 72
(1 rep)
Jul 30, 2025, 10:23 PM
• Last activity: Aug 1, 2025, 12:55 AM
2
votes
1
answers
76
views
What are the downsides of running a Physical Only Database Integrity Check job
I am unable to find an article yet, that is able to clearly help my understanding of the risks I run by running my Ola Hallengren DatabaseIntegrityCheck job with PHYSICALONLY parameter on our large databases. There is no mention of a FULL check being done: [https://ola.hallengren.com/][1] > Run Inte...
I am unable to find an article yet, that is able to clearly help my understanding of the risks I run by running my Ola Hallengren DatabaseIntegrityCheck job with PHYSICALONLY parameter on our large databases. There is no mention of a FULL check being done:
[https://ola.hallengren.com/]
> Run Integrity Checks of Very Large Databases The SQL Server
> Maintenance Solution has been designed to do integrity checks of very
> large databases. In the DatabaseIntegrityCheck procedure you can
> choose do the checks on the database level, the filegroup level, or
> the table level. It also supports limiting the checks to the physical
> structures of the database:
>
> EXECUTE dbo.DatabaseIntegrityCheck @Databases = 'USER_DATABASES',
> @CheckCommands = 'CHECKDB', @PhysicalOnly = 'Y'
These checks are limited to checking only the physical consistency i.e. that all data is present and can be read.
Is someone able to clarify for me please, or point me to an article that can clearly help me with exactly what are those logical checks that this PhysicalOnly check ignores, and what risks could be associated with ignoring the logical checks or doing a FULL check less frequently
PTL_SQL
(427 rep)
Jul 21, 2025, 10:36 PM
• Last activity: Jul 23, 2025, 10:54 AM
0
votes
1
answers
336
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.MSSQLSERVER\MSSQL exists on disk. I can see Ola's SQL maintenance output files on disk at E:\DATA\MSSQL...
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.MSSQLSERVER\MSSQL exists on disk. I can see Ola's SQL maintenance output files on disk at E:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log
Who / what is responsible for creating the error log folder as defined by the -e startup parameter?
Does OLA's maintenance solution have a fallback if it can't find the log folder defined with -E startup parameter?
user2368632
(1133 rep)
Nov 18, 2020, 03:22 PM
• Last activity: Jul 20, 2025, 04:06 AM
5
votes
3
answers
2294
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 that script didn't do anything to fragmented columnstore index. Is that by design?
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 that script didn't do anything to fragmented columnstore index. Is that by design?
VeePee
(53 rep)
Aug 28, 2018, 07:41 AM
• Last activity: Jun 21, 2025, 07:27 PM
1
votes
1
answers
594
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 output file, there was no record of what the error was. I eventually figured out what had happened by...
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 output file, there was no record of what the error was. I eventually figured out what had happened by looking at our monitoring tool, noticing the blocking for exactly the amount of time as the locketimout value and then looking at Ola's site to find that it throws a severity 16 error when the lock timeout is reached.
Is there a way to have the Ola scripts log the error so it's easier to figure out what went wrong?
EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES' ,
@FragmentationLow = NULL ,
@FragmentationMedium = NULL ,
@FragmentationHigh = NULL ,
@UpdateStatistics = 'ALL' ,
@OnlyModifiedStatistics = N'Y' ,
@LockTimeout = 180,
@LogToTable = N'Y';
Lee M
(396 rep)
Jul 17, 2020, 07:09 PM
• Last activity: Jun 17, 2025, 02:00 AM
0
votes
4
answers
301
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 any intention to not using the wild card searches, and there is no maintenance window. Since wildcard...
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 any intention to not using the wild card searches, and there is no maintenance window.
Since wildcard searches are being made indexes are also of no use as it is doing a full table scan and thus creates locking.
I am using Ola Hallengren script for indexing but the indexes maintenance is taking 10-12 hours to complete for a 300gb database as it busy 24/7. Below is the script:
EXECUTE dbo.IndexOptimize
@Databases = 'user_databases',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
This has become a frustrating issue and I am about to quit.
Please advise what are my options here.
SQL_NoExpert
(1117 rep)
Mar 23, 2021, 02:28 PM
• Last activity: May 22, 2025, 02:03 PM
0
votes
1
answers
257
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?
ola-hallengren cleanup will not remove AOAG old DB backups if server fails over
anyone else experience this?
Paul
(11 rep)
Sep 4, 2018, 04:21 PM
• Last activity: May 21, 2025, 05:04 PM
0
votes
1
answers
286
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 used a tool that puts them in order, so that's not my issue. I have my log backups set to run in 15 min...
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 used a tool that puts them in order, so that's not my issue. I have my log backups set to run in 15 minute intervals. This large database takes a couple of hours to perform a full backup. Could the log backups happening simultaneously during the full backup cause this situation?
The end result was not bad this time, but this is alarming. I am trying to maintain a RPO of 15 minutes.
This little test resulted in an 8 hr RPO.... unacceptable.
Regards,
Tim
Tim Shelton
(1 rep)
Dec 14, 2020, 06:52 PM
• Last activity: May 21, 2025, 11:07 AM
1
votes
1
answers
119
views
Ola Hallengren backup script not splitting files as expected
I'm using Ola Hallengren backup script, with the following configuration: >EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = NULL, @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 192, -- 2 backups, so w/in 8 days @CleanupMode = 'BEFORE_BACKUP', @CheckSum = 'Y', @LogToTab...
I'm using Ola Hallengren backup script, with the following configuration:
>EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = NULL,
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 192, -- 2 backups, so w/in 8 days
@CleanupMode = 'BEFORE_BACKUP',
@CheckSum = 'Y',
@LogToTable = 'Y',
@MaxFileSize = 256000 --Split files larger than 250GB
But my backups got split into 42 files, each sized just under 47GB. I wanted to split into <250GB files. Is something wrong with my configuration?
MAW74656
(394 rep)
May 12, 2025, 07:35 PM
• Last activity: May 14, 2025, 07:26 PM
2
votes
1
answers
766
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 defragmented weekly. The database is 24/7 OLTP database. Only way the checkdb completes is when we turn off the...
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 defragmented weekly.
The database is 24/7 OLTP database. Only way the checkdb completes is when we turn off the application. Now online solution suggests
1) Increase the size of the disk drive, I have 800gb free space on the drive which has a database with 550 gb used space
2) Defragment the data drive- Currently automatically defragment weekly
3) Break the database into smaller files- Currently not possible due to downtime
4) Consider placing the database files on ReFS volume which does not have the same ATTRIBUTE_LIST_ENTRY limits that NTFS presents. You must reformat the current NTFS volume using ReFS. – Again not possible due to downtime
Currently the checkdb checks with the option Physical only and using Ola Hallengreen script
My short term proposal is to do the checkdb into another server. But I am running out of ideas if there are other ways to cure the problem without any downtime.
SQL_NoExpert
(1117 rep)
Apr 22, 2021, 03:02 PM
• Last activity: May 12, 2025, 02:02 AM
1
votes
1
answers
259
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? That is - running integrity check after the backup is better because then you'd know for sure wh...
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? That is - running integrity check after the backup is better because then you'd know for sure whether that backup is corrupted or not.
variable
(3590 rep)
Sep 30, 2023, 04:22 PM
• Last activity: May 8, 2025, 12:03 PM
3
votes
1
answers
379
views
Index rebuild blocks itself and runs in serializable isolation level
I’ve implemented Ola Hallengren maintenance solution in one database, and I’m noticing that it’s getting blocked by itself, I don’t know why honestly. What I have noticed, is that it seems to be run within a serializable isolation level, which is weird since the default isolation level is read commi...
I’ve implemented Ola Hallengren maintenance solution in one database, and I’m noticing that it’s getting blocked by itself, I don’t know why honestly.
What I have noticed, is that it seems to be run within a serializable isolation level, which is weird since the default isolation level is read committed, and the maintenance script does not change it explicitly.
So, the questions are:
- Could the isolation level be related to this locking issue?
- Assuming that indexes are rebuild in sequential way….why would the script block itself within the same SPID?
Blocked process report sample can be found here .
Output of
Blocked process report sample can be found here .
Output of @LogToTable='Y' can be found here .
The process does complete without errors. This is still a problem, due to the fact that all running user requests are put on hold while the reindex process is running. Duration would not be an issue if no locks were generated. Since a lot of locks are generated, then duration (2 hours) is indeed an issue on a heavily used database.
dsuy
(480 rep)
Jun 1, 2018, 09:37 PM
• Last activity: Apr 24, 2025, 01:08 PM
2
votes
1
answers
75
views
Database Backups Error
I have just upgraded my SQL version from 2012 to 2022. Also, the databases now live on an EC2 VM in AWS. When I try to run a job, I get the error message: >(Message 50000) The @@SERVERNAME does not match SERVERPROPERTY('ServerName') I followed the http link suggested, but it made no sense to me. Can...
I have just upgraded my SQL version from 2012 to 2022. Also, the databases now live on an EC2 VM in AWS. When I try to run a job, I get the error message:
>(Message 50000) The @@SERVERNAME does not match SERVERPROPERTY('ServerName')
I followed the http link suggested, but it made no sense to me. Can someone explain what I need to do?
Mike
(21 rep)
Apr 22, 2025, 01:19 AM
• Last activity: Apr 23, 2025, 02:03 PM
3
votes
1
answers
181
views
xp_delete_file on linux throws Login failed for user 'servername\Administrator'
We are running a newly set up SQL Server Docker container (mcr.microsoft.com/mssql/server:2019-CU32-ubuntu-20.04). All user databases are restored from an Windows SQL Server installation and so far everything looks good. Except our backups, created with SQL Server Maintenance Solution (from Ola Hall...
We are running a newly set up SQL Server Docker container (mcr.microsoft.com/mssql/server:2019-CU32-ubuntu-20.04). All user databases are restored from an Windows SQL Server installation and so far everything looks good.
Except our backups, created with SQL Server Maintenance Solution (from Ola Hallengren), are not deleted.
There seems to be a problem with xp_delete_file, because when the cleanup of the backup script runs, we get a large number of "Login failed" error messages in SQL Server container logs.
When I try to run xp_delete_file manually I get the same error in the logs every time I run it:
2025-03-28 14:04:35.93 Logon Error: 18456, Severity: 14, State: 5.
2025-03-28 14:04:35.93 Logon Login failed for user 'SERVERNAME\Administrator'. Reason: Could not find a login matching the name provided. [CLIENT: 127.0.0.1]
Scarsto
(31 rep)
Mar 28, 2025, 01:29 PM
• Last activity: Mar 31, 2025, 10:59 AM
0
votes
1
answers
63
views
IndexOptimize on VLDBs with tight maintenance window
Our team has started to use IndexOptimize to replace our current index maintenance solution. We have a tight two hour maintenance window to perform our index maintenance, so on our 1TB+ databases often the entire two hours is spent returning fragmentation levels from sys.dm_db_index_physical_stats....
Our team has started to use IndexOptimize to replace our current index maintenance solution. We have a tight two hour maintenance window to perform our index maintenance, so on our 1TB+ databases often the entire two hours is spent returning fragmentation levels from sys.dm_db_index_physical_stats. When the two hours is up no index maintenance has actually been done. Has anyone encountered similar issues and come up with any good workarounds for this while using IndexOptimize?
We've considered using one job to execute IndexOptimize with Execute='N' before the maintenance window, and a second job to run the commands in the CommandLog table from the first job run. I'm just not sure the best way to automate that.
I would greatly appreciate any suggestions or recommendations!
sayjo1581
(1 rep)
Mar 18, 2025, 08:00 PM
• Last activity: Mar 19, 2025, 12:32 AM
4
votes
1
answers
326
views
IndexOptimize and Empty Statistics
A database that I support uses IndexOptimize to maintain indexes and statistics. Auto-update statistics is turned off in this database, so IndexOptimize is solely responsible for updating statistics. Recently, I noticed several empty statistics on tables that have rows. The call to IndexOptimize is...
A database that I support uses IndexOptimize to maintain indexes and statistics. Auto-update statistics is turned off in this database, so IndexOptimize is solely responsible for updating statistics.
Recently, I noticed several empty statistics on tables that have rows. The call to IndexOptimize is below. If I change the @OnlyModifiedStatistics parameter from 'Y' to 'N', it will update those empty statistics. However, it will also cause it to update all statistics, which is not desired.
Is there a way to have IndexOptimze only update modified stats AND empty stats on tables with rows?
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 2,
@FragmentationLevel2 = 5,
@MinNumberOfPages = 0,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@StatisticsSample = 100,
@Indexes='ALL_INDEXES',
@LogToTable = 'Y',
@DatabaseOrder='DATABASE_SIZE_DESC',
@TimeLimit=61200,
@WaitAtLowPriorityMaxDuration = 5,
@WaitAtLowPriorityAbortAfterWait = 'SELF'
TurkeyMortgage
(143 rep)
Feb 17, 2025, 10:39 PM
• Last activity: Feb 18, 2025, 02:09 AM
7
votes
2
answers
7013
views
IndexOptimize - Configuration
We recently switched to Ola Hallengren's maintenance script and automated the deployment of [*MaintenanceSolution.sql*][1] to our customers's SQL Server instance. We need to set these parameters for the job *IndexOptimize - USER_DATABASES*: * `@UpdateStatistics = 'ALL'` * `@OnlyModifiedStatistics =...
We recently switched to Ola Hallengren's maintenance script and automated the deployment of *MaintenanceSolution.sql* to our customers's SQL Server instance.
We need to set these parameters for the job *IndexOptimize - USER_DATABASES*:
* 
After I change the values of the above-mentioned parameters in *MaintenanceSolution.sql* to
My questions are:
1. Why don't "statistics" options appear in the job's command?
2. Is it wrong to edit *MaintenanceSolution.sql* directly for this?
3. Is there a way to add theses parameters to the job using a query?
@UpdateStatistics = 'ALL'
* @OnlyModifiedStatistics = 'Y'
I see that these parameters are present in *MaintenanceSolution.sql*:
After I change the values of the above-mentioned parameters in *MaintenanceSolution.sql* to
@UpdateStatistics nvarchar(max) = 'ALL'
@OnlyModifiedStatistics nvarchar(max) = 'Y'@UpdateStatistics = 'ALL' or @OnlyModifiedStatistics = 'Y' added in *Job Step properties - IndexOptimize - USER_DATABASES*:
My questions are:
1. Why don't "statistics" options appear in the job's command?
2. Is it wrong to edit *MaintenanceSolution.sql* directly for this?
3. Is there a way to add theses parameters to the job using a query?
William
(73 rep)
Aug 21, 2019, 10:38 AM
• Last activity: Feb 13, 2025, 09:58 AM
0
votes
2
answers
677
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. What is the best practices for this ? or What do you recommended? DatabaseBackup – SYSTEM_DATABASES...
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. What is the best practices for this ? or What do you recommended?
DatabaseBackup – SYSTEM_DATABASES – FULL job – Daily at 12:05AM
DatabaseIntegrityCheck – SYSTEM_DATABASES job – Daily at 12:10AM
IndexOptimize – USER_DATABASES job – Daily at 1:00AM
2- How can I check the progress / status when index optimize and databasSee integrity job ?
Cell-o
(1106 rep)
Mar 26, 2021, 12:04 PM
• Last activity: Feb 7, 2025, 11:04 AM
0
votes
0
answers
34
views
Some of our backup jobs have begun to hang, and quietly cause some issues
We’ve been using SQL Server Maintenance Solutions for decades, but recently I’ve started encountering issues with missing log backups. There are no error messages, and the jobs are still scheduled, but when I check the job history, I see that it hasn’t run since [X time]. Upon further investigation,...
We’ve been using SQL Server Maintenance Solutions for decades, but recently I’ve started encountering issues with missing log backups. There are no error messages, and the jobs are still scheduled, but when I check the job history, I see that it hasn’t run since [X time]. Upon further investigation, I found that the session executing the job is waiting on the OS (specifically on PREEMPTIVE_OS_GETPROCADDRESS). It also seems to be tied to the xp_create_subdir and xp_fileexist procs.
The issue can be temporarily resolved by killing the process, though it won't clear until a restart. Once I do that, the jobs start running again. This issue happens anywhere from a few times a week to not at all for several months.
I’d appreciate any help in identifying the root cause.
Pat Trongo
(1 rep)
Jan 24, 2025, 05:17 PM
2
votes
1
answers
1247
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 of backups. I could bang something together with PowerShell or similar, but I'd be more comfortable w...
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 of backups. I could bang something together with PowerShell or similar, but I'd be more comfortable with some known good code handling it instead.
longneck
(375 rep)
Aug 17, 2020, 03:08 PM
• Last activity: Jan 11, 2025, 04:01 PM
Showing page 1 of 20 total questions