13 votes

Copy maintenance plans to a different server

I found a more hacking way. Every Execution Plan is one record in the msdb.sysssispackages table. You can copy records from the msdb.sysssispackages table of source server to the msdb....
user avatar
11 votes
Accepted

Scripting a maintenance plan

When you create job using maintenance plan, a job is automatically created in SQL Server Agent under Jobs. So now go to SQL Server Agent, expand jobs, and look for your job. Right click on it and ...
user avatar
  • 18.4k
11 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 ...
user avatar
10 votes
Accepted

I/O is frozen on Database & also I/O was resumed on Database on daily basis in log with complete Backup

I believe its just a message and not an error unless and until the task is getting impacted when this message is logged in the error log: We also encountered the same for one of our production server,...
user avatar
  • 6,995
9 votes

Backup Database Task - What's the number at the end of the filename?

Since this logic is buried within the code for the SSIS package behind maintenance plans, I think you will only get guesses unless the author happens to swing by here. Here is my educated guess based ...
user avatar
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 ...
user avatar
  • 41.4k
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 ...
user avatar
8 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 ...
user avatar
  • 61.3k
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 (...
user avatar
  • 9,488
7 votes
Accepted

Job showing the running sign but not currently running

Use script instead to check status of currently running job. SELECT job.name, job.job_id, job.originating_server, activity.run_requested_date, DATEDIFF(MINUTE, ...
user avatar
  • 3,953
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 ...
user avatar
6 votes
Accepted

Alerts from Maintenance Plans

Since the Maintenance Plan uses EXECUTE msdb.dbo.sp_notify_operator @name=N'some operator',@subject=N'some subject',@body=N'some body' there is no way to automatically include details about the Job ...
user avatar
  • 67.5k
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 ...
user avatar
  • 14.6k
5 votes
Accepted

Backup Specific days monthly SQL Server

This will be tough to do with the built-in schedule UI, unless you want to create 12 different schedules, but you can easily build this into your job step. Assuming it runs every night: IF DAY(...
user avatar
5 votes
Accepted

Is there a Maintenance Plan that checks my database for corruption?

Good question. The integrity check in the maintenance plan is enough since it will run a dbcc checkdb. From MSDN: integrity check The Check Database Integrity task encapsulates the DBCC CHECKDB ...
user avatar
  • 1,345
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 ...
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), ...
user avatar
  • 41.4k
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 ...
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 ...
user avatar
  • 14.6k
4 votes

Can I export a maintenance plan without using Integration Services?

There is a way to do this. Assume that, like the OP, you have two SQL Server instances, one of which has SSIS installed and one of which does not (probably cannot, e.g. if it is SQL Server Web ...
user avatar
4 votes
Accepted

Backup Database Task - What's the number at the end of the filename?

Concurring with Aaron's assessment of ask the developers. I used the wizard to create a full backup plan, exported that to my machine and then opened it up in Visual Studio/BIDS/SSDT-BI. You'll see ...
user avatar
  • 15.6k
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 ...
user avatar
  • 3,428
3 votes
Accepted

Full backup at 1am, not started by my maintenace plan

Add columns: msdb.dbo.backupset.software_vendor_id, msdb.dbo.backupset.user_name to your query. If your software vendor id = 4608 then its sql native, anything else is a third party ...
user avatar
  • 324
3 votes

Deleting other files during maintenance plan

Even though, @KASQLDBA has answered the question, below will supplement his answer from a different perspective : Since you are using SQL Server 2012, you should use NATIVE COMPRESSION as opposed to ...
user avatar
  • 61.3k
3 votes
Accepted

Deleting other files during maintenance plan

As per MS here The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans. By using the Maintenance ...
user avatar
  • 6,995
3 votes
Accepted

T-SQL query to find backup folder for specific maintenance plan

You can use the available views in msdb to find the information regarding backups : dbo.backupset: provides information concerning the most-granular details of the backup process dbo....
user avatar
  • 6,995
3 votes
Accepted

ONLINE vs OFFLINE index rebuilds

Hard for us to know if it will be a net positive. These are pretty much no-brainers: setting all operations to ONLINE, because you can ignoring tables with < 1000 pages You should still test ...
user avatar
3 votes
Accepted

TempDB not selectable for DBCC maintenance plan

Tempdb won't be available for you in the Mantainance Plan database list box, but you sure can run it as a script: DBCC CHECKDB(N'tempdb') WITH NO_INFOMSGS According to Mr. Brent Ozar, you SHOULD ...
user avatar
3 votes

Script not emailing from Maintenance Plan/SSIS

Currently, your code will not send any email if any fields used to create the subject or body are NULL. I'd add a case to deal with that such as: IF (@EmailBody IS NOT NULL) BEGIN SET @Subject =...
user avatar
  • 67.5k

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