January 31, 2022 at 2:52 pm
SQL Server Strange behavior:
I have been Getting the following error massages nonstop after full backup fails. this massage gets generated for each database in this Availability Group. The job runs every night, and it call SSIS package. Both the SSIS and Job seems okay and the tricky part is that job fails once in a while like once in 10 days or so.
[298] SQLServer Error: 4060, Cannot open database "testDB" requested by the login. The login failed. [SQLSTATE 42000]
[298] SQLServer Error: 18456, Login failed for user 'Test\test-user-sql'. [SQLSTATE 28000]
Did I miss something here?
Thank you
Razel.
January 31, 2022 at 3:04 pm
I hope you are logging failed logins somehow. In the detail information it states the actual reason for the failure.
there must be more detailed information about the failure.
Maybe my article may get you on track: "Logon monitoring in SQL Server and Azure Managed Instances - Adopting Extended Events"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 31, 2022 at 3:32 pm
Yes the actual reason for the failure.
Description: Executing the query
"DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp..." failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id".
The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has been terminated.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I did run DBCC checkdb against MSDB there is no integrity or consistency issue and like I mentioned this job fails once in a while, so that tricks me from not considering to drop and create the maintenance plan allover again.
January 31, 2022 at 8:04 pm
I am 99.999% sure that an INSERT failure is going to be different than a failed login error. The INSERT failure sounds like you are modifying a maintenance plan with a duplicate subplan already in place. It doesn't look to me like something I'd jump to as being an "integrity or consistency" issue.
I would review the code behind that job step and see which stored procedure is being run from msdb, and I would also look at moving your backup process outside of a maintenance plan. I am not a huge fan of maintenance plans as they feel like extra overhead with little benefit and would MUCH rather use a custom stored procedure to handle my backup process. I say "custom" as I like to have my backup process specific to my backup tool of choice rather than an all in one script that I cannot properly support (like Ola's backup and maintenance scripts). I use RedGate SQL Backup, so my stored procedure is geared towards that and tuned for my environment.
There are also limitations with maintenance plans. If you have any index maintenance done by maintenance plans and then someone disables row locking on an index (for example), they will fail. AND you may have indexes that are highly fragmented and re-fragment quickly and you don't want to update those (side note - if they are re-fragmenting frequently, you probably want to address that, but rebuilding the index isn't your best bet). AND if I remember right, assuming you posted in the correct forum section, SQL 2005 doesn't have a way to restrict which indexes are rebuilt. It is an "all or nothing" if you do it through maintenance plans. An index that has low fragmentation you likely don't need to do frequent maintenance on.
My expectation is that you are trying to address 2 distinct problems and thinking it is one and the same. The insert failure is LIKELY unrelated to the LOGIN failure. They may occur around the same time, but they are likely to be 2 different problems.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply