20
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, ...
12
That job has to do with policy-based management, not SQL Server Agent jobs in general. In Object Explorer right-click SQL Server Agent and select Properties, then move to the History tab. What does it say here:
You can adjust those settings so that it keeps more rows overall, more rows per job, and keeps history longer. I am guessing either you have a lot ...
10
Maintenance plans are stored in msdb.dbo.sysssispackages like any other SSIS packages that are stored to SQL Server. I have a handy post on SSIS Package Extract from MSDB that should cure what ails you.
10
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
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 ...
9
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-...
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 ...
8
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 ...
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
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 ...
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
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....
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
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
I am not sure how dirty you want to get on this one but the only method is going to be opening up SQL Server Data Tools and building (or modifying) an SSIS package. One would be to import the maintenance plan into SQL Server 2012 Data Tools and modify the SSIS package. The other would be to create it from scratch. You have the same options as the maintenance ...
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
If your log file is growing that large then you are doing at least one of these things wrong:
if you need point-in-time recovery, then you are not backing up the log frequently enough (and perhaps the database too). Perhaps you have created a maintenance plan to back up the database daily or weekly, but haven't configured a maintenance plan that backs up ...
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
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
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
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 ...
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
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 ...
Only top voted, non community-wiki answers of a minimum length are eligible
