April 9, 2014 at 7:49 am
Transaction Log Details
The transaction log is around 94 GB (with 7 GB free) and is being backed up every 20 minutes. The backup sizes run the gamut between 6 MB - 300 MB (usually). I don't understand why the log size is so large. The recovery interval is set at 0, automatic configuration, so check point should be being issued regularly.
Here is typical backup script:
BACKUP LOG [PEP] TO DISK = N''\\sapdeploy001\dbBackupPEP$\PEP\TRN\PEP_backup_2014_04_08_150004_6878249.trn'' WITH NOFORMAT, NOINIT, NAME = N''PEP_backup_2014_04_08_150004_6858247'', SKIP, REWIND, NOUNLOAD, COMPRESSION, STATS = 10
What am I missing? Thanks in advance, Karen
April 9, 2014 at 7:56 am
Karen
What does this return?
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'PEP'
John
April 9, 2014 at 8:01 am
John -
ACTIVE_TRANSACTION
Thanks,
Karen
April 9, 2014 at 8:05 am
Karen
Your log won't be truncated until that transaction is complete. Do you have a long-running job or some other process that's been going for a while?
John
April 9, 2014 at 8:15 am
This is our SAP ECC production database. No jobs are currently active but we have a very complex installation with links to multiple other systems.
I'm wondering whether increasing the transaction log interval to a larger time period would allow the long running job to complete.
Is there something I can run to see the longest running process?
Thank you for the help.
April 9, 2014 at 8:20 am
Karen
Don't change your log backup interval. The log backups won't stop your processes from completing, so just let them run as normal.
You can use sp_who2 or the Activity Monitor to see a list of connections to the server, what time they connected, whether they are being blocked, and so on. Use DBCC INPUTBUFFER to see what code an individual SPID is executing. For more detailed information, search the web for sp_whoisactive.
John
April 9, 2014 at 8:22 am
I tried a script from PinalDave - Look at this! Here are the top ten queries:
MaxElapsedTimeAvgElapsedTimeLogCreatedOn
358497204826520494/8/2014
2855344316823810/18/2013
2853692221863110/18/2013
23460331852110/18/2013
23437700562687010/18/2013
204691007614910/18/2013
17853331153638082/26/2014
14070434783025910/18/2013
8880477932390210/18/2013
646335968180863/15/2014
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
GO
April 9, 2014 at 8:28 am
Karen
While that script is useful for identifying which are your most expensive and/or most used queries for tuning, it doesn't help with identifying what is running at the moment. You could write your own script using sys.dm_exec_requests and other DMVs, or just use sp_whoisactiveor sp_who2 or Activity Monitor, which does that for you.
John
April 9, 2014 at 8:34 am
Hope this won't look too garbled. sp_who2 shows a bunch of processes from 10/18. Checking the sql error log, I see that was after a server restart on the same day. The server has been restarted once since then (2/24), but I do not see any processes with that date.
Would the CHECKPOINT in this list be significant?
DBNameCommandCPUTimeDiskIOLastBatch
NULLRESOURCE MONITOR639401010/18/2014 0:18
NULLXE DISPATCHER 2730010/18/2014 0:18
NULLXE TIMER 5865010/18/2014 0:18
NULLLOG WRITER 6666765010/18/2014 0:18
NULLLAZY WRITER 4931300010/18/2014 0:18
masterSIGNAL HANDLER 0010/18/2014 0:18
NULLLOCK MONITOR 842499010/18/2014 0:18
masterTASK MANAGER 0010/18/2014 0:18
masterTRACE QUEUE TASK101743010/18/2014 0:18
masterBRKR TASK 363123110/18/2014 0:18
masterCHECKPOINT 755857443803907210/18/2014 0:18
masterTASK MANAGER 0010/18/2014 0:18
masterBRKR EVENT HNDLR07410/18/2014 0:18
masterBRKR TASK 0010/18/2014 0:18
masterBRKR TASK 0010/18/2014 0:18
masterTASK MANAGER 03310/18/2014 0:18
masterTASK MANAGER 058410/18/2014 0:18
masterTASK MANAGER 0757810/18/2014 0:18
masterTASK MANAGER 0597810/18/2014 0:18
masterTASK MANAGER 029510/18/2014 0:18
masterTASK MANAGER 034510/18/2014 0:18
masterTASK MANAGER 015910/18/2014 0:18
masterTASK MANAGER 066410/18/2014 0:18
masterTASK MANAGER 0322010/18/2014 0:18
April 9, 2014 at 8:39 am
Now i am confused! 18th October 2014? You need to disable DBCC TIMEWARP. Was that the full output of sp_who2 that you posted? You're mostly interested in SPIDs above 50 - the others are system tasks.
John
April 9, 2014 at 8:53 am
John,
Thanks for hanging in there with me. I didn't even notice the 2014. My mistake, I am sure.
Here's how the date looks from SQL, if I just copy the field: 10/18 00:18:19
I pasted it into Excel and formatted the column as a mm/dd/yy. There are 449 spids above 50.
One, an update, has Status of Suspended and is blocked by 507 (last batch 4/5).
Karen
SPIDStatusBlkByCommandCPUTimeDiskIOLastBatch
427sleeping .AWAITING325924453/26
166sleeping .AWAITING748234193/31
179sleeping .AWAITING2075862047593/31
181sleeping .AWAITING10630726390525883/31
185sleeping .AWAITING2232073803/31
188sleeping .AWAITING8743185453/31
201sleeping .AWAITING482415453/31
209sleeping .AWAITING207461283/31
215sleeping .AWAITING2571078522833/31
216sleeping .AWAITING1581168476237483/31
389sleeping .AWAITING207066559084/1
392sleeping .AWAITING467723430574/1
204sleeping .AWAITING424510824/4
212sleeping .AWAITING10485354/4
337sleeping .AWAITING3980421100494/4
342sleeping .AWAITING1239463225644/4
149SUSPENDED 507UPDATE 1504/5
161sleeping .AWAITING004/5
507sleeping .AWAITING3027633229384/5
508sleeping .AWAITING42893554044784/5
224sleeping .AWAITING198786404654/7
225sleeping .AWAITING530320248194/7
532sleeping .AWAITING30254/7
533sleeping .AWAITING23204/7
231sleeping .AWAITING54711212494/7
233sleeping .AWAITING20970122514/7
82sleeping .AWAITING004/7
118sleeping .AWAITING004/7
69sleeping .AWAITING176878467354/7
81sleeping .AWAITING15184/7
107sleeping .AWAITING15434/7
128sleeping .AWAITING13655974339314/7
335sleeping .AWAITING8388281370004/7
340sleeping .AWAITING1863738794644/7
334sleeping .AWAITING259137735564/7
339sleeping .AWAITING835402668784/7
64sleeping .AWAITING78937262664/7
73sleeping .AWAITING5838201182464/7
95sleeping .AWAITING4035331067664/7
76sleeping .AWAITING337019984/7
51sleeping .AWAITING4373174/7
89sleeping .AWAITING431145693114/8
61sleeping .AWAITING8953951671704/8
87sleeping .AWAITING385930660204/8
113sleeping .AWAITING20552085341804/8
117sleeping .AWAITING20971341112334/8
123sleeping .AWAITING29701824/8
129sleeping .AWAITING35571101754134/8
133sleeping .AWAITING603373317684/8
136sleeping .AWAITING49422277007194/8
140sleeping .AWAITING30180794661254/8
60sleeping .AWAITING61069119604/8
147sleeping .AWAITING274127163344/8
72sleeping .AWAITING5321874/8
122sleeping .AWAITING1144514/8
96sleeping .AWAITING680082890914/8
150sleeping .AWAITING343889510935004/8
83sleeping .AWAITING1271684/8
142sleeping .AWAITING60954/8
68sleeping .AWAITING3467889984/8
106sleeping .AWAITING26149967634/8
58sleeping .AWAITING6903411182174/8
134sleeping .AWAITING433398012029184/8
59sleeping .AWAITING11481132100694/8
138sleeping .AWAITING63525213677584/8
208sleeping .AWAITING295440533794/8
217sleeping .AWAITING605518240914/8
262sleeping .AWAITING923531234/8
263sleeping .AWAITING132610533984/8
280sleeping .AWAITING280073724/8
282sleeping .AWAITING336149314944/8
238sleeping .AWAITING293803921764/8
252sleeping .AWAITING874158230404/8
240sleeping .AWAITING117211486254/8
268sleeping .AWAITING461576148674/8
257sleeping .AWAITING97818487524/8
274sleeping .AWAITING40964272834/8
260sleeping .AWAITING955857794/8
275sleeping .AWAITING350531734/8
267sleeping .AWAITING166264692674/8
285sleeping .AWAITING1079689529164/8
199sleeping .AWAITING232738534/8
218sleeping .AWAITING152167153254/8
189sleeping .AWAITING8484151879724/8
195sleeping .AWAITING2116976825404/8
200sleeping .AWAITING24392103474/9
211sleeping .AWAITING646937684/9
241sleeping .AWAITING209316567434/9
246sleeping .AWAITING6351441947504/9
248sleeping .AWAITING20301141271404/9
249sleeping .AWAITING10027042322814/9
255sleeping .AWAITING28809831689824/9
261sleeping .AWAITING47574263734/9
276sleeping .AWAITING18616420024/9
278sleeping .AWAITING144015561104/9
283sleeping .AWAITING341218734084/9
286sleeping .AWAITING7089611886354/9
294sleeping .AWAITING819472164284/9
295sleeping .AWAITING135240465124/9
297sleeping .AWAITING1790611524724/9
305sleeping .AWAITING40485641134/9
315sleeping .AWAITING599943414774/9
317sleeping .AWAITING8654642176374/9
323sleeping .AWAITING26176264732524/9
296sleeping .AWAITING44439239064/9
369sleeping .AWAITING87595012088814/9
408sleeping .AWAITING179114724128304/9
203sleeping .AWAITING98487411852074/9
207sleeping .AWAITING7748102297624/9
270sleeping .AWAITING359870633244/9
277sleeping .AWAITING526733205764/9
291sleeping .AWAITING261611274/9
302sleeping .AWAITING93981334/9
345sleeping .AWAITING264293315929054/9
346sleeping .AWAITING13259291463084/9
348sleeping .AWAITING9238551351614/9
349sleeping .AWAITING13033912622114/9
359sleeping .AWAITING17336722815354/9
360sleeping .AWAITING249584/9
365sleeping .AWAITING26898971675224/9
368sleeping .AWAITING41694874/9
371sleeping .AWAITING399314/9
391sleeping .AWAITING54914/9
396sleeping .AWAITING9328581769534/9
401sleeping .AWAITING22949692567064/9
409sleeping .AWAITING4667381090234/9
410sleeping .AWAITING164494479034/9
417sleeping .AWAITING48215139874/9
418sleeping .AWAITING124761300034/9
426sleeping .AWAITING32811137594/9
428sleeping .AWAITING16488511553564/9
430sleeping .AWAITING542146543934/9
432sleeping .AWAITING748718182014/9
419sleeping .AWAITING6151931353254/9
431sleeping .AWAITING15767481959174/9
67sleeping .AWAITING96091011688194/9
124sleeping .AWAITING11034404513674/9
94sleeping .AWAITING98824910148334/9
135sleeping .AWAITING6937695600194/9
384sleeping .AWAITING6351866691524/9
388sleeping .AWAITING1644548410546344/9
256sleeping .AWAITING5488016110124/9
271sleeping .AWAITING1030789310424824/9
314sleeping .AWAITING5031755020294/9
322sleeping .AWAITING74776656105344/9
413sleeping .AWAITING17600850228940154/9
393sleeping .AWAITING11361137826214/9
319sleeping .AWAITING3232873313734/9
326sleeping .AWAITING57022045411844/9
171sleeping .AWAITING004/9
398sleeping .AWAITING214552319920904/9
405sleeping .AWAITING2783928694406834/9
92sleeping .AWAITING152760924977244/9
121sleeping .AWAITING3374058114971604/9
221sleeping .AWAITING246251670394/9
227sleeping .AWAITING1463569420714/9
446sleeping .AWAITING31314/9
450sleeping .AWAITING1125771724/9
158sleeping .AWAITING187725421800594/9
174sleeping .AWAITING27317314755454/9
473sleeping .AWAITING004/9
103sleeping .AWAITING6517094868074/9
148sleeping .AWAITING20102569824/9
183sleeping .AWAITING72993918504254/9
184sleeping .AWAITING25943948124485744/9
461sleeping .AWAITING109214/9
414sleeping .AWAITING004/9
437sleeping .AWAITING004/9
462sleeping .AWAITING004/9
467sleeping .AWAITING004/9
77sleeping .AWAITING144441222268734/9
112sleeping .AWAITING2911148988507784/9
406sleeping .AWAITING12575278638924/9
422sleeping .AWAITING19914300159794764/9
235sleeping .AWAITING792326884/9
66sleeping .AWAITING145162914851534/9
119sleeping .AWAITING33351194109533824/9
441sleeping .AWAITING004/9
364sleeping .AWAITING224206325088024/9
376sleeping .AWAITING208602052159404/9
464RUNNABLE .SELECT INTO 75446616855874/9
394sleeping .AWAITING7031614/9
155sleeping .AWAITING210415261354/9
156sleeping .AWAITING35834492754/9
357sleeping .AWAITING620809218616104/9
378sleeping .AWAITING2208463694545944/9
313sleeping .AWAITING95158512102854/9
318sleeping .AWAITING1897865315616454/9
385sleeping .AWAITING1504/9
423sleeping .AWAITING004/9
434sleeping .AWAITING004/9
436sleeping .AWAITING004/9
439sleeping .AWAITING034/9
443sleeping .AWAITING004/9
444sleeping .AWAITING004/9
445sleeping .AWAITING004/9
447sleeping .AWAITING004/9
327sleeping .AWAITING004/9
452sleeping .AWAITING1604/9
463sleeping .AWAITING004/9
465sleeping .AWAITING004/9
468sleeping .AWAITING004/9
469sleeping .AWAITING1504/9
472sleeping .AWAITING004/9
474sleeping .AWAITING004/9
475sleeping .AWAITING004/9
476sleeping .AWAITING004/9
477sleeping .AWAITING1604/9
480sleeping .AWAITING004/9
481sleeping .AWAITING004/9
56sleeping .AWAITING302001111554/9
163sleeping .AWAITING12804092085784/9
172sleeping .AWAITING30424423344954/9
187sleeping .AWAITING378965837944/9
192sleeping .AWAITING1184460716354/9
194sleeping .AWAITING33408712784194/9
205sleeping .AWAITING1813900933504/9
222sleeping .AWAITING10114171760774/9
226sleeping .AWAITING15137851277464/9
230sleeping .AWAITING2155407923424/9
287sleeping .AWAITING203483328314/9
289sleeping .AWAITING335396107234/9
292sleeping .AWAITING819529224/9
299sleeping .AWAITING169291054/9
329sleeping .AWAITING10992981936944/9
330sleeping .AWAITING51035862229734/9
336sleeping .AWAITING681128292424/9
478sleeping .AWAITING118971130964/9
479sleeping .AWAITING158810110534/9
52sleeping .AWAITING3135671964/9
54sleeping .AWAITING61008111594/9
55sleeping .AWAITING148528278224/9
70sleeping .AWAITING327540183144/9
71sleeping .AWAITING493878736604/9
79sleeping .AWAITING39557144704/9
80sleeping .AWAITING369733510434/9
84sleeping .AWAITING65071148014/9
91sleeping .AWAITING9624521725064/9
102sleeping .AWAITING27962117804/9
104sleeping .AWAITING27991676087604/9
108sleeping .AWAITING229291293104/9
114sleeping .AWAITING19159903353844/9
115sleeping .AWAITING931321425034/9
126sleeping .AWAITING130110258714/9
131sleeping .AWAITING419245677914/9
141sleeping .AWAITING14988711234/9
143sleeping .AWAITING25237213284/9
144sleeping .AWAITING25385393035964/9
162sleeping .AWAITING6577371187124/9
165sleeping .AWAITING248730486004/9
169sleeping .AWAITING1424811605324/9
173sleeping .AWAITING543413109854/9
180sleeping .AWAITING765714923224/9
182sleeping .AWAITING13712962127504/9
190sleeping .AWAITING9821371799504/9
191sleeping .AWAITING10319782333194/9
196sleeping .AWAITING23770254572974/9
198sleeping .AWAITING8173922003564/9
213sleeping .AWAITING10056251979024/9
220sleeping .AWAITING2409034510694/9
223sleeping .AWAITING5400091384444/9
264sleeping .AWAITING294598825444/9
266sleeping .AWAITING686132024/9
272sleeping .AWAITING1466343594514/9
281sleeping .AWAITING975092477414/9
284sleeping .AWAITING805358064/9
300sleeping .AWAITING8370461312514/9
331sleeping .AWAITING448996652354/9
347sleeping .AWAITING363551586834/9
351sleeping .AWAITING8026162200354/9
352sleeping .AWAITING14016392612574/9
353sleeping .AWAITING704356278944/9
355sleeping .AWAITING388951384724/9
358sleeping .AWAITING101808334104/9
362sleeping .AWAITING1068772331044/9
367sleeping .AWAITING17042534513854/9
370sleeping .AWAITING57619244676864/9
373sleeping .AWAITING1817433724/9
377sleeping .AWAITING286457504/9
380sleeping .AWAITING36665160754/9
381sleeping .AWAITING326186779634/9
386sleeping .AWAITING756386290454/9
412sleeping .AWAITING7574741244564/9
415sleeping .AWAITING1533153761424/9
416sleeping .AWAITING54986113214/9
429sleeping .AWAITING1001876504/9
332sleeping .AWAITING1898471184/9
324sleeping .AWAITING446416964/9
301sleeping .AWAITING2794902079984/9
320sleeping .AWAITING8630932504/9
85sleeping .AWAITING108391313094404/9
120sleeping .AWAITING1256219880724/9
293sleeping .AWAITING91260010534744/9
309sleeping .AWAITING9406998806254/9
350sleeping .AWAITING15655442554374/9
366sleeping .AWAITING28737131051114/9
523sleeping .AWAITING522310924174/9
524sleeping .AWAITING19867551826684/9
395sleeping .AWAITING6436144713894/9
400sleeping .AWAITING20080867554/9
250sleeping .AWAITING3554779954/9
202sleeping .AWAITING3151679360854/9
210sleeping .AWAITING1681153662602064/9
53sleeping .AWAITING16560967624/9
341sleeping .AWAITING157532504/9
236sleeping .AWAITING76241684/9
433sleeping .AWAITING004/9
438sleeping .AWAITING004/9
440sleeping .AWAITING004/9
448sleeping .AWAITING004/9
455sleeping .AWAITING0114/9
459sleeping .AWAITING1504/9
306sleeping .AWAITING1604/9
307sleeping .AWAITING004/9
328sleeping .AWAITING1604/9
451sleeping .AWAITING004/9
449sleeping .AWAITING1604/9
453sleeping .AWAITING004/9
456sleeping .AWAITING004/9
457sleeping .AWAITING004/9
458sleeping .AWAITING004/9
460sleeping .AWAITING004/9
466sleeping .AWAITING004/9
482sleeping .AWAITING004/9
168sleeping .AWAITING235934/9
316sleeping .AWAITING64071715744/9
325sleeping .AWAITING7976491031364/9
454sleeping .AWAITING1604/9
470sleeping .AWAITING004/9
471sleeping .AWAITING004/9
483sleeping .AWAITING004/9
484sleeping .AWAITING004/9
485sleeping .AWAITING004/9
486sleeping .AWAITING004/9
487sleeping .AWAITING004/9
488sleeping .AWAITING004/9
177sleeping .AWAITING444212964034/9
160sleeping .AWAITING273735647674/9
356sleeping .AWAITING4124834/9
258sleeping .AWAITING5941746354/9
170sleeping .AWAITING864671288154/9
343sleeping .AWAITING5780421363694/9
344sleeping .AWAITING18353693445474/9
404sleeping .AWAITING13473442519484/9
421sleeping .AWAITING25018782398784/9
164sleeping .AWAITING84788335934/9
176sleeping .AWAITING693746423884/9
186sleeping .AWAITING199847423405154/9
193sleeping .AWAITING31243871862934/9
100sleeping .AWAITING6981271071184/9
146sleeping .AWAITING33630656032674/9
86sleeping .AWAITING11348842508584/9
151sleeping .AWAITING647724811793484/9
214sleeping .AWAITING6208484771104/9
219sleeping .AWAITING18451468764/9
397sleeping .AWAITING55086115344/9
402sleeping .AWAITING5604011204/9
105sleeping .AWAITING212272382694/9
153sleeping .AWAITING11918651824414/9
99sleeping .AWAITING127431215323564/9
137sleeping .AWAITING37459013179194854/9
90sleeping .AWAITING620296889824/9
132sleeping .AWAITING27670046463394/9
98sleeping .AWAITING13377582206774/9
130sleeping .AWAITING670809310449034/9
244sleeping .AWAITING65139254334/9
178sleeping .AWAITING11601333705254/9
259sleeping .AWAITING7326232071074/9
74sleeping .AWAITING14193422518894/9
145sleeping .AWAITING948914510076444/9
279sleeping .AWAITING5735414/9
78sleeping .AWAITING159623292874/9
110sleeping .AWAITING52960169214/9
65sleeping .AWAITING7833398739424/9
125sleeping .AWAITING1141577953884/9
159sleeping .AWAITING12810911848274/9
167sleeping .AWAITING23435234888474/9
88sleeping .AWAITING6475884613234/9
127sleeping .AWAITING19205766284/9
63sleeping .AWAITING3548650024/9
312sleeping .AWAITING93228110545864/9
321sleeping .AWAITING15447468149624/9
62sleeping .AWAITING216336286464/9
116sleeping .AWAITING36903324464/9
290sleeping .AWAITING294882843454/9
304sleeping .AWAITING9629613088134/9
157sleeping .AWAITING140699383974/9
375sleeping .AWAITING248121608154/9
383sleeping .AWAITING652064619714/9
228sleeping .AWAITING186496322804574/9
229sleeping .AWAITING15643878427974/9
310sleeping .AWAITING2849112102324/9
311sleeping .AWAITING9467239614/9
265sleeping .AWAITING47182224154/9
269sleeping .AWAITING1984973639924/9
232sleeping .AWAITING6645935346574/9
234sleeping .AWAITING20492076424/9
251sleeping .AWAITING122350529384/9
197sleeping .AWAITING5787891137754/9
206sleeping .AWAITING1368944412804/9
237sleeping .AWAITING4123811203604/9
239sleeping .AWAITING11818142626074/9
333sleeping .AWAITING45438817937354/9
338sleeping .AWAITING283673196527834/9
407sleeping .AWAITING6454715193984/9
411sleeping .AWAITING198048523872094/9
424sleeping .AWAITING18858664584/9
425sleeping .AWAITING13716099198964/9
101sleeping .AWAITING226180286014/9
154sleeping .AWAITING36829124674/9
245sleeping .AWAITING15424826154/9
254sleeping .AWAITING378408124644/9
382sleeping .AWAITING7079401485814/9
387sleeping .AWAITING22130782425084/9
273sleeping .AWAITING3222241011884/9
288sleeping .AWAITING895555683654/9
75sleeping .AWAITING68733201764/9
247sleeping .AWAITING9332751898384/9
298sleeping .AWAITING94159382204/9
363sleeping .AWAITING18237202786594/9
390sleeping .AWAITING3364241674314/9
97sleeping .AWAITING783429084/9
139sleeping .AWAITING433356109814/9
152sleeping .AWAITING1497991257274/9
242sleeping .AWAITING302121831114/9
243sleeping .AWAITING13069282349234/9
303sleeping .AWAITING6938121773074/9
308sleeping .AWAITING1856437831424/9
379sleeping .AWAITING40761992045304/9
442sleeping .AWAITING35088939524/9
57sleeping .AWAITING5812221334194/9
93sleeping .AWAITING15475202695794/9
109sleeping .AWAITING33942342953724/9
111sleeping .AWAITING772007820251904/9
175sleeping .AWAITING437248461594/9
253sleeping .AWAITING28936775400154/9
354sleeping .AWAITING5484844/9
361sleeping .AWAITING9005581966014/9
372sleeping .AWAITING21942924/9
374sleeping .AWAITING20195192185534/9
399sleeping .AWAITING6120871323734/9
403sleeping .AWAITING44268592101134/9
420sleeping .AWAITING7349091192294/9
435sleeping .AWAITING1232843544764/9
April 9, 2014 at 9:01 am
OK, then use DBCC INPUTBUFFER to find out whether blocking or blocked process are doing anything on the PEP database. You'll need to resolve the blocking in order for the blocked process to complete.
John
April 9, 2014 at 9:24 am
John,
Here are the results of DBCC INPUTBUFFER.
Thanks,
Karen
**Blocking spid 507**
dbcc inputbuffer(507)
EventTypeParametersEventInfo
Language Event0select db_name()
**Suspended spid 149 **
dbcc inputbuffer(149)
EventTypeParametersEventInfo
Language Event0(@P1 nvarchar(1),@P2 nvarchar(12),@P3 nvarchar(8),@P4 nvarchar(6),@P5 nvarchar(8),@P6 nvarchar(6),@P7 nvarchar(12),@P8 nvarchar(12),@P9 nvarchar(40),@P10 nvarchar(45),@P11 nvarchar(3),@P12 nvarchar(12))UPDATE "SWT_LOGCAT" SET "HANDLETYPE" = @P1 ,"CREATED_BY" = @P2 ,"CREATED_ON" = @P3 ,"CREATED_AT" = @P4 ,"CHANGED_ON" = @P5 ,"CHANGED_AT" = @P6 ,"RETAIN_BY" = @P7 ,"WI_ID" = @P8 ,"DESCRIPTIO" = @P9 ,"OBJECT" = @P10 WHERE "MANDT" = @P11 AND "HANDLE" = @P12 /* R3:RTAB:0 T:SWT_LOGCAT */
From SP_WHO2, I see that the suspended spid (149) is doing an update. The blocking spid (507) is AWAITING COMMAND.
April 9, 2014 at 9:40 am
I can't see why 507 is blocking, given that it's AWAITING COMMAND. This is where sp_whoisactive gives you more information - it tells you wait types, wait resources and all sorts of other stuff. If 507 is a user connection, it might be worth contacting the user and asking him or her to disconnect.
John
April 9, 2014 at 9:59 am
John,
Thank you for the sp_who suggestion and all the other troubleshooting help, very much appreciated. Sp_who showed the 'default' sql login used by SAP (and which is used by almost all other processes).
I looked at the blocking spid with sys.sysprocesses. Since lastwaittype showed that the spid was not waiting for anything, I will recommend killing the spid.
Best Regards,
Karen
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply