26

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.


17

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. Full Backup If you create a full backup job, then you will normally add one ...


15

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 ...


13

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; ...


13

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 ...


13

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 ...


11

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 ...


11

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 ...


10

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 ...


9

Ah! It's the image column. [column11] [image] NULL, Online only works on tables without blobs. Guidelines for Performing Online Index Operations


9

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 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 ...


9

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 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 ...


9

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: BlockSize Specify the physical blocksize in bytes. The BlockSize ...


8

By default, the script does not perform statistics maintenance. The documentation for the script's parameters can be found here. Value Description ================================ ALL Update index and column statistics. INDEX Update index statistics. COLUMNS Update column statistics. NULL Do not perform statistics maintenance. This is the default....


8

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 ...


8

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 ...


7

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) EXECUTE dbo.DatabaseBackup @Databases = 'SYSTEM_DATABASES', -- when failover, change it to ALL @Directory = 'C:\Backup', @BackupType = ...


7

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.)


7

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 = '...


7

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 ...


7

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 ...


7

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. EXECUTE dbo.IndexOptimize @Databases ='USER_DATABASES', @LogToTable='Y' that these default parameters @UpdateStatistics = 'ALL' & @...


6

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 ...


6

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 ...


6

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 ...


6

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 ...


6

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 percent character (%) is used for wildcard selection. All of these operations can be combined by using the comma (,). Ola Documentation So as you list the ...


6

Here are a few possible solutions to your problem: Option 1: Don't rebuild that index That's a 30 GB index you have there. What measurable performance problem are you trying to solve by rebuilding it? Especially at 5% fragmentation, this seems like an incredibly expensive operation (in terms of system resources and locking) for very little gain. You can ...


6

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 is being run on a continually updating copy of a database (e.g. through a database mirror, SAN mirror, log-shipping or Availability Group secondary) are ...


5

Digging a bit on this, using a common query to retrieve index fragmentation took just about an hour even to return 160 rows on a 1.5TB database: SELECT s.name AS schemaname , t.name AS tablename , t.object_id , i.name AS indexname , i.index_id , x.page_count , x.avg_fragmentation_in_percent , x....


Only top voted, non community-wiki answers of a minimum length are eligible