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
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,005
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....
- 2,328
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,467
11
votes
1
answer
56k
views
I/O is frozen on Database & also I/O was resumed on Database on daily basis in log with complete Backup
I would like to say that as on daily basis i got the message in my log file.
First The I/O is frozen on Database (DatabaseName) message appears in log. The message is like
I/O is frozen on ...
- 6,447
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
- 1,382
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 ...
- 828
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 ...
- 894
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 ...
- 365
7
votes
2
answers
7k
views
Maintenance Plan Wizard generates invalid script?
I've used the Maintenance Plan Wizard to create a plan. When I execute the plan, the "Maintenance Cleaup Task" fails with this error message:
Executing the query "EXECUTE master.dbo.xp_delete_file
...
- 1,079
7
votes
5
answers
7k
views
Managing Transaction Log Size using a Maintenance Plan [duplicate]
Are there any best practices for configuring a Maintenance Plan in SQL Server 2008? Currently, I'm removing database backups and transaction logs greater than 40 hours old, then backing them up. ...
- 667
6
votes
2
answers
513
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.
...
- 119
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 "...
- 7,386
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 ...
- 344
5
votes
2
answers
2k
views
Backup Database Task - What's the number at the end of the filename?
When using a SQL Server 2012 EMaintenance Plan for backup purposes, the Backup Database Task creates a unique filename. I understand the format all the way to the final set of numbers.
For example, a ...
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 ...
- 145
5
votes
2
answers
6k
views
How to specify a SQL Server maintenance plan subtask to run unconditionally on success or failure of preceding tasks?
I am having a (rather standard) daily maintenance plan created with SQL Server Management Studio Maintenance Plan Wizard.
AFAIU, the penultimate task "Back Up Database Task" will be executed only ...
- 1,382
5
votes
2
answers
4k
views
Deleting other files during maintenance plan
I have an SQL Server 2012 maintenance plan which does the normal stuff (rebuild indexes and update statistics, back up DBs, deletes .bak files older than X days, etc). Everything is working fine with ...
- 153
5
votes
2
answers
3k
views
Deploy SQL Server Backup Maintenance Plan in multi-tenant environment
We have a multi-tenant environment in which I've been trying to deploy a standardised maintenance plan across all servers. I've been running into issues importing the maintenance plan properly, as ...
- 153
5
votes
2
answers
5k
views
Run a PowerShell script on a different server - from inside a SQL Server job
I have a procedure to backup SSAS Databases.
That is working as a charm.
Now my server is filling up with SSAS backups and I would like to delete the backup files that are older than 2 days.
to ...
- 16.2k
4
votes
1
answer
3k
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 ...
- 43
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 ...
- 2,601
4
votes
3
answers
4k
views
Very high I/O on database during a backup maintenance plan
I've recently been having some hellacious problems with a specific maintenance plan. I found that part of the problem was that I had Shrink and Rebuild Index in my nightly plans, those have since ...
- 1,385
4
votes
1
answer
8k
views
Which user does Windows Authentication of a maintenance plan authenticate? before whom, why and for what?
I am having a maintenance plan configured with Windows Authentication
being run by an SQL Server Agent job with owner sa
Whom does Windows Authentication of a maintenance plan authenticate, for ...
- 1,382
4
votes
1
answer
4k
views
How to specify a task in a SQL Server maintenance plan conditionally?
I have a weekly and nightly SQL Server Agent jobs executing a weekly and nightly maintenance plans, resp.
These weekly (on Sunday) and nightly (on other 6 nights of the week) maintenance plans ...
- 1,382
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 ...
- 41
4
votes
0
answers
728
views
How to debug the cause of my suddenly extremely slow running backup maintenance plan? [closed]
Alexander and the Terrible, Horrible, No Good, Very Bad...backups.
The Setup:
I have an on-premise SQL Server 2016 Standard Edition instance running on a virtual machine from VMWare.
@@Version:
...
- 37.5k
3
votes
2
answers
15k
views
What is the difference between Completion and Success in SQL Server Maintenance Plans?
Using SQL Server 2008/R2 Maintenance Plans to setup some automated backups/cleanups and notifications. One thing that I can't figure out is what is the difference between Success and Completion?
The ...
- 299
3
votes
2
answers
914
views
Is there a Maintenance Plan that checks my database for corruption?
Is there any way to make a maintenance plan check all my databases on a single instance?
Is the maintenance plan Check Database Integrity Task enough ?
- 211
3
votes
1
answer
2k
views
Alerts from Maintenance Plans
I would like to setup alerts from within the maintenance plans.
When you setup a notification from the Agent Job it sends out the Job name, duration, status, message.
If I add a Notify Operator Task ...
- 319
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 ...
3
votes
1
answer
8k
views
Changed server name, now maintenance plan fails
I am using SQL Server 2008 R2. When I try to execute a maintenance plan I get the following error:
Message [298] SQLServer Error: 15404, Could not obtain information
about Windows NT group/user '...
- 337
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 ...
- 33
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) ...
- 157
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 ...
- 387
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 ...
- 167
3
votes
1
answer
3k
views
SQL Maintenance Plan 'Could not find part of the path' - where is it getting the path from?
I have a SQL Server 2012 maintenance plan which backs up all of the databases. Today I had an issue with a failed backup which I was able to resolve by viewing the detail in the 'Log File Viewer' ...
- 233
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 ...
- 211
2
votes
3
answers
183
views
Regarding Backup and Restore Strategy
I have some questions regarding backup and restore strategies.
I want to take backup and restore of large databases approx. 60 to 70 GB and it is a critical database on SQL Server.
Backup schedule ...
2
votes
2
answers
3k
views
Maintenance Plan Job running at additional unscheduled times?
I have a situation that has left me really scratching my head. This is a SQL Server 2008r2 instance, used as the database server for a substantial corporate SharePoint service. As my company is ...
- 3,041
2
votes
1
answer
8k
views
Maintenance Cleanup Job Task Fails but Backup Jop is successful
I get this error:
Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'\\SQLSvr..." failed with the following error: "xp_delete_file() returned error 2, 'The system cannot find the file ...
- 83
2
votes
1
answer
7k
views
Any way to import-export-migrate-save-restore a maintenance plan without having SSIS installed (and running)? [duplicate]
I am having dozens of MSSQL Servers 2012 R2 on Windows Server 2008 R2 with similar setup, configuration (in virtual machines) in production. Neither has SSIS installed.
I have to similarly maintain ...
- 1,382
2
votes
2
answers
453
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 ...
- 163
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:
...
- 121
2
votes
1
answer
492
views
Full backup at 1am, not started by my maintenace plan
I set up a complete set of Maintenance Plan.
Full backup of all databases at 3AM.
Differential backup at 6h30, 12h30, 18h30 and 00h30.
Log backup at each hour, at 45 minutes (1h45, 2h45...)
In my ...
- 215
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, ...
- 123
2
votes
1
answer
1k
views
TempDB not selectable for DBCC maintenance plan
I'm trying to clean up some of our maintenance plans and noticed that our TempDB is not getting it's weekly dose of DBCC CHECKDB goodness. I've tried to go into the maintenance plan and make sure it'...
- 3,834
2
votes
1
answer
1k
views
Use @@servername and db_name() on notify operator task
I'm setting up a maintenance plan and on error I added a "Notify operator task" with some text. But I want to be able to add something like:
Backup failed on MyDatabase\Instance_name...blablabla
I ...
- 2,826
2
votes
2
answers
3k
views
How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans?
Follow up to my question "What's the owner of SqlServerAgent job for?"
Basically, I am trying to understand how to create a user-login with least privileges for CRUD-maintaining SQLServerAgent jobs ...
- 1,382
2
votes
1
answer
1k
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 ...
- 231
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 ...
- 247