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
8 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
1answer
76 views

SQL Server Maintenance plan job failed [closed]

I have created a maintenance plan to backup database, recently after a server reboot, the maintenance plan started to failed. I have recreated back the job but it's still failing with same error below....
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
40 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
23 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
57 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
76 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
91 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
107 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
142 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
692 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
0answers
352 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
41 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
42 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 ...
5
votes
2answers
197 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
120 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
42 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
23 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
266 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
28 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
vote
1answer
95 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
56 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
239 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
2k 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
369 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
2k 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
747 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
145 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
774 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
924 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
610 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
5k 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
151 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
125 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
46 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
3k 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 "...
1
vote
1answer
341 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 ...
1
vote
2answers
671 views

Maintenance Job Failing

I didn't realize but it appears you can put switches on your job to run a maintenance plan. Am I correct on that? We have 3 jobs that are running the same maintenance plan but doing different things ...
0
votes
1answer
206 views

Maintenance plan is backing up database transaction log but it is not selected

I have a strange problem, the transaction log for my database is being backed up however it is not selected. I change the names of the server and databases in the screenshots to protect my ...
0
votes
2answers
629 views

T-SQL Statement Task choose specific database name on server

I try to create a simple T-SQL Statement Task by Sql Server 2014 GUI. My flow: Right click on Maintenance Plan Choose Create new Maintenance Plan Choose the type (T-SQL Statement Task) I add by ...
1
vote
1answer
441 views

Rebuild Index Task options [closed]

I have to debug a performance issue on a SQL Server 2016 database. The query was pretty simple - a few temporary tables joined with SQL tables and aggregation. The amount of rows of involved in ...
4
votes
1answer
3k views

Error when modifying Notify Operator task in maintenance plan

I'm unable to modify a Notify Operator task in my new maintenance plan. SQL Server Management Studio displays this error after double clicking on the task: Property IsMailHost is not available for ...
0
votes
1answer
58 views

How can I automate the process of checking of column and executing SQL statement?

I am experiencing quite difficult problem and hope for your feedback. In my table I have a "Services"column in "MyData" table which contains names of my services. I have created SQL statement which ...
0
votes
1answer
569 views

Should I create multiple maintenance plans to backup more than 200 User databases

I have a SQL Server 2008 with more than 200 User databases. Some are larger than others and every two weeks or so a new db gets created. Should I create only 1 maintenance plan backing up all 200+ ...