Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: Can't expand SQL6.5 database

    I have a SQL 6.5 database where I need to expand the size
    of a database. The database device has been expanded but I
    seem to be missing something when it comes to expanding
    the database.

    The database in question is named edidata. When I enter
    the Expand Databases screen I see my database named
    edidata. At the top of the screen are two drop down boxes
    one lableled Data Device and the other is Log Device. In
    the the Data Device drop down I see only

    none
    new
    Database x
    Database y

    (But not edidata)

    I have several hundred Meg available on the device and
    additionally lots of Disk space not yet allocated.

    When I pull up the Log Device drop down this database device name
    does appear. If I attempt to expand any of the other 3 databases on this server I'm able to see this database device name in the drop down list.

    In case it matters I do have both a Data base
    and Log contained within this Database device.

    Any help would be greatly appreciated.

    Thank you
    ..

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Question Re: Can't expand SQL6.5 database

    Having mixed use devices always complicates things. (If you have run into the point where the Log starts, I think you may be out of luck for additional data space on that device for that DB. Other DBs would still be able to use free space beyond the Log boundary within the device.)

    The tsql to add 500MB (if available) should be something like:

    Alter DataBase edidata
    On Edidata_DeviceName = 500
    Go

    (You would have to use the correct names, of course.)
    Last edited by DBA; 12-10-02 at 00:03.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Lightbulb Re: Can't expand SQL6.5 database

    You might consider creating another device called something like EdiDataLogDevice, (designating it for Log use only), and moving the existing EdiData Log there (from its current location).

  4. #4
    Join Date
    Dec 2002
    Posts
    5
    As you can tell I don't do a great deal of DBA work with MSSQL. I know how to create the Database device but I'm not sure of how to move the Log to the new device. Does anyone have any pointers?

  5. #5
    Join Date
    Oct 2002
    Posts
    369
    Originally posted by Dale Marthaller
    As you can tell I don't do a great deal of DBA work with MSSQL. I know how to create the Database device but I'm not sure of how to move the Log to the new device.

    Q1 Does anyone have any pointers?
    A1 I rarely do anything with 6.x Sql Servers anymore, (other than a rare upgrade), but I'd probably start by testing the following on a dev server / dev DB (should work):

    /* I guess I'd do something Like this (#0 Full Backup Dump!) */
    /* 1 Create the extra device (use an appropriate size, not 2048): */
    Disk Init
    Name = 'EdiDataLogDev',
    PhysName = 'c:\EdiDataLogDev.Log',
    VDevNo = 8,
    Size = 2048
    Go
    /* 2 Make sure the extra device is there: */
    exec sp_helpdevice
    /* 3 Check out the existing DBs, existing DB Log / LogSpace: */
    exec sp_HelpDB
    exec sp_HelpDB EdiData
    exec sp_HelpLog
    dbcc Sqlperf(LogSpace)
    Go
    /* 4 Add the new device to the DB: */
    Alter DataBase EdiData On EdiDataLogDev
    exec sp_HelpDB EdiData
    /* 5 Mark the new device on the DB for Log Only: */
    exec sp_LogDevice EdiData, EdiDataMixDev
    Go
    /* 6 Verify the changes, carefully note the following: */
    exec sp_helpdevice
    exec sp_HelpDB EdiData
    /* 7 Then create some dummy tables, populate them in EdiData, dump the log etc. (flush the log from the old device): */
    Dump EdiData With Truncate_Only
    /* 8 Verify the changes, carefully note the following:*/
    exec sp_helpdevice
    exec sp_HelpDB EdiData
    /* 9 EdiDataLogDev should be Log Only, and the original Dev should be Data Only */
    Last edited by DBA; 12-10-02 at 02:22.

  6. #6
    Join Date
    Dec 2002
    Posts
    5
    Thank you I will tackle this today.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •