SQL Server – Number of Error Log Files

 

Background

Trying to review error logs on one of our SQL Server Instances and discovered that it is likely that the error logs we might have to mine is so far back and we might no longer have it.
And, so took to see if we have properly calibrated the Number of Error Logs Files to keep before rollover.

 

Review

GUI

SQL Server Management Studio ( SSMS )

Screen Shot

configuresqlservererrorlogs

 

Explanation

  1. In the screenshot above we can see that the Number of Errors has not been explicit set and so the system will use the default value of 6
  2. To set it we can check the “Limit the number of error log files before they are recycled” and provide a number to set it to

 

Script

For automation, script is a bit better and here is one …

Code

Stored Procedure

Stored Procedure – errorLog.sp_ConfigureNumberofErrorLogFiles


set nocount on;
go

USE [master]
GO

if schema_id('errorLog') is null
begin
	
	exec('create schema [errorLog] authorization [dbo] ')

end
go

if object_id('[errorLog].[sp_ConfigureNumberofErrorLogFiles]') is null
begin

	exec('create procedure [errorLog].[sp_ConfigureNumberofErrorLogFiles] as ')

end
go

alter procedure [errorLog].[sp_ConfigureNumberofErrorLogFiles] 
(
	  @numberofErrorLogsDesired int = 99
	, @scriptOnly bit = 0
)
as 
begin

	set nocount on;
	set XACT_ABORT on;

	declare @strNumberofErrorLogsCurrent	nvarchar(60)
	declare @iNumberofErrorLogsDefault		int
	declare @iNumberofErrorLogsCurrent		int
	declare @numberofErrorLogsMax			int

	declare @strLog							varchar(120)
	
	/*
		Set default values
	*/
	set @numberofErrorLogsMax = 99
	set @strNumberofErrorLogsCurrent = null
	set @iNumberofErrorLogsDefault = -1
	set @iNumberofErrorLogsCurrent = @iNumberofErrorLogsDefault

	if (@numberofErrorLogsDesired > 99)
	begin

		set @numberofErrorLogsDesired = @numberofErrorLogsMax

		set @strLog = 'Maximum Number of errors specified is : ' 
						+ cast(@numberofErrorLogsDesired as varchar(10))
						+ ' but max allowed is '		
						+ cast(@numberofErrorLogsMax as varchar(10))
						+ '!'
						+ ' will use ' 
						+ cast(@numberofErrorLogsMax as varchar(10))

		print @strLog

	end	

	/*
		Read Registry Value
			HKEY_LOCAL_MACHINE
			Branch : Software\Microsoft\MSSQLServer\MSSQLServer
			Item   :- NumErrorLogs
	*/
	exec master.dbo.xp_instance_regread
			  N'HKEY_LOCAL_MACHINE'
			, N'Software\Microsoft\MSSQLServer\MSSQLServer'
			, N'NumErrorLogs'
			, @iNumberofErrorLogsCurrent output

	/*
		If Number of Error Logs is set, then  convert to string
	*/
	if (@iNumberofErrorLogsCurrent is not null )
	begin


		/*
			If Number of Current Logs has been set
		*/
		if (
				(@iNumberofErrorLogsCurrent != @iNumberofErrorLogsDefault )
			)
		begin

			print '@iNumberofErrorLogsCurrent : ' 
					+ cast(
							isNull(@iNumberofErrorLogsCurrent, '')
								as varchar(10)
						  )


		end

		set @strNumberofErrorLogsCurrent
				= cast
					(
						isNull(@iNumberofErrorLogsCurrent, -1)
						as varchar(10)
					)


		/*
			If String is not null
		*/
		if (
				( @strNumberofErrorLogsCurrent is not null )
		   )
		begin

			set @strLog = '@strNumberofErrorLogsCurrent : ' 
								+ @strNumberofErrorLogsCurrent

			print @strLog

		end


	end -- if (@iNumberofErrorLogsCurrent is not null )


	/*
		If Number of Errors Logs is not set to what we desire, then review it
	*/
	if (
			( @iNumberofErrorLogsCurrent != @numberofErrorLogsDesired )
		)
	begin

		if (@scriptOnly = 0)
		begin

			set @strLog = 'Adjusting Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )
						+ ' .. '

			print @strLog


			EXEC master.dbo.xp_instance_regwrite 
						  N'HKEY_LOCAL_MACHINE'
						, N'Software\Microsoft\MSSQLServer\MSSQLServer'
						, N'NumErrorLogs'
						, REG_DWORD
						, @numberofErrorLogsDesired

			set @strLog = 'Adjusted Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )

			print @strLog

		end --script only
		else
		begin

			set @strLog = 'Skipping Adjustment of Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )

			print @strLog

		end
	end

end

GO


Invoke


use [master]
go

declare @numberofErrorLogsDesired int
declare @scriptOnly				  bit

set @numberofErrorLogsDesired = 99
set @scriptOnly =0

exec [errorLog].[sp_ConfigureNumberofErrorLogFiles] 
		  @numberofErrorLogsDesired = @numberofErrorLogsDesired
		, @scriptOnly = @scriptOnly

Output

RegQueryValueEx() returned error 2 – The System cannot find the file specified

Image

cannotfindthefilespecified

Textual
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1
@strNumberofErrorLogsCurrent : -1
Adjusting Number of Error Logs from -1 to 99 .. 
Adjusted Number of Error Logs from -1 to 99

Explanation

By default, the value is missing and so when we invoke master.dbo.xp_instance_regread to read it, we receive a warning message that reads “RegQueryValueEx() returned error 2, ‘The system cannot find the file specified.’

 

Registry

The actual registry branch where the information is kept is dependent on the version of the SQL Server Engine and the Instance Name

v2012

Here us what things look list for a SQL Server 2012 Instance, before setting the value

By Default

Image

v2012-default

Explanation

  1. This registry branch above is for a SQL Server v2012 instance
  2. The branch is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer

 

Post Setting

Once setting the value for Number of Error Logs to keep our registry will look like what we have set below.

v2012-set

 

Source Control

GitHub

Uploaded to github here

SQL Server – Reading Instance Configuration saved in Registry

Background

For a script that I am working on, I need to access System Properties such as the base folder of the errorlog.

 

SQL Server Management Studio ( SSMS )

Of course the information is available via SSMS, just click on the Server’s Property and you will see something like this…

sqlserver-mssqlserver-20170119-1107am

 

Explanation

  1. We can that “Root Directory” is “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL

 

Registry

Registry – CPE

Knowing the value that we are searching for took to the registry and searched for “C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL“.

Ended up here…

sqlserver-cpe

Registry – Setup

Most of the most important information is saved in the Setup branch.

sqlserver-setup-20170119-1126am

 

Registry – MSSQLServer

Btw, the MSSQLServer path is very important, as well.

sqlserver-mssqlserver-20170119-1116am

Code

xp_instance_regread

Let us read ErrorDumpDir from the CPE branch

 

Code Snippet


DECLARE @rootKey	 NVARCHAR(500)
DECLARE @path		 NVARCHAR(500)
DECLARE @name		 NVARCHAR(500)
DECLARE @value		 NVARCHAR(1000)

set @rootKey = N'HKEY_LOCAL_MACHINE'
set @path    = N'SOFTWARE\Microsoft\MSSQLServer\CPE'
set @name    = N'ErrorDumpDir'

EXEC master..xp_instance_regread
         @rootkey = @rootKey
       , @path    = @path
       , @name	  = @name
       , @value   = @value output

PRINT 'Error Dump Dir :' + isNull(@value, '')


 

Output

errordump

 

Knowledge Base

Please keep in mind that xp_instance_regread is an extended Stored Procedure.

As there could be a few SQL Server Instances on a box, Microsoft abstracted things a bit and xp_instance_regread is able to read information for the specific Instance it is ran on.

The more generic SP that does not abstract for the specific instance is xp_RegRead.

SQL Server – Error Log – Parsing – FindStr

Background

A need to parse all available SQL Server Error Logs for a SQL Server Instance and find I/O issues.

Code

Stored Procedure – dbo.sp_errorLog_findstr




use [master]
go

if object_id('dbo.sp_errorLog_findstr') is null
begin

	exec('create procedure [dbo].[sp_errorLog_findstr] as ')

end
go

alter procedure [dbo].[sp_errorLog_findstr]
(
	  @stringSought	varchar(255)
	, @stringSought2	varchar(255) = null
)
as

begin

	declare @FileID			int
	declare @FileMaxID		int
	declare @Subsystem		int

	SET NOCOUNT ON

	DECLARE @maxLog      INT
	DECLARE @searchStr   VARCHAR(256)

	DECLARE @errorLogs   TABLE 
	(
		  LogID    INT
		, LogDate  DATETIME
		, LogSize  BIGINT   
	);

	DECLARE @logData  TABLE 
	(
		  FileID   INT default (-1) NULL
		, LogDate     DATETIME
		, ProcInfo    VARCHAR(64)
		, LogText     VARCHAR(MAX)   
	);

	INSERT INTO @errorLogs
	(
		  LogID 
		, LogDate
		, LogSize
	)
	EXEC sys.sp_enumerrorlogs
	;

	SET @FileID = 0
	set @Subsystem = 1

	SELECT @FileMaxID = max([LogID])
	FROM   @errorLogs

	WHILE (@FileID <= @FileMaxID )
	BEGIN

		INSERT INTO @logData
		(
			  [LogDate] 
			, [ProcInfo]
			, [LogText]
		)
		EXEC sys.sp_readerrorlog 
				  @FileID
				, 1
				, @stringSought 
				, @stringSought2
				;

		update  tblLD
		set     tblLD.FileID = @FileID
		from    @logData tblLD
		where   (
					   ( tblLD.FileID is null )
					or ( tblLD.FileID = -1 )
				)

		SET @FileID = @FileID + 1

	END

	SELECT FileID, [LogDate], [LogText]
	FROM @logData
	ORDER BY
			 [LogDate] desc
			;

end
go

EXEC sys.sp_MS_marksystemobject '[dbo].[sp_errorLog_findstr]'
go




Sample


	declare @stringSought varchar(255)
	declare @stringSought2 varchar(255)

	set @stringSought  = 'SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file'
	set @stringSought2 = 'tempdb'

	exec [dbo].[sp_errorLog_findstr]
		  @stringSought = @stringSought
		, @stringSought2 = @stringSought2




Output

sqlserver-error-iostalls-20170117-0300pm

 

Explanation

  1. Part of the problem in our case is that all files are using our system drive ( Drive C:)

 

Source Code Repository

GitHub

Placed here

 

Referenced Work

Here are the code that I used:

  1. Mike Eastland
    • Search multiple SQL Server Error Logs at the same time
      Link

 

Microsoft – SQL Server – Error – Error: 17066, Severity: 16, State: 1 // SQL Server Assertion: File: cxrowset.cpp, line=1538 Failed Assertion = ‘pXacc–>FHasVirtualColumn()’. This error may be timing-related

We started getting this “error – Error: 17066, Severity: 16, State: 1:”

Due to its severity level 16 and SQL Server’s suggested remediation steps:

  • Run DBCC CheckDB (to check database for structural integrity)
  • Restart the server (to ensure in-memory data structures are not corrupted)

…. I am thinking I am in for long nights…and not the nice ones; actually the grumpy – I am a DBA ones…

SQL Server Error logs reads —


2013-01-03 01:22:28.12 spid59 Error: 17066, Severity: 16, State: 1.
2013-01-03 01:22:28.12 spid59 SQL Server Assertion: File: &lt;"cxrowset.cpp"&gt;, line=1538 Failed Assertion = 'pXacc-&gt;FHasVirtualColumn()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2013-01-03 01:22:28.12 spid59 Error: 3624, Severity: 20, State: 1.
2013-01-03 01:22:28.12 spid59 A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
2013-01-03 01:22:28.13 spid65 Using 'dbghelp.dll' version '4.0.5'
2013-01-03 01:22:28.13 spid65 **Dump thread - spid = 0, EC = 0x00000009E92D64B0
2013-01-03 01:22:28.13 spid65 ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0440.txt
2013-01-03 01:22:28.13 spid65 * *******************************************************************************
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * BEGIN STACK DUMP:
2013-01-03 01:22:28.13 spid65 * 01/03/13 01:22:28 spid 65
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * Location: "cxrowset.cpp":1538
2013-01-03 01:22:28.13 spid65 * Expression: pXacc-&gt;FHasVirtualColumn()
2013-01-03 01:22:28.13 spid65 * SPID: 65
2013-01-03 01:22:28.13 spid65 * Process ID: 2036
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * Input Buffer 255 bytes -
2013-01-03 01:22:28.13 spid65 * 16 00 00 00 12 00 00 00 02 00 00 00 00 00 00 00 00 00
2013-01-03 01:22:28.13 spid65 * ÿÿ &amp; 01 00 00 00 ff ff 0d 00 00 00 00 01 26 04 04 00 00 00
2013-01-03 01:22:28.13 spid65 * ç@ Ð 4Ž @ P 0 00 00 00 e7 40 1f 09 04 d0 00 34 8e 05 40 00 50 00 30
2013-01-03 01:22:28.13 spid65 * n v a r c h a r 00 20 00 6e 00 76 00 61 00 72 00 63 00 68 00 61 00 72
2013-01-03 01:22:28.13 spid65 * ( 4 0 0 0 ) , @ P 00 28 00 34 00 30 00 30 00 30 00 29 00 2c 00 40 00 50

Yes, young one, this is not an easy one.

And, you are possibly thinking “If you wanted cryptic error messages, you would have stayed with C++ and be an Old School hacker”

But, anyways as you have friends on the .Net, you have come to the right place.

Couple of things you want to keep in mind:

  1. What is your spid – In our case 65
  2. As spid is 65, quite a bit above 50 we are in user’s land; the session that is failing is not a system process, but a user process
  3. Also keep in mind the process ID, for those running multiple SQL Server Instances on the same server
  4. Then try your best to read through the InputBuffer — It contains every thing you need to tell you which SQL Statement is failing

Our problem was traced back to the day I took that proverbial short-cut:


create table dbo.store
(
	    storeID bigint not null identity(1,1)
	  , storeName sysname not null
	  , partitionIDOrig as 0 persisted

	  ,	CONSTRAINT [PK_Store] PRIMARY KEY NONCLUSTERED
		(
			  [storeID] ASC
			, [partitionIDOrig]
		)

		ON [partitionScheme]([partitionIDOrig ])

)
go

If you take a quick look at our definition for partitionIDOrig, you will see it is hard-coded to be 0.

Everything worked well when we were inserting into the table.

But, once we changed to Merge, things broke.

To fix:


create table dbo.store
(
	    storeID bigint not null identity(1,1)
	  , storeName sysname not null
	  , partitionID as isNull(cast(rand(100) as int), 0) persisted

	  ,	CONSTRAINT [PK_Store] PRIMARY KEY NONCLUSTERED
		(
			  [storeID] ASC
			, [partitionID]
		)

		ON [partitionScheme]([partitionID ])

)
go

Our new partitionID is an actual computed \ variant column:


    partitionID as isNull(cast(rand(100) as int), 0) persisted

 

I think when Microsoft invokes FHasVirtualColumn(), they are asking whether we have Virtual columns — And, things “stumble” a bit when the calculated column is actually a hard-coded value.


References:

Microsoft – SQL Server – Cannot open user default database – Error 4064

Microsoft – SQL Server – Cannot open user default database – Error 4064

Cannot Open Default Database
Cannot Open Default Database

Since I already googled for this problem in the past, I already know what to do.

    Syntax:
       use <db-name>
       exec sp_change_users_login 'report'

    Sample:

       use [HRSales]
       exec sp_change_users_login 'report'

Result of running sp_change_users_login:


So the obvious next step is to align the user accounts listed.

Syntax:

    exec sp_change_users_login 'update_one'
                                , <user-name-in-currentDB>
                                , <sql-login>;

Sample:

    exec sp_change_users_login 'update_one', 'jazzyb', 'jazzyb';

Most of what I “blog” about is what I ‘ve learnt from others.

And, so it is easy to question the motivation.

One is that you give credit to others via Referencing them.

Another much subtle one is to give credence to other peoples work you come about via Google.

Yes, I wish Google takes me to exactly what I need, but as a lot of search is through Word proximity, sometimes one ends up at different places.

Another reason is that as human beings it is difficult to realize how much litte things add up and matter.

Here is one of my favorite from Barbara Bush:

You don’t just luck into things as much as you’d like to think you do. You build step by step, whether it’s friendships or opportunities – Barbara Bush

So do self a favor and ‘firewall’ future hurt by wishing others well and give a little.  If you do it a little at a time, it wouldn’t weigh you down, as much as you think it will.

Either you ‘re sharing or you ‘re hoarding. Once you decide to hoard, you will hurt when others don’t follow suit – Daniel Adeniji

Going back to the earlier point about the person who I should credit with this solution.  I really do not remember him\her; save for the fact I found him via Google.

But, I will leave you with some of my favorite Arundhati Roy quotes:

You know, I always believe that even among the best writers, there are selfish writers and there are generous ones. Selfish writers leave you with the memory of their book. Generous writers leave you with the memory of the world they evoked. To evoke a world, to communicate it to someone, is like writing a letter to someone that you love. It’s a very thin line. For me, books are gifts. When I read a book, I accept it as a gift from an author. When I wrote this book, I presented if as a gift. The reader will do with it what they want.

That’s what careless words do. They make people love you a little less.
― Arundhati RoyThe God of Small Things

Another world is not only possible, she is on her way. On a quiet day, I can hear her breathing.
― Arundhati Roy

Microsoft – SQLServer – ErrorLog File – Grooming

Microsoft – SQLServer – ErrorLog File – Grooming

MS SQL Server ErrorLog files can grow for various reasons:

  1. Resource Issues – Particularly Low Storage
  2. TraceFlag Configuration
  3. Frequent backup, particularly frequent transaction backups which are logged as informational enytries

There are many other reasons.  But, what to do when your error log file has grown to be over a few hundred MB or GBs

Well, see if any of the following applies to you:

  1. Is MS SQL Installed on your System Drive (C:), if so that will create other problems.  It is not easy to change that situation.  If a re-install is a day too late, then using MS SQL Server Configuration Manager change the filename of your errorlog ( -e option).Please note that this entry is the fully qualified file name of your error log file…It is not the folder’s name.  It is the file name and SQL Server takes care of “journaling” the numbers at the end of the filename as it generates new files
  2. Consider using sp_cycle_errorlog to in-activate the current errorlog and thus create a new one that will then be the active one.Once we have a new active log, the system will allow you to move to simple the former active one.

References:

1) Using the SQL Server Service Startup Options
http://msdn.microsoft.com/en-us/library/ms190737(v=sql.90).aspx

2) How to: Configure Server Startup Options (SQL Server Configuration Manager)
http://msdn.microsoft.com/en-us/library/ms345416(v=sql.90).aspx

3) sp_cycle_errorlog (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms182512.aspx

4) Where is SQL Server ErrorLog?
http://beyondrelational.com/modules/2/blogs/44/posts/13728/help-where-is-sql-server-errorlog.aspx 

 

Microsoft SQL Server – Error – Service Broker needs to access the master key in the database

Error

Reviewing MS SQL Server Error Log and found:

Service Broker needs to access the master key in the database ‘database-name’;.
Error code:26. The master key has to exist and the service master key encryption is required:

Resolution

Outline

  1. Change Database Context to Identified Database
  2. Review existing Keys
  3. Create existing symmetric key

 

Steps

Change database context to identified database


Use [database-name];

Review existing keys

Select * 
from   sys.symmetric_keys;

Create new key

Syntax:


if not exist
(
    select 1
    from   sys.symmetric_keys
 )
 begin

     create master key 
          encryption by password = 'password';;

 end

Sample:


if not exist
(
     select 1
     from   sys.symmetric_keys
)

begin

     create master key 
          encryption by password = '9181671626514JAMES';

end

References

  1.  Database Engine How-to Topics > Administration How-to Topics  > Encryption How-to Topics
    • How to create a master key
      Link
  2. Docs /SQL / Relational databases / Security / Encryption/
    • Create a database master key
      Link