Where is the value for the max number of ERRORLOG files stored?

Question

Where is the value for the max number of ERRORLOG files stored?

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
September 11, 2008 at 9:35 am

#123554

One can set the value for the max number of SQL ERRORLOG files to be kept on disk by rightclicking on the SQL Server Logs node (SSMS), selecting Configure and setting the number in the resulting dialog.
Where in the system catalog is this value stored? I need to automate a process based on this number, so I need to know where to get it from on the fly.
Thanks for any replies!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

It's not stored anywhere in SQL. It's in the registry.

I can't remember offhand where, easiest way to find where is to run profiler and filter the trace based on your machine, then go into that dialog and change it. You'll see the SQL code to read the value from the registry

Beware, undocumented feature and I believe sysadmin is needed to run it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

GilaMonster (9/11/2008)
It's not stored anywhere in SQL. It's in the registry.
I can't remember offhand where, easiest way to find where is to run profiler and filter the trace based on your machine, then go into that dialog and change it. You'll see the SQL code to read the value from the registry
Beware, undocumented feature and I believe sysadmin is needed to run it.

Thank you, I think you gave me what I need.

Cheers!

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

You can right click SQL Server Logs and go to configure.

But here is the command:

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, X

Where X is the amount of error logs kept before they are recycled.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Adam Bean (9/11/2008)
You can right click SQL Server Logs and go to configure.
But here is the command:
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, X
Where X is the amount of error logs kept before they are recycled.

thanks! So to read the entry I'd have to use xp_instance_regread...

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Adam Bean One Orange Chip Points: 26631 More actions · Answer 5

Yes, or travel directly to the registry ... easiest way is to just use SSMS though.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

Adam Bean (9/11/2008)
Yes, or travel directly to the registry ... easiest way is to just use SSMS though.

I'm looking into automating this, so using the xp is the way to go.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]