Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ошибка работы сервера  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
 		Дорого времени суток!

ОС: Windows Server 2012 DataCenter
СУБД: Microsoft SQL Server 2012 - 11.0.5548.0 (X64)
Sep 6 2014 17:19:28
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: )


С определенного момента сервер начал тормазить и в логе стали появляться следующие ошибки:

1.
+


A time-out occurred while waiting for buffer latch -- type 3, bp 0000028422297380, page 7:1916856, stat 0x10b, database id: 2, allocation unit Id: 6488064, task 0x000003C4822E8188 : 77, waittime 300 seconds, flags 0x3d000002039, owning task 0x0000000007893868. Not continuing to wait.
A time-out occurred while waiting for buffer latch -- type 3, bp 0000028422297380, page 7:1916856, stat 0x10b, database id: 2, allocation unit Id: 6488064, task 0x000000000F8B1868 : 73, waittime 300 seconds, flags 0x3c000001039, owning task 0x0000000007893868. Not continuing to wait.
A time-out occurred while waiting for buffer latch -- type 4, bp 00000281CA678AC0, page 17:145730657, stat 0x109, database id: 6, allocation unit Id: 72058208667893760, task 0x00000122B2615868 : 0, waittime 300 seconds, flags 0x1018, owning task 0x0000000000000000. Not continuing to wait.
A time-out occurred while waiting for buffer latch -- type 4, bp 00000281CA678AC0, page 17:145730657, stat 0x109, database id: 6, allocation unit Id: 72058208667893760, task 0x0000019B3F87F0C8 : 0, waittime 300 seconds, flags 0x1038, owning task 0x0000000000000000. Not continuing to wait.



Далее
2.
+


Error: 26073, Severity: 16, State: 1.
TCP connection closed but a child process of SQL Server may be holding a duplicate of the connection's socket. Consider enabling the TcpAbortiveClose SQL Server registry setting and restarting SQL Server. If the problem persists, contact Technical Support.
FlushCache: cleaned up 7171 bufs with 2742 writes in 73133 ms (avoided 685 new dirty bufs) for db 8:0
average throughput: 0.77 MB/sec, I/O saturation: 285, context switches 7553
last target outstanding: 220800, avgWriteLatency 0
Using 'dbghelp.dll' version '4.0.5'




Далее
3.
+


SQL Server has encountered 5 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [DATA02.ndf] in database [] (6). The OS file handle is 0x0000000000003208. The offset of the latest long I/O is: 0x0000d796870000

SQL Server has encountered 12 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [DATA05.ndf] in database [] (6). The OS file handle is 0x0000000000003224. The offset of the latest long I/O is: 0x00000a8b340000

SQL Server has encountered 4 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [DATA11.ndf] in database [] (6). The OS file handle is 0x0000000000003230. The offset of the latest long I/O is: 0x00007170a50000

...



Далее
4.
+

There is insufficient system memory in resource pool 'default' to run this query.



И наконец
5.
+


0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 17033488
OBJECTSTORE_LOCK_MANAGER (node 7) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 6713104
OBJECTSTORE_LOCK_MANAGER (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 24
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------- ----------
VM Reserved 8390656
VM Committed 8390656
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 115437680
OBJECTSTORE_SECAUDIT_EVENT_BUFFER (node 6) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
OBJECTSTORE_XACT_CACHE (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4515568
OBJECTSTORE_XACT_CACHE (node 1) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3742568
OBJECTSTORE_XACT_CACHE (node 2) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3257368
OBJECTSTORE_XACT_CACHE (node 3) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3484080
OBJECTSTORE_XACT_CACHE (node 4) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4289752
OBJECTSTORE_XACT_CACHE (node 5) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3281536
OBJECTSTORE_XACT_CACHE (node 6) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4081816
OBJECTSTORE_XACT_CACHE (node 7) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3678616
OBJECTSTORE_XACT_CACHE (node 64) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
OBJECTSTORE_XACT_CACHE (Total) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 30331320
Buffer Pool Value
---------------------------------------- ----------
Database 170587106
Simulated 42274891
Target 1047904256
Dirty 705868
In IO 56
Latched 225
Page Life Expectancy 306
Procedure Cache Value
---------------------------------------- ----------
TotalProcs 17242
TotalPages 353846
InUsePages 44444
Global Memory Objects Pages
---------------------------------------- ----------
Resource 22829
Locks 14429722
XDES 17366
DirtyPageTracking 6044
SETLS 161
SubpDesc Allocators 359
SE SchemaManager 56545
SE Column Metadata Cache 26634
SE Column Metadata Cache Store 18
SQLCache 13373
Replication 2
ServerGlobal 187
XP Global 2
SortTables 3
Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 3
Waiting 0
Available 252738413
Current Max 252795488
Future Max 252795488
Physical Max 252795488
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Small Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 1
Waiting 0
Available 704356
Current Max 704512
Future Max 704512
Remote Query Memory Objects (internal) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 126397744
Current Max 126397744
Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 13
Waiting 0
Available 244658259
Current Max 252795488
Future Max 252795488
Physical Max 252795488
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Small Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 9
Waiting 0
Available 702870
Current Max 704512
Future Max 704512
Remote Query Memory Objects (default) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 126397744
Current Max 126397744
Query Memory Objects (reserved) Value
---------------------------------------- ----------
Grants 34
Waiting 0
Available 244712171
Current Max 252795488
Future Max 252795488
Physical Max 252795488
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Small Query Memory Objects (reserved) Value
---------------------------------------- ----------
Grants 20
Waiting 0
Available 700214
Current Max 704512
Future Max 704512
Remote Query Memory Objects (reserved) Value
---------------------------------------- ----------
Grants 0
Waiting 0
Available 126397744
Current Max 126397744
Optimization Queue (internal) Value
---------------------------------------- ----------
Overall Memory 2215116800000
Target Memory 2215116800000
Last Notification 1
Timeout 6
Early Termination Factor 5
Small Gateway (internal) Value
---------------------------------------- ----------
Configured Units 640
Available Units 640
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
Medium Gateway (internal) Value
---------------------------------------- ----------
Configured Units 160
Available Units 160
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
Big Gateway (internal) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
Optimization Queue (default) Value
---------------------------------------- ----------
Overall Memory 2215116800000
Target Memory 2215116800000
Last Notification 1
Timeout 6
Early Termination Factor 5
Small Gateway (default) Value
---------------------------------------- ----------
Configured Units 640
Available Units 639
Acquires 1
Waiters 0
Threshold Factor 380000
Threshold 380000
Medium Gateway (default) Value
---------------------------------------- ----------
Configured Units 160
Available Units 160
Acquires 0
Waiters 0
Threshold Factor 12
Threshold 184593066666
Big Gateway (default) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
Optimization Queue (reserved) Value
---------------------------------------- ----------
Overall Memory 2215116800000
Target Memory 2215116800000
Last Notification 1
Timeout 6
Early Termination Factor 5
Small Gateway (reserved) Value
---------------------------------------- ----------
Configured Units 640
Available Units 640
Acquires 0
Waiters 0
Threshold Factor 380000
Threshold 380000
Medium Gateway (reserved) Value
---------------------------------------- ----------
Configured Units 160
Available Units 160
Acquires 0
Waiters 0
Threshold Factor 12
Threshold -1
Big Gateway (reserved) Value
---------------------------------------- ----------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
Threshold -1
Memory Pool Manager Pages
---------------------------------------- ----------
Reserved Current 16283717
Reserved Limit 290878719
Memory Pool (internal) Pages
---------------------------------------- ----------
Allocations 23630873
Predicted 23630929
Private Target 0
Private Limit 0
Total Target 332800001
Total Limit 332800001
OOM Count 0
MEMORYBROKER_FOR_CACHE (internal) Pages
---------------------------------------- ----------
Allocations 802410
Rate 1
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_STEAL (internal) Pages
---------------------------------------- ----------
Allocations 41712
Rate -85
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_RESERVE (internal) Pages
---------------------------------------- ----------
Allocations 57231
Rate 48
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_COMMITTED (internal) Pages
---------------------------------------- ----------
Allocations 22729518
Rate 7
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_XTP (internal) Pages
---------------------------------------- ----------
Allocations 2
Rate 0
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
Memory Pool (default) Pages
---------------------------------------- ----------
Allocations 21806260
Predicted 21806433
Private Target 0
Private Limit 0
Total Target 332800001
Total Limit 332800001
OOM Count 0
MEMORYBROKER_FOR_CACHE (default) Pages
---------------------------------------- ----------
Allocations 13487272
Rate 173
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_STEAL (default) Pages
---------------------------------------- ----------
Allocations 180117
Rate -116
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_RESERVE (default) Pages
---------------------------------------- ----------
Allocations 8138871
Rate -10206
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_XTP (default) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
Memory Pool (reserved) Pages
---------------------------------------- ----------
Allocations 12691155
Predicted 12879603
Private Target 0
Private Limit 0
Total Target 332800001
Total Limit 332800001
OOM Count 0
MEMORYBROKER_FOR_CACHE (reserved) Pages
---------------------------------------- ----------
Allocations 4490978
Rate -46
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_STEAL (reserved) Pages
---------------------------------------- ----------
Allocations 189273
Rate 1708
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_RESERVE (reserved) Pages
---------------------------------------- ----------
Allocations 8010904
Rate 186740
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
MEMORYBROKER_FOR_XTP (reserved) Pages
---------------------------------------- ----------
Allocations 0
Rate 0
Target Allocations 270400000
Future Allocations 0
Overall 270400000
Last Notification 1
Memory Broker Clerk (Buffer Pool) Pages
---------------------------------------- ----------
Total 170587145
Simulated 42274879
Simulation Benefit 0.0000000043
Internal Benefit 0
External Benefit 0
Value Of Memory 0
Periodic Freed 0
Internal Freed 0




В момент тормазов на сервере я запускал:

SELECT * FROM sys.dm_exec_query_memory_grants where grant_time is null

и он мне ничего не выводил, т.е. всем запросам выделялась память.

Если я правильно понимаю у сервера стало резко не хватать памяти и он стал свопить на диск.
Если так, то можно ли сказать куда память делась?
30 сен 14, 17:03    [16640035]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка работы сервера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9155
 		Антивирь выключен?
30 сен 14, 17:49    [16640304]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка работы сервера  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
 		Владислав Колосов,

на сервере не стоит.
30 сен 14, 17:51    [16640309]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка работы сервера  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 9155
 		Я бы сказал, что проблема в дисках.
30 сен 14, 18:00    [16640355]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка работы сервера  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Владислав Колосов
Я бы сказал, что проблема в дисках.


На чем основано предположение?
30 сен 14, 21:47    [16641297]     Ответить | Цитировать Сообщить модератору
 Re: Ошибка работы сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
andrew shalaev
На чем основано предположение?

на том, что в большинстве сообщениях присутствует указатели на страницы данных базы

page 17:145730657, stat 0x109, database id: 6, allocation unit Id: 72058208667893760

on file [DATA05.ndf] in database [] (6). The OS file handle is 0x0000000000003224. The offset of the latest long I/O is: 0x00000a8b340000
30 сен 14, 21:52    [16641313]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить