ASE : 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