Implement SQL Server Agent Job Output Files

For SQL Server Agent Jobs we can setup text files to store the output of the job.

If we want to see the output of any log we normally use the below procedure for verification.

SSMS -- SQL Server Agent -- Job Name -- Properties -- Steps -- Edit --  Advanced

Click on View beside the log to table check box, It displays output of the job that is executed recently.

If there is any critical job we need to maintain daily job output in a separate file with date and time stamp, we can achieve with using SQL Server Tokens

Suppose we have job called DB_Integrity_Verification, follow the below steps to create a job output file using the below procedure.

SSMS -- SQL Server Agent -- DB_Integrity_Verification -- Steps -- Edit -- Advanced

In Output file Text Box paste the below syntax

C:\JobLogs\DB_Integrity_Verification_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt

Click on OK and close the job.

If you execute the job it creates a text file with date and time stamp in the C:\JobLogs folder, make sure JobLogs folder exists on C: Drive.

Configure Database Mail in SQL Server 2008

1. SQL Server SSMS -- Management -- Database Mail Right Click -- Select Configure database mail
2. Click on Next Button
3. Select Setup Database Mail by performing the following tasks -- Next
4. Profile Name -- Kalyan_Mail_Profile
    Description -- This profile is to test SQL Server Job Notifications -- Add
5. Provide valid email address and smtp address along with port number - Ok
6. Click n Next Button
7. Select the profile which you recently added -- Next -- Next -- Finish

With the above steps database will be configured in SQL Server, You can test this by using below steps


1. SQL Server SSMS -- Management -- Database Mail -- Right Click -- Send Test Mail
2. Ensure mail profile name is correct
3. Specify email Id
4. Hit Send Test Mail Button

Database Mail Catalog views

select * from msdb..sysmail_allitems
select * from msdb..sysmail_sentitems
select * from msdb..sysmail_unsentitems
select * from msdb..sysmail_faileditems
select * from msdb..sysmail_mailattachments
select * from msdb..sysmail_event_log
select * from msdb..sysmail_profile
select * from msdb..sysmail_account

A database snapshot cannot be created because it failed to start

A database snapshot cannot be created because it failed to start

a) When you receive that error message you can run the checkdb on all databases and try what are trying to invoke.