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
16
votes
3answers
44k 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).
12
votes
4answers
23k 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....
12
votes
2answers
24k 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. ...
9
votes
1answer
18k 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 ...
8
votes
1answer
9k 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 ...
8
votes
1answer
38k 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 ...
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 ...
7
votes
5answers
5k 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. ...
5
votes
2answers
1k 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
3answers
1k 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 ...
5
votes
1answer
3k 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 ...
5
votes
2answers
4k 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 ...
5
votes
2answers
186 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. ...
5
votes
2answers
3k 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 ...
5
votes
2answers
2k 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 ...
5
votes
2answers
3k 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 ...
4
votes
2answers
12k 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
4
votes
1answer
181 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 ...
4
votes
1answer
2k 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 "...
4
votes
3answers
5k 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 ...
4
votes
3answers
3k 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 ...
4
votes
1answer
3k 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 ...
4
votes
1answer
7k 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 ...
4
votes
1answer
1k 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 ...
4
votes
1answer
2k 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 ...
3
votes
2answers
656 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 ?
3
votes
1answer
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 ...
3
votes
1answer
6k 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 '...
3
votes
1answer
585 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 ...
3
votes
2answers
7k 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 ...
3
votes
1answer
124 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 ...
3
votes
0answers
2k 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' ...
2
votes
2answers
11k 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 ...
2
votes
3answers
937 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 ...
2
votes
2answers
2k 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 ...
2
votes
1answer
7k 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 ...
2
votes
1answer
6k 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 ...
2
votes
1answer
317 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 ...
2
votes
1answer
5k 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, ...
2
votes
1answer
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'...
2
votes
1answer
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
votes
2answers
2k 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 ...
2
votes
2answers
1k 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
2answers
558 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 ...
2
votes
1answer
106 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 ...
2
votes
1answer
567 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
651 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
1answer
3k 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) ...
2
votes
1answer
588 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
votes
1answer
31 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 ...