![]() ![]() Sp_dboption tempdb,"abort tran on log full",true One of the database options that can be set with the sp_dboption stored procedure can be used to prevent this. In a real-life situation this scenario could cause significant problems for users. This extends tempdb and makes it possible to drop table #a and to truncate the transaction log. Besides a reboot of the server you would have no other option than to increase tempdb ( alter database)with just a bit more space for the logsegment. In fact it's so full that the table #a cannot be dropped because this action must also be logged in the transaction log of tempdb. When you execute the lct_admin function the session is killed but tempdb is still full. ![]() Select lct_admin("abort",0,2) - 2 is dbid for tempdb. In the format lct_admin("abort",0,) you can kill sessions that are waiting on a log suspend. Your first task is to kill off the process that causes the problem, but how can you know which process to kill if you even can't run an sp_who? This problem can be solved with the lct_admin function. You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command. When you log on to ASE to resolve this problem and you run an sp_who, you will get Failed to allocate disk space for a work table in database 'tempdb'. At that point the ASE errorlog will show messages like 1 task(s) are sleeping waiting for space to become available in the log segment for database tempdb. This cycle repeats itself a number of times until tempdb is filled up to the point that even the transaction log cannot be truncated anymore. Then the log gets automatically truncated by ASE, allowing for more rows to be inserted in the table until tempdb is full again. Running the script populates table #a and the transaction log at the same time, until tempdb is full. The following script illustrates that this can lead to nasty problems. This type of segmentation, no matter the size of the database, has an undefined space for the transaction log the only limitation is the available size within the database. Tempdb is basically just another database within the server and has three segments ( What's a segment): 'system' for system tables like sysobjects and syscolumns, 'default' to store objects such as tables and 'logsegment' for the transaction log (syslogs table). 4 Prevention of a full segment for data. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |