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.
134
questions
0
votes
1
answer
46
views
Huge Transaction Log File - Full Database backup, but no Transaction log backups
I hope someone can help. Please be gentle, this type of admin is not what I usually do.
I have inherited a database which is set up to do a FULL database backup at 02:00 every day.
The transaction log ...
- 1
-1
votes
1
answer
128
views
SQL Server error on Maintenance Plan: could not continue scan with NOLOCK due to data movement
We're using the solution of Hallengren for the maintenance of the database.
The Maintenance of Hallengren we use is SQL Server Index and Statistics Maintenance. cc IndexOptimize
Lately, we're getting ...
- 28
2
votes
0
answers
148
views
Setting up Database Integrity Checks
I'm relatively new to SQL and, unfortunately, am definitely not at the level of a DBA - However, it's become my job to look into some SQL issues...
There's a Database Integrity Check set to run for ...
- 21
0
votes
1
answer
76
views
Very long timestamp for a backup file created via a Maintenance Plan
I have been using Maintenance Plans to back up my databases. It works well enough for me but I am curious to know why the .bak have a very long date/time suffix. Here is an example:
...
- 3
0
votes
0
answers
114
views
Maintenance plan run date wrong
Win server 2019 & SQL Server 2017
On Jan 28th, 2022, I setup 3 maintenance plans to backup 3 databases from 3:00AM – 3:40AM. The last one finishes at 3:45AM. For a few weeks I confirmed that I am ...
- 1
0
votes
1
answer
39
views
Stop Changes to Maintenance Plan from Renaming Linked SQL Agent Job
Is there a way to stop a SQL job (which calls a maintenance plan) from being renamed every time i open and save that maintenance plan?
Each time i do that, a prefix of "_SubPlan" gets added ...
- 11
0
votes
1
answer
625
views
DBCC CHECKDB job failed on SQL Server 2014
We have a DBCC CHECKDB job that runs weekly on Saturdays. This job was created thorugh Maintenance Plan wizard. Typical duration for this job is 1.5 hours. This past weekend the job failed with the ...
- 83
-3
votes
1
answer
46
views
What are possible backup command origination sources?
In the backupset table I can see couple of backups that I am unable to understand where they originated from. That is - who ran them.
I have checked:
Maintenance plans
Sql jobs
Virtualization tool ...
- 1
4
votes
0
answers
620
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:
...
- 35k
0
votes
0
answers
60
views
Cannot modify a job in SQL 2008 R2. The Modify window simply doesn't come up
I created a maintenance plan to perform daily backups with transaction log backups to be scheduled on four hour increments, however, I did not enable the job immediately, nor did I enter add the ...
- 21
1
vote
1
answer
421
views
*.bak file on another server file share
On a physical Dell server, I am trying to setup a backup job to *.bak file on to failover server.
In the video, DBA Fundamentals - backup 3 - setting up maintenance plans @6:24 backing up to a local ...
- 21
1
vote
2
answers
65
views
Simple Backup Plan Question
I'm using Microsoft SQL Server 2016 Standard (64-bit). I have the following backup plan (9 steps long) ...
What I want is just one full (simple) backup everyday. Do I need to do step 8 (Backup ...
- 11
0
votes
1
answer
318
views
ola.hallengren - Alternative MySQL scripts!
Having worked with MS SQL for many years and found ola.hallengren scripts incredibly useful - I find my self in a MySQL environment.
Is there a MySQL alternative to ola.hallengren? - Perhaps someone ...
- 113
0
votes
2
answers
2k
views
Maximum Error Count Reached
I created a maintenance plan to backup all the databases locally on a folder in E Drive.
Out of nowhere, the job is failing occasionally. sometimes the job runs successfully and sometimes sends an ...
1
vote
1
answer
114
views
Excluding log shipped DB from current database maintenance plan (Ola's solution)
The Senior DBA and I were discussing our most recent rollout of log shipping to 25+ servers and one of the issues we had ran into during our pilot was the log chain breaking randomly.
We narrowed down ...
- 135
0
votes
1
answer
184
views
Where to find the whole log of a maintenance plan package job that failed with error?
I have a maintenance plan that runs on SQL Server agent as a the job and finishes with error. However the error is not visible in job history in SSMS, the text of Message is seemingly truncated. How ...
- 619
0
votes
1
answer
584
views
Copy SQL Server Maintenance Plan between instances on same server
I have multiple SQL Server 2019 instances on the same server. I've created and exported the first instance's maintenance plan, edited its exported DTSX file to change the instance and plan names to ...
- 103
0
votes
0
answers
59
views
Maintenance Tasks causes CPU to peg 100% utilization
I do not believe the maintenance tasks themselves to be the primary issue here, however CheckDB and index rebuilds cause the server to become unresponsive w/ 100% cpu utilization.
It is on one ...
- 101
1
vote
1
answer
97
views
Are there differences between which AlwaysOn AG server (Primary or Secondary Replica) I store my backups and retention maintenance plans on?
I have an AlwaysOn AG system in place between two servers. Server A hosts the primary replica, Server B hosts the secondary replica.
Are there any differences if my Full & Transaction Log ...
- 35k
1
vote
0
answers
62
views
How to select specific databases in a backup retention policy maintenance plan cleanup task?
I have full backups of three different databases. Right now all of their backups follow the same retention policy, but I need to setup the retention to a different amount of time for each database.
...
- 35k
0
votes
1
answer
20
views
Assigned filetype for backups not applying
I am working to migrate backups over to a new storage point. I figured the easiest thing to do was to duplicate the configuration over from the existing Maintenance Plan to a new one, just in case ...
- 37
0
votes
1
answer
287
views
Ola's Maintenance Solution Plan script newer version
We have very old version of Ola's Maintenance Solution Plan script implemented in our many servers and wondering if i have to update to his latest scriptm how can i upgrade easily on all my servers?
...
- 11
0
votes
1
answer
252
views
Migrating a maintenance plan from ms sql 2008 to 2016
I have a bunch of maintenance plans that I want to copy/migrate to sql server 2016. I am able to connect to Integration Services on sql server 2016 but I get the following error when expanding 'MSDB' ...
- 1
1
vote
1
answer
614
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 @...
- 337
2
votes
2
answers
418
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
1
answer
792
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 ...
- 23
4
votes
1
answer
2k
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
0
votes
1
answer
3k
views
Unable to Create new Maintenance plan in SSMS 2017
Hi I have reinstalled SQL Server 2016 in my database server last day.
I am able to connect to SQL Instance(SQL-02) using the SSMS 2017 i had earlier in the same server before reinstall.
But when i ...
- 1,295
0
votes
1
answer
116
views
Unable to create maintenance plan
When trying to create a maintenance plan locally on two prod servers through SSMS, I receive this error:
No such error occurs on my UAT system. They're all running SQL Server 2017 (14.0.3162.1), and ...
- 101
1
vote
1
answer
172
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 ...
- 25
6
votes
2
answers
466
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
1
vote
2
answers
603
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
- 123
0
votes
0
answers
52
views
CheckDB Failure on most databases in Dev instance
I have a maintenance plan on one of our infrequently used dev boxes which keeps failing on CheckDB on all user databases and most system databases. When trying to run CheckDB manually, I get the ...
- 353
0
votes
1
answer
52
views
Maintenance Plan Wizard generates invalid date parameter in script for delete
The generated SQL for my Maintenance Plan is
EXECUTE master.dbo.xp_delete_file 0,N'''',N'''',N''2019-01-02T10:44:21''
but the execution returns error message
Msg 102, Level 15, State 1, Line 1 ...
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
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 ...
- 211
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
914
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
2
votes
0
answers
95
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 ...
- 21
-1
votes
1
answer
982
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-...
user129291
1
vote
1
answer
5k
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 ...
- 123
0
votes
0
answers
646
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 ...
- 353
2
votes
1
answer
3k
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).
...
- 201
1
vote
2
answers
2k
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 ...
- 11
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 ...
- 644
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 ...
- 95
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 ...
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
3
votes
1
answer
623
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
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