23
Go to the server where the jobs exist, in Management Studio's Object Explorer go to Management > SQL Server Agent and highlight the Jobs folder. Hit F7 (or View > Object Explorer Details). You should see multiple jobs in the right pane. Highlight the ones you want to copy, right-click, Script Job As > Create To > Clipboard. Now connect to the other server, ...
15
The job owner determines the execution context of the job's steps.
For a T-SQL job step: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified database user (set on the Advanced page of the Job Step Properties dialog). If the job owner is not a sysadmin then the step will execute as the job ...
13
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.sysssispackages table of the destination server.
Simply create a linked server on source server and use this Insert from the source server:
INSERT INTO
YOUR-DEST-...
11
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 select Script job as... Drop and recreate to... New query editor window.
Edit:
You can export the maintenance plans as well. You need to connect to Integration ...
10
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, where windows admin team enabled the SQL Server backup through VSS (Volume Shadow Copy Services). And when this process runs it freezes I/O temporarily to ...
9
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 on a few experiments:
For SQL Server 2008 and above, the 7 digits are the sub-second portion of the datetime2(7) output of SELECT SYSDATETIME(); at the moment ...
9
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 arguments from source code. Unofficial sources suggest these are its arguments:
File Type = 0 for backup files or 1 for report files.
Folder Path = The folder to ...
9
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 trigger to invoke job to create database backup.
e.g
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
EXEC msdb.dbo....
8
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 the same time survive ?
Based on the above answer, you should carefully choose your database recovery model.
In simple terms (not discussing bulk logged ...
8
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 look at (0 is current, 1 is the first archive, 2 is second, etc)
@p2 is null for the error log, 2 for Agent
@p3 and @p4 are strings to search for in the output
...
7
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, activity.run_requested_date, GETDATE()) as Elapsed,
case when activity.last_executed_step_id is null
then 'Step 1 executing'
else '...
7
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 (at least not without jumping through a ton of additional hoops beyond just an sp_configure option - it's not something you ever want to do on a production ...
7
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 scheduling application already that might be a better choice. Either way, you'll need to get comfortable with the sqlcmd utility if you aren't yet.
If I were ...
6
(edit: Although this answer was accepted, and is valid, please ensure you read the other answer by @Greenstone-Walker since it has excellent information)
As Billinkc noted, a job can be edited only by its owner or members of the sysadmin role. And as Shawn Melton noted, removing the owner from the job simply ensures your login will become the owner. You ...
6
There is no 'Restore DB Task' in the maintenance plan. You will have to use the Execute T-SQL Statement Task. In that task, add a restore script in your T-SQL statement. Simple example:
RESTORE DATABASE YourDBName FROM DISK = 'D:\DBbackups\YourDB.BAK'
WITH REPLACE
GO
Then create a schedule for weekly execution. Hope this is what the business requires, as ...
6
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 Name, duration, status, etc. You could add a "on failure" precedence constraint that only executes the Notify Operator Task when a step in the maintenance plan ...
6
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 become pretty huge (I've seen it reach many GB in certain situations).
The error log is stored as a text file, so opening it in the log viewer essentially ...
5
Agree with Jon.
Instead of going through the pain of setting up Maintenance Plans, I highly recommend to use
Ola Hallengren's SQL Server Maintenance Solution
This solution is flexible (can be adjusted as per your needs) and is supported on SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012 as well as it is widely used in SQL Server ...
5
The Manage Connections dialog is for a connection to the Database or Instance you are performing maintenance on.
So if you type in a sql authenticated user called 'bob' there, the user bob will be the one that connects to the instance to do the maintenance tasks you are specifying.
You can prove that out by running SQL Server Trace/Profiler (in a dev ...
5
There are 3 options in Value: Success, Failure and Completion (approximate). Changing the value to Completion for all the preceding precedence constraints should ensure that the backup task is reached regardless of the upstream tasks.
Execution Results
The precedence constraint can use the following execution results alone or in combination with an ...
5
Failure: When there is some issue in job execution.
Success: When the job executed successfully.
Completion: This will contain the completion of SQL job whether it completes successfully or with issues....
SQL JOB can be mapped with database mail service for e-mail notifications.
These mails will contain the information about success/failure of SQL job.
If ...
5
The execution result, in your case, success, completion, or failure is a precedence link that can create a relationship between two tasks. From BOL
A precedence link between two tasks establishes a relationship between the tasks. The second task (the dependent task) executes only if the execution result of the first task (the precedent task) matches ...
5
Much as the error message states, it can't find the file N'\\SQLSvr..."
If you know the file does exist, then that usually points to a permission problem with either the account SQL Agent is executing as or if you've created it, a credentials proxy account. Either way, it can't access the network share or it does not have permission to perform the delete ...
5
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(GETDATE()) <= 8 OR DAY(GETDATE()) >= 28
BEGIN
-- do backups
END
On other days (9th -> 27th), the job will still run, but everything inside the IF conditional ...
5
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 statement.
Running this task will be resource intensive. You don't want to do this in prod. servers if you don't have a maintenance window.
The common way is the ...
5
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 you have large tables. Read here how Auto update statistics works:
Auto Update Statistics Option Statistics are checked before query
compilation or before ...
5
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), you can run sp_WhoIsActive to list the running queries and see what command they're executing right now.
There's even a Percent Complete column that gets ...
5
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 to Output File
You will notice a string like:
$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseIntegrityCheck_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(...
4
If you have a working maintenance plan, which is really just an SSIS package, you can export that .dtsx file out of the database and redeploy to the next one.
For 2008/2012, those packages are stored in msdb.dbo.sysssispackages which you can verify with a query
;
WITH FOLDERS AS
(
-- Capture root node
SELECT
cast(PF.foldername AS varchar(...
Only top voted, non community-wiki answers of a minimum length are eligible
