Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

95 GB Transaction Log but Log Backups are much smaller Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 8:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:54 AM
Points: 16, Visits: 128
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

SPID Status BlkBy Command CPUTime DiskIO LastBatch
427 sleeping . AWAITING 3259 2445 3/26
166 sleeping . AWAITING 7482 3419 3/31
179 sleeping . AWAITING 207586 204759 3/31
181 sleeping . AWAITING 10630726 39052588 3/31
185 sleeping . AWAITING 22320 7380 3/31
188 sleeping . AWAITING 87431 8545 3/31
201 sleeping . AWAITING 4824 1545 3/31
209 sleeping . AWAITING 20746 128 3/31
215 sleeping . AWAITING 257107 852283 3/31
216 sleeping . AWAITING 15811684 7623748 3/31
389 sleeping . AWAITING 207066 55908 4/1
392 sleeping . AWAITING 467723 43057 4/1
204 sleeping . AWAITING 4245 1082 4/4
212 sleeping . AWAITING 10485 35 4/4
337 sleeping . AWAITING 398042 110049 4/4
342 sleeping . AWAITING 1239463 22564 4/4
149 SUSPENDED 507 UPDATE 15 0 4/5
161 sleeping . AWAITING 0 0 4/5
507 sleeping . AWAITING 302763 322938 4/5
508 sleeping . AWAITING 4289355 404478 4/5
224 sleeping . AWAITING 198786 40465 4/7
225 sleeping . AWAITING 530320 24819 4/7
532 sleeping . AWAITING 30 25 4/7
533 sleeping . AWAITING 232 0 4/7
231 sleeping . AWAITING 54711 21249 4/7
233 sleeping . AWAITING 209701 2251 4/7
82 sleeping . AWAITING 0 0 4/7
118 sleeping . AWAITING 0 0 4/7
69 sleeping . AWAITING 176878 46735 4/7
81 sleeping . AWAITING 15 18 4/7
107 sleeping . AWAITING 154 3 4/7
128 sleeping . AWAITING 1365597 433931 4/7
335 sleeping . AWAITING 838828 137000 4/7
340 sleeping . AWAITING 1863738 79464 4/7
334 sleeping . AWAITING 259137 73556 4/7
339 sleeping . AWAITING 835402 66878 4/7
64 sleeping . AWAITING 78937 26266 4/7
73 sleeping . AWAITING 583820 118246 4/7
95 sleeping . AWAITING 403533 106766 4/7
76 sleeping . AWAITING 3370 1998 4/7
51 sleeping . AWAITING 437 317 4/7
89 sleeping . AWAITING 431145 69311 4/8
61 sleeping . AWAITING 895395 167170 4/8
87 sleeping . AWAITING 385930 66020 4/8
113 sleeping . AWAITING 2055208 534180 4/8
117 sleeping . AWAITING 2097134 111233 4/8
123 sleeping . AWAITING 29701 82 4/8
129 sleeping . AWAITING 3557110 175413 4/8
133 sleeping . AWAITING 603373 31768 4/8
136 sleeping . AWAITING 4942227 700719 4/8
140 sleeping . AWAITING 3018079 466125 4/8
60 sleeping . AWAITING 61069 11960 4/8
147 sleeping . AWAITING 274127 16334 4/8
72 sleeping . AWAITING 532 187 4/8
122 sleeping . AWAITING 1144 51 4/8
96 sleeping . AWAITING 680082 89091 4/8
150 sleeping . AWAITING 3438895 1093500 4/8
83 sleeping . AWAITING 127 168 4/8
142 sleeping . AWAITING 609 5 4/8
68 sleeping . AWAITING 34678 8998 4/8
106 sleeping . AWAITING 261499 6763 4/8
58 sleeping . AWAITING 690341 118217 4/8
134 sleeping . AWAITING 4333980 1202918 4/8
59 sleeping . AWAITING 1148113 210069 4/8
138 sleeping . AWAITING 6352521 367758 4/8
208 sleeping . AWAITING 295440 53379 4/8
217 sleeping . AWAITING 605518 24091 4/8
262 sleeping . AWAITING 9235 3123 4/8
263 sleeping . AWAITING 132610 53398 4/8
280 sleeping . AWAITING 28007 372 4/8
282 sleeping . AWAITING 336149 31494 4/8
238 sleeping . AWAITING 293803 92176 4/8
252 sleeping . AWAITING 874158 23040 4/8
240 sleeping . AWAITING 117211 48625 4/8
268 sleeping . AWAITING 461576 14867 4/8
257 sleeping . AWAITING 97818 48752 4/8
274 sleeping . AWAITING 409642 7283 4/8
260 sleeping . AWAITING 9558 5779 4/8
275 sleeping . AWAITING 35053 173 4/8
267 sleeping . AWAITING 166264 69267 4/8
285 sleeping . AWAITING 1079689 52916 4/8
199 sleeping . AWAITING 2327 3853 4/8
218 sleeping . AWAITING 152167 15325 4/8
189 sleeping . AWAITING 848415 187972 4/8
195 sleeping . AWAITING 2116976 82540 4/8
200 sleeping . AWAITING 24392 10347 4/9
211 sleeping . AWAITING 64693 768 4/9
241 sleeping . AWAITING 209316 56743 4/9
246 sleeping . AWAITING 635144 194750 4/9
248 sleeping . AWAITING 2030114 127140 4/9
249 sleeping . AWAITING 1002704 232281 4/9
255 sleeping . AWAITING 2880983 168982 4/9
261 sleeping . AWAITING 47574 26373 4/9
276 sleeping . AWAITING 186164 2002 4/9
278 sleeping . AWAITING 144015 56110 4/9
283 sleeping . AWAITING 341218 73408 4/9
286 sleeping . AWAITING 708961 188635 4/9
294 sleeping . AWAITING 819472 16428 4/9
295 sleeping . AWAITING 135240 46512 4/9
297 sleeping . AWAITING 1790611 52472 4/9
305 sleeping . AWAITING 404856 4113 4/9
315 sleeping . AWAITING 599943 41477 4/9
317 sleeping . AWAITING 865464 217637 4/9
323 sleeping . AWAITING 2617626 473252 4/9
296 sleeping . AWAITING 444392 3906 4/9
369 sleeping . AWAITING 875950 1208881 4/9
408 sleeping . AWAITING 17911472 412830 4/9
203 sleeping . AWAITING 984874 1185207 4/9
207 sleeping . AWAITING 7748102 29762 4/9
270 sleeping . AWAITING 359870 63324 4/9
277 sleeping . AWAITING 526733 20576 4/9
291 sleeping . AWAITING 2616 1127 4/9
302 sleeping . AWAITING 9398 133 4/9
345 sleeping . AWAITING 2642933 1592905 4/9
346 sleeping . AWAITING 1325929 146308 4/9
348 sleeping . AWAITING 923855 135161 4/9
349 sleeping . AWAITING 1303391 262211 4/9
359 sleeping . AWAITING 1733672 281535 4/9
360 sleeping . AWAITING 249 58 4/9
365 sleeping . AWAITING 2689897 167522 4/9
368 sleeping . AWAITING 4169 487 4/9
371 sleeping . AWAITING 3993 1 4/9
391 sleeping . AWAITING 549 1 4/9
396 sleeping . AWAITING 932858 176953 4/9
401 sleeping . AWAITING 2294969 256706 4/9
409 sleeping . AWAITING 466738 109023 4/9
410 sleeping . AWAITING 164494 47903 4/9
417 sleeping . AWAITING 48215 13987 4/9
418 sleeping . AWAITING 124761 30003 4/9
426 sleeping . AWAITING 328111 3759 4/9
428 sleeping . AWAITING 1648851 155356 4/9
430 sleeping . AWAITING 542146 54393 4/9
432 sleeping . AWAITING 748718 18201 4/9
419 sleeping . AWAITING 615193 135325 4/9
431 sleeping . AWAITING 1576748 195917 4/9
67 sleeping . AWAITING 960910 1168819 4/9
124 sleeping . AWAITING 11034404 51367 4/9
94 sleeping . AWAITING 988249 1014833 4/9
135 sleeping . AWAITING 6937695 60019 4/9
384 sleeping . AWAITING 635186 669152 4/9
388 sleeping . AWAITING 16445484 1054634 4/9
256 sleeping . AWAITING 548801 611012 4/9
271 sleeping . AWAITING 10307893 1042482 4/9
314 sleeping . AWAITING 503175 502029 4/9
322 sleeping . AWAITING 7477665 610534 4/9
413 sleeping . AWAITING 17600850 22894015 4/9
393 sleeping . AWAITING 1136113 782621 4/9
319 sleeping . AWAITING 323287 331373 4/9
326 sleeping . AWAITING 5702204 541184 4/9
171 sleeping . AWAITING 0 0 4/9
398 sleeping . AWAITING 2145523 1992090 4/9
405 sleeping . AWAITING 27839286 9440683 4/9
92 sleeping . AWAITING 1527609 2497724 4/9
121 sleeping . AWAITING 33740581 1497160 4/9
221 sleeping . AWAITING 246251 67039 4/9
227 sleeping . AWAITING 1463569 42071 4/9
446 sleeping . AWAITING 31 31 4/9
450 sleeping . AWAITING 112577 172 4/9
158 sleeping . AWAITING 1877254 2180059 4/9
174 sleeping . AWAITING 27317314 75545 4/9
473 sleeping . AWAITING 0 0 4/9
103 sleeping . AWAITING 651709 486807 4/9
148 sleeping . AWAITING 201025 6982 4/9
183 sleeping . AWAITING 729939 1850425 4/9
184 sleeping . AWAITING 25943948 12448574 4/9
461 sleeping . AWAITING 109 21 4/9
414 sleeping . AWAITING 0 0 4/9
437 sleeping . AWAITING 0 0 4/9
462 sleeping . AWAITING 0 0 4/9
467 sleeping . AWAITING 0 0 4/9
77 sleeping . AWAITING 1444412 2226873 4/9
112 sleeping . AWAITING 29111489 8850778 4/9
406 sleeping . AWAITING 1257527 863892 4/9
422 sleeping . AWAITING 19914300 15979476 4/9
235 sleeping . AWAITING 7923 2688 4/9
66 sleeping . AWAITING 1451629 1485153 4/9
119 sleeping . AWAITING 33351194 10953382 4/9
441 sleeping . AWAITING 0 0 4/9
364 sleeping . AWAITING 2242063 2508802 4/9
376 sleeping . AWAITING 20860205 215940 4/9
464 RUNNABLE . SELECT INTO 754466 1685587 4/9
394 sleeping . AWAITING 703 161 4/9
155 sleeping . AWAITING 210415 26135 4/9
156 sleeping . AWAITING 358344 9275 4/9
357 sleeping . AWAITING 6208092 1861610 4/9
378 sleeping . AWAITING 22084636 9454594 4/9
313 sleeping . AWAITING 951585 1210285 4/9
318 sleeping . AWAITING 18978653 1561645 4/9
385 sleeping . AWAITING 15 0 4/9
423 sleeping . AWAITING 0 0 4/9
434 sleeping . AWAITING 0 0 4/9
436 sleeping . AWAITING 0 0 4/9
439 sleeping . AWAITING 0 3 4/9
443 sleeping . AWAITING 0 0 4/9
444 sleeping . AWAITING 0 0 4/9
445 sleeping . AWAITING 0 0 4/9
447 sleeping . AWAITING 0 0 4/9
327 sleeping . AWAITING 0 0 4/9
452 sleeping . AWAITING 16 0 4/9
463 sleeping . AWAITING 0 0 4/9
465 sleeping . AWAITING 0 0 4/9
468 sleeping . AWAITING 0 0 4/9
469 sleeping . AWAITING 15 0 4/9
472 sleeping . AWAITING 0 0 4/9
474 sleeping . AWAITING 0 0 4/9
475 sleeping . AWAITING 0 0 4/9
476 sleeping . AWAITING 0 0 4/9
477 sleeping . AWAITING 16 0 4/9
480 sleeping . AWAITING 0 0 4/9
481 sleeping . AWAITING 0 0 4/9
56 sleeping . AWAITING 302001 11155 4/9
163 sleeping . AWAITING 1280409 208578 4/9
172 sleeping . AWAITING 3042442 334495 4/9
187 sleeping . AWAITING 378965 83794 4/9
192 sleeping . AWAITING 1184460 71635 4/9
194 sleeping . AWAITING 3340871 278419 4/9
205 sleeping . AWAITING 1813900 93350 4/9
222 sleeping . AWAITING 1011417 176077 4/9
226 sleeping . AWAITING 1513785 127746 4/9
230 sleeping . AWAITING 2155407 92342 4/9
287 sleeping . AWAITING 203483 32831 4/9
289 sleeping . AWAITING 335396 10723 4/9
292 sleeping . AWAITING 8195 2922 4/9
299 sleeping . AWAITING 16929 105 4/9
329 sleeping . AWAITING 1099298 193694 4/9
330 sleeping . AWAITING 5103586 222973 4/9
336 sleeping . AWAITING 681128 29242 4/9
478 sleeping . AWAITING 118971 13096 4/9
479 sleeping . AWAITING 158810 11053 4/9
52 sleeping . AWAITING 31356 7196 4/9
54 sleeping . AWAITING 61008 11159 4/9
55 sleeping . AWAITING 148528 27822 4/9
70 sleeping . AWAITING 327540 18314 4/9
71 sleeping . AWAITING 493878 73660 4/9
79 sleeping . AWAITING 39557 14470 4/9
80 sleeping . AWAITING 369733 51043 4/9
84 sleeping . AWAITING 65071 14801 4/9
91 sleeping . AWAITING 962452 172506 4/9
102 sleeping . AWAITING 27962 11780 4/9
104 sleeping . AWAITING 2799167 608760 4/9
108 sleeping . AWAITING 229291 29310 4/9
114 sleeping . AWAITING 1915990 335384 4/9
115 sleeping . AWAITING 931321 42503 4/9
126 sleeping . AWAITING 130110 25871 4/9
131 sleeping . AWAITING 419245 67791 4/9
141 sleeping . AWAITING 149887 1123 4/9
143 sleeping . AWAITING 252372 1328 4/9
144 sleeping . AWAITING 2538539 303596 4/9
162 sleeping . AWAITING 657737 118712 4/9
165 sleeping . AWAITING 248730 48600 4/9
169 sleeping . AWAITING 1424811 60532 4/9
173 sleeping . AWAITING 543413 10985 4/9
180 sleeping . AWAITING 765714 92322 4/9
182 sleeping . AWAITING 1371296 212750 4/9
190 sleeping . AWAITING 982137 179950 4/9
191 sleeping . AWAITING 1031978 233319 4/9
196 sleeping . AWAITING 2377025 457297 4/9
198 sleeping . AWAITING 817392 200356 4/9
213 sleeping . AWAITING 1005625 197902 4/9
220 sleeping . AWAITING 2409034 51069 4/9
223 sleeping . AWAITING 540009 138444 4/9
264 sleeping . AWAITING 294598 82544 4/9
266 sleeping . AWAITING 6861 3202 4/9
272 sleeping . AWAITING 1466343 59451 4/9
281 sleeping . AWAITING 975092 47741 4/9
284 sleeping . AWAITING 80535 806 4/9
300 sleeping . AWAITING 837046 131251 4/9
331 sleeping . AWAITING 448996 65235 4/9
347 sleeping . AWAITING 363551 58683 4/9
351 sleeping . AWAITING 802616 220035 4/9
352 sleeping . AWAITING 1401639 261257 4/9
353 sleeping . AWAITING 704356 27894 4/9
355 sleeping . AWAITING 388951 38472 4/9
358 sleeping . AWAITING 101808 33410 4/9
362 sleeping . AWAITING 1068772 33104 4/9
367 sleeping . AWAITING 1704253 451385 4/9
370 sleeping . AWAITING 5761924 467686 4/9
373 sleeping . AWAITING 18174 3372 4/9
377 sleeping . AWAITING 28645 750 4/9
380 sleeping . AWAITING 366651 6075 4/9
381 sleeping . AWAITING 326186 77963 4/9
386 sleeping . AWAITING 756386 29045 4/9
412 sleeping . AWAITING 757474 124456 4/9
415 sleeping . AWAITING 1533153 76142 4/9
416 sleeping . AWAITING 54986 11321 4/9
429 sleeping . AWAITING 100187 650 4/9
332 sleeping . AWAITING 18984 7118 4/9
324 sleeping . AWAITING 4464 1696 4/9
301 sleeping . AWAITING 279490 207998 4/9
320 sleeping . AWAITING 86309 3250 4/9
85 sleeping . AWAITING 1083913 1309440 4/9
120 sleeping . AWAITING 12562198 8072 4/9
293 sleeping . AWAITING 912600 1053474 4/9
309 sleeping . AWAITING 9406998 80625 4/9
350 sleeping . AWAITING 1565544 255437 4/9
366 sleeping . AWAITING 2873713 105111 4/9
523 sleeping . AWAITING 522310 92417 4/9
524 sleeping . AWAITING 1986755 182668 4/9
395 sleeping . AWAITING 643614 471389 4/9
400 sleeping . AWAITING 200808 6755 4/9
250 sleeping . AWAITING 35547 7995 4/9
202 sleeping . AWAITING 315167 936085 4/9
210 sleeping . AWAITING 16811536 6260206 4/9
53 sleeping . AWAITING 16560 96762 4/9
341 sleeping . AWAITING 15753 250 4/9
236 sleeping . AWAITING 7624 168 4/9
433 sleeping . AWAITING 0 0 4/9
438 sleeping . AWAITING 0 0 4/9
440 sleeping . AWAITING 0 0 4/9
448 sleeping . AWAITING 0 0 4/9
455 sleeping . AWAITING 0 11 4/9
459 sleeping . AWAITING 15 0 4/9
306 sleeping . AWAITING 16 0 4/9
307 sleeping . AWAITING 0 0 4/9
328 sleeping . AWAITING 16 0 4/9
451 sleeping . AWAITING 0 0 4/9
449 sleeping . AWAITING 16 0 4/9
453 sleeping . AWAITING 0 0 4/9
456 sleeping . AWAITING 0 0 4/9
457 sleeping . AWAITING 0 0 4/9
458 sleeping . AWAITING 0 0 4/9
460 sleeping . AWAITING 0 0 4/9
466 sleeping . AWAITING 0 0 4/9
482 sleeping . AWAITING 0 0 4/9
168 sleeping . AWAITING 235 93 4/9
316 sleeping . AWAITING 64071 71574 4/9
325 sleeping . AWAITING 797649 103136 4/9
454 sleeping . AWAITING 16 0 4/9
470 sleeping . AWAITING 0 0 4/9
471 sleeping . AWAITING 0 0 4/9
483 sleeping . AWAITING 0 0 4/9
484 sleeping . AWAITING 0 0 4/9
485 sleeping . AWAITING 0 0 4/9
486 sleeping . AWAITING 0 0 4/9
487 sleeping . AWAITING 0 0 4/9
488 sleeping . AWAITING 0 0 4/9
177 sleeping . AWAITING 444212 96403 4/9
160 sleeping . AWAITING 273735 64767 4/9
356 sleeping . AWAITING 4124 83 4/9
258 sleeping . AWAITING 59417 4635 4/9
170 sleeping . AWAITING 864671 28815 4/9
343 sleeping . AWAITING 578042 136369 4/9
344 sleeping . AWAITING 1835369 344547 4/9
404 sleeping . AWAITING 1347344 251948 4/9
421 sleeping . AWAITING 2501878 239878 4/9
164 sleeping . AWAITING 84788 33593 4/9
176 sleeping . AWAITING 693746 42388 4/9
186 sleeping . AWAITING 1998474 2340515 4/9
193 sleeping . AWAITING 31243871 86293 4/9
100 sleeping . AWAITING 698127 107118 4/9
146 sleeping . AWAITING 3363065 603267 4/9
86 sleeping . AWAITING 1134884 250858 4/9
151 sleeping . AWAITING 6477248 1179348 4/9
214 sleeping . AWAITING 620848 477110 4/9
219 sleeping . AWAITING 184514 6876 4/9
397 sleeping . AWAITING 55086 11534 4/9
402 sleeping . AWAITING 56040 1120 4/9
105 sleeping . AWAITING 212272 38269 4/9
153 sleeping . AWAITING 1191865 182441 4/9
99 sleeping . AWAITING 1274312 1532356 4/9
137 sleeping . AWAITING 37459013 17919485 4/9
90 sleeping . AWAITING 620296 88982 4/9
132 sleeping . AWAITING 2767004 646339 4/9
98 sleeping . AWAITING 1337758 220677 4/9
130 sleeping . AWAITING 6708093 1044903 4/9
244 sleeping . AWAITING 65139 25433 4/9
178 sleeping . AWAITING 1160133 370525 4/9
259 sleeping . AWAITING 732623 207107 4/9
74 sleeping . AWAITING 1419342 251889 4/9
145 sleeping . AWAITING 9489145 1007644 4/9
279 sleeping . AWAITING 57354 1 4/9
78 sleeping . AWAITING 159623 29287 4/9
110 sleeping . AWAITING 529601 6921 4/9
65 sleeping . AWAITING 783339 873942 4/9
125 sleeping . AWAITING 11415779 5388 4/9
159 sleeping . AWAITING 1281091 184827 4/9
167 sleeping . AWAITING 2343523 488847 4/9
88 sleeping . AWAITING 647588 461323 4/9
127 sleeping . AWAITING 192057 6628 4/9
63 sleeping . AWAITING 35486 5002 4/9
312 sleeping . AWAITING 932281 1054586 4/9
321 sleeping . AWAITING 15447468 14962 4/9
62 sleeping . AWAITING 216336 28646 4/9
116 sleeping . AWAITING 369033 2446 4/9
290 sleeping . AWAITING 294882 84345 4/9
304 sleeping . AWAITING 962961 308813 4/9
157 sleeping . AWAITING 140699 38397 4/9
375 sleeping . AWAITING 248121 60815 4/9
383 sleeping . AWAITING 652064 61971 4/9
228 sleeping . AWAITING 1864963 2280457 4/9
229 sleeping . AWAITING 15643878 42797 4/9
310 sleeping . AWAITING 284911 210232 4/9
311 sleeping . AWAITING 94672 3961 4/9
265 sleeping . AWAITING 47182 22415 4/9
269 sleeping . AWAITING 1984973 63992 4/9
232 sleeping . AWAITING 664593 534657 4/9
234 sleeping . AWAITING 204920 7642 4/9
251 sleeping . AWAITING 122350 52938 4/9
197 sleeping . AWAITING 578789 113775 4/9
206 sleeping . AWAITING 1368944 41280 4/9
237 sleeping . AWAITING 412381 120360 4/9
239 sleeping . AWAITING 1181814 262607 4/9
333 sleeping . AWAITING 454388 1793735 4/9
338 sleeping . AWAITING 28367319 652783 4/9
407 sleeping . AWAITING 645471 519398 4/9
411 sleeping . AWAITING 1980485 2387209 4/9
424 sleeping . AWAITING 188586 6458 4/9
425 sleeping . AWAITING 13716099 19896 4/9
101 sleeping . AWAITING 226180 28601 4/9
154 sleeping . AWAITING 368291 2467 4/9
245 sleeping . AWAITING 154248 2615 4/9
254 sleeping . AWAITING 378408 12464 4/9
382 sleeping . AWAITING 707940 148581 4/9
387 sleeping . AWAITING 2213078 242508 4/9
273 sleeping . AWAITING 322224 101188 4/9
288 sleeping . AWAITING 895555 68365 4/9
75 sleeping . AWAITING 68733 20176 4/9
247 sleeping . AWAITING 933275 189838 4/9
298 sleeping . AWAITING 94159 38220 4/9
363 sleeping . AWAITING 1823720 278659 4/9
390 sleeping . AWAITING 336424 167431 4/9
97 sleeping . AWAITING 7834 2908 4/9
139 sleeping . AWAITING 433356 10981 4/9
152 sleeping . AWAITING 149799 125727 4/9
242 sleeping . AWAITING 302121 83111 4/9
243 sleeping . AWAITING 1306928 234923 4/9
303 sleeping . AWAITING 693812 177307 4/9
308 sleeping . AWAITING 1856437 83142 4/9
379 sleeping . AWAITING 4076199 204530 4/9
442 sleeping . AWAITING 350889 3952 4/9
57 sleeping . AWAITING 581222 133419 4/9
93 sleeping . AWAITING 1547520 269579 4/9
109 sleeping . AWAITING 3394234 295372 4/9
111 sleeping . AWAITING 7720078 2025190 4/9
175 sleeping . AWAITING 437248 46159 4/9
253 sleeping . AWAITING 2893677 540015 4/9
354 sleeping . AWAITING 548 484 4/9
361 sleeping . AWAITING 900558 196601 4/9
372 sleeping . AWAITING 2194 292 4/9
374 sleeping . AWAITING 2019519 218553 4/9
399 sleeping . AWAITING 612087 132373 4/9
403 sleeping . AWAITING 4426859 210113 4/9
420 sleeping . AWAITING 734909 119229 4/9
435 sleeping . AWAITING 1232843 54476 4/9
Post #1560003
Posted Wednesday, April 9, 2014 9:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,490, Visits: 10,347
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
Post #1560008
Posted Wednesday, April 9, 2014 9:24 AM This worked for the OP Answer marked as solution
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:54 AM
Points: 16, Visits: 128
John,

Here are the results of DBCC INPUTBUFFER.

Thanks,

Karen

**Blocking spid 507**
dbcc inputbuffer(507)

EventType Parameters EventInfo
Language Event 0 select db_name()


**Suspended spid 149 **
dbcc inputbuffer(149)

EventType Parameters EventInfo
Language Event 0 (@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.


Post #1560024
Posted Wednesday, April 9, 2014 9:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:39 AM
Points: 5,490, Visits: 10,347
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
Post #1560031
Posted Wednesday, April 9, 2014 9:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:54 AM
Points: 16, Visits: 128
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
Post #1560041
Posted Wednesday, April 9, 2014 10:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
No, don't. It's not necessarily the blocking session which is causing the large log. It's an active transaction. It may be the same session, it may not.

What does DBCC OPENTRAN return?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1560072
Posted Wednesday, April 9, 2014 10:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:54 AM
Points: 16, Visits: 128
Hi Gail,

Thanks for the suggestion. Here you go:

Oldest active transaction:
SPID (server process ID): 507
UID (user ID) : -1
Name : user_transaction
LSN : (86450:32972:1)
Start time : Apr 5 2014 10:10:32:687PM
SID : 0x175cce5b0a5e7e478614a5b3d139fac8

--Karen
Post #1560087
Posted Wednesday, April 9, 2014 11:00 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:22 AM
Points: 40,632, Visits: 37,094
Ok, so it is session 507 which has had an active transaction since the 5th of April (last week friday)

You may want to do some investigation as to how and why the session has an open transaction that long, so that you can ensure it doesn't happen again, then kill it.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1560098
Posted Wednesday, April 9, 2014 1:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 19, 2014 8:54 AM
Points: 16, Visits: 128
Thanks Gail, will do.
Post #1560152
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse