Post reply

T-SQL basic Query

  • September 14, 2006 at 8:58 am

    #116690

    Firstly, let me apologise for the question i'm about to ask!  It's soooo basic i'm sure it will be answered quickly.

    I have a SQL 2000 Server with approximately 40+ databases on it and it's in a right mess.  I would like to find out the physical location of both the .mdf & .ldf files as they would appear to have been installed on different drives.  So, i'm trying to tidy things up... the question is rather than input the following script for each server in Query Analyzer is there a quick way of doing this for all databases?

    use db1

    go

    sp_helpfile

    go

    use db2

    go

    sp_helpfile

    go

    etc, etc

    Thanks for any help you can give.

     

  • September 14, 2006 at 9:09 am

    #660434

    Mark

    This uses the undocumented (and unsupported) stored procedure sp_MSforeachdb:

    sp_MSforeachdb

    'use ?

    exec sp_helpfile'

    John

  • September 14, 2006 at 11:33 am

    #660480

    But it is pretty handy and it works on 2005 too

    By the way the syntax can be rewritten as :

    exec sp_MSforeachdb '?.dbo.sp_helpfile'


    * Noel

  • September 14, 2006 at 8:24 pm

    #660564

    I've found the even Microsoft uses the "undocumented" features   Works for me!

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 15, 2006 at 2:28 am

    #660612

    Thanks guys for all you help... this is exactly what I wanted.  Many Thanks!

    As a side issue how did you find out about these 'undocumented' features?

    Cheers,

    Mark

  • September 15, 2006 at 2:55 am

    #660628

    Mark

    By reading the posts on this site, I think!

    John

  • September 15, 2006 at 9:09 am

    #660747

    Mark,

    You say "I would like to find out the physical location of both the .mdf & .ldf files as tehy would appear to have been installed on different drives.  So, i'm trying to tidy things up..." 

    I would caution you and tell you that they most likely should be on different drives.  If possible you want to segregate your log and data files for both performance and recoverability.

    Just my two cents...

    Good luck.

  • September 15, 2006 at 9:31 am

    #660754

    Hi Okafor,

    I appreciate where you're coming from.  As a standard SQL server build we tend to split the data onto one mirrored pair and logs/backups onto another mirrored pair.  I was in a bit of a rush typing the initial forum post and made some obvious mistakes!  Although the majority of databases and log files are spilt onto different drives, the server in question has a number of databases and log files on the same drive (which goes against our standard SQL build).

    I just wanted to bring this particular SQL installation in line with our other servers.

    Sorry for the confusion.

     

     

  • September 15, 2006 at 9:34 am

    #660759

    Great to hear!  So many times I hear of data, logs and even backups on the same drives.  Makes me cringe!!! 

Viewing 9 posts - 1 through 8 (of 8 total)

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