2

We have a 2.5TB refresh in Production, I have automated the refresh of latest production backup to lower environment every Sunday night. And during this activity, SQL error log is filled with

Login failed for user 'xxxx'. Reason: Failed to open the explicitly specified database 'xxxx'.

The options to stop these I could think of is:

  1. Ask app team to stop connecting to DB during refresh - they have lot of process, so they are not ready.
  2. Recycle error log post refresh - I need to search the entire log file for any messages during refresh (non login failed errors)
  3. Disable the login - that creates alerts too.

What is the best approach? Any thoughts? Or am I missing something stupid?

Unfortunately, due to other apps that connect to this instance, I can't:

  • disable logging of failed logins entirely, or
  • block all connections (via the firewall, etc)
0
2

One option might be to change the login for this user to have a different default database:

ALTER LOGIN [xxxx]
WITH DEFAULT_DATABASE = [tempdb];

This way at least the connections will succeed and the logging of errors will stop (the apps will likely have mixed results, mostly failures, with whatever they are trying to do of course).

And then you can change it back to the usual one after the refresh is done.

0
1

let SQL Server continue to log the failures since you can't disable them, due to other databases residing on that instance.

Instead, use xp_readerrorlog to load the latest error log and remove the login errors.

Something like this:

if object_id('tempdb..#errorLog') is not null drop table #errorLog

create table #errorLog(LogDate datetime2
                      ,ProcessInfo varchar(64)
                      ,LogText varchar(max))

insert into #errorLog
exec master.dbo.xp_readerrorlog 0, 1, NULL, NULL, NULL, NULL, N'desc' 

select *
from #errorLog
where LogText not like '%logon failure%'

This would prevent you from altering a bunch of logins and having to ensure they were mapped correctly afterwards which could violate your security policies, etc.

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.