September 7, 2011 at 6:33 am
Hello everyone. I've set up a maintenance plan scheduled to back up a database after work hours. When I came in this morning, I realized that the job did not run. I checked SQL Server Agent and it is running, but when I checked the database logs, I saw the following errors:
Date,Source,Severity,Message
09/07/2011 03:15:53,Server,Unknown,The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x45b<c/> state: 4. Administrator should deregister this SPN manually to avoid client authentication errors.
09/07/2011 03:15:53,spid5s,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
09/07/2011 03:15:53,spid12s,Unknown,Service Broker manager has shut down.
09/07/2011 03:15:53,spid12s,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
09/07/2011 03:15:42,Server,Unknown,The current event was not reported to the Windows Events log. Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full.
09/07/2011 03:15:42,Server,Unknown,Error: 17054<c/> Severity: 16<c/> State: 1.
09/07/2011 03:15:42,Server,Unknown,The connection has been lost with Microsoft Distributed Transaction Coordinator (MS DTC). Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) will begin once the connection is re-established. This is an informational message only. No user action is required.
09/07/2011 03:15:41,Server,Unknown,SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
09/07/2011 03:13:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:13:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:12:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:12:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:11:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:11:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:12:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:12:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:09:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:09:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:08:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:08:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:07:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:07:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:06:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:06:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:05:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:05:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
09/07/2011 03:04:35,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 12.18.0.123]
09/07/2011 03:04:35,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
I realize that the errors have something to do with a Logon account, but I don't know which one or how to correct the problem. Also, since I didn't get a backup of the database last night, is it safe to back it up while it is online and currently in use?
Thanks.
September 7, 2011 at 6:40 am
It's safe to run the backup during prod. The main side effect is that your response time to queries might be slower (couple ms in all systems I've tried, but they were on light load).
I'd check the connection you are using to make sure you have a valid login / password set there.
September 7, 2011 at 6:56 am
looks like SQL shutdown to me.
---------------------------------------------------------------------
September 7, 2011 at 10:02 am
When you set up a maintenance plan, it creates an ssis package and then schedules a job. You must check the credentials on the maintenance plan itself, and then the job as well.
Thanks,
Jared
Jared
CE - Microsoft
September 7, 2011 at 7:04 pm
09/07/2011 03:15:41,Server,Unknown,SQL Server is terminating because of a system shutdown.
The Server shutdown. Looking at the time it looks like it may be a scheduled reboot.
At what time are you running backup? It could be that the Server was rebooting and could not perform the backup.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 8, 2011 at 5:26 am
Am I mistaken, or do I not see any backup even starting in the logs? What time, exactly, is the backup scheduled for. The shutdown may be unrelated, or it may be related.
Thanks,
Jared
Jared
CE - Microsoft
September 8, 2011 at 5:50 am
I scheduled the maintenance plan for 8:00 pm. However, I deleted the maintenance plan yesterday and recreated it and it ran last night. The only problem I have now is that the backup is double the size of the original database. Any ideas as to why this may happen?
Thanks for your help.
September 8, 2011 at 5:54 am
troe_atl (9/8/2011)
I scheduled the maintenance plan for 8:00 pm. However, I deleted the maintenance plan yesterday and recreated it and it ran last night. The only problem I have now is that the backup is double the size of the original database. Any ideas as to why this may happen?Thanks for your help.
You're appending the backup to the backup file instead of overwriting it.
Also you need to NOT overwrite it. Name the backup something different for each day and then delete it once you've validated the new backup can be restored.
September 8, 2011 at 7:15 am
Ninja's_RGR'us (9/8/2011)
troe_atl (9/8/2011)
I scheduled the maintenance plan for 8:00 pm. However, I deleted the maintenance plan yesterday and recreated it and it ran last night. The only problem I have now is that the backup is double the size of the original database. Any ideas as to why this may happen?Thanks for your help.
You're appending the backup to the backup file instead of overwriting it.
Also you need to NOT overwrite it. Name the backup something different for each day and then delete it once you've validated the new backup can be restored.
If you wish to overwrite it, you do the backup WITH init. If you have SQL Server 2008 R2, you can also compress the backup when it is created. Also, can you tell me if your database is in simple recovery or full recovery mode?
Thanks,
Jared
Jared
CE - Microsoft
September 8, 2011 at 7:42 am
It's in full recovery mode.
September 8, 2011 at 8:18 am
Ok, this would have no bearing on the size of the database backup (as was previously mentioned you probably appended instead of overwrote)... However, you should add log backups to your backup strategy if you are using full recovery mode, otherwise simple may be a better choice for you.
http://msdn.microsoft.com/en-us/library/ms189275.aspx
Thanks,
Jared
Jared
CE - Microsoft
September 13, 2011 at 7:23 am
Also enable backup verification before overriding yesterday's backup.
September 13, 2011 at 7:27 am
THE-FHA (9/13/2011)
Also enable backup verification before overriding yesterday's backup.
Not enough, you need to test the restore part too.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply