Question :
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 'domainsvc-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.
Answer :
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:
- Restarting SQL Server
OR
- 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:
- Expand the “Management” folder.
- Right click on “SQL Server Logs”
- Select “Configure”
- Check the box “Limit the number of error log files before they are
recycled” - Pick some value to put in the “Maximum number of error log failes”
box - Click “OK”
Check out Forgotten Maintenance – Cycling the SQL Server Error Log for more information about configuring the SQL Server ERRORLOG
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?