Pardon for newbie questions:

when we usually get tempdb full and what is the primary remedy and
what is the long term solution?

when we usually get trans log full and what is the primary remedy and
what is the long term solution?

what is the best configuration for tempdb and trans log.

soalvajavab1 (AT) yahoo (DOT) com wrote:
If tempdb is filling up, the usual remedy is to add more space to
tempdb. Tempdb is the scratch space that Sybase uses for a lot of things
including work tables, sorting tables, user-defined temp tables (the
#tables), etc. Certain maintenance jobs, like update statistics will
also create work tables. You won't always see tables in the sysobjects
table in tempdb for these cases either.

If you are using the default tempdb size, then that is definitely too
small. On most systems today, I would think a tempdb of several GB is a
good minimum, but I have worked on systems with tempdbs in the 10's or
even 100 GB and size, given a 1TB+ size db.

If you don't feel that tempdb should be running out of space, then it is
mostly like a bad query that is being run, maybe doing an order by or
distinct on a query with a cartesian product.

For tran log issues, the issue is again usually to add more tran log
space. There are lots of rules of thumb as to how big the tran log needs
to be, but it really all depends on the nature of the application.
Remember, the tran long needs to be bigger than the single largest
transaction, and in theory, needs to be bigger then the total size of
the largest set of concurrent transactions.

The usual cases for tran log issues are bad queries that are doing
updates or deletes affecting more rows than expected. Trying to delete a
whole table with 1000's to 10000's of rows will do it, since that is a
single transaction. Need to look at sp_who output to see which session
is causing the log to fill up. Also look at the master..syslogshold
table, which will show you the spid with the oldest open transaction.
That typically is the session that is causing the problem.

If the database is not set for "truncate log on checkpoint" with
sp_dboption, then you need to make sure that the tran log is being
dumped reguarly, otherwise it will also fill up.

If you are using Replication Server, another culprit is that the change
rate of the database is after than RepServer can keep up. This typically
happens when Rep Server is on the same box as ASE, and the box is
running out of CPU. Rep Server then tends to get starved for CPU, and is
not able to drain the tran log fast enough. There are also some tuning
options for sp_config_rep_agent to improve that performance a bit.

If none of these help, and you are still stuck, then either call Tech
Support for some guidance. Note, they will try to help, but if it is a
lack of knowledge issue or training, or a general performance issue that
cannot be tied to a bug or technical issue, you might be shifted to
Sybase Professional Services to have someone come in an evaluate the
system and provide feedback and help.

soalvajavab1 (AT) yahoo (DOT) com wrote:
If tempdb is filling up, the usual remedy is to add more space to
tempdb. Tempdb is the scratch space that Sybase uses for a lot of things
including work tables, sorting tables, user-defined temp tables (the
#tables), etc. Certain maintenance jobs, like update statistics will
also create work tables. You won't always see tables in the sysobjects
table in tempdb for these cases either.

If you are using the default tempdb size, then that is definitely too
small. On most systems today, I would think a tempdb of several GB is a
good minimum, but I have worked on systems with tempdbs in the 10's or
even 100 GB and size, given a 1TB+ size db.

If you don't feel that tempdb should be running out of space, then it is
mostly like a bad query that is being run, maybe doing an order by or
distinct on a query with a cartesian product.

For tran log issues, the issue is again usually to add more tran log
space. There are lots of rules of thumb as to how big the tran log needs
to be, but it really all depends on the nature of the application.
Remember, the tran long needs to be bigger than the single largest
transaction, and in theory, needs to be bigger then the total size of
the largest set of concurrent transactions.

The usual cases for tran log issues are bad queries that are doing
updates or deletes affecting more rows than expected. Trying to delete a
whole table with 1000's to 10000's of rows will do it, since that is a
single transaction. Need to look at sp_who output to see which session
is causing the log to fill up. Also look at the master..syslogshold
table, which will show you the spid with the oldest open transaction.
That typically is the session that is causing the problem.

If the database is not set for "truncate log on checkpoint" with
sp_dboption, then you need to make sure that the tran log is being
dumped reguarly, otherwise it will also fill up.

If you are using Replication Server, another culprit is that the change
rate of the database is after than RepServer can keep up. This typically
happens when Rep Server is on the same box as ASE, and the box is
running out of CPU. Rep Server then tends to get starved for CPU, and is
not able to drain the tran log fast enough. There are also some tuning
options for sp_config_rep_agent to improve that performance a bit.

If none of these help, and you are still stuck, then either call Tech
Support for some guidance. Note, they will try to help, but if it is a
lack of knowledge issue or training, or a general performance issue that
cannot be tied to a bug or technical issue, you might be shifted to
Sybase Professional Services to have someone come in an evaluate the
system and provide feedback and help.

soalvajavab1 (AT) yahoo (DOT) com wrote:
If tempdb is filling up, the usual remedy is to add more space to
tempdb. Tempdb is the scratch space that Sybase uses for a lot of things
including work tables, sorting tables, user-defined temp tables (the
#tables), etc. Certain maintenance jobs, like update statistics will
also create work tables. You won't always see tables in the sysobjects
table in tempdb for these cases either.

If you are using the default tempdb size, then that is definitely too
small. On most systems today, I would think a tempdb of several GB is a
good minimum, but I have worked on systems with tempdbs in the 10's or
even 100 GB and size, given a 1TB+ size db.

If you don't feel that tempdb should be running out of space, then it is
mostly like a bad query that is being run, maybe doing an order by or
distinct on a query with a cartesian product.

For tran log issues, the issue is again usually to add more tran log
space. There are lots of rules of thumb as to how big the tran log needs
to be, but it really all depends on the nature of the application.
Remember, the tran long needs to be bigger than the single largest
transaction, and in theory, needs to be bigger then the total size of
the largest set of concurrent transactions.

The usual cases for tran log issues are bad queries that are doing
updates or deletes affecting more rows than expected. Trying to delete a
whole table with 1000's to 10000's of rows will do it, since that is a
single transaction. Need to look at sp_who output to see which session
is causing the log to fill up. Also look at the master..syslogshold
table, which will show you the spid with the oldest open transaction.
That typically is the session that is causing the problem.

If the database is not set for "truncate log on checkpoint" with
sp_dboption, then you need to make sure that the tran log is being
dumped reguarly, otherwise it will also fill up.

If you are using Replication Server, another culprit is that the change
rate of the database is after than RepServer can keep up. This typically
happens when Rep Server is on the same box as ASE, and the box is
running out of CPU. Rep Server then tends to get starved for CPU, and is
not able to drain the tran log fast enough. There are also some tuning
options for sp_config_rep_agent to improve that performance a bit.

If none of these help, and you are still stuck, then either call Tech
Support for some guidance. Note, they will try to help, but if it is a
lack of knowledge issue or training, or a general performance issue that
cannot be tied to a bug or technical issue, you might be shifted to
Sybase Professional Services to have someone come in an evaluate the
system and provide feedback and help.

soalvajavab1 (AT) yahoo (DOT) com wrote:
If tempdb is filling up, the usual remedy is to add more space to
tempdb. Tempdb is the scratch space that Sybase uses for a lot of things
including work tables, sorting tables, user-defined temp tables (the
#tables), etc. Certain maintenance jobs, like update statistics will
also create work tables. You won't always see tables in the sysobjects
table in tempdb for these cases either.

If you are using the default tempdb size, then that is definitely too
small. On most systems today, I would think a tempdb of several GB is a
good minimum, but I have worked on systems with tempdbs in the 10's or
even 100 GB and size, given a 1TB+ size db.

If you don't feel that tempdb should be running out of space, then it is
mostly like a bad query that is being run, maybe doing an order by or
distinct on a query with a cartesian product.

For tran log issues, the issue is again usually to add more tran log
space. There are lots of rules of thumb as to how big the tran log needs
to be, but it really all depends on the nature of the application.
Remember, the tran long needs to be bigger than the single largest
transaction, and in theory, needs to be bigger then the total size of
the largest set of concurrent transactions.

The usual cases for tran log issues are bad queries that are doing
updates or deletes affecting more rows than expected. Trying to delete a
whole table with 1000's to 10000's of rows will do it, since that is a
single transaction. Need to look at sp_who output to see which session
is causing the log to fill up. Also look at the master..syslogshold
table, which will show you the spid with the oldest open transaction.
That typically is the session that is causing the problem.

If the database is not set for "truncate log on checkpoint" with
sp_dboption, then you need to make sure that the tran log is being
dumped reguarly, otherwise it will also fill up.

If you are using Replication Server, another culprit is that the change
rate of the database is after than RepServer can keep up. This typically
happens when Rep Server is on the same box as ASE, and the box is
running out of CPU. Rep Server then tends to get starved for CPU, and is
not able to drain the tran log fast enough. There are also some tuning
options for sp_config_rep_agent to improve that performance a bit.

If none of these help, and you are still stuck, then either call Tech
Support for some guidance. Note, they will try to help, but if it is a
lack of knowledge issue or training, or a general performance issue that
cannot be tied to a bug or technical issue, you might be shifted to
Sybase Professional Services to have someone come in an evaluate the
system and provide feedback and help.