Knowledge in sybase tempdb space
Sybase Tempdb 100% Full
Sybase Tempdb 100% Full tempdb is 100% full ? Ideally tempdb capacity should be sized enough to handle largest and parallel transactions. However this can’t be achieved all the time. So one of the unlikely situation occurs when tempdb becomes 100% full. If users and administrators are using the same tempdb, commands such as sp_who don’t work for the sa leaving people wondering what to do next. Errors when tempdb is full As the sa: Failed to allocate disk space for a work table in database 'tempdb'. 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. sp_who shows: The transaction log in database tempdb is almost full. Your transaction is being suspended until space is made available in the log. select * from syslogshold then shows: Failed to allocate disk space for a work table in database 'tempdb'. 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. Use a query that works when tempdb is full select “spid=” + convert( varchar(3), SPID) + ” login=” + suser_name(ServerUserID) + ” SQLText=” + SQLText from master..monProcessSQLText 1> select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText 2> go --------------------------------------------------------------------------------------------------------- spid=16 login=sa SQLText=sp_who spid=17 login=fsmith SQLText=insert junk select * from raw_test..junk spid=20 login=sa SQLText=select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText (3 rows affected) 1> Option 1 kill offending spid In the test example above we can identify that spid 17 has the offending query. kill 17 go During a simple test it took over 4 minutes for the tempdb to become free so a degree of patience is needed. Option 2 kill off all spids using tempdb If it was impossible to determine what the offending spid was then to kill off all spids using tempdb (dbid=2): select lct_admin(“abort”,0,2) 1> select lct_admin("abort",0,2) 2> go lct_admin(abort): Process 25 waiting on log-suspend state on database 'tempdb is being aborted. lct_admin(abort): Process 16 waiting on log-suspend state on database 'tempdb is being aborted. Option 3 Increasing tempdb size If the process is important and needs to finish then increasing the size of the tempdb database is the way to go. It might be that tempdb is undersized for the application. use master go 1> disk init name="tempdb_tmp12", size="400M", 2> physname="/home/sybase/SYB157/data/tempdb_tmp12.dat" , dsync="false", directio="false" 1> alter database tempdb on tempdb_tmp12=400 2> go Extending database by 102400 pages (400.0 megabytes) on disk tempdb_tmp12 Choices of options Hastily increasing tempdb might mean that all of the corresponding test, development and disaster recovery environments need to be changed to match, possibly due to one stray user query which shouldn’t have been there in the first place. It is for this reason that sites are sometimes reluctant to increase database sizes without good planning. Checking progress of a rollback The following query doesn’t always give meaningful values ( it can show negative values when tempdb full) but does give an indication of progress: select (lct_admin(“logsegment_freepages”,2) – 1.0 * lct_admin(“reserved_for_rollbacks”,2)) /1048576.*@@maxpagesize For the above query “,2” signifies tempdb dbid = 2 This query shows 6.32 Mb free on database 16 which is in log suspend: 1> select (lct_admin("logsegment_freepages",16) - 1.0 * lct_admin("reserved_for_rollbacks",16)) /1048576.*@@maxpagesize 2> go ------------------------------------ 6.320312320 (1 row affected) 1> Prevention: Using abort tran on log full Depending on the site and the application setting tempdb to “abort tran on log full” prevents getting into a position where tempdb is unavailable to all users. 1> use master 2> go 1> sp_dboption tempdb, "abort", true 2> go Warning: Attempting to change database options for a temporary database. Database options must be kept consistent across all temporary databases. Database option 'abort tran on log full' turned ON for database 'tempdb'. Running CHECKPOINT on database 'tempdb' for option 'abort tran on log full' to take effect. (return status = 0) 1> use tempdb 2> go 1> checkpoint 2> go abort tran on log full tested ok for Sybase ASE 15.7 with a mixed data and log tempdb. Creating another tempdb and binding the users to it If users and administrators use different a tempdb from each other, then system stored procedures can still be used by “sa” logins if a user or batch process fills up tempdb. 1> create temporary database tempdb_raw on RAW_DATA002=350 2> go 1> sp_tempdb 'bind','lg','fsmith','db','tempdb_raw' 2> go (return status = 0) tempdb is full – what to do now ? One of the worst things about tempdb being full is that it can prevent both users and administrators from using the system. If users and administrators are using the same tempdb, commands such as sp_who don’t work for the sa leaving people wondering what to do next. Errors when tempdb is full As the sa: Failed to allocate disk space for a work table in database 'tempdb'. 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. sp_who shows: The transaction log in database tempdb is almost full. Your transaction is being suspended until space is made available in the log. select * from syslogshold then shows: Failed to allocate disk space for a work table in database 'tempdb'. 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. Use a query that works when tempdb is full select “spid=” + convert( varchar(3), SPID) + ” login=” + suser_name(ServerUserID) + ” SQLText=” + SQLText from master..monProcessSQLText 1> select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText 2> go --------------------------------------------------------------------------------------------------------- spid=16 login=sa SQLText=sp_who spid=17 login=fsmith SQLText=insert junk select * from raw_test..junk spid=20 login=sa SQLText=select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText (3 rows affected) 1> Option 1 kill offending spid In the test example above we can identify that spid 17 has the offending query. kill 17 go During a simple test it took over 4 minutes for the tempdb to become free so a degree of patience is needed. Option 2 kill off all spids using tempdb If it was impossible to determine what the offending spid was then to kill off all spids using tempdb (dbid=2): select lct_admin(“abort”,0,2) 1> select lct_admin("abort",0,2) 2> go lct_admin(abort): Process 25 waiting on log-suspend state on database 'tempdb is being aborted. lct_admin(abort): Process 16 waiting on log-suspend state on database 'tempdb is being aborted. Option 3 Increasing tempdb size If the process is important and needs to finish then increasing the size of the tempdb database is the way to go. It might be that tempdb is undersized for the application. use master go 1> disk init name="tempdb_tmp12", size="400M", 2> physname="/home/sybase/SYB157/data/tempdb_tmp12.dat" , dsync="false", directio="false" 1> alter database tempdb on tempdb_tmp12=400 2> go Extending database by 102400 pages (400.0 megabytes) on disk tempdb_tmp12 Choices of options Hastily increasing tempdb might mean that all of the corresponding test, development and disaster recovery environments need to be changed to match, possibly due to one stray user query which shouldn’t have been there in the first place. It is for this reason that sites are sometimes reluctant to increase database sizes without good planning. Checking progress of a rollback The following query doesn’t always give meaningful values ( it can show negative values when tempdb full) but does give an indication of progress: select (lct_admin(“logsegment_freepages”,2) – 1.0 * lct_admin(“reserved_for_rollbacks”,2)) /1048576.*@@maxpagesize For the above query “,2” signifies tempdb dbid = 2 This query shows 6.32 Mb free on database 16 which is in log suspend: 1> select (lct_admin("logsegment_freepages",16) - 1.0 * lct_admin("reserved_for_rollbacks",16)) /1048576.*@@maxpagesize 2> go ------------------------------------ 6.320312320 (1 row affected) 1> Prevention: Using abort tran on log full Depending on the site and the application setting tempdb to “abort tran on log full” prevents getting into a position where tempdb is unavailable to all users. 1> use master 2> go 1> sp_dboption tempdb, "abort", true 2> go Warning: Attempting to change database options for a temporary database. Database options must be kept consistent across all temporary databases. Database option 'abort tran on log full' turned ON for database 'tempdb'. Running CHECKPOINT on database 'tempdb' for option 'abort tran on log full' to take effect. (return status = 0) 1> use tempdb 2> go 1> checkpoint 2> go abort tran on log full tested ok for Sybase ASE 15.7 with a mixed data and log tempdb. Creating another tempdb and binding the users to it If users and administrators use different a tempdb from each other, then system stored procedures can still be used by “sa” logins if a user or batch process fills up tempdb. 1> create temporary database tempdb_raw on RAW_DATA002=350 2> go 1> sp_tempdb 'bind','lg','fsmith','db','tempdb_raw' 2> go (return status = 0)
Nupur Sharma
INTRODUCTION OF DATABASE MANAGEMENT SYSTEM (First semester notes) Chapter-1 (Part-1) Makhanlal chaturvedi national University,Bhopal
(Part-1) IN This, There is a chapter SEVEN of DATABASE MANAGEMENT SYSTEM Subject Part-1 named INTRODUCTION OF DATABASE MANAGEMENT SYSTEM Makhanlal Chaturvedi national journalism and communication University, Bhopal. There is a very important note oF DATABASE MANAGEMENT SYSTEM For BCA first semester Students. Share with your friends and help them to learn Fundamental of Computers. There are Five subjects in BCA first semester .
Piyush Gupta
OPERATION AREA OF DATABASE (First semester notes) Chapter-1 (Part-2) Makhanlal chaturvedi national University,Bhopal
(Part-2) IN This, There is a chapter SEVEN of DATABASE MANAGEMENT SYSTEM Subject Part-2 named INTRODUCTION OF DATABASE MANAGEMENT SYSTEM Makhanlal Chaturvedi national journalism and communication University, Bhopal. There is a very important note oF DATABASE MANAGEMENT SYSTEM For BCA first semester Students. Share with your friends and help them to learn Fundamental of Computers. There are Five subjects in BCA first semester .
E-R DATA MODEL DBMS (First semester notes) Chapter-1 (Part-10) Makhanlal chaturvedi national University,Bhopal
(Part-10) IN This, There is a chapter SEVEN of DATABASE MANAGEMENT SYSTEM Subject Part-10 named INTRODUCTION OF DATABASE MANAGEMENT SYSTEM Makhanlal Chaturvedi national journalism and communication University, Bhopal. There is a very important note oF DATABASE MANAGEMENT SYSTEM For BCA first semester Students. Share with your friends and help them to learn Fundamental of Computers. There are Five subjects in BCA first semester .