After Work
The best club in Chicago
for Chicago Singles is
Highlife Adventures!
Kyoto Sushi
in Chicago, on Lincoln Ave.
Moody's Pub
in Chicago, has great burgers
Skylark
in Chicago, on Halsted .. excellent beer selection!
|
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
|
|
|
|
