AUTO_CLOSE<\/i><\/a> \u0441\u043d\u0438\u0436\u0430\u0435\u0442 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c. \u0422\u0430\u043a \u0432\u043e\u0442 AUTO_SHRINK<\/i> \u043f\u043e\u0441\u0442\u0443\u043f\u0430\u0435\u0442 \u0435\u0449\u0435 \u0445\u0443\u0436\u0435\u2026 \u041a\u0430\u0436\u0434\u044b\u0439 30 \u043c\u0438\u043d\u0443\u0442 SQL Server<\/i> \u043f\u044b\u0442\u0430\u0435\u0442\u0441\u044f \u0441\u0434\u0435\u043b\u0430\u0442\u044c \u0443\u0441\u0435\u0447\u0435\u043d\u0438\u0435 \u0441\u0432\u043e\u0431\u043e\u0434\u043d\u043e\u0433\u043e \u043c\u0435\u0441\u0442\u0430 \u0432 \u0444\u0430\u0439\u043b\u0430\u0445 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445. \u0414\u0430\u043d\u043d\u044b\u0439 \u043f\u0440\u043e\u0446\u0435\u0441\u0441 \u0434\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0435\u043c\u043a\u0438\u0439 \u0438 \u043c\u043e\u0436\u0435\u0442 \u043f\u0440\u0438\u0432\u043e\u0434\u0438\u0442\u044c \u043a \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u0438 \u0444\u0430\u0439\u043b\u043e\u0432 \u043d\u0430 \u0434\u0438\u0441\u043a\u0435. \u041f\u0440\u0438 \u0443\u0441\u0435\u0447\u0435\u043d\u0438\u0438 \u0444\u0430\u0439\u043b\u043e\u0432 \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0435\u0442 \u0432\u044b\u0441\u043e\u043a\u0430\u044f \u0444\u0440\u0430\u0433\u043c\u0435\u043d\u0442\u0430\u0446\u0438\u044f \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432, \u0447\u0442\u043e \u0443\u0432\u0435\u043b\u0438\u0447\u0438\u0432\u0430\u0435\u0442 \u043b\u043e\u0433\u0438\u0447\u0435\u0441\u043a\u0438\u0435 \u0447\u0442\u0435\u043d\u0438\u044f \u0438 \u0441\u043d\u0438\u0436\u0430\u0435\u0442 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432.<\/p>\n \u041f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u0438\u043c \u043f\u0440\u043e\u0441\u0442\u0443\u044e \u0441\u0438\u0442\u0443\u0430\u0446\u0438\u044e\u2026 \u0423\u0434\u0430\u043b\u0438\u043b\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u2013 \u0443\u0441\u0435\u0447\u0435\u043d\u0438\u0435 \u0444\u0430\u0439\u043b\u0430. \u0412\u0441\u0442\u0430\u0432\u0438\u043b\u0438 \u043d\u043e\u0432\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u2013 \u043d\u0435 \u0445\u0432\u0430\u0442\u0438\u043b\u043e \u043c\u0435\u0441\u0442\u0430 \u0438 SQL Server<\/i> \u043f\u0440\u0438\u0448\u043b\u043e\u0441\u044c \u0443\u0432\u0435\u043b\u0438\u0447\u0438\u0432\u0430\u0442\u044c \u0440\u0430\u0437\u043c\u0435\u0440 \u0444\u0430\u0439\u043b\u0430. \u0423\u0434\u0430\u043b\u0438\u043b\u0438 \u0434\u0430\u043d\u043d\u044b\u0435 \u0438 \u043e\u043f\u044f\u0442\u044c \u0432\u0441\u0435 \u043f\u043e-\u043d\u043e\u0432\u043e\u043c\u0443\u2026<\/p>\nSELECT StartTime , EndTime , Duration = Duration \/ 1000 , DatabaseName = DB_NAME(DatabaseID) , [FileName] , GrowType = CASE WHEN EventClass = 94 THEN 'DATA' ELSE 'LOG' END , NTDomainName , ApplicationName , LoginName , TextData , IsSystem FROM sys.traces i CROSS APPLY sys.fn_trace_gettable([path], DEFAULT) t WHERE t.EventClass IN ( 94, -- Data File Auto Shrink 95 -- Log File Auto Shrink ) AND i.is_default = 1 <\/code><\/pre>\n <\/p>\n
StartTime EndTime Duration DatabaseName FileName GrowType ----------------------- ----------------------- ----------- ------------------------- ------------------------ -------- 2016-02-10 11:57:54.333 2016-02-10 11:57:54.450 116 AdventureWorks2012 AdventureWorks2012_Log LOG 2016-02-10 14:58:21.087 2016-02-10 14:58:21.200 113 AdventureWorks2012 AdventureWorks2012_Log LOG 2016-02-10 19:30:02.350 2016-02-10 19:30:02.463 113 AdventureWorks2012 AdventureWorks2012_Log LOG 2016-02-10 21:00:26.083 2016-02-10 21:00:26.100 16 AdventureWorks2012 AdventureWorks2012_Log LOG <\/code><\/pre>\n \u041e\u0434\u043d\u043e\u0437\u043d\u0430\u0447\u043d\u043e \u0441\u043e\u0432\u0435\u0442\u0443\u044e \u0434\u0430\u043d\u043d\u0443\u044e \u043e\u043f\u0446\u0438\u044e \u043e\u0442\u043a\u043b\u044e\u0447\u0430\u0442\u044c:<\/p>\n
SELECT 'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_SHRINK OFF WITH NO_WAIT;' FROM sys.databases WHERE is_auto_shrink_on = 1 <\/code><\/pre>\n 3. DBCC Events<\/b><\/p>\n
\u0415\u0449\u0435 \u043e\u0434\u043d\u0430 \u043f\u043e\u043b\u0435\u0437\u043d\u043e\u0441\u0442\u044c \u0434\u0435\u0444\u043e\u043b\u0442\u043d\u043e\u0433\u043e \u0442\u0440\u0435\u0439\u0441\u0430 \u2013 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044c \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u0442\u044c \u043a\u0442\u043e \u0438 \u043a\u043e\u0433\u0434\u0430 \u0437\u0430\u043f\u0443\u0441\u043a\u0430\u043b DBCC<\/i> \u043a\u043e\u043c\u0430\u043d\u0434\u044b. \u0420\u0443\u0433\u0430\u0442\u044c \u0437\u0430 DBCC CHECKDB<\/i> \u043e\u0431\u044b\u0447\u043d\u043e \u043d\u0435 \u0441\u0442\u043e\u0438\u0442, \u043d\u043e \u0432\u043e\u0442 \u0435\u0441\u043b\u0438 \u043a\u0442\u043e-\u0442\u043e \u043d\u0430 \u043f\u0440\u043e\u0434\u0430\u043a\u0448\u0435\u043d\u0435 \u0431\u0435\u0437 \u0443\u043c\u0430 \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u0442:<\/p>\nDBCC SHRINKDATABASE DBCC FREEPROCCACHE DBCC DROPCLEANBUFFERS <\/code><\/pre>\n \u0442\u043e \u043c\u043e\u0436\u043d\u043e \u044d\u0442\u043e \u043b\u0435\u0433\u043a\u043e \u043e\u0442\u0441\u043b\u0435\u0434\u0438\u0442\u044c:<\/p>\n
SELECT t.TextData , t.ApplicationName , t.LoginName , t.StartTime FROM sys.traces i CROSS APPLY sys.fn_trace_gettable([path], DEFAULT) t WHERE i.is_default = 1 AND t.EventClass = 116 -- Audit DBCC Event AND t.ApplicationName IS NOT NULL <\/code><\/pre>\n \u0438 \u043a\u043e\u0435 \u0441 \u043a\u0435\u043c \u043d\u0443\u0436\u043d\u043e \u043f\u0440\u043e\u0432\u0435\u0441\u0442\u0438 \u043f\u0440\u043e\u0444\u0438\u043b\u0430\u043a\u0442\u0438\u0447\u0435\u0441\u043a\u0443\u044e \u0431\u0435\u0441\u0435\u0434\u0443 \u043e \u043f\u043e\u043b\u044c\u0437\u0435 \u0441\u0442\u043e\u044f\u043d\u0438\u044f \u0432 \u0443\u0433\u043b\u0443 \u043d\u0430 \u0433\u0440\u0435\u0447\u043a\u0435: <\/p>\n
TextData ApplicationName LoginName StartTime ----------------------- -------------------------------------- ----------- ----------------------- DBCC SHRINKDATABASE(1) Microsoft SQL Server Management Studio PC\\IgorS 2016-02-10 20:03:46.307 DBCC FREEPROCCACHE Microsoft SQL Server Management Studio PC\\IgorS 2016-02-10 20:03:43.430 DBCC DROPCLEANBUFFERS Microsoft SQL Server Management Studio PC\\IgorS 2016-02-10 20:03:44.767 <\/code><\/pre>\n 4. Errors and Warnings Events<\/b><\/p>\n
\u0412 \u0441\u0438\u0442\u0443\u0430\u0446\u0438\u0438, \u043a\u043e\u0433\u0434\u0430 SQL Server<\/i> \u043d\u0435 \u0438\u043c\u0435\u0435\u0442 \u0434\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e\u0433\u043e \u043e\u0431\u044a\u0451\u043c\u0430 \u0441\u0432\u043e\u0431\u043e\u0434\u043d\u043e\u0439 \u043f\u0430\u043c\u044f\u0442\u0438, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u0434\u043b\u044f \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0437\u0430\u043f\u0440\u043e\u0441\u0430, \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0430 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u043e\u0432 \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0445 \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440\u043e\u0432 \u0431\u0443\u0434\u0435\u0442 \u043f\u0440\u043e\u0438\u0441\u0445\u043e\u0434\u0438\u0442\u044c \u0432 tempdb<\/i>. \u0422\u0430\u043a\u043e\u0435 \u0436\u0435 \u043f\u043e\u0432\u0435\u0434\u0435\u043d\u0438\u0435 \u0431\u0443\u0434\u0435\u0442, \u043a\u043e\u0433\u0434\u0430 \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0442\u043e\u0440\u043e\u043c \u0431\u044b\u043b\u0430 \u0441\u0434\u0435\u043b\u0430\u043b\u0430 \u043d\u0435\u0432\u0435\u0440\u043d\u0430\u044f \u043e\u0446\u0435\u043d\u043a\u0430 \u043e\u0436\u0438\u0434\u0430\u0435\u043c\u043e\u0433\u043e \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0430 \u0441\u0442\u0440\u043e\u043a. <\/p>\n
\u041f\u043e\u0441\u043c\u043e\u0442\u0440\u0438\u043c \u043d\u0430 \u043f\u043b\u0430\u043d \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0434\u0430\u043d\u043d\u043e\u0433\u043e \u0437\u0430\u043f\u0440\u043e\u0441\u0430:<\/p>\n
SELECT * FROM Sales.SalesOrderHeader WHERE DueDate > ShipDate ORDER BY OrderDate DESC <\/code><\/pre>\n ![](\"https:\/\/habrastorage.org\/files\/9e4\/691\/1d4\/9e46911d4caf47d4916e1b45d30cdfd2.png\"\/)
<\/p>\n
\u0411\u043b\u0430\u0433\u043e\u0434\u0430\u0440\u044f \u0441\u043e\u043e\u0431\u0449\u0435\u043d\u0438\u044e \u0438\u0437 \u0434\u0435\u0444\u043e\u043b\u0442\u043d\u043e\u0433\u043e \u0442\u0440\u0435\u0439\u0441\u0430 \u043c\u044b \u043c\u043e\u0436\u0435\u043c \u043e\u0442\u0441\u043b\u0435\u0434\u0438\u0442\u044c \u0442\u0430\u043a\u0438\u0435 \u0437\u0430\u043f\u0440\u043e\u0441\u044b:<\/p>\n
SELECT TOP(10) EventName = e.name , t.TextData , t.ApplicationName , t.LoginName , t.StartTime , DatabaseName = DB_NAME(t.DatabaseID) FROM sys.traces i CROSS APPLY sys.fn_trace_gettable([path], DEFAULT) t JOIN sys.trace_events e ON e.trace_event_id = t.EventClass WHERE i.is_default = 1 AND e.category_id = 3 ORDER BY t.StartTime DESC <\/code><\/pre>\n <\/p>\n
EventName TextData ApplicationName LoginName StartTime DatabaseName ------------------ ----------- ---------------------------------------------------- ------------- ----------------------- --------------------- Sort Warnings NULL Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:06:44.867 AdventureWorks2012 <\/code><\/pre>\n \u041d\u0430\u0439\u0442\u0438 \u0438\u0445 \u043f\u043b\u0430\u043d \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0438 \u043f\u043e\u043f\u044b\u0442\u0430\u0442\u044c\u0441\u044f \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0437\u0430\u043f\u0440\u043e\u0441:<\/p>\n
USE AdventureWorks2012 GO SELECT TOP(5) p.query_plan , e.[text] , qyery_cost = p.query_plan.value( '(\/*:ShowPlanXML\/*:BatchSequence\/*:Batch\/*:Statements\/*:StmtSimple\/@StatementSubTreeCost)[1]', 'FLOAT' ) , s.last_execution_time , last_exec_ms = s.last_worker_time \/ 1000 , s.execution_count FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) e WHERE e.[text] NOT LIKE '%sys%' AND s.last_execution_time >= DATEADD(MS, -2500, '2016-02-10 19:41:45.983') AND e.[dbid] = DB_ID() ORDER BY s.last_execution_time <\/code><\/pre>\n <\/p>\n
query_plan text qyery_cost last_execution_time last_exec_ms execution_count ------------------------------------ ----------------------------------------- ----------- ----------------------- -------------- ---------------- <ShowPlanXML xmlns="http:\/\/schem... SELECT * FROM Sales.SalesOrderHeader... 1,10126 2016-02-11 13:06:44.850 1538 1 <\/code><\/pre>\n \u041a \u0441\u043b\u043e\u0432\u0443 \u0441\u043a\u0430\u0436\u0443, \u0430\u043a\u0442\u0443\u0430\u043b\u044c\u043d\u044b\u0435 \u043f\u043b\u0430\u043d\u044b \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u043d\u0435\u0434\u043e\u0441\u0442\u0443\u043f\u043d\u044b \u0447\u0435\u0440\u0435\u0437 DMV<\/i>. \u041e\u043d\u0438 \u0434\u043e\u0441\u0442\u0443\u043f\u043d\u044b \u0442\u043e\u043b\u044c\u043a\u043e \u043d\u0430 \u044d\u0442\u0430\u043f\u0435 Post Query Execution Showplan<\/i>, \u0447\u0435\u0440\u0435\u0437 \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0443\u044e\u0449\u0438\u0439 Trace event<\/i>, \u0438\u043b\u0438 \u043f\u043e \u043a\u043e\u043c\u0430\u043d\u0434\u0435 SET STATISTICS XML ON<\/i>. \u041d\u0430\u0447\u0438\u043d\u0430\u044f \u0441 SQL Server 2012<\/i> \u0441\u043f\u0435\u0446\u0438\u0430\u043b\u044c\u043d\u043e \u0434\u043b\u044f \u0442\u0430\u043a\u0438\u0445 \u0446\u0435\u043b\u0435\u0439 \u0434\u043e\u0431\u0430\u0432\u0438\u043b\u0438 \u043d\u043e\u0432\u044b\u0439 XEvent<\/i> \u2013 post_query_execution_showplan<\/i>.<\/p>\n
\u041d\u0435\u043c\u043d\u043e\u0433\u043e \u043e\u0442\u0432\u043b\u0451\u043a\u0441\u044f\u2026 \u041c\u0435\u043d\u044f \u043b\u0438\u0447\u043d\u043e \u0440\u0430\u0434\u0443\u0435\u0442, \u0447\u0442\u043e \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u0442\u044c \u043c\u043e\u0436\u043d\u043e \u0440\u0430\u0437\u043d\u043e\u0433\u043e \u0440\u043e\u0434\u0430 \u043f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0436\u0434\u0435\u043d\u0438\u044f:<\/p>\n
SELECT DISTINCT d.SalesOrderID, d.UnitPrice, h.OrderDate FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID WHERE h.DueDate > h.ShipDate <\/code><\/pre>\n ![](\"https:\/\/habrastorage.org\/files\/b4f\/c39\/f7d\/b4fc39f7dd704eef9c006d3fd1fadd0a.png\"\/)
<\/p>\n
EventName TextData ApplicationName LoginName StartTime DatabaseName ---------------- ----------- ------------------------------------------------- ------------- ----------------------- --------------------- Hash Warning NULL Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:14:44.433 AdventureWorks2012 <\/code><\/pre>\n \u041d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u043a\u043e\u0433\u0434\u0430 \u043f\u043e \u043e\u0448\u0438\u0431\u043a\u0435 \u0437\u0430\u0431\u044b\u043b\u0438 \u0443\u043a\u0430\u0437\u0430\u0442\u044c \u043f\u043e\u043b\u044f \u043f\u043e \u043a\u043e\u0442\u043e\u0440\u044b\u043c \u0438\u0434\u0435\u0442 \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u0435:<\/p>\n
SELECT * FROM Sales.Currency c, Sales.CountryRegionCurrency r --WHERE c.CurrencyCode = r.CurrencyCode <\/code><\/pre>\n ![](\"https:\/\/habrastorage.org\/files\/f8f\/1d6\/202\/f8f1d62028cb4914b5669e9173b24e7a.png\"\/)
<\/p>\n
EventName TextData ApplicationName LoginName StartTime DatabaseName ----------------------- ----------- ------------------------------------------------- ------------- ----------------------- --------------------- Missing Join Predicate NULL Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:18:20.507 AdventureWorks2012 <\/code><\/pre>\n \u0438\u043b\u0438, \u043a\u043e\u0433\u0434\u0430 \u043d\u0430 \u0441\u0442\u043e\u043b\u0431\u0446\u0435 \u043f\u043e \u043a\u043e\u0442\u043e\u0440\u043e\u043c\u0443 \u0438\u0434\u0435\u0442 \u0444\u0438\u043b\u044c\u0442\u0440\u0430\u0446\u0438\u044f \u043e\u0442\u0441\u0443\u0442\u0441\u0442\u0432\u0443\u0435\u0442 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430:<\/p>\n
SELECT DatabaseLogID FROM dbo.DatabaseLog WHERE PostTime = '2012-03-14 13:14:18.847' <\/code><\/pre>\n ![](\"https:\/\/habrastorage.org\/files\/837\/e9c\/6ef\/837e9c6ef53947ed88e01792809ddc7b.png\"\/)
<\/p>\n
EventName TextData ApplicationName LoginName StartTime DatabaseName --------------------------- ---------------------------------------------------------------- ------------------------------------------------- --------------- ----------------------- ---------------------- Missing Column Statistics NO STATS:([AdventureWorks2012].[dbo].[DatabaseLog].[PostTime]) Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:19:39.813 AdventureWorks2012 <\/code><\/pre>\n 5. Object Events<\/b><\/p>\n
\u0412 \u0434\u0435\u0444\u043e\u043b\u0442\u043d\u043e\u043c \u0442\u0440\u0435\u0439\u0441\u0435 \u043c\u043e\u0436\u043d\u043e \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u0442\u044c \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u0438 \u0443\u0434\u0430\u043b\u0435\u043d\u0438\u0435 \u043e\u0431\u044a\u0435\u043a\u0442\u043e\u0432:<\/p>\n
USE [master] GO IF DB_ID('test') IS NOT NULL DROP DATABASE [test] GO CREATE DATABASE [test] GO USE [test] GO CREATE TABLE dbo.tbl (ID INT) GO ALTER TABLE dbo.tbl ADD Col VARCHAR(20) GO CREATE UNIQUE CLUSTERED INDEX ix ON dbo.tbl (ID) GO USE [master] GO IF DB_ID('test') IS NOT NULL DROP DATABASE [test] GO <\/code><\/pre>\n <\/p>\n
SELECT EventType = e.name , t.DatabaseName , t.ApplicationName , t.LoginName , t.StartTime , t.ObjectName , ObjectType = CASE t.ObjectType WHEN 8259 THEN 'Check Constraint' WHEN 8260 THEN 'Default Constraint' WHEN 8262 THEN 'Foreign Key' WHEN 8272 THEN 'Stored Procedure' WHEN 8274 THEN 'Rule' WHEN 8275 THEN 'System Table' WHEN 8276 THEN 'Server Trigger' WHEN 8277 THEN 'Table' WHEN 8278 THEN 'View' WHEN 8280 THEN 'Extended Stored Procedure' WHEN 16724 THEN 'CLR Trigger' WHEN 16964 THEN 'Database' WHEN 17222 THEN 'FullText Catalog' WHEN 17232 THEN 'CLR Stored Procedure' WHEN 17235 THEN 'Schema' WHEN 17985 THEN 'CLR Aggregate Function' WHEN 17993 THEN 'Inline Table-valued SQL Function' WHEN 18000 THEN 'Partition Function' WHEN 18004 THEN 'Table-valued SQL Function' WHEN 19280 THEN 'Primary Key' WHEN 19539 THEN 'SQL Login' WHEN 19543 THEN 'Windows Login' WHEN 20038 THEN 'Scalar SQL Function' WHEN 20051 THEN 'Synonym' WHEN 20821 THEN 'Unique Constraint' WHEN 21075 THEN 'Server' WHEN 21076 THEN 'Transact-SQL Trigger' WHEN 21313 THEN 'Assembly' WHEN 21318 THEN 'CLR Scalar Function' WHEN 21321 THEN 'Inline scalar SQL Function' WHEN 21328 THEN 'Partition Scheme' WHEN 21333 THEN 'User' WHEN 21572 THEN 'Database Trigger' WHEN 21574 THEN 'CLR Table-valued Function' WHEN 21587 THEN 'Statistics' WHEN 21825 THEN 'User' WHEN 21827 THEN 'User' WHEN 21831 THEN 'User' WHEN 21843 THEN 'User' WHEN 21847 THEN 'User' WHEN 22601 THEN 'Index' WHEN 22611 THEN 'XMLSchema' WHEN 22868 THEN 'Type' END FROM sys.traces i CROSS APPLY sys.fn_trace_gettable([path], DEFAULT) t JOIN sys.trace_events e ON t.EventClass = e.trace_event_id WHERE e.name IN ('Object:Created', 'Object:Deleted', 'Object:Altered') AND t.ObjectType != 21587 AND t.DatabaseID != 2 AND i.is_default = 1 AND t.EventSubClass = 1 <\/code><\/pre>\n <\/p>\n
EventType DatabaseName ApplicationName LoginName StartTime ObjectName ObjectType ---------------- -------------- ----------------------------------------------- ------------- ----------------------- ------------- -------------- Object:Created test Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:36:46.727 NULL Database Object:Created test Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:36:46.760 tbl Table Object:Altered test Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:36:46.803 tbl Table Object:Created test Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:36:46.837 ix Index Object:Deleted test Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-02-11 13:36:56.347 NULL Database <\/code><\/pre>\n 6. Server Events<\/b><\/p>\n
\u0422\u0430\u043a\u0436\u0435 \u0447\u0435\u0440\u0435\u0437 \u0434\u0435\u0444\u043e\u043b\u0442\u043d\u044b\u0439 \u0442\u0440\u0435\u0439\u0441 \u0441\u0443\u0449\u0435\u0441\u0442\u0432\u0443\u0435\u0442 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044c \u0441\u043b\u0435\u0434\u0438\u0442\u044c \u0437\u0430 \u0442\u0435\u043c, \u043a\u0442\u043e \u0438 \u043a\u043e\u0433\u0434\u0430 \u0434\u0435\u043b\u0430\u043b \u0431\u0435\u043a\u0430\u043f\u044b \u0438 \u0440\u0435\u0441\u0442\u043e\u0440\u0438\u043b \u0431\u0430\u0437\u044b:<\/p>\n
SELECT CASE WHEN t.EventSubClass = 1 THEN 'BACKUP' ELSE 'RESTORE' END , t.TextData , t.ApplicationName , t.LoginName , t.StartTime FROM sys.traces i CROSS APPLY sys.fn_trace_gettable([path], DEFAULT) t WHERE i.is_default = 1 AND t.EventClass = 115 -- Audit Backup\/Restore Event <\/code><\/pre>\n <\/p>\n
TextData ApplicationName LoginName StartTime -------------------- ------------------------------------------------ ----------- ------------------------- BACKUP DATABASE Microsoft SQL Server Management Studio - Query PC\\SergeyS 2016-01-21 13:05:26.390 RESTORE DATABASE dbForge Studio for SQL Server PC\\SergeyS 2016-01-22 12:46:45.717 BACKUP DATABASE SQLCMD sa 2016-01-24 10:16:40.317 <\/code><\/pre>\n \u0418\u043b\u0438 \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u0442\u044c \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u0435\u043d\u0438\u0435 \u043f\u0430\u043c\u044f\u0442\u0438 SQL Server<\/i>-\u043e\u043c:<\/p>\nSELECT t.StartTime , [Type] = CASE EventSubClass WHEN 1 THEN 'UP' WHEN 2 THEN 'DOWN' END , t.IntegerData FROM sys.traces i CROSS APPLY sys.fn_trace_gettable([path], DEFAULT) t WHERE t.EventClass = 81 -- Server Memory Change AND i.is_default = 1 <\/code><\/pre>\n <\/p>\n
StartTime Type IntegerData ------------------------ ----- ----------- 2016-02-10 02:52:42.887 UP 191 2016-02-10 02:53:00.640 UP 371 2016-02-10 02:53:16.780 UP 552 2016-02-10 02:53:34.917 UP 734 2016-02-10 02:53:52.140 UP 916 2016-02-10 10:05:00.027 DOWN 736 2016-02-10 10:17:17.417 UP 921 2016-02-10 11:52:14.930 DOWN 735 2016-02-10 12:00:32.577 DOWN 553 2016-02-10 13:06:11.540 UP 751 2016-02-10 15:11:10.487 UP 936 2016-02-10 15:15:26.107 DOWN 714 2016-02-10 15:31:19.453 UP 894 2016-02-10 15:42:03.383 DOWN 698 2016-02-10 15:53:42.490 DOWN 518 2016-02-10 21:14:38.363 UP 697 <\/code><\/pre>\n \u0412\u044b\u0432\u043e\u0434\u044b<\/b><\/p>\n
\u0414\u0435\u0444\u043e\u043b\u0442\u043d\u044b\u0439 \u0442\u0440\u0435\u0439\u0441 \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0434\u043e\u0441\u0442\u0430\u0442\u043e\u0447\u043d\u043e \u043c\u043e\u0449\u043d\u044b\u043c \u0441\u0440\u0435\u0434\u0441\u0442\u0432\u043e\u043c \u0434\u043b\u044f \u043e\u0442\u0441\u043b\u0435\u0436\u0438\u0432\u0430\u043d\u0438\u044f \u0441\u043e\u0441\u0442\u043e\u044f\u043d\u0438\u044f \u0441\u0435\u0440\u0432\u0435\u0440\u0430. \u0420\u0430\u0437\u0443\u043c\u0435\u0435\u0442\u0441\u044f, \u043e\u043d \u0438\u043c\u0435\u0435\u0442 \u043c\u043d\u043e\u0433\u043e \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u0439, \u043d\u0430\u043f\u0440\u0438\u043c\u0435\u0440, \u0443\u043f\u043e\u043c\u044f\u043d\u0443\u0442\u043e\u0435 \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u0435 \u0432 20\u041c\u0431. \u041d\u043e \u0432\u0441\u0435 \u0436\u0435 \u0438\u043d\u043e\u0433\u0434\u0430 \u043e\u043d \u0431\u044b\u0432\u0430\u0435\u0442 \u043e\u0447\u0435\u043d\u044c \u043f\u043e\u043b\u0435\u0437\u043d\u044b\u043c \u043d\u0430 \u044d\u0442\u0430\u043f\u0435 \u043f\u0435\u0440\u0432\u0438\u0447\u043d\u043e\u0439 \u0434\u0438\u0430\u0433\u043d\u043e\u0441\u0442\u0438\u043a\u0438 \u043f\u0440\u043e\u0431\u043b\u0435\u043c \u0441 SQL Server<\/i>. \u041d\u0430\u0434\u0435\u044e\u0441\u044c \u043c\u043e\u0438 \u043f\u0440\u0438\u043c\u0435\u0440\u044b \u044d\u0442\u043e \u043d\u0430\u0433\u043b\u044f\u0434\u043d\u043e \u0441\u043c\u043e\u0433\u043b\u0438 \u043f\u043e\u043a\u0430\u0437\u0430\u0442\u044c. <\/p>\n<\/div>\n
\u0441\u0441\u044b\u043b\u043a\u0430 \u043d\u0430 \u043e\u0440\u0438\u0433\u0438\u043d\u0430\u043b \u0441\u0442\u0430\u0442\u044c\u0438 https:\/\/habrahabr.ru\/post\/277053\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" ![](\"https:\/\/habrastorage.org\/files\/c20\/dff\/841\/c20dff841cb74ee68676f2499f76dc45.jpg\")
\u0427\u0430\u0441\u0442\u043e \u043c\u043d\u0435 \u0432\u0441\u043f\u043e\u043c\u0438\u043d\u0430\u0435\u0442\u0441\u044f \u043f\u0435\u0440\u0432\u0430\u044f \u0440\u0430\u0431\u043e\u0442\u0430. \u0421\u0440\u0435\u0434\u043d\u0435\u043d\u044c\u043a\u0438\u0439 \u043e\u0444\u0438\u0441, \u043c\u043e\u043d\u0438\u043a 943N<\/i> \u0438 \u043e\u0431\u043e\u0433\u0440\u0435\u0432\u0430\u0442\u0435\u043b\u044c Pentium D<\/i> \u043f\u043e\u0434 \u043d\u043e\u0433\u0430\u043c\u0438. \u041a\u0430\u043a \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u043b \u0438\u0437 \u043d\u0438\u043e\u0442\u043a\u0443\u0434\u0430 \u0411\u043e\u0440\u0438\u0441 (\u043d\u0435\u0442\u2026 \u043d\u0435 \u0411\u043e\u0440\u0438\u0441-\u0431\u0440\u0438\u0442\u0432\u0430) \u0441 \u043b\u0438\u043d\u0435\u0439\u043a\u043e\u0439 \u0432 \u0440\u0443\u043a\u0430\u0445 \u0438 \u043d\u0430\u0441\u0442\u043e\u0439\u0447\u0438\u0432\u043e \u043f\u0440\u043e\u0441\u0438\u043b \u043d\u0435 \u0434\u0435\u043b\u0430\u0442\u044c \u00ab\u0431\u043e\u043b\u044c\u043d\u043e\u00bb SQL Server<\/i>.<\/p>\n
\u0418\u043c\u0435\u043d\u043d\u043e \u0432 \u0442\u0435 \u0434\u0430\u043b\u0435\u043a\u0438\u0435 \u0432\u0440\u0435\u043c\u0435\u043d\u0430 \u044f \u0432\u043f\u0435\u0440\u0432\u044b\u0435 \u043f\u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u043b\u0441\u044f \u0441 \u043f\u0440\u043e\u0444\u0430\u0439\u043b\u0435\u0440\u043e\u043c. \u041f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u0441\u043a\u0438\u0435 \u0442\u0440\u0435\u0439\u0441\u044b \u043e\u043a\u0430\u0437\u0430\u043b\u0438\u0441\u044c \u043e\u0447\u0435\u043d\u044c \u043a\u0441\u0442\u0430\u0442\u0438 \u043f\u0440\u0438 \u043e\u0442\u043b\u0430\u0434\u043a\u0435 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u0439 \u0438 \u043f\u043e\u0438\u0441\u043a\u0435 \u043c\u0435\u0434\u043b\u0435\u043d\u043d\u044b\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432. \u041f\u043e\u0442\u043e\u043c \u0434\u043b\u044f \u0441\u0435\u0431\u044f \u044f \u043e\u0442\u043a\u0440\u044b\u043b DMV<\/i> \u0438 XEvents<\/i>\u2026 \u0438 \u043f\u0440\u043e\u0444\u0430\u0439\u043b\u0435\u0440\u043e\u043c \u0441\u0442\u0430\u043b \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c\u0441\u044f \u0440\u0435\u0436\u0435. \u041f\u0440\u0438\u0447\u0438\u043d\u0430 \u0442\u0430\u043a\u043e\u0433\u043e \u043f\u043e\u0441\u0442\u0443\u043f\u043a\u0430 \u043f\u0440\u043e\u0441\u0442\u0430 \u2013 \u0442\u0440\u0435\u0439\u0441\u044b \u043e\u0447\u0435\u043d\u044c \u0440\u0435\u0441\u0443\u0440\u0441\u043e\u0435\u043c\u043a\u0438\u0435.<\/p>\n
\u041e\u0434\u043d\u0430\u043a\u043e, \u0434\u0430\u043d\u043d\u044b\u0439 \u0444\u0443\u043d\u043a\u0446\u0438\u043e\u043d\u0430\u043b \u043d\u0435 \u0441\u0442\u043e\u0438\u0442 \u043f\u0440\u0435\u0436\u0434\u0435\u0432\u0440\u0435\u043c\u0435\u043d\u043d\u043e \u043f\u0440\u0438\u0434\u0430\u0432\u0430\u0442\u044c \u0430\u043d\u0430\u0444\u0435\u043c\u0435. \u041d\u0430\u0447\u0438\u043d\u0430\u044f \u0441 2005<\/i> \u0432\u0435\u0440\u0441\u0438\u0438 \u043f\u0440\u0438 \u0443\u0441\u0442\u0430\u043d\u043e\u0432\u043a\u0435 SQL Server<\/i> \u043f\u043e \u0443\u043c\u043e\u043b\u0447\u0430\u043d\u0438\u044e \u0441\u043e\u0437\u0434\u0430\u0435\u0442\u0441\u044f \u043b\u0435\u0433\u043a\u043e\u0432\u0435\u0441\u043d\u044b\u0439 \u0441\u0438\u0441\u0442\u0435\u043c\u043d\u044b\u0439 \u0442\u0440\u0435\u0439\u0441, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0445\u0440\u0430\u043d\u0438\u0442 \u0432 \u0441\u0435\u0431\u0435 \u043c\u043d\u043e\u0433\u043e \u043f\u043e\u043b\u0435\u0437\u043d\u043e\u0439 \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u0438. <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-274094","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/274094","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=274094"}],"version-history":[{"count":0,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=\/wp\/v2\/posts\/274094\/revisions"}],"wp:attachment":[{"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=274094"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=274094"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/savepearlharbor.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=274094"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}