26
votes
Accepted
Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups
The @CleanupTime is always specified for a specific backup job. For example, if you create a Full backup job, a Differential backup job and a Transaction Log backup job, then the @CleanupTime always ...
- 16.2k
15
votes
Alternatives to Network Backup
There are ways to tune backups by messing with different knobs like MAXTRANSFERSIZE or BUFFERCOUNT, or striping the file (which you've noted you're already doing).
The problem is that touching those ...
- 36.7k
14
votes
Accepted
What are pros and cons of Ola’s scripts vs using maintenance plan?
I have written here
Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient.
To add more,
Ola'...
- 61.6k
14
votes
Accepted
Index fragmentation problem after disabling page-level locking for an Index
The answer to your query is buried in some documentation:
Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options.
The ...
- 28.5k
13
votes
FillFactor: IndexOptimize script by Ola Hallengren
Ola's script will only change Fill Factor if you assign a value to the @FillFactor variable.
IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @...
- 36.7k
11
votes
Ola Hallengren Index maintenance on servers with Availability Groups
Yes, the script is intelligent enough.
This is explicitly stated in the FAQ:
How should I configure IndexOptimize for an Availability Group?
Indexes can only be rebuilt or reorganized and ...
- 15.5k
11
votes
Accepted
Fragmentation Level for Heaps
Heaps have a few special challenges that you can't experience with clustered indexes:
Forwarded Records
Captive Pages
I'd suggest running sp_BlitzIndex against your database to find out if either of ...
- 36.7k
11
votes
Accepted
Queries and updates extremely slow after IndexOptimize
I suspect you've got a different sample rate defined between your two maintenance approaches. I believe Ola's scripts use default sampling unless you specify the @StatisticsSample parameter, which it ...
- 9,202
10
votes
Accepted
Alternatives to Network Backup
The alternative you mentioned seems to be the best choice.
What you can do is a 2 step process :
Take native sql server backups with compression using Ola's backup solution locally.
Use Robocopy to ...
- 61.6k
9
votes
Accepted
Ola Hallengren Index Script not Reindexing
Ah! It's the image column.
[column11] [image] NULL,
Online only works on tables without blobs.
Guidelines for Performing Online Index Operations
- 670
9
votes
Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups
I upvoted @hot2use's answer as it covers this question in detail, but I did want to share an easy way to test this stuff.
It might help you (as it helped me) to fully understand how the script works ...
- 23.5k
9
votes
Accepted
Server Integrity Check CheckCommands question
If you run the base CHECKDB command, you don't need to run the others. The other commands make up what is run by the full command.
See:
CHECKDB From Every Angle: Complete description of all ...
- 36.7k
9
votes
Alternatives to Network Backup
A couple of potential solutions:
Going from full-only to a weekly full backup and nightly differential can be an easy solution.
There are a number of performance-related parameters that you can tweak ...
- 676
9
votes
How can I configure Ola Hallengren IndexOptimize script to run against a subset of tables
Don't defrag your indexes every night. You don't need to anymore on modern hardware (especially with solid-state storage with zero seek times). For daily maintenance, you only need to update modified ...
- 3,603
8
votes
DBCC CheckDb on Secondary Asynchronous Replica
Can I run Ola Hallengren CheckDb job on a Secondary Asynchronous Replica (DR) that is readable during business hours?
Sure, assuming the routine doesn't try to update any command or history tables in ...
- 27.3k
8
votes
Accepted
Table Fragmentation
Ola Hallengren's index optimize script does not perform Heap defragmentation.
In other words table rebuilds are not happening when running the procedure and that is expected.
You could run ALTER ...
- 16.2k
7
votes
Ola's maintenance solution is not processing sysjob* tables
You need an additional parameter to tell Ola's script to consider Microsoft-shipped objects, since those job-related tables in msdb are marked as is_ms_shipped:
@MSShippedObjects = N'Y'
(This ...
- 178k
7
votes
Accepted
How often should we use Update Statistcs.(More frequent or Not)
How often should we have it scheduled?
You will need to decide this for yourself.
It will depend on the modification (insert update delete)of your
columns that are related to that statistics in ...
- 12.7k
7
votes
Accepted
How to change job output log location, when using Ola Hallengren's scripts
I’m going to reword your questions a little:
Q: Should I log Agent jobs directly to a remote file server? No, because if you lose network connectivity or the file share goes down, your Agent job ...
- 42.4k
7
votes
Filter Schema In Index Optimize Script
Yes, if you look at the documentation, you see that you can use the @indexes parameter to specify which indexes to defragment. You can use % wildcards in your object names and you can prefix them with ...
- 15.5k
7
votes
Accepted
Ola Hallengren backup systax when excluding a database
it has to be
@Databases = 'USER_DATABASES, -Db1'
See https://ola.hallengren.com/sql-server-backup.html
Assuming you want to backup all User Databases except one named DBA, your statement should ...
- 61.6k
7
votes
Accepted
DBCC CheckDb on Secondary Asynchronous Replica
This method is not safe because it is almost meaningless. It is a quote of P. Randal who is author/architect of DBCC CHECKDB and SQL Server storage subsystem.
Any of the answers where DBCC CHECKDB ...
- 122
7
votes
Accepted
Scripting a Maintenance Plan in SQL Server Express
You've pretty much self answered here - you won't be able to rely on SQL Server to do the scheduling for you.
The most common method is to use Windows Task Scheduler, but if you have any other job ...
- 10.9k
7
votes
Accepted
IndexOptimize - Configuration
Why don't "statistics" options appear in the job's command?
Because you updated the default parameter in the procedure call. This means that if you call the procedure without these parameters, E.G.
...
- 16.2k
6
votes
Transaction Log Backups Serial or Parallel?
In general, always run your T-log backups in serial; many of my instances have a couple of dozen databases, and several that are very active, and the transaction log backups only take a few seconds ...
- 1,678
6
votes
Accepted
Transaction Log Backups Serial or Parallel?
Should I set up one job that uses ALL_DATABASES? or is it better to set up one job for each database and kick them all off in parallel?
I would suggest to setup one job that would backup the ...
- 18.7k
6
votes
Accepted
Index Optimize cause DB size increasement
Yes that is normal.
See: Disk Space Requirements for Index DDL Operations
When a new index structure is created, disk space for both the old
(source) and new (target) structures is required in ...
- 15.5k
6
votes
Ola Maintenance Solution and striped Azure URL backups - possible?
I have added support for striping of backups to Azure Blob Storage.
https://ola.hallengren.com/versions.html
Here is how it works:
EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES'
, @URL = '...
- 1,355
6
votes
Accepted
Full Back up over to Network failed due to unknown network error 59
Aaron's answer provides great advice on how to work around this issue. I thought I'd add an answer to provide some more technical details.
According to Microsoft's err.exe utility, error 59 is ...
- 68.5k
6
votes
Can a single database be excluded from an AG backup?
Select databases. The keywords SYSTEM_DATABASES, USER_DATABASES,
ALL_DATABASES, and AVAILABILITY_GROUP_DATABASES are supported. The
hyphen character (-) is used to exclude databases, and the ...
- 3,072
Only top scored, non community-wiki answers of a minimum length are eligible