How to handle Log full issue :-
1. select * from syslogshold
go
2. sp_who '100'
go
3. sp_showplan '100' , null,null,null
go
4. dbcc sqltext(100)
go
5.kill 100
6. re-run query in small batch to prevent log full issue.
1. select * from syslogshold
go
2. sp_who '100'
go
3. sp_showplan '100' , null,null,null
go
4. dbcc sqltext(100)
go
5.kill 100
6. re-run query in small batch to prevent log full issue.
Step 1:- Log in the dataserever using isql
ReplyDeleteisql -U -S -w999
Step 2:- Check the long running trans as below
If there is any row in syslogshold, see the starttime column and current time, If there is big diff, it means you have log runningg trans in dataserver and you need to esclate to APP team.
cmd:
use master
go
select * from syslogshold
go
select getdate()
go
Example:
1> select * from syslogshold
2> go
dbid reserved spid page xactid masterxactid starttime
name xloid
------ ----------- ------ ----------- -------------- -------------- --------------------------
------------------------------------------------------------------- -----------
4 0 627 43859290 0x029d3d5a0005 0x000000000000 Jun 12 2011 9:34AM
$REORG REBUILD dmASSET_HIST ID=621764747 1254
2 0 314 332908 0x0005146c000a 0x000000000000 Jun 12 2011 3:02PM
$ins 628
(2 rows affected)
1> select getdate()
2> go
--------------------------
Jun 12 2011 4:00PM
(1 row affected)
Step 3:- Now we need to find the detail of blocking spid as below
cmd:
use master
go
select * from sysprocesses where spid=
go
sp_who ""
go
dbcc traceon(3604)
go
dbcc sqltext()
go
sp_showplan ,null,null,null
go
sp_lock
go
example : (I have colllected the detail for 843 , you need to find out detail for all the blocking spids)
1> select * from sysprocesses where spid=843
2> go
spid kpid enginenum status suid hostname program_name hostprocess cmd cpu
physical_io memusage blocked dbid uid gid
tran_name time_blocked network_pktsz fid
execlass priority affinity id stmtnum linenum origsuid
block_xloid clientname clienthostname clientapplname sys_id
ses_id loggedindatetime ipaddr
------ ----------- ----------- ------------ ----------- ---------- ---------------- ----------- ---------------- -----------
----------- ----------- ------- ------ ----------- -----------
---------------------------------------------------------------- ------------ ------------- ------
------------------------------ ---------- ------------------------------ ----------- ----------- ----------- -----------
----------- ------------------------------ ------------------------------ ------------------------------ ------
----------- -------------------------- ---------------
843 283443444 4 lock sleep 546 nyggmgrotc RESETREP 23822 INSERT 3
0 22 627 4 405 16390
$user_transaction 15143 512 0
EC2 HIGH ANYENGINE 637764804 5 439 NULL
0 0
0 Jun 12 2011 11:24AM 10.152.115.100
(1 row affected)
to be continued ......
Delete1> sp_who "843"
ReplyDelete2> go
fid spid status loginame origname hostname blk_spid
dbname cmd block_xloid
------ ------ ------------ ------------------------------ ------------------------------ ---------- --------
------------------------------ ---------------- -----------
0 843 lock sleep lcprusr lcprusr nyggmgrotc 627
summitdb INSERT 0
(1 row affected)
(return status = 0)
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(843)
2> go
SQL Text: 320110714CONV_AVG
AUTO CARRY
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> sp_showplan 843,null,null,null
2> go
QUERY PLAN FOR STATEMENT 5 (at line 439).
STEP 1
The type of query is INSERT.
The update mode is direct.
TO TABLE
dmASSET_HIST
Using I/O Size 2 Kbytes for data pages.
(return status = 0)
1> sp_lock 843
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 843 1686 Ex_intent 589764633 0 0 summitdb
Non Cursor Lock
0 843 1686 Ex_row 589764633 199169 0 summitdb
Non Cursor Lock
0 843 1686 Ex_intent 635720842 0 0 summitdb
Non Cursor Lock
0 843 1686 Ex_row 635720842 22607248 0 summitdb
Non Cursor Lock
0 843 1686 Ex_intent 667720956 0 0 summitdb
Non Cursor Lock
0 843 1686 Ex_row 667720956 46143296 0 summitdb
Non Cursor Lock
0 843 1686 Ex_intent 1076770418 0 0 summitdb
Non Cursor Lock
0 843 1686 Ex_row-blk 1076770418 13586806 2 summitdb
Non Cursor Lock
0 843 1686 Ex_row 1076770418 13807873 0 summitdb
Non Cursor Lock
0 843 1686 Ex_intent 1108770532 0 0 summitdb
Non Cursor Lock
0 843 1686 Ex_row 1108770532 18465042 2 summitdb
Non Cursor Lock
0 843 1686 Ex_row 1108770532 33477240 0 summitdb
Non Cursor Lock
0 843 1686 Ex_intent-blk 1732772755 0 0 summitdb
Non Cursor Lock
0 843 1686 Ex_row 1732772755 13475329 0 summitdb
Non Cursor Lock
0 843 1686 Ex_intent 1764772869 0 0 summitdb
Non Cursor Lock
0 843 1686 Ex_row 1764772869 19673089 0 summitdb
Non Cursor Lock
(16 rows affected)
(return status = 0)
Step 4:- With above detail you need to send the mail to APP Team and if required,then kill it.