Output files in SQL Server
When it comes to moving contents of drives, some output files may be a bit hesitant to move. In this case, you may need to find out what is generating and populating this output file so you may relocate to its new destination. It may be several things including jobs, server audits or even the replication agent. In this article, we will go through the steps of finding out who is populating these output files.
- Check for output files generated/populated via jobs
Enter the below query to list all jobs which output to a file by searching through all jobs and all their steps.
select *
from msdb.dbo.sysjobs j
inner join msdb.dbo.sysjobsteps js
on j.job_id = js.job_id
WHERE js.output_file_name <> ‘[NULL]’
order by js.output_file_name
(M, 2016)
- Check for audit files
A DBA may configure the database engine to track and log events. These can be stored to file. To view files written via SQL Server Audits, use the below query
select * from
sys.server_file_audits
(Guyer, et al., 2016)
- Check replication agent for output files
The replication agent may be set to output details of replication events to an external file. To check whether files are output as a result of the replication agent, follow the below steps.
- In SQL Server Management Studio, go to Job Activity Monitor
- Sort jobs by category
- Right click all jobs in relation to the replication agent.
- Click Properties
- Click Steps
- Click on step 2 “Run Agent” and click Edit
- Search for the –Output command in the command textbox. This will give you the path of the file being generated.
(Chen, 2013)
References
Chen, J. (2013, March 2). How to enable replication agents to log to output files? Retrieved from Hands on SQL Server: http://www.handsonsqlserver.com/how-to-enable-replication-agents-to-log-to-output-files/
Guyer, C., Macauley, E., Kumar, S., Milener, G., Hamilton, B., Rabeler, C., . . . Byham, R. (2016, November 11). SQL Server Audit (Database Engine). Retrieved from Microsoft Docs: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-2017
M, S. (2016, August 24). Query to find the Job Output file location at each job step. Retrieved from SQL Server Central: http://www.sqlservercentral.com/scripts/Agent+jobs/125403/
