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.
135
questions
3
votes
1
answer
17k
views
Changing MaximumErrorCount when executing maintenance plan
I have defined a Maintenance Plan in Microsoft SQL Server 2012. It doesn't execute and it says
DTS_W_MAXIMUMERRORCOUNTREACHED.
The Execution method succeeded, but the number of errors raised (2) ...
CommunityBot
- 1
1
vote
1
answer
2k
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 ...
- 10.9k
1
vote
0
answers
36
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 (...
- 191
-1
votes
1
answer
1k
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 ...
- 67
12
votes
2
answers
38k
views
Scripting a maintenance plan
I created a maintenance plan for my SQL Server 2008 R2 using SQL Server Management Studio (SSMS).
However, I am unable to export/convert it to a script, in case I need to redo it on another server.
...
- 1
2
votes
0
answers
96
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 ...
- 84.1k
-1
votes
1
answer
1k
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-...
CommunityBot
- 1
21
votes
3
answers
60k
views
Copy maintenance plans to a different server
Is there a way to copy maintenance plans from one SQL Server 2012 to another? Both servers are not accessible via the same copy of Management Studio at the same time (on different networks).
- 1
0
votes
0
answers
701
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 ...
- 8,520
1
vote
1
answer
532
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 ...
CommunityBot
- 1
3
votes
2
answers
10k
views
Backup name in Maintenance plan
I used to have SQL Server 2005 installed on my server. In my maintenance plan, the backup name format was like this: mydatabase_backup_yyyymmddhhss.bak.
Now I am using SQL Server 2012 but I am ...
- 23.7k
1
vote
1
answer
159
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 ...
CommunityBot
- 1
2
votes
3
answers
2k
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 ...
- 84.1k
2
votes
1
answer
2k
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 ...
CommunityBot
- 1
2
votes
1
answer
2k
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 ...
CommunityBot
- 1
5
votes
3
answers
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 ...
CommunityBot
- 1
3
votes
1
answer
626
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 ...
- 43k
4
votes
1
answer
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 ...
CommunityBot
- 1
2
votes
2
answers
9k
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:
...
- 182
2
votes
0
answers
37
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 ...
- 83
3
votes
1
answer
283
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 ...
- 84.1k
1
vote
2
answers
197
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 ...
- 625
2
votes
2
answers
11k
views
BACKUP failed to complete the command BACKUP DATABASE
Not sure if this is a false-positive issue or not, but I need quell my curiosity about it. I get the following error message:
Error: 3041, Severity: 16, State: 1.
BACKUP failed to complete the ...
CommunityBot
- 1
8
votes
4
answers
3k
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,211
0
votes
1
answer
97
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 ...
CommunityBot
- 1
4
votes
3
answers
7k
views
ONLINE vs OFFLINE index rebuilds
I am looking for an opinion, which I know is usually frowned upon, so my apologies in advance.
I'm currently in an environment where every instance is SQL Server enterprise edition.
The maintenance ...
- 31.3k
6
votes
1
answer
6k
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,646
14
votes
4
answers
37k
views
Can I export a maintenance plan without using Integration Services?
I'm trying to export a simple maintenance plan from an SQL Server instance.
I want to check the export into source control, and apply the exported maintenance plan to functionally identical instances....
CommunityBot
- 1
1
vote
2
answers
877
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 ...
CommunityBot
- 1
0
votes
1
answer
272
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 ...
CommunityBot
- 1
0
votes
2
answers
769
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 ...
CommunityBot
- 1
2
votes
1
answer
7k
views
T-SQL query to find backup folder for specific maintenance plan
I want to check check if backup plans are set up correctly and need to query different servers, mostly SQL Server 2008, but also 2012.
Is there any way to query the maintenance plans for databases, ...
- 1
0
votes
1
answer
74
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 ...
- 5,043
0
votes
1
answer
658
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+ ...
CommunityBot
- 1
1
vote
1
answer
2k
views
List databases in Maintenance Plan
I'm trying to list all databases in existing Maintenance Plans. (or, more importantly those NOT in a plan...)
I'm using the followed code, based on this blog:
Select DISTINCT p.name as '...
- 62.2k
1
vote
2
answers
679
views
SQL Server backup practice with limited disk space
I have a SQL Server 2012 Std Edition installed, contain 1 database in full recovery mode, so the log grown up quickly, but I just need a PIT recovery in past 7 days.
Can I schedule a full database ...
- 3,252
0
votes
1
answer
1k
views
Backup set expire option - Backup SQL Server 2012
I had seen an option while taking a backup i.e. set backup expire date, I have a doubt. What does this mean:
Backup will be no longer valid after date DD/MM/YYYY?
Backup declared with option, is it ...
CommunityBot
- 1
10
votes
1
answer
31k
views
Best way to backup & truncate transaction logs on a schedule
I'm no DBA, but things being what they are, I have to wear the DBA hat and set up maintenance plans on my SQL Server instance.
So for a while I've been having my SSIS overnight process run a Execute ...
CommunityBot
- 1
1
vote
0
answers
1k
views
Maintenance Plan job failing with access denied for NT SERVICE\SQLSERVERAGENT
I used the Maintenance Plan Wizard under SQL Server 2014 to create a simple maintenance plan that once a week:
Check Database Integrity including indexes
Rebuild Indexes
Clean Up History
When the ...
- 84.1k
6
votes
1
answer
3k
views
SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan
I modified a maintenance plan yesterday, to find out this morning (by using the last modified date from sysjobs) that all jobs related to the maintenance plan are disabled.
I can't figure out why ...
CommunityBot
- 1
10
votes
2
answers
21k
views
What's the owner of SqlServerAgent job for?
What is the possible use of owner of an SQL Server Agent job, I wonder?
Removed, and left it blank and the job still succeeds
CommunityBot
- 1
3
votes
2
answers
2k
views
Cannot see new drive in SSMS maintenance wizard
I'm trying to setup a Backup Maintenance Plan in SSMS for Sql Server 2012, running in a failover cluster on Windows Server 2012. The current plan backs up to the same cluster volume where the ...
2
votes
0
answers
244
views
Change version number of maintenance plan package
We have a Maintenance Plan on SQL 2012 that has been running fine. I made a change to it yesterday using SSMS 2014. When the plan ran last night I got the following error:
The version number in the ...
- 84.1k
2
votes
2
answers
702
views
Backing up multiple instance System Databases with distributed Maintenance Plan
I am running maintenance plans on a server, which is running several instances of SQL Server 2104.
I have set up a nightly maintenance plan to run backups for all user databases, with the backups set ...
- 84.1k
0
votes
1
answer
133
views
Will an hourly T-Log backup in a Maintenance plan write a file if there are no changes to a DB?
I have an hourly maintenance plan the runs a T-Log backup. Some of my databases may have every hour represented in the file system, while others have some hours "missing" or even none at all. Is it ...
- 320
1
vote
2
answers
1k
views
Transaction log backups cleaning
I have a quick question regarding the cleaning of no longer necessary transaction log backups. So, I have a database in FULL recovery mode. Every night, at 10 PM a backup takes place (via maintenance ...
- 180k
1
vote
2
answers
698
views
How do I view operators in a Notify Operator task in a Maintenance Plan?
In the past, we have added each DBA to a Notify Operator task on failure in Maintenance Plans ranging from scheduled backups to indexing and occasionally one-off operations. One of our DBAs recently ...
CommunityBot
- 1
10
votes
1
answer
11k
views
SQL Server Job History Disappears After 5 Days
I feel like this is a silly question but for the life of me, I can't track down what is going on. I have just taken over administration of several servers and on all of them, the job history is being ...
- 180k
2
votes
1
answer
2k
views
permissions for schema compare and view job history
I need to grant these permissions,
what would be the minimum level I could grant so that they can do their job?
I am looking at SQL Server Agent Fixed Database Roles
and also Database Engine ...
- 2,268
1
vote
1
answer
843
views
Task generated T-SQL shows unknown property IsMemoryOptimized
I have modified a Maintenance plan on SQL Server 2008 using SSMS 2014, particularly Reorganize index task - I have added another two databases for this task.
When I wanted to check if the T-SQL is OK,...
- 7,159