Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Philippines
    Posts
    3

    Question Unanswered: Can't allocate space

    Novice MS SQL User

    Dell PowerEdge 2300
    NT 4 server SP4
    MS SQL Server 6.5

    i'm trying to upload 250,000 records

    Error : 1105, Severity: 17, State: 2
    Can't allocate space for object 'Syslogs' in database 'clearance_data' because the 'logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

    what i'm planning to do is to add a new hard drive to the server, but HOW?

    please help

    see attached files (jpeg zip) for
    Disk Administration
    SQL Server
    Devices
    Databases
    Available space

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Can't allocate space

    (Couldn't read your attached files), but:

    You might consider trying some of the following:

    Temporarily set the DB 'clearance_data' to truncate on checkpoint until you have loaded your data.

    Add or increase the size of the existing transaction log for DB 'clearance_data'.

    I assume you are loading 250,000 records into the the DB 'clearance_data'? (If so, try loading fewer records at any one time e.g., load 10 25,000 record data subsets, instead of all 250,000 at once.)

    To clear the error 'logsegment' segment is full try: Dump Log 'clearance_data' with Truncate_Only

    Finally, RE: " ...add a new hard drive to the server, but HOW?"
    What kind of (hardware?) issues are you having with the new hard drive?

  3. #3
    Join Date
    Dec 2002
    Location
    Philippines
    Posts
    3

    Question Re: Can't allocate space

    Thanks for the quick response

    Finally, RE: " ...add a new hard drive to the server, but HOW?"
    What kind of (hardware?) issues are you having with the new hard drive?

    please download again the attached file, I used WinZip 8.0

    NT Server Setup

    4338MB DISK 0 C: - 1083MB Mirror Set E: - 3240 Stripe Set
    4338MB DISK 1 C: - 1083MB Mirror Set E: - 3240 Stripe set
    4299MB DISK 2 F: - 4299MB Primary Partition


    SQL Server Setup
    Database devices Data Size Available
    clearance_device 5500MB 1290MB
    DATA2
    master
    MSDBData
    MSDBLog
    Databases
    clearance_data
    master
    model
    msdb
    pubs
    tempdb

    if I add, lets say 18GB HD
    this will become

    18GB DISK 3

    can I still add/expand the clearance_device to 18GB more
    is this doable?

    Thanks again in adavce
    Attached Files Attached Files

  4. #4
    Join Date
    Oct 2002
    Posts
    369

    Re: Can't allocate space

    Q1 please download again the attached file, I used WinZip 8.0
    Q2 can I still add/expand the clearance_device to 18GB more
    is this doable?
    A1 This time the error (with the image zip) was "images.zip: Either multipart or corrupt ZIP archive."

    A2 You may not actually be able to use 100% of the new volume's free space, (even implementing raw partion devices), but generally, yes.

    Note: It appears likely that you have separate devices for both DB Log and DB Data (if so, a good thing). In your particular situation, (if you must perform a single large operation in one step); you must provide the DB LOG sufficient space to complete the operation. Obviously, there must also be sufficient room for the actual DB data itself, as well; so it is possible you may also need to provide additional free data device space to successfully complete your load operation. The reason for mentioning this (in this manner) is that Sql Server 6.x allowed the co-mingling of Log / Data on the same device (generally not a desirable configuration, for a number of reasons).

  5. #5
    Join Date
    Dec 2002
    Location
    Philippines
    Posts
    3

    Red face

    I tried downloading the file... yes it was corrupted
    I'll try to attached it again
    . +-----------------+
    4338MB DISK 0 C: - 1083MB Mirror Set | E: - 3240 Stripe Set
    4338MB DISK 1 C: - 1083MB Mirror Set | E: - 3240 Stripe Set
    . +-----------------+

    on those stripe set resides the clearance_data

    A2 You may not actually be able to use 100% of the new volume's free space, (even implementing raw partion devices), but generally, yes.

    assuming I have installed a new device

    how can I configure MS SQL 6.5 to use the new device for clearance_data for expansion

    Thanks again

    please try to download the new attached file
    Attached Files Attached Files
    Last edited by oJe; 12-08-02 at 22:47.

  6. #6
    Join Date
    Oct 2002
    Posts
    369

    Question

    RE:
    Q1 Assuming I have installed a new device, how can I configure MS SQL 6.5 to use the new device for clearance_data for expansion?
    Q2 Please try to download the new attached file.
    A1 I'm not sure exactly what you are asking (I had assumed hardware mirroring, are you using Sql Server DB mirroring? If so I believe you must break the mirror before altering the DB, then remirror subsequently if possible.). If not, as I recall, you may do everything necessary from Sql EM once the new drive volume is available (visible) to the OS (unless maybe, you have done some intersting things with segments). You could use tsql in isqlw as well, for example: (not tested)

    /* Find out the maximum VDevNo (XX): Then XXX = XX + 1 (where XX is the first digit of low byte column data) */

    Use Master
    Go

    exec sp_HelpDevice
    Go
    Select * From SysDevices
    Go

    /* G: = 18GB DISK 3*/
    /* The following tsql would use half of G for Log and half G for Data: */

    Disk INIT
    Name = 'Clearance_Data_DataDevG_001',
    PhysName = 'G:\MsSql\Data\Clearance_Data_DataDevG001.dat'
    VDevNo = XXX,
    Size = 4608000 /* 4608000 x 2KB = 9GB */
    Go

    Disk INIT
    Name = 'Clearance_Data_LogDevG_001',
    PhysName = 'G:\MsSql\Data\Clearance_Data_LogDevG001.log'
    VDevNo = XXX,
    Size = 4608000 /* 4608000 x 2KB = 9GB */
    Go

    /* The following tsql should then Alter the clearance_data DB to use about half of each of the log and data device files created on G. : */

    Alter DataBase clearance_data
    On Clearance_Data_DataDevG_001 = 4500,
    Log On Clearance_Data_LogDevG_001 = 4500
    Go

    /* - NOTE - */
    /* If the above statement fails, then the Log On clause may not be valid to use with Alter Table. In that case, use the following tsql which should work: */

    Alter DataBase clearance_data
    On Clearance_Data_DataDevG_001 = 4500,
    Clearance_Data_LogDevG_001 = 4500
    Exec sp_LogDevice 'clearance_data', 'Clearance_Data_LogDevG_001'
    Go

    A2 The latest file has a zero byte size (downloaded).
    Last edited by DBA; 12-10-02 at 00:17.

Posting Permissions

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