AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Sql Server Dump File Location5/3/2021
SQLSTATE 42000 (ConnIsLoginSysAdmin) 2015-06-29 16:47:56 298 SQLServer Error: 229, The EXECUTE permission was denied on the object spsqlagentgetstartupinfo, database msdb, schema dbo.There have been many occasions where I need to guide them to find location of ERRORLOG file generated by SQL Server.
Most DBAs are intelligent and know some of these, but this is my try to share my learning about ERRORLOG location. I decided to write this blog so that I can reuse it rather than sending steps every time. At this point I must point out that even if the name says ERRORLOG, it contains not only the errors but information message also. Here are various ways to find the SQL Server ErrorLog location. A) If SQL Server is running and we are able to connect to SQL Server then we can do various things. EXEC xpreaderrorlog 0, 1, NLogging SQL Server messages in file. B) If we are not able to connect to SQL Server then we should SQL Server Configuration Manage r use. Below is the place in SQL Server Configuration Manager (SQL 2012 onwards) where we can see them. C) If you dont want to use both ways, then here is the little unknown secret. The ERRORLOG is one of startup parameters and its values are stored in registry key and here is the key in my server. SQLArg1 shows parameter starting with -e parameters which point to Errorlog file. Here is the key which I highlighted in the image: HKEYLOCALMACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL12.SQL2014MSSQLServerParameters Note that MSSQL12.SQL2014 would vary based on SQL Server Version and instance name which is installed. Here is the quick table with version reference SQL Server Version Key Name SQL Server 2008 MSSQL10 SQL Server 2008 R2 MSSQL1050 SQL Server 2012 MSSQL11 SQL Server 2014 MSSQL12 In SQL Server 2005, we would see a key name in the format of MSSQL.n (like MSSQL.1) the number n would vary based on instance ID. Here is a key where we can get mapping of Instance ID and directory. In the above image, you can see that this computer has a default instance (Instance Name MSSQLSERVER) of SQL Server 2012 and named instance (Instance Name SQL2014) of SQL Server 2014. In case you are contacting me for any error, get the Errorlog location using this blog. Reference: Pinal Dave ( ) SQL Error Messages, SQL Log, SQL Scripts, SQL Server Previous Post Interview Question of the Week 012 Steps to Restore Bak File to Database Next Post SQL SERVER Knowing Nested Transactions Behavior with SQL Server Related Posts SQL SERVER Quick Introduction to Startup Procedures July 23, 2018 SQL SERVER Wait Statistics Generated by oStress Insert Workload May 2, 2020 SQL SERVER How to Pass Parameters to the Stored Procedure July 11, 2017 10 Comments. Leave new RAO March 24, 2015 9:55 am Very useful tip Reply Pinal Dave March 26, 2015 7:59 pm Rao I am glad that you liked it. Most of the times it is in the default location, but from time to time when a new DBA joins a team, they need to make sure the Errorlogs are placed in locations as prescribed by company standards. I am sure you would have seen a number of blog wherein I rely on the error messages or warnings put on the Errorlog files. Reply RJC June 29, 2015 5:02 pm i am unable to connect to sql server through visual studio. SQLSTATE 42000 (DisableAgentXPs) 2015-06-29 16:47:56 298 SQLServer Error: 229, The EXECUTE permission was denied on the object spsqlagenthasserveraccess, database msdb, schema dbo.
0 Comments
Read More
Leave a Reply. |