In this blog I will show you how to analyze logs like a pro, using four of my favorite methods: manually, Excel, command line, and finally, Splunk. This add-on uses the sys.fn_get_audit_file function via DB Connect.Hi there! My name is Meredith and I love to eat logs ( they’re big, they’re heavy, they’re wood) for Breakfast, Lunch, Dinner, and as an awesome 2am snack. Audit logs can be read by the sys.fn_get_audit_file system function. See Create audit objects in Microsoft SQL Server for more information. SQL Server audit lets you create server audits for server-level, database-level, and table-level events. This add-on uses the fn_trace_gettable system function via DB Connect. You can open default trace logs with SQL Server Profiler or query them with Transact-SQL by using the fn_trace_gettable system function. Mssql:transaction:dm_tran_current_transactionĭefault trace provides troubleshooting support. Mssql:transaction:dm_tran_session_transactions Mssql:transaction:dm_tran_current_snapshot Mssql:index:dm_db_index_operational_statsĭm_tran_active_snapshot_database_transactions Mssql:execution:dm_exec_query_optimizer_info Mssql:execution:dm_exec_query_memory_grants Mssql:execution:dm_exec_background_job_queue Mssql:database:dm_db_persisted_sku_features Mssql:database:dm_db_fts_index_physical_stats Mssql:database:dm_db_uncontained_entities Mssql:alwayson:dm_hadr_database_replica_states Source types collected through Splunk DB Connect Data from Dynamic Management View Typeĭm_hadr_availability_replica_cluster_nodesĭm_hadr_availability_replica_cluster_states Transactions Longest Transaction Running Time Number of Deadlocks/sec Average Wait Time (ms) Latch Waits/sec Avg Latch Wait Time (ms) Total Latch Wait Time (ms) User Connections Processes blocked Logins/sec Logout/secīatch Requests/sec SQL Compilations/sec SQL re-Compilations/sec SQL Attention Rate/sec Auto-Param Attempts/sec Failed Auto-Params/sec Safe Auto-Params/sec Unsafe Auto-Params/secįorwarded Records/sec Full Scans/sec Index Searches/sec Page Splits/sec Workfiles Created/sec Worktables Created/sec Worktables From Cache Ratio Table Lock Escalations/sec Processor Queue Length Context Switches/sec Disk sec/Transfer Disk Read Bytes/sec Disk Write Bytes/sec Avg. Total Server Memory (KB) Granted Workspace Memory (KB) Maximum Workspace Memory (KB) Memory Grants Outstanding Memory Grants Pending Target Server Memory (KB)ĭisk Reads/sec Disk Writes/sec Avg. % Committed Bytes In Use Pages/sec Available Mbytes PagesĪctive Transactions Data File(s) Size (KB) Log File(s) Size (KB) Log File(s) Used Size (KB) Transactions/sec Source types collected through Windows Performance Monitoring Object Example Location: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT The agent log records SQL Server agent service related activities.Īfter you install and start the Microsoft SQL Server agent, the server creates this log file under the SQL Server installation folder. Example Location: C:\Program Files\Microsoft SQL Server\ The error log contains error messages as well as some activities of SQL Server.Īfter you install and start Microsoft SQL Server, the server creates this log file under the SQL Server installation folder. Source types collected through file monitoring Log It collects data via file monitoring, Windows Performance Monitoring, and through Splunk DB Connect: The Splunk Add-on for Microsoft SQL Server collects different kinds of data from Microsoft SQL Server and assigns a source type for each kind of data. Source types for the Splunk Add-on for Microsoft SQL Server
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |