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....
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 ...
- 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 ...
- 1,039
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,...
- 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 ...
- 177k
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 ...
- 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 ...
- 397
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 ...
- 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 (...
- 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, ...
- 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 ...
- 10.9k
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 ...
- 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 ...
- 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(...
- 177k
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 ...
- 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 ...
- 979
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), ...
- 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 ...
- 16.1k
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 ...
- 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 ...
- 141
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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....
- 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 ...
- 177k
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 ...
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 =...
- 67.5k
Only top scored, non community-wiki answers of a minimum length are eligible