tempdb database or the temporary database in Sybase ASE is the place where Sybase Server while trying to execute the user queries creates all sort of worktables and temporary tables etc. The tempdb database hence is one of the most important databases on a Sybase server. But have you ever asked as what should be the tempdb size which I should keep to keep my Sybase server running happily ever after?
But before that one may ask as why is the size really our concern? To answer this we need to check the exact purpose of tempdb database. As already said the tempdb database is used to do all the intermediary things required by our Sybase server like creating worktables, sorting results and temporary table creation etc, so if all these operations are done in the tempdb database then we must be very sure that the tempdb database doesn't run out of space as there might be lots of users accessing the tempdb database.
So, with reason justified should we start calculating the tempdb size which will serve our purpose? Well, the major problem with this question is that there is no hard and fast rule to determine the tempdb size in Sybase to be very sure that it serves our purpose. Tempdb size is usually determined based on the size and the purpose of the applications using the Sybase databases. If we have very short queries for our applications then we need not require a very large tempdb space, however if we have very complex and lengthy queries that gets fired every now and than, then we need to have a large tempdb size to avoid running out of space. Usually it is recommended that the tempdb size should be approximately 20% - 25% of the largest database supported on a Sybase server. But as already said this figure will not hold true in every case. So, the next time you think of creating a tempdb you know that you need to experiment a lot to choose the optimistic approach.
But before that one may ask as why is the size really our concern? To answer this we need to check the exact purpose of tempdb database. As already said the tempdb database is used to do all the intermediary things required by our Sybase server like creating worktables, sorting results and temporary table creation etc, so if all these operations are done in the tempdb database then we must be very sure that the tempdb database doesn't run out of space as there might be lots of users accessing the tempdb database.
So, with reason justified should we start calculating the tempdb size which will serve our purpose? Well, the major problem with this question is that there is no hard and fast rule to determine the tempdb size in Sybase to be very sure that it serves our purpose. Tempdb size is usually determined based on the size and the purpose of the applications using the Sybase databases. If we have very short queries for our applications then we need not require a very large tempdb space, however if we have very complex and lengthy queries that gets fired every now and than, then we need to have a large tempdb size to avoid running out of space. Usually it is recommended that the tempdb size should be approximately 20% - 25% of the largest database supported on a Sybase server. But as already said this figure will not hold true in every case. So, the next time you think of creating a tempdb you know that you need to experiment a lot to choose the optimistic approach.
I am new to sybase. I am working on a development server. Where I configured 2 separate disks like 1 for data an another for log. loaded the data,after creating the database.
ReplyDeleteMy tempdb is with master.(I think so ,not sure,since I haven't created any separate disk for that).
I got 4GB of data. Whenever I am trying to access from application, every thing works fine but certain links giving error messages as "Can't allocate space for object 'temp work table' in database 'tempdb' because 'system' segment is full/has no free extents sybase"
(1)I did Dump tran < db_name > with truncate_only
(2)Increase the log disk memory space.
But the thing here is for a particular link only I am seeing this error.After seeing this error, and I click another link in the application ,I am seeing the data and normally the application fine.
Not sure what is this problem in database is and why it is happening for only for certain link.
I would like to request you to help , why I am seeing the same error even I did above steps.
Tahnks a lot
Viswanadh
Hi Viswanadh, there seems to be some confusion regarding the way your database server has been established. I am assuming you are working on a Sybase ASE database server and hence in this case you would be having three segments namely system, default and log. Looking at the error I am thinking that your two seperate disks might be actually serving the same three segments namely system, default and log. Probably your single link must be executing a large query which might be performing aggregates, group by etc which would be creating work tables for deriving the result.
DeleteThis clearly shows that you might be running out of either tempdb space or default segment space. Hence, try running alter database command to increase first the size of default segment else tune your log size for your requirement. Also, do check of long running transaction in your tempdb by querying syslogshold table and reconfirm if your tempdb has been set for trun log on checkpoint. I believe this should help.
Hello. My own personal rule of thumb when it comes to database tempfiles is make it as big as the largest table. Since this tempdatabase performs sorts as one of its tasks, it should be able to accommodate any table in your database in order to carry out a sort.
ReplyDeleteThis comment has been removed by the author.
DeleteI think, what Sansrival said makes sense. Adding to that i would say, the size of the tempdb should be the size of the largest table plus 10 % overhead.
ReplyDelete