----------------------------------------------------------------------------------------------------------------------------------------------------
--This script uses the PrepareLogTables procedure and then SELECT statement to 
--analyze the SQL Server and SQL Server Agent log files.
----------------------------------------------------------------------------------------------------------------------------------------------------
USE sqlmaint
GO

----------------------------------------------------------------------------------------------------------------------------------------------------
--Populate tables
----------------------------------------------------------------------------------------------------------------------------------------------------
EXEC PrepareLogTables 4, 4

----------------------------------------------------------------------------------------------------------------------------------------------------
--SQL Server logs
----------------------------------------------------------------------------------------------------------------------------------------------------

--Size of SqlLogs table
EXEC sp_tableinfo 'SqlLogs' -- http://www.karaszi.com/SQLServer/util_sp_tableinfo.asp

--Timespan
SELECT MIN(LogDate) AS FirstEntryErrLog, MAX(LogDate) AS LastEntryErrLog FROM SqlLogs


--Delete entries of non-interest from SQL Server log, this so the analysis part will be easier.
--I recommend that you quickly browse through each LIKE below and if you feel you want to 
--analyse messages for some certain LIKE condition, then just comment or remove it from below DELETE.
--Pay particular attention to the last some 20 LIKE conditions.
DELETE FROM SqlLogs
WHERE text LIKE '(c) Microsoft Corporation.'
   OR text LIKE 'All rights reserved.'
   OR text LIKE 'Server process ID is %.'
   OR text LIKE 'System Manufacturer: %, System Model: %.'
   OR text LIKE 'Authentication mode is %.'
   OR text LIKE 'Logging SQL Server messages in file %.'
   OR text LIKE 'The service account is %.'
   OR text LIKE 'Registry startup parameters:%'
   OR text LIKE 'Command Line Startup Parameters:%'
   OR text LIKE '%cores per socket and%'
   OR text LIKE 'Detected % CPUs. This is an informational message; no user action is required.'
   OR text LIKE 'SQL Server is starting at %'
   OR text LIKE 'Detected % of RAM. This is an informational message; no user action is required.'
   OR text LIKE 'Using conventional memory in the memory manager.'
   OR text LIKE 'This instance of SQL Server last reported using a process ID of %'
   OR text LIKE 'Node configuration: node %'
   OR text LIKE 'Using dynamic lock allocation.  Initial allocation of%'
   OR text LIKE 'Lock partitioning is enabled.  This is an informational message only. No user action is required.'
   OR text LIKE 'Software Usage Metrics is disabled.'
   OR text LIKE 'CLR version v4.0.30319 loaded.'
   OR text LIKE 'Common language runtime (CLR) functionality initialized using CLR version %'
   OR text LIKE 'SQL Server Audit is starting the audits. This is an informational message.%'
   OR text LIKE 'SQL Server Audit has started the audits. This is an informational message.%'
   OR text LIKE 'Server name is %. This is an informational message only. No user action is required.'
   OR text LIKE 'A self-generated certificate was successfully loaded for encryption.'
   OR text LIKE 'Server is listening on %.'
   OR text LIKE 'Server local connection provider is ready to accept connection on %.'
   OR text LIKE 'Dedicated admin connection support was established for listening locally on port %.'
   OR text LIKE 'SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service.%'
   OR text LIKE 'SQL Server is now ready for client connections. This is an informational message; no user action is required.'
   OR text LIKE 'The SQL Server Network Interface library could not register the Service Principal Name (SPN)%'
   OR text LIKE 'The SQL Server Network Interface library successfully registered the Service Principal Name (SPN)%'
   OR text LIKE 'A new instance of the full-text filter daemon host process has been successfully started.'
   OR text LIKE 'The resource database build version is %'
   OR text LIKE 'The Service Broker endpoint is in disabled or stopped state.'
   OR text LIKE 'The Database Mirroring endpoint is in disabled or stopped state.'
   OR text LIKE 'Service Broker manager has started.'
   OR text LIKE 'Recovery is complete. This is an informational message only. No user action is required.'
   OR text LIKE 'Resource governor reconfiguration succeeded.'
   OR text LIKE 'SQL Trace ID 1 was started by login "sa".'
   OR text LIKE 'SQL Trace was stopped due to server shutdown. Trace ID = %'
   OR text LIKE 'The tail of the log for database master is being rewritten to match the new sector size of %'
   OR text LIKE 'Recovery is writing a checkpoint in database %. This is an informational message only. No user action is required.'
   OR text LIKE 'Recovery completed for database % This is an informational message only. No user action is required.'
   OR text LIKE 'Recovery of database % is % complete (approximately % seconds remain). %. This is an informational message only. No user action is required.'
   OR text LIKE 'FILESTREAM: effective level = %, configured level = %, file system access share name = %.'
   OR text LIKE 'The Service Broker protocol transport is disabled or not configured.'
   OR text LIKE 'The Database Mirroring protocol transport is disabled or not configured.'
   OR text LIKE 'Server named pipe provider is ready to accept connection on %'
   OR text LIKE 'Clearing tempdb database.'
   OR text LIKE 'Configuration option ''Agent XPs'' changed from 0 to 1. Run the RECONFIGURE statement to install.'
   OR text LIKE 'Attempting to load library ''xpsqlbot.dll'' into memory. This is an informational message only. No user action is required.'
   OR text LIKE 'Attempting to load library ''xpstar.dll'' into memory. This is an informational message only. No user action is required.'
   OR text LIKE 'Attempting to load library ''xplog70.dll'' into memory. This is an informational message only. No user action is required.'
   OR text LIKE 'Using ''xpsqlbot.dll'' version % to execute extended stored procedure ''xp_qv''. This is an informational message only; no user action is required.'
   OR text LIKE 'Using ''xpstar.dll'' version % to execute extended stored procedure ''xp_instance_regread''. This is an informational message only; no user action is required.'
   OR text LIKE 'Using ''xplog70.dll'' version % to execute extended stored procedure ''xp_msver''. This is an informational message only; no user action is required.'
   OR text LIKE 'Using ''dbghelp.dll'' version %'
   OR text LIKE 'This instance of SQL Server has been using a process ID of % since %'
   OR text LIKE 'Starting up database %.'
   OR text LIKE 'Database backed up. Database: %'
   OR text LIKE 'Database differential changes were backed up. Database: %'
   OR text LIKE 'Log was backed up. Database:%'
   OR text LIKE '% transactions rolled forward in database %. This is an informational message only. No user action is required.'
   OR text LIKE '% transactions rolled back in database %. This is an informational message only. No user action is required.'
   OR text LIKE 'I/O is frozen on database%'
   OR text LIKE 'I/O was resumed on database%'
   OR text LIKE 'Setting database option %'
   OR text LIKE 'Error: 1205, Severity: 13, State:%'
   OR text LIKE 'CHECKDB for database % finished without errors on %'
   OR text LIKE 'DBCC CHECKDB (%'
   OR text LIKE 'Error: 3041, Severity: 16, State: %.'
   OR text LIKE 'Error: 18456%' --These will be followed by a verbose message anyhow
   OR text LIKE 'Error: 18056%'
   OR text LIKE 'Error: 28054%'
   OR text LIKE 'Error: 17806%'
   OR text LIKE 'Error: 18452%'
   OR text LIKE 'Error: 18210%'
   OR text LIKE 'Error: 17054%'


--Work the WHERE clause
SELECT * FROM SqlLogs
WHERE LogDate > '2012-01-25'
--AND text NOT LIKE 'Login failed for user %'
--AND text NOT LIKE 'Transaction (Process ID %) was deadlocked on lock %resources with another process and has been chosen as the%'
--AND text NOT LIKE 'SQL Trace%'
--AND text NOT LIKE 'The database % is marked RESTORING and is in a state that does not allow recovery to be run.'
--AND text NOT LIKE 'Restore is complete on database %.  The database is now available.'
--AND text NOT LIKE 'Database was restored: Database: %'
--AND text NOT LIKE 'BACKUP failed to complete the command %. Check the backup application log for detailed messages.'
--AND text NOT LIKE 'Error: 18204, Severity: 16, State: 1.'
--AND text NOT LIKE '%: Backup device % failed to open. Operating system error %.'
--AND text NOT LIKE 'Error: 18210, Severity: 16, State: 1.'
--AND text NOT LIKE 'BackupIoRequest::ReportIoError: write failure on backup device %. Operating system error %'
--AND text NOT LIKE 'BackupIoRequest::ReportIoError: read failure on backup device %. Operating system error %'
--AND text NOT LIKE 'AppDomain % unloaded.'
--AND text NOT LIKE 'AppDomain % created.'
--AND text NOT LIKE 'Configuration option % changed from % to %. Run the RECONFIGURE statement to install.'
--AND text NOT LIKE 'Error: 1479, Severity: 16, State: 1.'
--AND text NOT LIKE 'The mirroring connection to%'
--AND text NOT LIKE 'Error: 1105, Severity: 17, State: 2.%'
--AND text NOT LIKE 'Could not allocate space for object%'
ORDER BY LogDate


----------------------------------------------------------------------------------------------------------------------------------------------------
--Agent logs
----------------------------------------------------------------------------------------------------------------------------------------------------

--Size of SqlLogs table
EXEC sp_tableinfo 'AgentLogs' -- http://www.karaszi.com/SQLServer/util_sp_tableinfo.asp

--Timespan
SELECT MIN(LogDate) AS FirstEntryAgentLog, MAX(LogDate) AS LastEntryAgentLog FROM AgentLogs


--Delete entries of non-interest from Agent log
--I recommend that you quickly browse through each LIKE below and if you feel you want to 
--analyse messages for some certain LIKE condition, then just comment or remove it from below DELETE.
DELETE FROM AgentLogs
WHERE text LIKE '% The SQL Server Agent startup service account is%'
   OR text LIKE '% Configuration option % changed from 0 to 1%'
   OR text LIKE '% SQL Server % version % (% connection limit)'
   OR text LIKE '% SQL Server ODBC driver version %'
   OR text LIKE '% NetLib being used by driver is %; Local host server is %'
   OR text LIKE '% processor(s) and % MB RAM detected'
   OR text LIKE '% Local computer is % running %'
   OR text LIKE '% There are % subsystems in the subsystems cache'
   OR text LIKE '% The Messenger service has not been started - NetSend notifications will not be sent'
   OR text LIKE '% starting under Windows NT service control'
   OR text LIKE '% Database Mail is not enabled for agent notifications.'
   OR text LIKE '% An idle CPU condition has not been defined - OnIdle job schedules will have no effect'
   OR text LIKE '% Configuration option % changed from 1 to 0%'
   OR text LIKE '% % service stopping due to a stop request from a user, process, or the OS...'
   OR text LIKE '% Waiting for SQL Server to recover database ''msdb''...'


--Work the WHERE clause
SELECT * FROM AgentLogs
WHERE LogDate > '2012-01-25'
--AND teext NOT LIKE '%Unable to open the eventlog on forwarding server %'
--AND text NOT LIKE '%Request to run job ExecuteWebpayReplicationService%'
--AND text NOT LIKE '%SQLServer Error: 18456, Login failed for user %'
--AND text NOT LIKE '%SQLServer Error: 4060, Cannot open database %'
--AND text NOT LIKE 'Reloading agent settings'
ORDER BY LogDate