Microsoft SQL Server Error Log Scanning

SQL Server has an abundance of information in the ERROR.LOG file, from restart information to errors that are logged. Reviewing the ERROR.LOG can be a long and time consuming task especially if you are managing multiple SQL Server installations. However you usually need to see only the error information and not the informational entries. This article will help you with tracking the important information from the errorlog.
To accomplish this task here is what I've done to help.

1. Create a database and table to hold all the data from current ERROR.LOG
2. Truncate table and upload the current ERROR.LOG
3. Execute T-SQL script to analyze the data and produce an output with only error type information

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DVW9naE9BZFZQNkE/edit?usp=sharing

This article will walk you through these steps.

First we need to create a database and a table to hold the error log data

-- Create a database
USE [master]
GOCREATE DATABASE [ErrorLogStorage] ON PRIMARY ( NAME = N'SQLSystemsSupport', FILENAME = N'D:\SQLDATA\MSSQL.1\MSSQL\Data\SQLSystemsSupport.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON ( NAME = N'SQLSystemsSupport_log', FILENAME = N'D:\SQLDATA\MSSQL.1\MSSQL\Data\SQLSystemsSupport_log.ldf' , SIZE = 64128KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
-- Create a table
USE [ErrorLogStorage]
GOCREATE TABLE [dbo].[ErrLogData](
 [LogID] [int] IDENTITY(1,1) NOT NULL,
 [LogDate] [datetime] NULL,
 [ProcessInfo] [nvarchar](50) NULL,
 [LogText] [nvarchar](4000) NULL,
 [SQLServerName] [nvarchar](150) NULL,
PRIMARY KEY CLUSTERED (
 [LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Next we will clear the table and insert the ERROR.LOG data into the table. The script below will also add the SQL Server's name to the table. This is done so that you can have data from many SQL Servers inserted into the same table.

USE [ErrorLogStorage]
GO
-- Remove older data 
IF EXISTS (SELECT * FROM [ErrorLogStorage].[dbo].[ErrLogData])
 BEGIN
 TRUNCATE TABLE [ErrorLogStorage].[dbo].[ErrLogData] 
 END
 DECLARE @SQLCmd VARCHAR(1024)
 SELECT @SQLCmd = 'Insert Into [ErrorLogStorage].dbo.ErrLogData (LogDate, ProcessInfo, LogText) Exec master..xp_readerrorlog '
 EXEC (@SQLCmd)
-- Cycle through the ErrLogData table and insert the server's name
DECLARE SrvName_Cursor CURSOR FOR
 SELECT [SQLServerName] FROM [ErrorLogStorage].[dbo].[ErrLogData] WHERE [SQLServerName] IS NULL
 OPEN SrvName_Cursor
 FETCH NEXT FROM SrvName_Cursor
 WHILE @@FETCH_STATUS = 0
 BEGIN
 UPDATE [ErrorLogStorage].[dbo].[ErrLogData] SET [SQLServerName] = @@servername
 FETCH NEXT FROM SrvName_Cursor
 END
CLOSE SrvName_CursorDEALLOCATE SrvName_Cursor
GO

Now we can analyze the data collected with a query. Modify the WHERE clause to show more or less data.

SELECT [LogID]
 ,[LogDate]
 ,[ProcessInfo]
 ,[LogText]
 ,[SQLServerName]
FROM [ErrorLogStorage].[dbo].[ErrLogData]
WHERE ([logtext] NOT LIKE '% found 0 errors %'
 AND [logtext] NOT LIKE '%\ERRORLOG%'
 AND [logtext] NOT LIKE '%Attempting to cycle errorlog%'
 AND [logtext] NOT LIKE '%Errorlog has been reinitialized.%'
 AND [logtext] NOT LIKE '%found 0 errors and repaired 0 errors.%'
 AND [logtext] NOT LIKE '%without errors%'
 AND [logtext] NOT LIKE '%This is an informational message%'
 AND [logtext] NOT LIKE '%Setting database option ANSI_WARNINGS%'
 AND [logtext] NOT LIKE '%Error: 15457, Severity: 0, State: 1%'
 AND [logtext] NOT LIKE '%finished without errors%')
ORDER BY [SQLServerName] ,[LogID]
GO

The line '%Error: 15457, Severity: 0, State: 1%' does not actually indicate an error but is more informational, which is why I do not show this data.

Here is a sample output from the script above.

25021 2010-09-01 12:01:23' spid12 The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity. DEVSRV1
25022 2010-09-01 12:01:25' spid12 REVOKE ALL ON [sp_dump_dtstasklog] FROM PUBLIC DEVSRV1
25023 2010-09-01 12:01:26' spid12 The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity. DEVSRV1
25024 2010-09-01 12:01:28' Logon Error: 18401, Severity: 14, State: 1. DEVSRV1
25025 2010-09-01 12:01:29' Logon Login failed for user 'DBATester'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. [CLIENT: 10.10.6.215] DEVSRV1

There are many ways to look for specific information in the error log and you can customize the script to show specific information. Personally, I have created a scheduled job to automatically populate the table with error logs of all my SQL servers, which helps when I need to review an issue that effects other SQL Servers. With the error information in a table you can also create a report in SSRS to show this data in a nicer format.

I hope this helps in your ability to review the ERROR.LOG file for your SQL servers.

Thanks,
Rudy

Identify SQL Server's Port Number

Just copy and paste the T-SQL code and execute to get the port number used by SQL server.
This is how it works.

•Determine which version of SQL server you are running
•Look at the specific location in the registery
•Display the port number

I have tested this script on the following versions
•SQL 2000 - 32 bit
•SQL 2005 - 32 and 64 bit
•SQL 2008 - 32 and 64 bit
•SQL 2008R2 - 32 and 64 bit

It is quick, simple and could be executed on develoopment and production servers as there
are not changes being made to SQL server and/or the Window's registery.
You will need to ensure you have rights to read the register in order for the script to
execute properly and must be executed on each instance (this version) to get the port number)

Thanks,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DWGdOMWRZYXRGYjg/edit?usp=sharing

Rebuild SQL Server Master Databases in Less than 5 Minutes

Rebuild SQL Server Master Databases in Less than 5 Minutes

Have you ever has an issues with a SQL server (2000, 2005, 2008 and so on) that you needed to rebuilt the master database? I've had that problem not too long ago and had to spend some time researching how this can be done. Here is a good site with the information you will need to rebuild the master database (http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx). But with a little work you can rebuild the master and the other system databases in less than 5 minutes. Let's see how this can be done.

It's All Proactive

To quickly rebuild the master and system database you need to have done some proactive work before you need to do a rebuild. Here is a quick and easy list of task to do proactively.

1. Stop all SQL server services
2. Copy the master.mdf and master.ldf files from the SQL server's data folder to another folder
3. Restart all SQL server services

Now you have performed all you proactive tasks. A backup of these files exists in their native format. This is not a backup done via SQL server but a command line level or file level backup.

Time to Rebuild

So now what? Now when a master database has to be rebuilt due to corruption of some kind you can quick get back to a good state by performing the following.

1. Stop all SQL server services
2. Rename the master.mdf to master.mdf.old and master.ldf to master.ldf.old
3. Copy the master.mdf and master.ldf files from your folder to the SQL server's data folder
4. Restart all SQL server services
5. Log into SQL server to ensure you can connect
6. Perform a restore of the master database from your backups

At this point your SQL server will start up as normal and you can then continue to perform your additional tasks as needed.

Real World Example

Here is my real world example. At a disaster recovery test I had to build a SQL server 2008 environment and restore all of the SQL server's databases. This included all user and system databases. After the initial installation of SQL server 2008 I copied the master database files to a new folder I called 'D:\OrgFileBackup'. Then I performed the restore of the master database from our tape backups. During the restore an error occurred and the master database became corrupted. I could no longer log into SQL server nor restore any database. My only choice was to rebuild the master database. I performed the steps above and was able to connect to SQL server and perform my restores.
If I had to rebuild the master database or worse uninstall SQL server and reinstall SQL server completely the restore time for the whole environment would have taken a lot longer.

Rebuild Completed

This process is not intended to replace your backups but to enhance it. Now that you can log into SQL server you can perform a restore of the master database. You must restore from backups to get all the latest information back into the master database.
This process will not cure everything, but it is a faster way to rebuild your master database.

Hope you find this helpful,

Rudy