expanding databases in SQL 6.5

[Jeffrey Kilpatrick]

I have a SQL 6.5 database that we parse some data into everyday using
an access program. All this was devises and setup by a programmer that
I can't get in contact with anymore and it has actually run for about
five years without a hickup! But just a few days ago our parsing
program just stops dead before completing and I did get this error
message.

"exportaLLdataToSQLifnoerror(): number 3146 Description- odbd- call
failed. [Micorsoft][odbc SQL Server Driver] [sql server] Can't
allocate space for object 'syslogs' in database 'newpdatasql' 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. (#1105)"

I have looked at the database in SQL referred to and I notice that it
says there is 200 MEg allocated for the log size and 250 meg for the
data size and that in both categories there is "0" space available.
When I select "EXPAND" on this page it takes me to a screen which has
a graphical presentation with a bar chart showing the available space
in red and the used space in blue on each of what it refers to as
'database devices". There are 10 items shown--
Temp_DB available space 30 used 30, Newplog 200 available 200 used,
NewpDev 250 available 250 used, MSDBlog available 2.00 used 2.00,
MSDBData 6 available 6 used, Master 50 available 50 used, Data_log
2.00 available 2.00 used, Dale_data 12 available 12 "free space" (
this one says free space instead of used?), Contact_log 25 available
15 used, Contact_dev 100 available 75 used.

So I can see on the first screen that there was 450 meg allocated to
this database called newpdatasql and that all 450 is being used. And
when I go to the next screen that shows the devices in this database
that many of them show that their available space is completely used.

The fix is probably to expand the available space for the database. On
the second graphical screen that shows the bar chart there is an
button called "expand now" that is greyed out except when you select
the different devices in a drop-down box at the top of the screen.
There are two drop down boxes -- one titled "data devices and one
titled log devices. THe only time the Expand now button is not greyed
out is when I select "Dale_Data, "Contact_log", or "Contact_dev". I
did select the "contact_Dev" device clicked on expand and it seemed to
I guess expand this device to a fully used level as after I did this
it says that the space available is still 100 meg ( see comments
above) but now instead of 75 used it says 100 used! Progress or what?
I guess I could do this for the other devices as well. One issue for
me is whether when the space available for a device is the same as the
space used does this mean that it is at maximum capacity in terms of
data or only that the space available on the disk for this device is
completely being made available as potential storage space?

Interestingly the original error message mentions "syslogs" and
"logsegment" neither are available as devices or are named in the
screens relating to database expansion.

Any help would be greatly appreciated.

My tollfree number is 866-957 1081.

Jeffrey Kilpatrick

[Erland Sommarskog]

[posted and mailed, please reply in news]

Jeffrey Kilpatrick (je**@newportsecurities.com) writes:

"exportaLLdataToSQLifnoerror(): number 3146 Description- odbd- call
failed. [Micorsoft][odbc SQL Server Driver] [sql server] Can't
allocate space for object 'syslogs' in database 'newpdatasql' 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. (#1105)"
On SQL 6.5 the transaction is a table, syslogs. So what happened is
that you run out of log space.

This could happen because the data import actually needs 200 MB of
log space, so your regular dumping of the transaction log does not
help.

But it could also you have never taken any transaction log backups,
and the database is not set to "truncate log on checkpoint". If the
case is that you don't care about T-log backups on this database, just
issue "DUMP TRANSACTION db WITH NO_LOG". And then execute:

sp_dboption db, "trunc", true

which sets on "truncate log on checkpoint" which is the same as simple
recovery in SQL2000.

Assuming now that you actually have to make the log space larger (which
my guess is that you don't), then I'll try to explain the oddities of 6.5.
When I select "EXPAND" on this page it takes me to a screen which has
a graphical presentation with a bar chart showing the available space
in red and the used space in blue on each of what it refers to as
'database devices". There are 10 items shown--

In 6.5 all databases are on devices. A device may host a single database,
or it may host many. And a database can be spread out on several devices.
It is a confusing scheme in the NT world, but when Sybase devised this
they had Unix boxes in mind, where the devices typically would reside on
raw disk.

To expand your log, you first need to find some available space on
a device or create some new device space. While you add 25 MB to
that contact_dev, space, expanding the existing device or adding a
new device is a better way to go.

Whether you actually can extend a device I don't remember off-hand. But
as long as you have the disk space, you can always add a new device and
expand the log onto that one.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp