Post reply

Database Location query

  • January 31, 2005 at 6:27 am

    #85364

    Morning All

    When I run the following TSQL

    select * from sysdatabases

    it give me the location for my master Database as

    d:\mssql\MSSQL\data\master.mdf

    However, if I check the Db properties through Enterprise Manager, it reports it as

    d:\sql\MSSQL\data\master.mdf

    Both files exist, have virtually the same timestamp, and are the same size.  How do I establsh which is correct?

    Thanks

    Gary

  • January 31, 2005 at 6:38 am

    #539720

    I'd make sure you have fully refreshed EM, it's notorious for being out of date if you don't refresh manually.

    If you open up properties for your server and click on "startup paramaters" you'll see the location of your master database files ( please don't change anything - at all in here -)

    rename the files you think are the wrong ones , ultimate test is to stop and restart the service if it fails you got it wrong.

    The only question I might ask is do you have more than one instance on your box ?

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • January 31, 2005 at 6:43 am

    #539722

    Got it, thanks

    This came about as I am trying to standardise locations of files (data and log) between our two Servers, and this is the first discrepancy I noticed

    Thanks

  • January 31, 2005 at 3:57 pm

    #539828

    One way to check if a database file (master or otherwise) is in use: while SQL Server is running and the database is attached, try and rename the file. (Windows Explorer; select file; F2; add an "x" to the start of the file name; hit enter.)

    If SQL Server is using the file, Windows will not allow you to change it's name (or the name of any folder it is nested under...) If SQL isn't using the file, you can change it. (If SQL isn't but something else is, you've got other problems...)

       Philip

     

  • February 1, 2005 at 6:13 am

    #539905

    If you run a SELECT * from MASTER..SYSFILES that should give you the location where the files are actually running from.


    Shamless self promotion - read my blog http://sirsql.net

  • February 1, 2005 at 9:38 am

    #539967

    Since you are in the process of 'standardizing' you probably want the same information for all databases on a given server. sysfiles exists in all databases and contains the file information for that database only. In the master database you can query sysaltfiles (it has the file information for all databases in that instance) with something like:

    select 'database_name'=db_name(dbid), name, filename from sysaltfiles

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

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