Questions tagged [maintenance-plans]

Workflows created through SQL Server Management Studio that help automate SQL Server maintenance tasks like backups, index rebuilds, and updating statistics.

Filter by
Sorted by
Tagged with
0
votes
0answers
14 views

Reporting & Logging Failing on SQL Server Job

System Info: SQL Server 2014 Enterprise, Windows Server 2012 R2, running on a VM I have a Maintenance Plan set up to perform a log backup of a database every 17 minutes. The job normally runs fine ...
0
votes
2answers
27 views

Simple Backup Plan Question

I'm using Microsoft SQL Server 2016 Standard (64-bit). I have the following backup plan (9 steps long) ... What I want is just one full (simple) backup everyday. Do I need to do step 8 (Backup ...
0
votes
1answer
23 views

ola.hallengren - Alternative MySQL scripts!

Having worked with MS SQL for many years and found ola.hallengren scripts incredibly useful - I find my self in a MySQL environment. Is there a MySQL alternative to ola.hallengren? - Perhaps someone ...
0
votes
2answers
189 views

Maximum Error Count Reached

I created a maintenance plan to backup all the databases locally on a folder in E Drive. Out of nowhere, the job is failing occasionally. sometimes the job runs successfully and sometimes sends an ...
1
vote
1answer
42 views

Excluding log shipped DB from current database maintenance plan (Ola's solution)

The Senior DBA and I were discussing our most recent rollout of log shipping to 25+ servers and one of the issues we had ran into during our pilot was the log chain breaking randomly. We narrowed down ...
0
votes
1answer
26 views

Where to find the whole log of a maintenance plan package job that failed with error?

I have a maintenance plan that runs on SQL Server agent as a the job and finishes with error. However the error is not visible in job history in SSMS, the text of Message is seemingly truncated. How ...
0
votes
1answer
38 views

Copy SQL Server Maintenance Plan between instances on same server

I have multiple SQL Server 2019 instances on the same server. I've created and exported the first instance's maintenance plan, edited its exported DTSX file to change the instance and plan names to ...
0
votes
0answers
41 views

List of databases present in specific maintenance plan

I have two maintenance plans and also I have two user-defined databases demo1 and demo2. The first maintenance plan is used to take the backup of all the databases. the second maintenance plan is used ...
0
votes
0answers
23 views

CMS Maintenance Plans not copying to TSX

I recently setup an SQL 2017 instance to act as a central management server. I've deployed a number of dynamic jobs w/o issues, but when trying to deploy a standard maintenance plan, the plan never ...
0
votes
0answers
32 views

Maintenance Tasks causes CPU to peg 100% utilization

I do not believe the maintenance tasks themselves to be the primary issue here, however CheckDB and index rebuilds cause the server to become unresponsive w/ 100% cpu utilization. It is on one ...
1
vote
1answer
45 views

Are there differences between which AlwaysOn AG server (Primary or Secondary Replica) I store my backups and retention maintenance plans on?

I have an AlwaysOn AG system in place between two servers. Server A hosts the primary replica, Server B hosts the secondary replica. Are there any differences if my Full & Transaction Log ...
1
vote
0answers
27 views

How to select specific databases in a backup retention policy maintenance plan cleanup task?

I have full backups of three different databases. Right now all of their backups follow the same retention policy, but I need to setup the retention to a different amount of time for each database. ...
0
votes
1answer
14 views

Assigned filetype for backups not applying

I am working to migrate backups over to a new storage point. I figured the easiest thing to do was to duplicate the configuration over from the existing Maintenance Plan to a new one, just in case ...
0
votes
1answer
132 views

Ola's Maintenance Solution Plan script newer version

We have very old version of Ola's Maintenance Solution Plan script implemented in our many servers and wondering if i have to update to his latest scriptm how can i upgrade easily on all my servers? ...
0
votes
1answer
145 views

Migrating a maintenance plan from ms sql 2008 to 2016

I have a bunch of maintenance plans that I want to copy/migrate to sql server 2016. I am able to connect to Integration Services on sql server 2016 but I get the following error when expanding 'MSDB' ...
1
vote
1answer
154 views

SQL Server Maintenance plan that goes to next step when the condition passes

I am trying to create a maintenance plan in SQL Server. What I want to do is I have an Execute T-SQL Task and inside the task I have condition below: Use master go DECLARE @dbname varchar(1000) set @...
2
votes
2answers
165 views

Availability Group - Deleting old backup files

I'm using Azure VMs with a SQL Server Availability Group, and Ola Hallengren' scripts. Backups are working without issues, however I've noticed that old backup files are not being removed from the ...
2
votes
1answer
265 views

Log Shipping and Daily Backup

My SQL Server 2012 DR setup is a mess. We've had a maintenance plan in place from the beginning that takes a daily full backup, with hourly log backups. Recently, we set up log shipping to an off-site ...
4
votes
1answer
1k views

Scripting a Maintenance Plan in SQL Server Express

How would I run each aspect of Ola Halengren's sql maintenance plan on SQL Express? It's my understanding that each job should be run in a specific order. I'm sure that a .bat or cmd file can do ...
0
votes
1answer
920 views

Unable to Create new Maintenance plan in SSMS 2017

Hi I have reinstalled SQL Server 2016 in my database server last day. I am able to connect to SQL Instance(SQL-02) using the SSMS 2017 i had earlier in the same server before reinstall. But when i ...
0
votes
1answer
52 views

Unable to create maintenance plan

When trying to create a maintenance plan locally on two prod servers through SSMS, I receive this error: No such error occurs on my UAT system. They're all running SQL Server 2017 (14.0.3162.1), and ...
1
vote
1answer
87 views

Multi server administration - Failed to import Maintenance Plan to named instance

I have a multi server administration environment and several maintenance plans deployed already. My MSX server is SQL2014. The problem i'm having is deploying maintenance plans to a 2008R2 named ...
6
votes
2answers
239 views

Full backup on database creation

When new databases are created the Differential and transaction log backup maintenance plans start failing and sending alerts as full backup is not taken. What could be the best solution to handle it. ...
1
vote
2answers
211 views

Logging to file

I am using the ola-hallengren maintenance scripts. I am logging to table and file. I want to change the location of the file logs. Can I do this? Many thanks
0
votes
0answers
45 views

CheckDB Failure on most databases in Dev instance

I have a maintenance plan on one of our infrequently used dev boxes which keeps failing on CheckDB on all user databases and most system databases. When trying to run CheckDB manually, I get the ...
0
votes
1answer
24 views

Maintenance Plan Wizard generates invalid date parameter in script for delete

The generated SQL for my Maintenance Plan is EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21'' but the execution returns error message Msg 102, Level 15, State 1, Line 1 ...
2
votes
1answer
393 views

SQL Server maintenance plan not working after renaming administrator

I have renamed the Windows administrator account to TEST using Local Group Policy editor. After that, the SQL Server maintenance plans are not working anymore: Unable to determine if the owner of ...
1
vote
1answer
1k views

Cannot create maintenance plan, I get the message “The task with the name '' and the creation name is not registered for use on this computer”

I just installed SQL server 2016 (Standard edition) on a new server, I'm trying to add a Maintenance plan (I've tried back up plan, rebuild indexes and many more) I'm getting a weird message when I'm ...
1
vote
0answers
30 views

Maintenance Plan Failing After Database Collation Change

We currently use maintenance plans to perform index rebuilds each night (we inherited this system). Yesterday, i amended to the database collation to match the instance level collation (...
-1
votes
1answer
242 views

Maintenance Job not running but not Failing [closed]

I set up a maintenance plan that contains three sub-plans. One sub-plan for hourly log backups, one sub-plan for daily maintenance, one for weekly maintenance. These are custom plans set up using ...
2
votes
0answers
67 views

In SQL Server 2014 Is it normal for for maintenance job to pick up a table that does not exist in database

As above, we are hitting an error on the maintenance plan running on SQL Server 2014. During update statistics when we got the error. "Msg 2706, Level 16, State 6 , Line 1" Table Flow Detail does ...
-1
votes
1answer
378 views

Transaction Log Backup during Index Rebuild and Integrity Maintenance Jobs

Brent Ozar mentioned there is nothing wrong about conducting transaction log backups during full backups. https://www.brentozar.com/archive/2015/12/what-happens-to-transaction-log-backups-during-full-...
1
vote
1answer
3k views

What does [298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'WIN-443PT0LULQU\Administrator', error code 0x534

I'm going slightly nuts. I'm a programmer by trade - and I only dabble in NT security and DB setup issues. However, we had a server fail to reboot after a MS Update. I've restored the entire ...
0
votes
0answers
427 views

Reorganize index maintenance job is more than 20 times slower

I have a reorganize maintenance job which runs daily (I know, this have been altered to weekly) which for the majority of executions it takes around 2 hours however over the weekend has failed with ...
1
vote
1answer
3k views

Creating a Maintenance Plan for a SQL Azure Database

I need to create a maintenance plan for a number of SQL Azure databases. Unfortunately, my many years of experience as a SQL Server DBA does not include experience with this task (sarcasm intended). ...
1
vote
1answer
977 views

Does SSD drive make indexes rebuild and update stats useless with SQL Server?

Now that SSD is a standard de-facto. Can the maintenance job of rebuilding indexes and updating statistics, using Ola Hallengren https://ola.hallengren.com/ jobs for instance, be done only on demand ...
1
vote
1answer
154 views

Back up Chain LSN

If I move my network back up to local then will it break the back up chain ? My network drive will be unavailable for 30 minutes and there is no specific time. My logs are taken every 6 hrs and full ...
2
votes
1answer
927 views

Problems with a job that rebuilds indexes [closed]

We have a Maintenance Plan (MP) job that rebuilds indexes every week. The last time, the job failed because of a deadlock. Should I rerun the whole job, or do I just need to rerun the rebuild of the ...
2
votes
1answer
1k views

Compression setting not effective for differential backup maintenance plan?

We are taking backups (Full, Differential, and Transaction Log) of databases using the maintenance plans feature in SQL Server. While taking the backup, I have chosen the compress option to compress ...
2
votes
3answers
1k views

Maintenance plan don't stop & don't backup all databases [closed]

I have a server with 100 databases, I have a maintenance plan (MP) for backups. I noticed that the maintenance plan don't backup all databases when I'm looking at the backup folder destination I can ...
3
votes
1answer
617 views

Transaction Log Backup Maintenance Plan and Newly Restored Databases

When we add a new database (restore from a staging instance) to our live instance we have a job which sets it to Full recovery (in case the tech forgot that) and then a maintenance plan that runs a ...
5
votes
3answers
2k views

Could an update on all statistics separately cause bad performance?

We’ve been utilizing Ola Hallengren's maintenance scripts for our index optimization for years on our OLTP databases. (we are on sql server 2008 R2) 2 weeks ago though one of the DBA’s here has ...
1
vote
2answers
6k views

SQL Server AlwaysOn High Availability Maintenance Plans

I have a SQL Server AlwaysOn High Availability with Primary & Secondary Database Servers The Availability Group Backup Preferences is set to Primary. Maintenance Plan is on Primary and does: ...
2
votes
0answers
32 views

SQL Server 2012 - Is There a Log That Captures DB Maintenance Type Activity?

I had a backup maintenance job setup for all system databases. I also have another job that sends me an email whenever a job fails. After receiving notification of the system database backup job ...
3
votes
1answer
166 views

Diagnosing a maintenance plan that's ballooned in execution time

Environment is SQL 2008 R2, 26GB RAM, 4-core Xeon E5-2650 @ 2.6GHz. Please see the image below, our maintenance plan which reorganises indexes (not rebuilds). This is run on a weekly basis, and ...
7
votes
4answers
2k views

Why does it take so long to view logs on a server?

I am just trying to look up more verbose information for a maintenance plan task that failed. I open up the log file viewer and check the box to view the logs for my maintenance plan. There are no ...
1
vote
2answers
131 views

Backup of Databases not present in Availability Groups

How can I take backups of databases that are not present in Availability Groups? Can I use a single maintenance plan for both AG dbs and non AG dbs? Should the copy_only option be enabled for non AG ...
0
votes
1answer
49 views

How can I share values in maintenance plan tasks?

A simple example would be a maintenance plan that clears a directory of old files and create some copy backups of all databases on that instance to that same directory. This is run daily The first ...
5
votes
1answer
4k views

What does 'sp_configure allow updates' do?

Today I got an error ( DTSER_FAILURE ) but I could fix it by editing the maintenance plan ( this error is due to the maintenance plan searching for a database that was deleted, and I was using "...
2
votes
1answer
382 views

Include stored procedure text output in the maintenance plan log

I have a maintenance plan subtask in SQL Server 2012 Enterprise that executes a stored procedure (or a SQL Server agent job). In the log file for this maintenance plan, there are a few lines for this ...