Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
0answers
13 views

High Avaiablity Group backups

I have a couple of questions: I have a 2 node High Availability Group, when I run a FULL backup job on they PRIMARY, the databases are backed up but they fail to backup when I run the FULL backup job ...
-2
votes
0answers
22 views

Restart SQL Services without disconnecting application [on hold]

I was asked by Ola to post my question I emailed on here. I have a batch file which restarts SQL services but disconnects the application when this is done. @ECHO OFF NET STOP SQLSERVERAGENT NET ...
1
vote
1answer
54 views

Trouble with Ola Hallengren's @DatabasesInParallel parameter

I am testing out this new parameter @DatabasesInParallel and it seems to only back up 1 databases at a time but it doesn't specificy this as a limitation in the documentation so I am wondering if I ...
0
votes
1answer
32 views

SQL Server hangs on index rebuild, works well after SQL service restart

We have a SQL Server 2014 enterprise cluster. Server 1 and Server 2. SQL Server is limited to 64GB of memory. We are using Ola Hallengren scripts for index rebuilding and optimizations once per week ...
1
vote
0answers
24 views

OLA Output File Cleanup - Better Options

Does anyone have a way to change the Ola Hallengren "Output File Cleanup" job that doesn't require CmdExec? My company is trying to eliminate CmdExec where ever possible. I was thinking a PowerShell ...
1
vote
0answers
61 views

Error with Ola Hallengren backup job after upgrading SQL Server 2016 to SP2 [closed]

Recently I updated SQL Server 2016 with SP2. After that, the Ola Hallengren backup job failed throwing the error below: Source: https://ola.hallengren.com [SQLSTATE 01000] (Message 50000) ...
1
vote
2answers
40 views

SQL Maintenance Tasks on Azure [Ola Hallengren Scripts]

I am trying to implement the SQL maintenance scripts from Ola on my Azure elastic pool databases. I did the steps as provided in this link. I could create the job, but I always get this error after ...
0
votes
1answer
26 views

Excluding Read only Log backups using ola-hallengren script

I know that Full backups are excluded from readable secondaries, but how do I exclude log backups from read-only secondaries? The is_read_only column in sys.databases does not get set on ...
1
vote
2answers
142 views

DBCC CheckDb on Secondary Aynchronous Replica

Can I run Ola Hallengren CheckDb job on a Secondary Asynchronous Replica (DR) that is readable during business hours? Currently the Asynchronous Replica is not readable... First question is if it is ...
0
votes
0answers
22 views

Data Domain Feature in OLA backup

I have a question pertaining to backup to data domain using Ola Hallengreen Backup script. While configuring backup on an Always on server, I noticed that the client name parameter being picked is ...
0
votes
0answers
64 views

How long to keep SQL Server Backup before backing up to separate storage

We use ola hallengran scripts for SQL Server backups. We perform full backups daily. We keep SQL Server backups 7 days and tlogs for 48 hours. We perform 4 weekly checkpoints, or snapshots, to our ...
-1
votes
1answer
25 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?
-1
votes
0answers
108 views

Ola Hallengren T-log backups not working

I've been running Ola Hallengren's backup scripts. The database backup works well, but I noticed the T-log backups aren't working. EXECUTE dba.dbo.DatabaseBackup @Databases = 'Ouvvi' , @Directory = ...
0
votes
1answer
57 views

Can not backup multiple (not all) databases Ola Hallengren [closed]

This error is quite easy to replicate. So before I send a bug to Ola, I'd like to check if maybe I'm doing it wrong. Version is 2018-06-27 20:44:12 I created 3 databases. DB1, DB2 and DB3. I'd like ...
2
votes
1answer
59 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 ...
0
votes
1answer
59 views

Index Maintenance - Reorganize After BIG SQL Server Shrink

I'm planning to execute the below index maintenance created by Ola Hallengren in a 1TB Database. EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @...
3
votes
2answers
326 views

Understanding CleanupTime in Ola Hallengren's SQL Server scripts in relation to FULL backups and LOG backups

I am having trouble understanding what exactly to expect from the CleanupTime option in the Ola Hallengren Server Maintenance Solution. I'm finding some related questions, and elaborate answers, but ...
0
votes
1answer
86 views

Ola Hallengren Index Maintenance with compressed indexes

If the Ola Hallengren index maintenance solution is installed onto a server which already has compression applied to several of the indexes, will the script respect the current compression level when ...
0
votes
0answers
26 views

Transaction log files not found in a path

I am using Ola Hallengren backup maintenance job for Log backups and I am on SQL Server 2008 R2. Ola Hallengren log backup maintenance script is utilized to take all three types of backups but ...
0
votes
2answers
50 views

Optionally exclude certain partitions using OLA's scripts

One scenario. I have been using OLA's scripts for a long time and love them. I have a very large database - 20+ TB with hundreds of partitioned tables. I purge old data by dropping (merging the old ...
1
vote
1answer
105 views

Index Maintenance for DW- DB ( ETL process )

I'm planning to set up a t-sql job to perform index maintenance in a DW DB (Stores data through an ETL process) currently overnight and eventually will need to be run more frequently as the business ...
0
votes
2answers
67 views

Rebuild Index Causing Data File Growth Issue

I have a database which is 200 GB in size and I have scheduled a Rebuild Index maintenance plan for all databases every Saturday at 2:00 AM. The job ran for 8 hours and then it failed. I knew while ...
1
vote
1answer
57 views

Log size is different between primary and secondary node with log_since_last_log_backup_mb

We get log backups from the secondary node. But now I see that log_since_last_log_backup_mb in sys.dm_db_log_stats returns null for databases which are in Availability groups, so Ola Hallengren's log ...
3
votes
1answer
128 views

Ola Hallengren Index Optimize - Skip “unused” Indexes

Is there a way to skip "unused" indexes based on the total reads of that index during an index maintenance w/ Ola's scripts? I understand that Index stats are only since the last restart, so ...
3
votes
3answers
345 views

Does Index Rebuild Reduces physical read from disk

Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index ...
1
vote
2answers
127 views

Database Space Used Decreased after Index Rebuild (Ola Script)

I have rebuilt some indexes which were highly fragmented. After the index rebuild I am seeing my database space used reduced by almost 50% (from 77gb to 33 gb) Is that normal behavior? I dont have ...
1
vote
1answer
54 views

Ola Hallengren maintenance script - how can I tell a backup file is still being written to?

My question is whether there is a way to identify a backup file that is still being written to.​ I want to copy the completed backup files to another place. By reading the backup extension, I can't ...
-1
votes
1answer
179 views

Ola Hallengren backup systax when excluding a database [closed]

I would like to backup most of the databases except the database DBA. I use below syntax in the job step: EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', '-DBA' @Directory = N'G:...
1
vote
1answer
68 views

Find outlier statistic in SQL server?

I have a stored proc which searches "bad" data in our application database (sql server 2016 enterprise, ~500gb data). "bad" in a business/process sense. It normally takes 10-30s to execute. After ...
0
votes
0answers
28 views

Does Change Tracking affect IndexOptimize?

For the past couple years we have had IndexOptimize run every Sunday morning without a problem, but now that we have turned on Change Tracking (for our new data warehouse) the utility seems to get ...
0
votes
0answers
35 views

SQL Maintenance script not running in command prompt

I have successfully executed the MainetenanceSolution.sql in SQL Server Management studio 2012. But when I am trying execute the following script in command prompt (as administrator) sqlcmd -M -E -S ...
0
votes
0answers
44 views

Warning in Ola Hallengren Index Maintenance Job [duplicate]

I am using Ola Hallengren index optimize SP, I have included command execute and Index optimize SP, I am using SQL Server project(DACPAC) to manage the script. When I am building it, I am getting lots ...
5
votes
1answer
76 views

Ola Hallengren: encrypt, but verify?

I've been using Ola Hallengren's scripts for a few years now, and a recent upgrade to MSSQL2017 has allowed me to start testing encrypted back-ups. Before anyone asks: yes, master keys and encryption ...
4
votes
2answers
166 views

Is it good practice to rebuild index on FileTable?

We are using Ola Hallengren scripts, both to backup our databases and for index maintenance. We have a FileTable in our database and from time to time, REBUILDING its PK index takes close to three (3) ...
0
votes
1answer
68 views

SQL Maintenance script not running with SQL Server Express

Hello and thank you for making such a well-designed script available to the public. I have client with some heavy db fragmentation and performance issues. I would like to use your scripts to help ...
0
votes
1answer
64 views

How to control blocking sessions while an index maintenance job runs?

I would like to know, is there any possibility to control the locks while Ola's index optimize job is running? I have implemented Ola Hallengren's index optimize job and am running it every day, but ...
-2
votes
2answers
56 views

Add Argument to IndexOptimize.sql to Execute Custom Command After Each Index Is Processed [closed]

I need some way to check the space usage and backup the transaction log of the database before it gets full when we run your IndexOptimize.sql script. Our databases are mirrored, so I cannot change ...
1
vote
2answers
103 views

Is it possible to log backup results to table in a different database on a different server

I am using Ola Hallengren's maintenance solution to back up a SQL 2012 Express instance in our environment. I am a brand new DBA, only 10 weeks old so I am not too familiar yet but I am learning fast. ...
3
votes
1answer
167 views

TDE enabled database backup taking too long

We have recently implemented TDE and backup compression is also enabled. Before TDE backup used to take about 20min but now it takes about one hour. I have set @MaxTransferSize to 3MB, 2MB, 1MB and ...
0
votes
1answer
38 views

Index re/organize/Rebuild Report using Ola

Is there any way we can generate report after running IndexOptimize - USER_DATABASES jobs. I need a report which tells me which indexes have been rebuild/re-organised. Select * from commandlog ...
-1
votes
1answer
117 views

Index not re-organised

I am using Ola's INDEX Maintenance solution. Few indexes are not being re-organised after running job.Below is my code EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @...
2
votes
0answers
112 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 ...
0
votes
1answer
52 views

Change Order of Database Backups

Can I specify a specific database to be backed up first and another database after that? For example in the following order: DB2 DB3 DB1 The code which I am currently using is as follows: EXECUTE ...
2
votes
3answers
245 views

(Ola Hallengren) Deleting transaction log backups

We're using Ola's script to run weekly full, nightly diff and hourly transaction logs. All the jobs are correctly creating their backups, and the full and diff jobs are cleaning up after a week, ...
0
votes
2answers
47 views

Dealing with varying fill factors in a SQL Server 2014 environment

I've recently become a DBA of sorts for our company's ERP system. I've been making small changes to the server & improvements have been steady. One area I'm concerned with is the index fill factor....
0
votes
2answers
68 views

Is Ola Hallengrens Stored Procedure what we need and safe to use [closed]

I stumbled across a stored procedure and wanted to clarify a few things first as I’ve been told it needs “rigorous review before being executed on any server”, and “testing before we run it on a ...
0
votes
0answers
52 views

Encrypting backups for SQL 2014 SP2 Standard Edition using Ola Hallengran scripts is failing

I am trying to encrypt my backups and the job is failing with the error message below. We are using SQL Server 2014 SP2 Standard Edition. The value for the parameter @ServerCertificate is not ...
1
vote
2answers
110 views

Schedule Maintenance Plan - Request For a Good Sequence

I have installed Ola Hallengren's MaintenanceSolution.sql script. So what I'll get out-of-the-box are a lot of jobs: My questions is: What schedules should I implement based on the jobs that ...
0
votes
1answer
126 views

Adding Time Limit parameter to Ola hallengren Index Maintenance

I would like to limit the duration of the index maintenance procedure of Ola Hallengren to be 7200 seconds (2 Hours) I found parameter TimeLimit defined as: Set the time, in seconds, after which no ...
2
votes
1answer
123 views

SQL Server 2012 Ola's maintenance solution ALTER Index Job failing

We setup ALTER Index job with Ola's maintenance solution, the job is failing with below error, seems the job is failing with locking, can someone help me how do we fix it, very much appreciate your ...