Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

Workflows created through SQL Server Management Studio that help automate SQL Server maintenance tasks like backups, index rebuilds, and updating statistics. Each plan is comprised of an SSIS package but can be executed directly by the SQL Agent.

138 questions
22
votes
3 answers

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).
Greg Bala
  • 1,025
  • 3
  • 10
  • 20
15
votes
4 answers

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. StackOverflow and SQL Server Newbie recommend…
Iain Samuel McLean Elder
  • 2,408
  • 5
  • 26
  • 39
12
votes
2 answers

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. Is there a way? If no, will there be a way with a…
carlo.borreo
  • 1,477
  • 6
  • 22
  • 37
11
votes
2 answers

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
Fulproof
  • 1,392
  • 2
  • 26
  • 36
11
votes
1 answer

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 SQL Task to perform the backups - basically…
Mathieu Guindon
  • 914
  • 2
  • 7
  • 16
11
votes
1 answer

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 Database (DatabaseName).No User action is required. …
Md Haidar Ali Khan
  • 6,523
  • 9
  • 40
  • 62
10
votes
1 answer

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 retained for only 5 days. There is a cleanup…
PseudoToad
  • 838
  • 2
  • 12
  • 23
8
votes
4 answers

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 active filters besides that in this example. Then I…
Matt
  • 365
  • 6
  • 20
7
votes
2 answers

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 0,N'',N'',N'2016..." failed with the following…
Álvaro González
  • 1,089
  • 5
  • 17
  • 32
7
votes
5 answers

Managing Transaction Log Size using a Maintenance Plan

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. Problem I've seen is that the transaction log is…
Neil Knight
  • 677
  • 3
  • 12
  • 21
6
votes
2 answers

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. I tried to do full backup on database created…
shilan
  • 119
  • 3
6
votes
1 answer

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 "selected databases" in the setup ). But over the…
Racer SQL
  • 7,546
  • 16
  • 77
  • 140
6
votes
1 answer

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 this may be. The SQL Job Owner is sa and I merely…
hpk89
  • 344
  • 3
  • 9
5
votes
2 answers

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 Full Database Backup initiated via the Backup…
Travis Crooks
  • 55
  • 6
5
votes
2 answers

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 achieve this I am using the following POWERSHELL…
Marcello Miorelli
  • 17,274
  • 53
  • 180
  • 320
1
2 3
…
9 10