I recommend that you have the jobs enabled and scheduled on both servers and with the same configurations. Databases that are in a mirror role will automatically be skipped. There are checks for that in the script.
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 relates to the extension of the job.
Let's take a look at a Full backup example.
If you create a full backup job, then you will normally add one ...
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 knobs may still result in hitting the limits of your network and/or storage, and them not having any real impact on backup time.
Your first job should be to ...
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 @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)
Which means someone:
Modified the stored procedure to use 90 as a default value;
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's maintenance solution is widely accepted in the community and large organizations.
Its open sourced and issues can be raised at github/issues with a likelyhood ...
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 reasons for this are explained in detail in the following MSDN blog post:
How It Works: Online Index Rebuild – Can Cause Increased Fragmentation
Some options to ...
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 statistics only updated
on the primary replica in an availability group. IndexOptimize has a
check for this.
The jobs should be configured identically and ...
Heaps have a few special challenges that you can't experience with clustered indexes:
I'd suggest running sp_BlitzIndex against your database to find out if either of these things is happening with your Heaps. If not, then leave them alone. If they are, you may need to consider rebuilding them.
At this time, you can't ...
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 doesn't look like you're currently doing.
At this point, this is speculation, but you can check to see what sampling rate is currently being used on your ...
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 do the transfers to a network share. This is decoupled and can run as a Windows scheduled task.
This way, your backups are local and they will be fast. You ...
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 if you:
Install the backup script (and other dependencies) on a test instance - I tested on my local computer.
Now, alter the script and search/replace hh with ...
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.
CHECKDB From Every Angle: Complete description of all CHECKDB stages
CHECKDB From Every Angle: Consistency Checking Options for a VLDB
Breaking the full check into its constituent parts is sometimes done on very large ...
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 in Ola's scripts, you might be able to tweak these to get the performance that you want:
Specify the physical blocksize in bytes.
The BlockSize ...
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 statistics, and even then you only need to do this when you notice performance degradation (i.e. check what your monitoring system tells you).
Per Ola's site, ...
By default, the script does not perform statistics maintenance. The documentation for the script's parameters can be found here.
ALL Update index and column statistics.
INDEX Update index statistics.
COLUMNS Update column statistics.
NULL Do not perform statistics maintenance. This is the default....
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 that database (since it's read-only).
... and secondly if is safe as I have a primary server that is Live and I am doing a checkdb on a secondary during ...
For now just run it on the Primary database server.
When you do a failover, you can run it on the mirrored server.
e.g when you deploy it on mirrored server, you (refer to: http://ola.hallengren.com/sql-server-backup.html)
@Databases = 'SYSTEM_DATABASES', -- when failover, change it to ALL
@Directory = 'C:\Backup',
@BackupType = ...
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 parameter defaults to N'N', which means ignore Microsoft-shipped objects, and is documented here.)
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 - to exclude indexes.
Using this information, the syntax you want to use is @indexes = 'ALL_INDEXES, -databasename.excludedschema.%', or @indexes = '...
it has to be
@Databases = 'USER_DATABASES, -Db1'
Assuming you want to backup all User Databases except one named DBA, your statement should be:
@Databases = 'USER_DATABASES, -DBA',
@Directory = N'G:\BACKUP',
@BackupType = 'DIFF',
@Verify = 'Y',
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 TABLE dbo.tablename REBUILD;
to remove the fragmentation.
This also rebuilds the nonclustered indexes on the heap.
Better option than rebuilding your heap ...
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 scheduling application already that might be a better choice. Either way, you'll need to get comfortable with the sqlcmd utility if you aren't yet.
If I were ...
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.
that these default parameters @UpdateStatistics = 'ALL' & @...
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 total; up to half a minute or so when it's particularly busy.
Running backups in parallel only really would be beneficial if all of the following conditions are ...
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 transaction logs (serially). This would also make sure backup does not heavily utilities the I/O because you are running backup for database one at a time.
What could ...
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 question. You can use my script TSQL to Find Status of SQL Server Statistics to find the amount of rows modified since last updated statistics.
Is your auto ...
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 could fail. Your Agent job might not require network connectivity, like an update stats job, so that failure would suck.
Q: If I want to centralize Agent job ...
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 their appropriate
files and filegroups. The old structure is not deallocated until the
index creation transaction commits.
The following index DDL ...
I have added support for striping of backups to Azure Blob Storage.
Here is how it works:
@Databases = 'USER_DATABASES'
, @URL = 'https://account.blob.core.windows.net/container'
, @BackupType = 'FULL'
, @Verify = 'Y'
, @Compress = 'Y'
, @CheckSum = 'Y'
, @NumberOfFiles = 8