Skip to main content
13 votes
Accepted

Maintenance Plan Wizard generates invalid script?

The master.dbo.xp_delete_file procedure appears to be undocumented and it also seems to be a binary procedure (sp_helptext 'master.dbo.xp_delete_file' prints xpstar.dll) so we can't figure out ...
Álvaro González's user avatar
9 votes
Accepted

Best way to backup & truncate transaction logs on a schedule

Only overnight SSIS is making writes, daytime is all reads - I only need daily recovery. You should choose your recovery model based on your business needs : How much data business can loose and at ...
Kin Shah's user avatar
  • 62.3k
9 votes
Accepted

Why does it take so long to view logs on a server?

Yes, reading logs takes a long time in the log file viewer. A few things to fix it: Try using xp_readerrorlog with filtered parameters to get just the data you want: @p1 is the log file you want to ...
Brent Ozar's user avatar
9 votes

Full backup on database creation

To achieve this task kindly follow below steps:- create server level trigger on create_database. Create sql job and add code to dynamic get the name of database and initiate backup. Add Code in the ...
Vinod Narwal's user avatar
8 votes
Accepted

What does 'sp_configure allow updates' do?

This is an old option from the SQL Server 2000 days that would allow you to directly make updates to the system catalog tables (sysobjects, syscomments, etc.). Since 2005, this is no longer possible (...
db2's user avatar
  • 9,658
7 votes
Accepted

Scripting a Maintenance Plan in SQL Server Express

You've pretty much self answered here - you won't be able to rely on SQL Server to do the scheduling for you. The most common method is to use Windows Task Scheduler, but if you have any other job ...
LowlyDBA - John M's user avatar
6 votes

Why does it take so long to view logs on a server?

By default, SQL Server will only roll over the error log when the instance restarts. If you have excellent server uptime (perhaps you only patch & reboot a few times per year), the error log could ...
AMtwo's user avatar
  • 16.2k
5 votes
Accepted

Could an update on all statistics separately cause bad performance?

Question: I am not too familiar with stat maintenance, so I am wondering, is it really necessary to run a weekly job separately to update all statistics? Yes, you need to maintain your statistics if ...
Daniel Björk's user avatar
5 votes

Maintenance plan don't stop & don't backup all databases

If the job is still running, check what command it's currently executing with Adam Machanic's excellent free stored procedure, sp_WhoIsActive. After installing it (typically in the master database), ...
Brent Ozar's user avatar
5 votes
Accepted

Logging to file

Changing it per jobstep while keeping Ola's log naming convention You can change the location in the job step properties, opened with either double clicking or pressing Edit. Select Advanced And go ...
Randi Vertongen's user avatar
5 votes

What are possible backup command origination sources?

If you're seeing backups in the msdb backup history, they were either performed via T-SQL or VSS. T-SQL backups can be invoked manually, via SQL Agent, via maintenance plans, or any huge number of ...
AMtwo's user avatar
  • 16.2k
3 votes

Why does it take so long to view logs on a server?

Maintenance Plans generate their own log files if you choose that option. When looking at MP history, it is reading from [msdb].[dbo].[sysmaintplan_log] and [msdb].[dbo].[sysmaintplan_logdetail], ...
Kevin3NF's user avatar
  • 1,211
3 votes

Why does it take so long to view logs on a server?

You should definitely roll your SQL Error log and SQL Agent Error logs as @AMtwo suggested in comment above. Since the question was regarding reading maintenance plan log I will explain how you can ...
SqlWorldWide's user avatar
  • 13.2k
3 votes
Accepted

Should I create multiple maintenance plans to backup more than 200 User databases

I wouldn't use maintenance plans but either Hallengren's backup solution, or, Minion Backup (the latter would be better because you can still have a single job, but write a table into each database ...
Cody Konior's user avatar
  • 3,508
3 votes
Accepted

Maintenance plan is backing up database transaction log but it is not selected

Your radio button is on All user databases and NOT on These Databases
Scott Hodgin - Retired's user avatar
3 votes

Can I export a maintenance plan without using Integration Services?

I've been struggling with the EXACT same issue. Here's the main take-away: No integration services needed on your WEBSERVER. One documented way is to use DTUTIL. Just use ANY SQL Server (even the ...
Sting's user avatar
  • 2,788
3 votes
Accepted

Cannot see new drive in SSMS maintenance wizard

+1 for sensibly wanting to get the backups on a separate drive. However, if the new cluster volume is on the same SAN, you're not really accomplishing much. It would likely be much better to run ...
Hannah Vernon's user avatar
  • 70.1k
3 votes

SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

You haven't done anything wrong or gone crazy - it's a known bug and we've experienced the same thing. Our process was changed that we would script out job status and reapply after any maintenance ...
Cody Konior's user avatar
  • 3,508
3 votes

SQL Server AlwaysOn High Availability Maintenance Plans

The best way is to use Ola Hallengren's maintenance plans, because the ability to determine AGs in the maintenance is already "baked in". However, if for some reason you don't want to or can't use his ...
SQL_Hacker's user avatar
3 votes
Accepted

Transaction Log Backup Maintenance Plan and Newly Restored Databases

Check out Ola Hallengren's maintenance scripts. They have a @ChangeBackupType parameter that lets you automatically escalate to fulls if necessary.
Brent Ozar's user avatar
3 votes

Does SSD drive make indexes rebuild and update stats useless with SQL Server?

I'm going to disagree that SSDs are the de facto standard, though they certainly are far more common than a short time ago. I know my company doesn't set any standards, but nearly all of our virtual ...
Fredric Shope's user avatar
3 votes
Accepted

Maintenance Job not running but not Failing

Instead of using the Maintenance Plans feature, simply create a SQL Server Agent Job with steps for each of the tasks you've defined in the plan. SQL Server Agent Jobs provide a far easier mechanism ...
Hannah Vernon's user avatar
  • 70.1k
2 votes

Changing MaximumErrorCount when executing maintenance plan

Each task in a Maintenance Plan has a variety of options that can be changed by right-clicking the desired task, then selecting the "Properties" option from the context-menu. As in: To allow a task ...
Hannah Vernon's user avatar
  • 70.1k
2 votes

Transaction log backups cleaning

I would play it safe and delete files older than three days, a week, two weeks, what have you. You never know when management will run to you saying someone accidentally deleted data yesterday or ...
Aaron Bertrand's user avatar
2 votes

Regarding Backup and Restore Strategy

I want to take backup and restore of ... a critical database on SQL Server. Let's start at the right end of this story ... When your database explodes in a ball of flame: How long will you take ...
Phill  W.'s user avatar
  • 8,741
2 votes

Regarding Backup and Restore Strategy

Is this correct approach? if not then please suggest better option? It looks fine, your backups should depend on your RPO and RTO, which should be set by the business. Often when no backups are in ...
Yannick Liekens's user avatar
2 votes

Regarding Backup and Restore Strategy

Your backup plan seems to be good. Of course, everything depends on: Recovery Point Objective (RPO) in other words – how much data can you afford to lose? And Recovery Time Objective (RTO) if ...
Alexandr Omelchenko's user avatar
2 votes

Backup set expire option - Backup SQL Server 2012

Answered here by @JamesAnderson The "Backup set will Expire" option is used to indicate how long a backup should be prohibited from being over written. It will not clean up old backup files. You can ...
Scott Hodgin - Retired's user avatar
2 votes
Accepted

SQL Server backup practice with limited disk space

If you need to be able to restore to any point in time then you need to be in full recovery mode (which you are). When in full recovery mode, you need to do transaction log backups in addition to your ...
Greg's user avatar
  • 3,252
2 votes

Could an update on all statistics separately cause bad performance?

If the UPDATE STATISTICS command you are running manually is not sampling enough rows, it could be causing bad estimates, which could in turn cause poor performance until statistics are updated ...
Tony Hinkle's user avatar
  • 7,924

Only top scored, non community-wiki answers of a minimum length are eligible