Post reply

Use T-SQL to determine file in use

  • September 16, 2005 at 5:25 am

    #91196

    I'm looking for a way to determine whether a file is in use, from T-SQL.

    I'm open to using xp_cmdshell if we have to, but ideally I'm trying yto do this from T-SQL, from a SQL Server Agent Job.

    Any ideas?

  • September 16, 2005 at 5:33 am

    #590503

    http://www.sqlservercentral.com/scripts/contributions/1028.asp




    My Blog: http://dineshasanka.spaces.live.com/

  • September 16, 2005 at 6:18 am

    #590521

    Thanks Dinesh - looks good. I'll play around this weekend...

  • September 16, 2005 at 6:44 am

    #590529

    good luck




    My Blog: http://dineshasanka.spaces.live.com/

  • September 16, 2005 at 7:36 am

    #590576

    Ever wondered if a file was available to copy, for example a SQL backup file? Here’s something interesting, although I’m not 100% certain of the risks it may create, especially in a clustered environment. I'd appreciate thoughts/ comments... (btw - the function comes from http://www.sqlservercentral.com/scripts/contributions/1028.asp - it is not my work).

    Create Function fn_FileAvailable (@FileName VarChar(255)) 
    Returns Int 
    As 
    Begin 
    /* Return Values 
    = 0 FileSystemObject can open the file, so deemed "available" 
    = -1 means FileSystemObject could not be created 
    = -2146828235 (0x800A0035) File Not Found  
    = -2146828218 (0x800A0046) Permission Denied (in use) ...etc */ 
    DECLARE @fso int, @hr int, @file int, @Result Int 
    EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT  
    IF @hr <> 0 
    BEGIN  
      EXEC sp_OAGetErrorInfo @fso -- Error Processing
      Return -1 
    END  
    EXEC @hr = sp_OAMethod @fso, 'OpenTextFile', @file OUT, @FileName, 1 
    SELECT @Result = @hr 
    IF @hr = 0 
    BEGIN 
    EXEC @hr = sp_OAMethod @file, 'Close' 
    EXEC @hr = sp_OADestroy @file 
    END 
    EXEC @hr = sp_OADestroy @fso 
    Return @Result 
    End

    --Now, in another session, start a backup (in my case, of a database called AccountStatus):

    backup database AccountStatusto disk = 'c:\sql_backups\AccStat.bak' with init

    --example of use:

    ugly:
    if (select dbo.fn_fileavailable ('c:\sql_backups\AccStat.bak')) <> 0
    begin
    WAITFOR DELAY '00:01:00'
    goto ugly
    end
    else
    print 'file available'

    Interesting at the least 🙂

    *##* *##* *##* *##*

    Chaos, Disorder and Panic ... my work is done here!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply