transaction log full
randynovick
I'm not a DBA, but I play one on TV. I have to get a sybase
ASE installation up and running for test purposes. We don't
use ASE in any production context, we test against it to
make sure that our customers can harmoniously use our
product with ASE.
I'm able to create our test database (in master, I execute
"create database <db_name>"). I'm able to run some SQL to
create the necessary tables. Once, however, I start using
the db, I get a "The trandaction log for database <db_name>
is almost full. Your transaction is being suspended until
more space is made available." ... or something like that.
I've run a "sp_dboption <db_name>, trunc, true", but this
does not make the problem go away. I confess that I know
very little about database administration, so there might be
some obvious setting somewhere that I don't know about.
Running a "sp_helpdb" shows me that my database is 16KB in
size, which is what I recall setting the page size to at
installation time. Is this part of the constriction? If so,
how do I enlarge it after the fact?
Many thanks,
-- Randy
MJ
You can either break up your transactions into smaller ones, or alter your
database, increasing the size of the transaction log.
alter database <dbname>
log on <log device> = nn
(where nn is the size added, in MB)
I would also recommend that this database NOT be put on the master device.
Rather, create two new devices - one to hold the data segment, and the other
for the log segment.
MJ
<Randy Novick> wrote in message news:40ed8d04.60a...@sybase.com...
Mark A. Parsons
>
> I'm able to create our test database (in master, I execute
> "create database <db_name>"). I'm able to run some SQL to
> create the necessary tables. Once, however, I start using
> the db, I get a "The trandaction log for database <db_name>
> is almost full. Your transaction is being suspended until
> more space is made available." ... or something like that.
You'll need to issue one of the following to clear out the log:
dump tran <db_name> to "some dump file or tape device"
(assumes you want to keep the tran log file)
(won't do any good if you have a long-running
open transaction that keeps the tran log from
being truncated/cleared after the dump)
or
dump tran <db_name> with truncate_only
(assumes you just want to clear the tran log)
or
dump tran <db_name> with no_log
(assumes not enough space to use 'truncate_only' option)
(to be used as a last option)
> I've run a "sp_dboption <db_name>, trunc, true", but this
> does not make the problem go away. I confess that I know
> very little about database administration, so there might be
> some obvious setting somewhere that I don't know about.
'trunc log on chkpt' will only take effect in the future ... it does *NOT*
clear the current log (see above 'dump tran' commands).
Assuming you get the tran log cleared up *THEN* 'trunc log on chkpt' should
kick in in the future thus keeping your tran log trimmed.
A couple caveats ...
1 - it *is* possible for the tran log to fill up even with 'trunc log on
chkpt' enabled; this could occur if there's a looooonggg running/open
transaction that keeps the tran log from being truncated (in a nutshell ...
the tran log can only be cleared of completed/closed transactions; the tran
log cannot be cleared past the beginning of the first open tran it finds);
if this situation occurs you'll need to a) kill the offending query and
issue one of the 'dump tran' commands, b) bounce (shutdown and restart) the
server thus doing the same thing as 'a' or c) extend the transaction log
for your database (this just allows more room for the tran log but it does
*NOT* do anything about that open tran, ie, your log will just fill up
again at some point).
2 - you do *NOT* want to have 'trunc log on chkpt' enabled in production;
'trunc log on chkpt' will throw away important tran log data/information
that your client would need should they have to recover their database;
'course, this implies that you'll want to track down and eliminate that
rogue/long-running/open transaction so that the tran log does not fill up
(and, of course, issue a 'dump tran <db_name> to "dump file"'at regular
intervals so as to a) keep a backup of the tran log and b) clear the tran
log).
> Running a "sp_helpdb" shows me that my database is 16KB in
> size, which is what I recall setting the page size to at
> installation time. Is this part of the constriction? If so,
> how do I enlarge it after the fact?
You sure it showed 16KB as the size of your database? It should show
something (at least in the low) MB range. If in doubt, please post the
entire contents of:
select @@version
go
sp_helpdb <db_name>
go
--
Mark A. Parsons
Iron Horse, Inc.
iron_...@NOSPAM.compuserve.com
donmillion
> in production;
Nit-picking, I admit it, but as usual there are exceptions.
I've had production databases where it made no sense to save
transaction logs, because you couldn't do anything with them
anyway. So, while this is the usual advice, and is most
often correct, there are plenty of exceptions.
Randy Novick
As far as the suggestions go, I did the following:
In SQL Advantage, I used master and tried to execute "dump tran dwe to
"c:\dwe_tran_log.txt"
(my database name is "dwe")
I got this in response:
Server Message: Number 4205, Severity 16
Line 1:
Syslogs does not exist in its own segment in database 'dwe' with segmap '7'
with logical start page number of '0'. You cannot use DUMP TRANSACTION in
this case, use DUMP DATABASE instead.
Clearly, I've missed something. I get the same message when I use dwe before
executing the command. I don't really understand what the error means,
frankly, though I'm guessing that "does nto exist in its own segment" points
to MJ's suggestion to use seperate devices away from master. Am I off-base
on this?
Anyhoo, after that one failed, I tried "dump tran dwe with truncate_only",
which went through without any complaint. I was able to then drop and
re-create all of my tables and indexes, but when I tried to drop them all
and re-create them again, I got the same "The transaction log in database
dwe is almost full. Your transaction is being suspended until space is made
available in the log." error.
When I've gotten into the "transaction suspended" quandary in the past, I've
flipped the db service and I'm then able to reconnect and try other stuff.
It sounds like this is one way to get unstuck, anyway, if not the best way.
So, then I reconnected and using dwe again, I issued a "dump tran dwe with
no_log", which was accepted. I then tried to dump and re-create the tables
and indexes a couple times, but ran into my old friend the "transaction
suspended" error once again.
This was awfully frustrating, especially because I know that it's a PEBKAC
(Problem Exists Between Keyboard and Chair) problem.
*sigh*
So I ditched SQL Advantage in favor of trying to set things up along the
lines of MJ's "seperate devices" advice, using Sybase Central. First, I
nuked the old, useless dwe database. I then created two new database
devices, one named dwe_tran and one named dwe_dev. I set their respective
sizes to 100MB each. I then added two new databases: one named dwe (100MB in
size, for data, and associated with the dwe_dev device) and one named
dwe_transactions (also 100MB in size, for transaction logs, and associated
with the dwe_tran device). When adding the dwe_transactions database, I
received an error saying that I needed to specify a nonlogging device as
well. I created another device for this purpose (dwe_nonlog, 50MB in size)
and included it, seeing that it would not accept the dwe_dev device I had
already created.
So, after I got all those devices and dbs created, I went to the database
properties->options for the dwe database and checked "alow nulls by default"
('cause we need this), "trunc log on chkpt" to have the logged clipped when
a checkpoint gets fired and there are 50+ rows present (p705 of the SAG
enlighted me as to what this does), and "ddl in tran", because we do issue a
couple of the commands listed on p702 of the SAG within transactions. The
caveat wrt not using trunc log on chkpt in production environments is
well-taken, as I could see that you'd want more than 50 rows (okay, a lot
more) to make sure you could successfully rollback when needed. This db is
for testing only, and if things go bad or get corrupted, we can just nuke
the tables and start fresh again.
So I started running our app against the dwe database and all appears just
fine. No errors or anything. Whee! While I like the fact that I can
successfully set Sybase ASE up for a test environment, I get the feeling
that I'm not using seperate devices for transaction log and data. I didn't
see anyplace where I could specify that. Can someone enlighten me as to what
I missed? In addition, what were my obvious screw-ups as I was setting
things up with Sybase Central? (I'm sure there were a few.)
Thanks very much to the community for inspiring me to try a few different
things.
-- Randy
"Mark A. Parsons" <iron_...@NOSPAM.compuserve.com> wrote in message
news:40EDD517...@NOSPAM.compuserve.com...
MJ
You're getting closer!
When you create a database, it automatically comes with 3 segments: system
(for system tables), data (for all that good user data), and log (for the
transaction log)
Typically, you want to create your database to cover a data device and log
device. In your case, that would be something like:
create database db_dev
on dwe_dev = 100
log on dwe_tran = 100
This will put your system and data segments on dwe_dev, and the log segment
on dwe_tran.
(As a starting point, Sybase generally recommends creating your transaction
log at about 25% the size of your data...More if you've got really active
data, less if it's mostly reads)
After that, you can either set 'trunc log on chkpt' to true as you've been
doing, or start regularly backing up the transaction log...depending on
whether the incremental backups are useful to you.
I really recommend that you give the Sybase manuals a shot. They are quite
excellent. For now, you probably want to give the System Admin Guide a
quick read.
MJ
"Randy Novick" <randy....@dralasoft.com> wrote in message
news:40eef580@forums-2-dub...
Mark A. Parsons
Fair enough! ;-)
The original OP isn't a DBA so I figured I'd spout the 'normal' reply ...
then let him come back with a reason to have it enabled in production.
'course, this opens up a whole 'nother round of posts re: backups and
disaster recovery planning, etc., etc., etc.
Mark A. Parsons
>
> Clearly, I've missed something. I get the same message when I use dwe before
> executing the command. I don't really understand what the error means,
> frankly, though I'm guessing that "does nto exist in its own segment" points
> to MJ's suggestion to use seperate devices away from master. Am I off-base
> on this?
yes.
> Anyhoo, after that one failed, I tried "dump tran dwe with truncate_only",
> which went through without any complaint. I was able to then drop and
> re-create all of my tables and indexes, but when I tried to drop them all
> and re-create them again, I got the same "The transaction log in database
> dwe is almost full. Your transaction is being suspended until space is made
> available in the log." error.
I would suggest you throw some 'dump tran ... truncate_only' commands in
between your various 'create' commands.
Assuming you have 'trunc log on chkpt' enabled it's possible that the
'create' commands are filling up the log faster than it can be truncated.
Then again, with data and log sitting on the same segment you have to
realize that each 'create' statement uses up space that can't be used by
the log. In other words each 'create' statement creates some log entries
while at the same time reducing the amount of space available to the log
... this can cause the log to fill up a *lot* faster than would be normal
if the log were on it's own segment.
> When I've gotten into the "transaction suspended" quandary in the past, I've
> flipped the db service and I'm then able to reconnect and try other stuff.
> It sounds like this is one way to get unstuck, anyway, if not the best way.
Not sure what you mean by 'flip the db service'?
If you mean shutdown/startup the service ... ok, you're bouncing the
server. This in and of itself won't clear your transaction log though ...
so something else is going on???
> So, then I reconnected and using dwe again, I issued a "dump tran dwe with
> no_log", which was accepted. I then tried to dump and re-create the tables
> and indexes a couple times, but ran into my old friend the "transaction
> suspended" error once again.
Yep, back to the same problem ... after 'flipping the db service' you still
have to do something to clear the log (hence the success of the 'dump tran
... with no_truncate/no_log') ... but then you fill the log up again. See
rec above.
'course, something else to think about ... if you're using up all of the
available space just creating your tables ... are you going to have enough
space to actually add data to the database, (ie, the data will take up more
room thus cutting down further on space available for logging, not to
mention the log space used up by the data insertions/updates/deletes).
Point being ... maybe you need to increase the size of your database ...
which, in turn, may help alleviate some of the problems with running out of
tran log until you can dump/truncate it.
Did you ever enable 'trunc log on chkpt'?
> So I ditched SQL Advantage in favor of trying to set things up along the
> lines of MJ's "seperate devices" advice, using Sybase Central. First, I
> nuked the old, useless dwe database. I then created two new database
> devices, one named dwe_tran and one named dwe_dev. I set their respective
> sizes to 100MB each. I then added two new databases: one named dwe (100MB in
> size, for data, and associated with the dwe_dev device) and one named
> dwe_transactions (also 100MB in size, for transaction logs, and associated
> with the dwe_tran device). When adding the dwe_transactions database, I
> received an error saying that I needed to specify a nonlogging device as
> well. I created another device for this purpose (dwe_nonlog, 50MB in size)
> and included it, seeing that it would not accept the dwe_dev device I had
> already created.
Sybase devices are defined at the dataserver level.
Databases are defined on top of devices, ie, space for the database is
taken from the devices.
A database may be made up of fragments from several devices.
Within a database logical segments can be mapped to these device fragments
... with the 'default', 'system' and 'logsegment' being 3 system segments
that are automatically created for each database. (In other words, each
database has it's own transaction log ... which consists of space allocated
*within* said database.)
The DBA can then move the system segments around to the various device
fragments; the DBA can also create new user-defined (logical) segments to
be mapped to various device fragments.
User-defined objects (tables and indices) can then be built on top of the
various (logical) segments.
Anyhoo ... in your situation you it sounds like you only need one database
(dwe) which could be created as follows:
disk init ... dwe_dev ...
go
disk init ... dwe_tran ...
go
create database dwe
on dwe_dev = <size1> -- data
log on dwe_tran = <size2> -- tran log
go
This will create a database called 'dwe' with the 'default' and 'system'
segments mapped to the dwe_dev fragment and 'logsegment' mapped to the
dwe_tran segment.
<size1> and <size2> will be limited to the amount of 'free' space still
available on the dwe_dev and dwe_tran, respectively.
Once you get this database setup you may want to compare the various
databases (eg, master and dwe) ... take a gander at the output from
'sp_helpdevice' and 'sp_helpdb <dbname>' ... see if you can match the
various devices with where/how they're used in the various databases (and
the associated segments). Alternatively you're welcome to match the rows
from master..sysdatabases, master..sysdevices, master..sysusages and
<dbname>..syssegments.
> So I started running our app against the dwe database and all appears just
> fine. No errors or anything. Whee! While I like the fact that I can
> successfully set Sybase ASE up for a test environment, I get the feeling
> that I'm not using seperate devices for transaction log and data. I didn't
> see anyplace where I could specify that. Can someone enlighten me as to what
> I missed? In addition, what were my obvious screw-ups as I was setting
> things up with Sybase Central? (I'm sure there were a few.)
To verify that your log is on it's own segment take a gander at 'sp_helpdb
dwe' ... you're looking for the 'device_fragments' section ... each
fragment should state under 'usage' that the fragment is used for 'data
only' or 'log only'. If you have any 'usage' entries that state 'data and
log' then you have not put the log on it's own device.
Alternatively you can look at the output from 'sp_helpdb' (no arguments).
This will give you a list of all databases, their sizes, when they were
created and their current status. If a given database's status listing
includes 'mixed log and data' ... then said database does *not* have a
separate log.
If in doubt, please post the entire contents of:
sp_helpdb <dbname>
go
> Thanks very much to the community for inspiring me to try a few different
> things.
*snicker* You only have about 6,800 more pages of ASE documentation to try
out! We'll keep ya busy as long as you want! :-P
Mark A. Parsons
Mark A. Parsons
>
> Clearly, I've missed something. I get the same message when I use dwe before
> executing the command. I don't really understand what the error means,
> frankly, though I'm guessing that "does nto exist in its own segment" points
> to MJ's suggestion to use seperate devices away from master. Am I off-base
> on this?
yes.
> Anyhoo, after that one failed, I tried "dump tran dwe with truncate_only",
> which went through without any complaint. I was able to then drop and
> re-create all of my tables and indexes, but when I tried to drop them all
> and re-create them again, I got the same "The transaction log in database
> dwe is almost full. Your transaction is being suspended until space is made
> available in the log." error.
I would suggest you throw some 'dump tran ... truncate_only' commands in
between your various 'create' commands.
Assuming you have 'trunc log on chkpt' enabled it's possible that the
'create' commands are filling up the log faster than it can be truncated.
Then again, with data and log sitting on the same segment you have to
realize that each 'create' statement uses up space that can't be used by
the log. In other words each 'create' statement creates some log entries
while at the same time reducing the amount of space available to the log
... this can cause the log to fill up a *lot* faster than would be normal
if the log were on it's own segment.
> When I've gotten into the "transaction suspended" quandary in the past, I've
> flipped the db service and I'm then able to reconnect and try other stuff.
> It sounds like this is one way to get unstuck, anyway, if not the best way.
Not sure what you mean by 'flip the db service'?
If you mean shutdown/startup the service ... ok, you're bouncing the
server. This in and of itself won't clear your transaction log though ...
so something else is going on???
> So, then I reconnected and using dwe again, I issued a "dump tran dwe with
> no_log", which was accepted. I then tried to dump and re-create the tables
> and indexes a couple times, but ran into my old friend the "transaction
> suspended" error once again.
Yep, back to the same problem ... after 'flipping the db service' you still
have to do something to clear the log (hence the success of the 'dump tran
... with no_truncate/no_log') ... but then you fill the log up again. See
rec above.
'course, something else to think about ... if you're using up all of the
available space just creating your tables ... are you going to have enough
space to actually add data to the database, (ie, the data will take up more
room thus cutting down further on space available for logging, not to
mention the log space used up by the data insertions/updates/deletes).
Point being ... maybe you need to increase the size of your database ...
which, in turn, may help alleviate some of the problems with running out of
tran log until you can dump/truncate it.
Did you ever enable 'trunc log on chkpt'?
> So I ditched SQL Advantage in favor of trying to set things up along the
> lines of MJ's "seperate devices" advice, using Sybase Central. First, I
> nuked the old, useless dwe database. I then created two new database
> devices, one named dwe_tran and one named dwe_dev. I set their respective
> sizes to 100MB each. I then added two new databases: one named dwe (100MB in
> size, for data, and associated with the dwe_dev device) and one named
> dwe_transactions (also 100MB in size, for transaction logs, and associated
> with the dwe_tran device). When adding the dwe_transactions database, I
> received an error saying that I needed to specify a nonlogging device as
> well. I created another device for this purpose (dwe_nonlog, 50MB in size)
> and included it, seeing that it would not accept the dwe_dev device I had
> already created.
Sybase devices are defined at the dataserver level.
Databases are defined on top of devices, ie, space for the database is
taken from the devices.
A database may be made up of fragments from several devices.
Within a database logical segments can be mapped to these device fragments
... with the 'default', 'system' and 'logsegment' being 3 system segments
that are automatically created for each database. (In other words, each
database has it's own transaction log ... which consists of space allocated
*within* said database.)
The DBA can then move the system segments around to the various device
fragments; the DBA can also create new user-defined (logical) segments to
be mapped to various device fragments.
User-defined objects (tables and indices) can then be built on top of the
various (logical) segments.
Anyhoo ... in your situation it sounds like you only need one database
(dwe) which could be created as follows:
disk init ... dwe_dev ...
go
disk init ... dwe_tran ...
go
create database dwe
on dwe_dev = <size1> -- data
log on dwe_tran = <size2> -- tran log
go
This will create a database called 'dwe' with the 'default' and 'system'
segments mapped to the dwe_dev fragment and 'logsegment' mapped to the
dwe_tran fragment.
<size1> and <size2> will be limited to the amount of 'free' space still
available on the dwe_dev and dwe_tran, respectively.
Once you get this database setup you may want to compare the various
databases (eg, master and dwe) ... take a gander at the output from
'sp_helpdevice' and 'sp_helpdb <dbname>' ... see if you can match the
various devices with where/how they're used in the various databases (and
the associated segments). Alternatively you're welcome to match the rows
from master..sysdatabases, master..sysdevices, master..sysusages and
<dbname>..syssegments.
> So I started running our app against the dwe database and all appears just
> fine. No errors or anything. Whee! While I like the fact that I can
> successfully set Sybase ASE up for a test environment, I get the feeling
> that I'm not using seperate devices for transaction log and data. I didn't
> see anyplace where I could specify that. Can someone enlighten me as to what
> I missed? In addition, what were my obvious screw-ups as I was setting
> things up with Sybase Central? (I'm sure there were a few.)
To verify that your log is on it's own segment take a gander at 'sp_helpdb
dwe' ... you're looking for the 'device_fragments' section ... each
fragment should state under 'usage' that the fragment is used for 'data
only' or 'log only'. If you have any 'usage' entries that state 'data and
log' then you have not put the log on it's own device.
Alternatively you can look at the output from 'sp_helpdb' (no arguments).
This will give you a list of all databases, their sizes, when they were
created and their current status. If a given database's status listing
includes 'mixed log and data' ... then said database does *not* have a
separate log.
If in doubt, please post the entire contents of:
sp_helpdb <dbname>
go
> Thanks very much to the community for inspiring me to try a few different
> things.
*snicker* You only have about 6,800 more pages of ASE documentation to try
out! We'll keep ya busy as long as you want! :-P
--