1

The SQL Server's ERRORLOG file has a lot of messages like the following in AG in publisher (primary) as you know that distributor is out of AG Server. Subscriber is in another AG setup.

 Logon       Login failed for user 'domain\svc-prod'. Reason: Failed to open the explicitly specified database 'DB'. [CLIENT: xx.xxx.xxx.xxx]
 Logon       Error: 18456, Severity: 14, State: 38.

This error is because the replication server thinks there are either Publication on production SQL Server and I need to clean up these publisher/subscriber items and error messages.

These messages are related to Replicated DB that are hosted in AG, these messages are occurring continuously and I wanted to stop them from occurring. Somewhere I need to map the above mentioned user login to the DB but I am not sure how to do that to stop these messages.

1

I'm not sure I understand your motivation behind wanting to Cleanup Error Messages in SQL Server ERRORLOG. You can certainly close the current file and create a new by either:

  1. Restarting SQL Server

OR

  1. Executing sp_cycle_errorlog

Keep in mind that the SQL Server error log is a file that is full of messages generated by SQL Server. By default this tells you when log backups occurred, other informational events, and even contains pieces and parts of stack dumps. In short, it’s a treasure trove of information. When SQL Server is in trouble, it’s nice to have this available as a source of information during troubleshooting.

SQL Server keeps up to 6 error log files around by default. You can easily change this. Open up your copy of SSMS and:

  1. Expand the “Management” folder.
  2. Right click on “SQL Server Logs”
  3. Select “Configure”
  4. Check the box “Limit the number of error log files before they are recycled”
  5. Pick some value to put in the “Maximum number of error log failes” box
  6. Click “OK”

Check out Forgotten Maintenance – Cycling the SQL Server Error Log for more information about configuring the SQL Server ERRORLOG

  • These messages are related to Replicated DB that are hosted in AG, these messages are occurring continuously and i wanted to cleanup. somewhere i need to map the above mentioned user login to DB but i am not sure to stop these messages. I know how to recycle the error log this is already implemented. Please suggest me how to stop these messages. – Vijaya Apr 12 '18 at 12:56
  • This SQL Server are aws vm. SQLServer are installed with Copy of image in this AG group. – Vijaya Apr 12 '18 at 13:03
1

This error could come from a wrong Set default DB in the login's settings. In case its not explicitly set during connection, the default is used. If it fails for example due to missing permission you see those error messages over and over.

If a DB Name is used in the connection string, check the database name and also check whether integrated Security is turned on or off.

If everything looks fine check if the user is mapped to the database it tries to connect to. In order to map the login you have to create a User for the login in the DB Where it is needed. Read here : https://docs.microsoft.com/de-de/sql/t-sql/statements/create-user-transact-sql

What sql server version are you Using?

  • SQL Server 2014 SP2 – Vijaya Apr 16 '18 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.