Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





Sybase » Administration » Transaction » Segment

Transaction Log Filled Up - Log Suspended : What To Do

     




This session shows how to debug a transaction log full situation, by checking the
actual query that is causing the problem.




Some transactions can actually prevent the log dump from occuring .. the only recourse
may be:
dump transaction dbname with no_log









1> sp_helpsegment logsegment
2> go



|segment|name |status
|-------|------------------------------|------
| 2|logsegment | 0
|device |size
|------------------------------|----------------------
|data08 |500.0MB
|free_pages
|-----------
| 915
|table_name |index_name |indid
|------------------------------|------------------------------|------
|syslogs |syslogs | 0
|total_size |total_pages |free_pages |used_pages |reserved_pages
|-----------------|---------------|---------------|---------------|---------------
|500.0MB |64000 |915 |63071 |14
(return status = 0)



1> select @@servername
2> go



|
|------------------------------
|PTEST01
> select spid,cmd,blocked,dbid from sysprocesses where cmd like "LOG SUSPEND"
2> go
|spid |cmd |blocked|dbid
|------|----------------|-------|------
| 548|LOG SUSPEND | 0| 7

(1 row affected)
1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> dbcc sqltext(548)
2> go
SQL Text:

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> sp_showplan 548,null,null,null
2> go

QUERY PLAN FOR STATEMENT 1 (at line 1).



STEP 1
The type of query is BULK INSERT.
TO TABLE
p_dbtest1..reporting_data
Using I/O Size 8 Kbytes for data pages.

(return status = 0)

1> sp_lock 548
2> go

The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users.

|fid |spid |loid |locktype |table_id |page |row |dbname |class |context

|------|------|-----------|----------------------------|-----------|-----------|------|---------------|------------------------------|----------------------------

| 0| 548| 1096|Ex_table | 1378100919| 0| 0|p_dbtest1 |Non Cursor Lock |

(1 row affected)
(return status = 0










Sybase : Related Topics


Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.