Dear SQL folks,

this is the first time I see no other way but sharing my trouble with you.

I'm using the following SQL code to load list of files from a certain directory into a table variable.

declare @file_list table (rowid int identity(1,1), the_file_name varchar(512), depth int, is_file bit, file_path varchar(1024))
insert into @file_list (the_file_name, depth, is_file)
exec xp_DirTree '\\server_name\shared_folder\', 1, 1

I repeat the insert several times as I'm retrieving file list from like 6 different folders. Then I use some DMLs on the @file_list table to get rid of the junk I'm not interested in.

All the above mentioned code is encapsulated in a stored procedure which is being executed from within an SQL job. For the job to run it usually takes less than a minute.

The job succeeds to run in 28 of 30 times. There are no regularly happening issues, just time to time the job keeps running for hours and I have to forcibly stop it.

When it comes to the situation of a long run, the job causes a massive growth of tempdb up until all the drive space gets consumed.

Obviously the tempdb grows as there is the variable table used but normally it stores tens of thousands of rows, so no big chunks of data.

I'm on MS SQL Server 2008 R2 and the SQL Server instance is sitting on another physical server (machine). The SQL Server instance has CMD shell commands disabled and enabling them is a no go.

Questions:

1. Is there a way to set a timeout for a specific SQL job? I know it's possible to set a timeout for the SQL server agent (i.e. basically on the SQL Server instance level) but I have other jobs that I don't want to be impacted with this.

2. Is it possible to set a timeout for a specific t-SQL command within a stored procedure body?

3. Will it anyhow help to encapsulate the "exec xp_DirTree"'s in a try-catch block?

4. Is there any other approach to kill an SQL job or a stored procedure execution after a certain period of time?

Any feedbacks will be greatly appreciated!

Thanks in advance.

VJ

>Any feedbacks will be greatly appreciated!

xp_DirTree is an undocumented and unsupported system stored procedure.  You should not use it in your code.  Write a PowerShell job step instead, or EXTERNAL_ACCESS CLR code if you have to manage files from inside SQL Server.

David

Thanks David, are you able to answer any of my 4 questions?

xp_DirTree isn't actually my main concern as I'm perfectly fine with how it works for me, I'm more wondering about how to have an SQL job killed after certain period of time.

• Edited by Vaclav Jansa 9 minutes ago