SQL Server Database administrators often copy(refer Fig 1.0) huge files, such as Full backup files and transaction log backup files from production to QA or from production to development environment and so on. Sometimes they need to copy source data files for importing. If they want to restore those Full backups or transaction log backup files or import those huge source files, they have to wait until the copy is complete.

In SQL Server there are three way to check the status of the file. They are:

Xp_getfiledetails reports that the file exists and shows more details about the file.

Xp_cmdshell command executes the MS-DOS command and shows that file Exists and it has been created.

The output of XP_cmdshell with DIR command is shown below

 Volume in drive D is New Volume
 Volume Serial Number is 284B-A370

 Directory of d:\

11/19/2004  01:47a          16,121,856 master.bak
               1 File(s)     16,121,856 bytes
               0 Dir(s)   7,940,255,744 bytes free


(9 row(s) affected)

xp_fileexist 'D:\PERFMON.CSV' also reports that the file exists.

However, when a file is being copied (refer Fig 1.0), the above three statements report that the file exists and is ready to be used, which is false. Therefore, in order to discover the real status of the file we have to create a user defined function as shown below. This Function takes advantage of SP_Oamethod and the File system object to get the real status of the File.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Create function udf_CheckFileStatus (@filename varchar(1000))
returns bit
as

BEGIN
DECLARE @FS int
DECLARE  @OLEResult int
DECLARE  @FileID int
--set @filename = 'D:\sqldumps\etst.bak'
--declare  @filename varchar(1000)
DECLARE @source NVARCHAR(255)
DECLARE @description NVARCHAR(255)
DECLARE @flag bit
set @source ='Exist'
set @description='Exist'
--set @filename = 'D:\sqldumps\msdb_20040916_23_00_40.BAK'
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0  
begin
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT 
goto displayerror
end
--Open a file
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT,@filename , 1
IF @OLEResult <> 0  
begin
EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT 
goto displayerror
end
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
DisplayError:
if @source is NULL and @description is NULL
begin
--print @filename + '  in Use'
set @flag = 1
end
else
begin
--print @filename + '  is ready'
set @flag = 0
end
return @flag
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Download udf_CheckFileStatus.sql.

How to make use of the udf_CheckFileStatus function when doing BULK INSERT of a file that is being copied

When you rely on xp_fileexist, xp_cmdshell and Xp_getfiledetails to obtain the status information of a file that is being copied, the following BULK insert command fails with the error shown below. In addition, you have to keep trying the SQL Statements until the file copy is complete.

Bulk Insert Statement

BULK INSERT [PERFMON]
   FROM 'D:\PERFMON.CSV'
   WITH 
      (
         FIELDTERMINATOR = ',',
         ROWTERMINATOR = '\n'
      )

Output Message

Server: Msg 4861, Level 16, State 1, Line 1
Could not bulk insert because file 
  'D:\PERFMON.CSV' could not be opened. 
  Operating system error code 32
  (The process cannot access the file because 
  it is being used by another process.).

You can avoid such errors by making use of the udf_CheckFileStatus function shown below. The SQL Statement shown below checks for the real status of the file that is being copied. If the return status of the udf_CheckFileStatus is not Zero it waits for one minute and continues trying until the status is 1. Therefore, the database administrators do not have to spend time waiting for the file to completely copy and then do the BULK INSERT. When the status is 1 the file is automatically inserted using the BULK INSERT statement.

SQL Statement

while 1=1
begin
print getdate()
print 'Status='+Convert(varchar(1),dbo.udf_CheckFileStatus('D:\PERFMON.CSV') )
if dbo.udf_CheckFileStatus('D:\PERFMON.CSV') =0
begin
BULK INSERT [PERFMON]
   FROM 'D:\PERFMON.CSV'
   WITH 
      (
         FIELDTERMINATOR = ',',
         ROWTERMINATOR = '\n'
      )
BREAK
end

waitfor delay '00:01:00'
END

Output

Mar  1 2005  8:27AM
Status=1
Mar  1 2005  8:28AM
Status=1
Mar  1 2005  8:29AM
Status=1
Mar  1 2005  8:30AM
Status=1
Mar  1 2005  8:31AM
Status=1
Mar  1 2005  8:32AM
Status=0

(12585 row(s) affected)

Imported

How to make use of the udf_CheckFileStatus function when restoring database that is being copied

When you rely on xp_fileexist, xp_cmdshell and Xp_getfiledetails to obtain the status information of a file that is being copied, the following RESTORE statement fails, with the error shown below.

Restore Statement

Restore database master_emp from disk ='D:\master_emp.BAK' with replace,
move 'master_emp_Data' to 'd:\data\Master_Emp_Data.mdf',
move 'master_emp_log' to 'd:\data\Master_Emp_log.ldf'

Output Message

Server: Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'D:\master_emp.BAK'. Device error or device off-line. 
  See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

You can avoid such errors by using the udf_CheckFileStatus function as shown below. The SQL Statement shown below checks for the real status of the file that is being copied. If the return status of the udf_CheckFileStatus is not zero, it waits for one minute and keeps trying until the status becomes 1. Therefore, the database administrators do not have to spend time waiting for the file to completely copy. When the status is 1, the backup file is automatically restored.

SQL Statement

while 1=1
begin
print getdate()
print 'Status='+Convert(varchar(1),dbo.udf_CheckFileStatus(' D:\master_emp.BAK') )
if dbo.udf_CheckFileStatus('D:\master_emp.BAK') =0
begin
--Kill processes using this master_emp database
Restore database master_emp from disk ='D:\master_emp.BAK' with replace,
move 'master_emp_Data' to 'd:\data\Master_Emp_Data.mdf',
move 'master_emp_log' to 'd:\data\Master_Emp_log.ldf'
BREAK
end

waitfor delay '00:01:00'
END

Output Message

Processed 45520 pages for database 'master_emp', file 'master_emp' on file 1.
Processed 1 pages for database 'master_emp', file 'master_emp_log' on file 1.
RESTORE DATABASE successfully processed 45521 pages in 73.525 seconds (5.071 MB/sec).

How to use the udf_CheckFileStatus function to notify whether the file is ready for processing

If you require SQL Server to send an email to you when the copy process is complete, you can use the SQL Statement below. The SQL Statement shown below checks for the real status of the file that is being copied. If the return status of the udf_CheckFileStatus is not zero it waits for one minute and keeps trying until the status becomes 1. When the status is 1 it sends out the email using xp_sendmail.

while 1=1
begin
print getdate()
print 'Status='+Convert(varchar(1),dbo.udf_CheckFileStatus(' D:\master_emp.BAK') )
if dbo.udf_CheckFileStatus('D:\master_emp.BAK') =0
begin
xp_sendmail @recipients ='mak_999@yahoo.com',
@subject = 'Files arrived safely',
@message ='Copy complete: The File D:\master_emp.BAK has been copied'
BREAK
end

waitfor delay '00:01:00'
END

Conclusion

The intention of this article has been to demonstrate how to use the user defined function, udf_CheckFileStatus, in order to avoid waiting for a file to be copied and how to run a process more efficiently. The same function can be used in a SQL Server scheduled job for checking files before running a DTS package, etc.