SQL Server Performance Forum – Threads Archive
Useing T-SQL to determine file in use
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? Panic, Chaos, Disorder … my work here is done –unknownhttp://www.sqlservercentral.com/scripts/contributions/1028.asp
—————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka
lol – thanks again, Dinesh (I’m sure you recognize the question <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> )<br /><br />Panic, Chaos, Disorder … my work here is done –unknown
Ever wondered if a file was available to copy, for example a SQL backup file? Here#%92s something interesting, although I#%92m not 100% certain of the risks it may create, especially in a clustered environment. I’d appreciate thoughts/ comments… (btw – the function comes from<a target="_blank" href=http://www.sqlservercentral.com/scripts/contributions/1028.asp>http://www.sqlservercentral.com/scripts/contributions/1028.asp</a> – it is not my work).<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Create Function fn_FileAvailable <br />(@FileName VarChar(255)) <br />Returns Int <br />As <br />Begin <br />/* <br />Return Values <br />= 0 FileSystemObject can open the file, so deemed "available" <br />= -1 means FileSystemObject could not be created <br />= -2146828235 (0x800A0035) File Not Found <br /> = -2146828218 (0x800A0046) Permission Denied (in use) <br />…etc <br />*/ <br />DECLARE @fso int, @hr int, @file int, @Result Int <br />EXEC @hr = sp_OACreate ‘Scripting.FileSystemObject’, @fso OUT <br />IF @hr <> 0 BEGIN <br />EXEC sp_OAGetErrorInfo @fso — Error Processing<br />Return -1 <br />END <br />EXEC @hr = sp_OAMethod @fso, ‘OpenTextFile’, @file OUT, @FileName, 1 <br />SELECT @Result = @hr <br />IF @hr = 0 BEGIN <br />EXEC @hr = sp_OAMethod @file, ‘Close’ <br />EXEC @hr = sp_OADestroy @file <br />END <br />EXEC @hr = sp_OADestroy @fso <br />Return @Result <br />End <br /></font id="code"></pre id="code"><br /><br />–Now, in another session, start a backup (in my case, of a database called AccountStatus):<br /><pre id="code"><font face="courier" size="2" id="code"><br />backup database AccountStatus<br />to disk = ‘c:sql_backupsAccStat.bak’ with init<br /></font id="code"></pre id="code"><br /><br />–example of use:<br /><pre id="code"><font face="courier" size="2" id="code"><br />ugly:<br />if (select dbo.fn_fileavailable (‘c:sql_backupsAccStat.bak’)) <> 0<br />begin<br />WAITFOR DELAY ’00:01:00′<br />goto ugly<br />end<br />else<br />print ‘file available'<br /></font id="code"></pre id="code"><br /><br />Interesting at the least <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br /><br />Panic, Chaos, Disorder … my work here is done –unknown
]]>