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
2
votes
1answer
47 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 ...
2
votes
0answers
48 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 ...
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 ...
2
votes
0answers
438 views

Proxy Account SSIS and NETWORK SERVICE

I currently have an instance of SQL running as NETWORK SERVICE which for various reasons I can't change. I have a maintenance plan running under a proxy account which has access to a network share to ...
2
votes
0answers
233 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 ...
1
vote
3answers
5k views

Restore database - maintenance plan task

I need to schedule a task that restores a database once a week from a backup. I have setup maintenance plans for backing up databases (Management > Maintenance Plans) but cannot find out how to make a ...
1
vote
2answers
81 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
1
vote
1answer
921 views

Backup Specific days monthly SQL Server

How to backup only specific days each month on SQL Server (I use both SQL Server 2005 and 2008) using a maintenance plan or Agent job. I mean by that to backup my databases from 28th of each month to ...
1
vote
2answers
1k views

Why does my SSIS package produce different results when executed from a maintence plan

I have a SSIS package that picks up a file in one network location transforms the data and drops off files ready for import in another network location. The package reads a file and writes a new file ...
1
vote
2answers
339 views

Reorganize Index Task [closed]

Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance? Is this an adequate step of triage for degraded application/query performance?
1
vote
2answers
474 views

Backing up my SQL Server System Databases

I'm trying to create a recurring scheduled job to fully backup the master, msdb and model databases, I'm using the Maintenance Plans for SQL Server 2008 R2, in the "Specific Databases to choose from ...
1
vote
1answer
79 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 ...
1
vote
2answers
76 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 ...
1
vote
2answers
690 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 ...
1
vote
1answer
560 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
2answers
4k 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: ...
1
vote
2answers
111 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 ...
1
vote
1answer
1k views

Maintenance plan fails on non-existent database

We have a maintenance plan on our production server which does all the usual stuff... checking integrity, rebuilding indexes, backups, etc. Lately, it has been failing every night, complaining about ...
1
vote
1answer
3k views

How to modify maintenance plan using T-SQL script in SQL Server 2008

How can I modify a maintenance plan in SQL Server 2008 using a T-SQL script? I want to change the schedule of a maintenance plan and change the path of the .bak file . All these changes should be ...
1
vote
2answers
158 views

When next will my fullback up run

I created a maintenance plan to run full backups on a SQL Server 2008 R2. The schedule runs every last Thursday of the month. Sometimes due to some unknown reason, such as electric outage. When the ...
1
vote
1answer
572 views

Object name 'Clean Up History' in use when trying to complete the maintenance plan wizard in SQL Server 2008 R2

I am trying to create a maintenance plan on a instance running SQL Server 2008 R2 SP1 (no CU's installed). When completing the wizard I get the following error: The object name cannot be changed ...
1
vote
1answer
23 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 ...
1
vote
1answer
955 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
1answer
858 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 ...
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
132 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 ...
1
vote
2answers
604 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 ...
1
vote
1answer
1k 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 '...
1
vote
1answer
340 views

Can I pass data between stages in a maintenance task

I have a Maintenance plan that I wish to execute nightly. The purpose is to set an archived bit against records that are more than 60 days old. This bit all works fine, however I'd like to log (and ...
1
vote
1answer
615 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,...
1
vote
2answers
1k views

Order of maintenance plan tasks

Please could you tell me the correct order of maintenance plan operations in SQL Server? Check integrity Update statistics Rebuild indexes Is correct this order?
1
vote
0answers
25 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
312 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
1answer
403 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 ...
1
vote
0answers
593 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 ...
1
vote
2answers
401 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 ...
1
vote
1answer
108 views

Why do you have to execute a full backup after a new database for trans backup jobs to run [closed]

Playing around on my local sql server at home after an issue occurred at work. We have full backup jobs scheduled on Mondays only. I created a DB on Tuesday (when doffs and trans are scheduled). ...
1
vote
1answer
120 views

Automatic database restore fails as cannot find file

A while back I asked for help automating a database restore using a backup file provided from another server: Automatically restore SQL Server database from file from another server. I'm pleased to ...
1
vote
0answers
102 views

Storing Configuration for bespoke maintenance plans

I got fed up with creating maintenance plans on several SQL servers. So I've devised a script that generate a series of SQL Agent Jobs (with notifications) that utilises Ola Hallengren's SQL Server ...
1
vote
1answer
1k views

Dealing with large transaction logs in a cluster

Disclaimer: I'm a dev, not a DBA. I've gleaned what I can from MSDN, but could do with some expert guidance! I'm working with a SharePoint 2013 installation on a SQL Server 2012 cluster of two DB ...
1
vote
2answers
5k 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 ...
0
votes
2answers
9k views

Job showing the running sign but not currently running

I have a script to get the currently running jobs: -- get the running jobs --marcelo miorelli -- 10-dec-2013 SELECT sj.name ,DATEDIFF(SECOND,aj.start_execution_date,GetDate()) AS Seconds FROM msdb.....
0
votes
1answer
173 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
1answer
537 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+ ...
0
votes
1answer
166 views

Script not emailing from Maintenance Plan/SSIS

I have created the following script to send emails from my plan but it isn't sending out. It works correctly in a query window and under an agent job. This is added to the plan as a 'Execute T-SQL ...
0
votes
1answer
363 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 ...
0
votes
1answer
35 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 @...
0
votes
2answers
482 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 ...
0
votes
1answer
124 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 ...
0
votes
1answer
717 views

How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL?

I would like to say that i had configured "Automatic DB Backup" as well as "Automatic DB Cleanup Task" through "Maintenance Plan" in MS SQL Server 2012 on my Production Database. And that is working ...