Какую информацию о событиях я могу получить по умолчанию от SQL Server?

Я часто вижу вопросы, по которым люди хотят знать, произошло ли что-то или когда это произошло, или кто совершил действие. Во многих случаях SQL Server просто не отслеживает эту информацию самостоятельно. Например:

  • Кто последний выполнил хранимую процедуру dbo.MyProcedure?
  • Кто обновил столбец salary в таблице dbo.Employees?
  • Кто последний запросил таблицу dbo.Orders из Management Studio?

Но есть несколько других событий, которые по умолчанию отслеживают SQL Server по умолчанию, и могут изначально отвечать на вопросы, например:

  • Когда в последний раз в базе данных AdventureWorks произошел автоматический рост и сколько времени прошло?
  • Кто удалил таблицу dbo.EmployeeAuditData и когда?
  • Сколько ошибок памяти произошло сегодня?

Как получить эту информацию и как долго она останется доступной?

55 голосов | спросил Aaron Bertrand 13 PM00000080000005831 2013, 20:14:58

1 ответ


61

Существует довольно много ценной информации, которую SQL Server отслеживает по умолчанию. Так как SQL Server 2005 был «трассировкой по умолчанию», которая работает в фоновом режиме, а с SQL Server 2008 был запущен сеанс расширенных событий, называемый system_health.

Вы также можете найти определенную информацию из журнала ошибок SQL Server, журнала агента SQL Server, журналов событий Windows и дополнительного ведения журнала из таких вещей, как Аудит SQL Server , Хранилище данных управления , Уведомления о событиях , триггеры DML , Триггеры DDL , SCOM /System Center , ваши собственные трассы на стороне сервера или сеансы расширенных событий или сторонние события, (например, сделанные мой работодатель, SQL Sentry ). Вы также можете включить так называемую «трассировку Blackbox», чтобы помочь в устранении неполадок .

Но для этого сообщения я собираюсь сфокусировать сферу на вещах, которые, как правило, наиболее широко доступны: трассировка по умолчанию, сеансы расширенных событий и журнал ошибок.

Трассировка по умолчанию

Трассировка по умолчанию обычно выполняется в большинстве систем, если у вас нет отключил его, используя sp_configure . Пока он включен, это может быть богатым источником ценной информации. Ниже перечислены события трассировки, которые были захвачены:

DECLARE @TraceID INT;

SELECT @TraceID = id FROM sys.traces WHERE is_default = 1;

SELECT t.EventID, e.name as Event_Description
  FROM sys.fn_trace_geteventinfo(@TraceID) t
  JOIN sys.trace_events e ON t.eventID = e.trace_event_id
  GROUP BY t.EventID, e.name;

Вы можете получить более подробную информацию, присоединившись к sys.trace_columns, чтобы узнать, какие события идут с какими данными, но я сейчас пропущу это, так как вы можете видеть, что у вас есть вы действительно запрашиваете данные трассировки для определенных событий. Это события, которые доступны в моей системе (вы должны запустить запрос на своем, чтобы убедиться, что они совпадают):

 EventID  Event_Description
-------  ----------------------------------------------
18       Audit Server Starts And Stops
20       Audit Login Failed
22       ErrorLog
46       Object:Created
47       Object:Deleted
55       Hash Warning
69       Sort Warnings
79       Missing Column Statistics
80       Missing Join Predicate
81       Server Memory Change
92       Data File Auto Grow
93       Log File Auto Grow
94       Data File Auto Shrink
95       Log File Auto Shrink
102      Audit Database Scope GDR Event
103      Audit Schema Object GDR Event
104      Audit Addlogin Event
105      Audit Login GDR Event
106      Audit Login Change Property Event
108      Audit Add Login to Server Role Event
109      Audit Add DB User Event
110      Audit Add Member to DB Role Event
111      Audit Add Role Event
115      Audit Backup/Restore Event
116      Audit DBCC Event
117      Audit Change Audit Event
152      Audit Change Database Owner
153      Audit Schema Object Take Ownership Event
155      FT:Crawl Started
156      FT:Crawl Stopped
164      Object:Altered
167      Database Mirroring State Change
175      Audit Server Alter Trace Event
218      Plan Guide Unsuccessful

Обратите внимание, что трассировка по умолчанию использует файлы опрокидывания, и поэтому доступные вам данные будут возвращаться до сих пор - диапазон дат доступных данных зависит от того, сколько из указанных выше событий будет зафиксировано и на какой частоте. Если вы хотите, чтобы вы сохранили более длинную историю, вы можете настроить задание, которое периодически архивирует оставшиеся неактивные файлы, связанные с трассировкой.

Примеры

В вопросе я задал пару вопросов, которые я нашел. Ниже приведены примерные запросы на извлечение этой конкретной информации из трассы по умолчанию.

  

Вопрос: Когда в последний раз в базе данных AdventureWorks произошел автоматический рост, и сколько времени прошло?

Этот запрос вытащит все события AutoGrow в базе данных AdventureWorks, как для файлов журналов, так и для данных, которые все еще находятся в файлах журнала трассировки по умолчанию:

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
   DatabaseName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;
  

Вопрос: Кто удалил таблицу dbo.EmployeeAuditData и когда?

Это приведет к возврату любых событий DROP для объекта с именем EmployeeAuditData. Если вы хотите убедиться, что он обнаруживает только события DROP для таблиц, вы можете добавить фильтр: ObjectType = 8277 ( полный список описан здесь ). Если вы хотите ограничить пространство поиска определенной базой данных, вы можете добавить фильтр: DatabaseName = N'db_name'.

DECLARE @path NVARCHAR(260);

SELECT 
   @path = REVERSE(SUBSTRING(REVERSE([path]), 
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT 
  LoginName,
  HostName,
  StartTime,
  ObjectName,
  TextData
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 47    -- Object:Deleted
AND EventSubClass = 1
AND ObjectName = N'EmployeeAuditData'
ORDER BY StartTime DESC;

Здесь есть сложность, и это очень красноватый случай, но в любом случае было бы разумно упомянуть. Если вы используете несколько схем и можете иметь одно и то же имя объекта в нескольких схемах, вы не сможете определить, какой из них (если только его коллега не существует). Существует внешний случай, когда UserA может отбросить SchemaB.Tablename, в то время как UserB, возможно, сбросил SchemaA.Tablename. Трассировка по умолчанию не отслеживает схему объекта (а также не отображает TextData для этого события) и ObjectID, включенных в трассировку не подходит для прямого совпадения (поскольку объект был удален и больше не существует). Включение этого столбца в результат в этом случае может оказаться полезным для перекрестной ссылки на любые копии таблицы с тем же именем, которое все еще существует, но если система находится в этом большом беспорядке (или если все такие копии были удалены), все еще не может быть надежным способом догадаться, по какой копии таблицы была удалена кем.

Расширенные события

От Поддержка SQL Server 2008: сеанс system_health (блог SQLCSS) , ниже приведен список данных, которые вы можете отбирать из сеанса system_health в SQL Server 2008 и 2008 R2

  • sql_text и session_id для любых сеансов, которые сталкиваются с ошибкой с серьезностью> = 20
  • sql_text и session_id для любых сеансов, которые сталкиваются с ошибкой типа «память», например 17803, 701 и т. д. (мы добавили это, потому что не все ошибки памяти имеют серьезность> = 20)
  • Запись каких-либо «невыносимых» проблем (вы иногда видели их в ERRORLOG как Msg 17883)
  • Любые обнаруженные блокировки
  • Столбец, sql_text и session_id для любых сеансов, которые ждали защелки (или другие интересные ресурсы) для> 15 секунд
  • Столбец, sql_text и session_id для любых сеансов, которые ждали блокировки для> 30 секунд
  • Столбец, sql_text и session_id для любого сеанса, который ждал длительный период времени для «внешних» ожиданий или «упреждающих ожиданий».

От Используйте сеанс событий system_health (MSDN) , список несколько расширяется в SQL Server 2012 (и остается неизменным для SQL Server 2014):

  • sql_text и session_id для любых сеансов, которые сталкиваются с ошибкой, которая имеет серьезность> = 20.
  • sql_text и session_id для любых сеансов, которые сталкиваются с ошибкой, связанной с памятью. Ошибки включают 17803, 701, 802, 8645, 8651, 8657 и 8902.
  • Запись любых проблем, связанных с невыполнением планировщика. (Они появляются в журнале ошибок SQL Server как ошибка 17883.)
  • Любые обнаруженные блокировки.
  • Столбец, sql_text и session_id для любых сеансов, которые ждали на защелках (или других интересных ресурсах) для> 15 секунд.
  • Столбец, sql_text и session_id для любых сеансов, которые ждали блокировки для> 30 секунд.
  • Столбец, sql_text и session_id для любых сеансов, которые долгое время ждали превентивных ожиданий. Длительность зависит от типа ожидания. Упреждающее ожидание - это то, где SQL Server ожидает внешних вызовов API.
  • Столбец вызова и session_id для распределения CLR и отказов виртуального распределения.
  • События ring_buffer для брокера памяти, монитора планировщика,узел памяти OOM, безопасность и подключение.
  • Системный компонент получается из sp_server_diagnostics.
  • Состояние экземпляра, собранное scheduler_monitor_system_health_ring_buffer_recorded.
  • Неисправности распределения CLR.
  • Ошибки подключения с помощью connectivity_ring_buffer_recorded.
  • Ошибки безопасности с использованием security_error_ring_buffer_recorded.

В SQL Server 2016 захватываются еще два события:

  • Когда процесс уничтожается с помощью команды KILL.
  • При завершении работы SQL Server.

(Документация еще не обновлена, но я писал о том, как Я обнаруживаю эти и другие изменения .)

Чтобы получить более криптографическую конфигурацию, применимую для вашей конкретной версии, вы всегда можете запустить следующий запрос напрямую, но вам придется интерпретировать имена и анализировать предикаты, чтобы они соответствовали более естественным языковым спискам выше:

SELECT e.package, e.event_id, e.name, e.predicate
  FROM sys.server_event_session_events AS e
  INNER JOIN sys.server_event_sessions AS s
  ON e.event_session_id = s.event_session_id
 WHERE s.name = N'system_health'
 ORDER BY e.package, e.name;

Если вы используете группы доступности, есть также два новых сеанса, которые вы найдете: AlwaysOn_failover и AlwaysOn_health. Вы можете увидеть данные, которые они собирают, со следующим запросом:

SELECT s.name, e.package, e.event_id, e.name, e.predicate
  FROM sys.server_event_session_events AS e
  INNER JOIN sys.server_event_sessions AS s
  ON e.event_session_id = s.event_session_id
 WHERE s.name LIKE N'AlwaysOn[_]%'
 ORDER BY s.name, e.package, e.name;

В этих сеансах сеанса используются цепочки кольцевого буфера для хранения данных, поэтому - как пул буферов и кеш плана - старые события будут постепенно отменены, поэтому вы не сможете вывести события из диапазона дат, который вы хотите .

Пример

В вопросе я задал этот фиктивный вопрос:

  

Сколько ошибок памяти произошло сегодня?

Вот пример (и, вероятно, не очень эффективный) запрос, который может вывести эту информацию из сеанса system_health:

;WITH src(x) AS
(
  SELECT y.query('.')
  FROM
  (
    SELECT x = CONVERT(XML, t.target_data)
      FROM sys.dm_xe_sessions AS s
      INNER JOIN sys.dm_xe_session_targets AS t
      ON s.[address] = t.event_session_address
      WHERE s.name = N'system_health'
  ) AS x
  CROSS APPLY x.x.nodes('/RingBufferTarget/event') AS y(y)
)
SELECT 
  x, ts = CONVERT(DATETIME, NULL), err = CONVERT(INT, NULL)
INTO #blat FROM src;

DELETE #blat WHERE x.value('(/event/@name)[1]', 'varchar(255)') <> 'error_reported';

UPDATE #blat SET ts = x.value('(/event/@timestamp)[1]', 'datetime');

UPDATE #blat SET err = x.value('(/event/data/value)[1]', 'int');

SELECT err, number_of_events = COUNT(*)
  FROM #blat
  WHERE err IN (17803, 701, 802, 8645, 8651, 8657, 8902)
  AND ts >= CONVERT(DATE, CURRENT_TIMESTAMP)
  GROUP BY err;

DROP TABLE #blat;

(В этом примере проистекает из вводное сообщение блога Амита Банерджи на сеансе system_health .)

Для получения дополнительной информации о расширенных событиях (включая множество примеров, где вы можете запросить конкретные данные), см. эту серию блога из 31 части Джонатана Кехайяса:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/01/a-xevent-a-day-31-days-of-extended-events.aspx р >

Журнал ошибок

SQL Server по умолчанию сохраняет текущий плюс 6 последних файлов журнала ошибок (но вы можете изменить это ) , Здесь хранится много информации, включая информацию о запуске (сколько ядер используется, установлены ли блокирующие страницы в памяти, режим проверки подлинности и т. Д.), А также ошибки и другие сценарии, достаточно серьезные для документирования (а не записи в другом месте). Одним из недавних примеров был тот, кто ищет, когда база данных была отключена. Вы можете определить это, просмотрев каждый из последних 7 журналов ошибок для текста Setting database option OFFLINE:

EXEC sys.sp_readerrorlog 0,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 1,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 2,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 3,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 4,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 5,1,'Setting database option OFFLINE';
EXEC sys.sp_readerrorlog 6,1,'Setting database option OFFLINE';

В этом недавнем ответе я рассмотрел некоторые другие детали , и есть также хорошая справочная информация в toadworld , а также в официальной документации .

Одна группа «ошибок» журнала ошибок отслеживает по умолчанию - и может значительно ускорить получение важной информации с хвоста - это каждое успешное резервное сообщение. Вы можете запретить им заполнять журнал ошибок с помощью включение флага трассировки 3226 .

ответил Aaron Bertrand 13 PM00000080000005831 2013, 20:14:58

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132