Post reply

Data Files In Limbo

  • June 23, 2005 at 8:56 am

    #87923

    I have just been clearing out our test SQL server here. There were hundreds of databases on there. I have stumbled across an MDF and LDF file that give me no indication as to what database they are attached to in Enterprise Manager, therefore I am unable to delete them. Is there any way of finding out what database they are attached to?

    Thanks

    Kindest Regards,

    Steve Williams

  • June 23, 2005 at 9:00 am

    #567942

    Select * from master.dbo.SysFiles where

    name = 'FileNameGoesHere'

    --and FileName = 'FullPathRelativeToServer'

  • June 23, 2005 at 9:05 am

    #567945

    Like this you mean?

    Select * from master.dbo.SysFiles where name = 'Main1st_Bruce1_Data.MDF' and FileName = 'H:\samf\bruce\Main1st_Bruce1_Data.MDF'

    Kindest Regards,

    Steve Williams

  • June 23, 2005 at 9:06 am

    #567946

    any of the 2 conditions will do... I'd go with the path for precision or the shortname for lazyness .

  • June 23, 2005 at 9:08 am

    #567947

    hehe cheers for that.

    aaaarrrggghhh!!! Problem is, this doesn't find anything, but tells me its in use when I try and manually delete the MDF file.

    Kindest Regards,

    Steve Williams

  • June 23, 2005 at 9:11 am

    #567949

    Do you have multiple installations on the same pc?

    Have you tried both the lazy and precise solutions?

  • June 23, 2005 at 9:12 am

    #567950

    Only one installation. I've tried both methods.

    Kindest Regards,

    Steve Williams

  • June 23, 2005 at 9:23 am

    #567959

    Win XP?

  • June 23, 2005 at 9:34 am

    #567971

    Just found my mistake :

    Select FileName from master.dbo.SysDatabases

    SysFile is a virtual table that contains the fileinfo for the current database only.

  • June 23, 2005 at 9:41 am

    #567974

    STAR!!! I got it. Cheers for that mate.

    Kindest Regards,

    Steve Williams

  • June 23, 2005 at 9:58 am

    #567989

    Got something even better now (works nice if all the dbs are in the same directory).

    --list the database data files found in the directory that are not listed in master :

    Create table #temp

    ( output varchar (2000) null

    )

    GO

    insert into #temp

    exec master.dbo.xp_cmdshell 'DIR "E:\Program Files\Microsoft SQL Server\MSSQL\Data"'

    Select dtDirectory.FileName FROM

    (SELECT SUBSTRING(output, 40, LEN(OUTPUT) - 38) as FileName from #temp where RIGHT(output, 4) = '.MDF') dtDirectory

    LEFT OUTER JOIN

    (Select RIGHT(FileName, charindex('\', Reverse(FileName), 1) - 1) as FileName from master.dbo.SysDatabases) dtMaster

    on dtDirectory.FileName = dtMaster.FileName

    where dtMaster.FileName is null

    DROP TABLE #temp

Viewing 11 posts - 1 through 10 (of 10 total)

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