Wednesday, July 16, 2008

Locked file detection

As previously stated here the Blog, I spend quite some time doing automation of SQL Server maintenance.
One challenge has been to detect if a given SQL Server was currently locking a specific device file (backup file). To solve this problem I came up with this small function:

function TestFileLock {
    ## Attempts to open a file and trap the resulting error if the file is already open/locked
    param ([string]$filePath )
    $filelocked = $false
    $fileInfo = New-Object System.IO.FileInfo $filePath
    trap {
        Set-Variable -name locked -value $true -scope 1
    $fileStream = $fileInfo.Open( [System.IO.FileMode]::OpenOrCreate, [System.IO.FileAccess]::ReadWrite, [System.IO.FileShare]::None )
    if ($fileStream) {
    $obj = New-Object Object
    $obj | Add-Member Noteproperty FilePath -value $filePath
    $obj | Add-Member Noteproperty IsLocked -value $filelocked

PS> TestFileLock "c:\pagefile.sys"
PS> TestFileLock "\\Server01\backup\MyInstance05\devDATABASE_00A1.bak"


Anonymous said...

A more common way of doing a test is to have the cmdlet/script return either true or false. For an example of that see Test-Path. If you were to return the same type of output, it would be more intuitive.

Jakob Bindslet said...

I agree that a simple true/false might be more intuitive, however the way the function is currently written it is easier to use it against a whole range of files and get a list of Filenames & IsLocked status back.

Anonymous said...

Fix :

Set-Variable -name Filelocked -value $true -scope 1