NT Backup causes SQL Login failures

Last Post 26 Nov 2008 07:55 AM by Doley. 8 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Doley
New Member
New Member

--
24 Nov 2008 06:28 AM
Hi,
I have a SQL Server 2005 SP2 64 Bit server running on Windows 2003 SP2 (x64). The database on the server supports a high traffic web server. I only have a default instance.

My issue is that our hosting company runs a daily NT backup (ntbackup backup systemstate) on the server at 2:15 am. For the 3 or 4 minutes while the backup is initializing, I get lots of failed logins to my SQL server. Then they go away as soon as the backup starts. Has anyone seen this before, any recommendations? I've already changed the time to lessen the impact, but I'd like a permanent fix.

From the event log:

Event Type: Information
Event Source: NTBackup
Event Category: None
Event ID: 8018
Date: 11/24/2008
Time: 2:16:21 AM
User: N/A
Description:
Begin Operation



Bunch of login failures....
Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 11/24/2008
Time: 2:18:21 AM
User: N/A
Description:
Login failed for user xyz


Event Type: Information
Event Source: NTBackup
Event Category: None
Event ID: 8000
Date: 11/24/2008
Time: 2:19:12 AM
User: N/A
Description:
Begin Backup of 'System State'

Verify: Off
Mode: Replace
Type: Copy

Login failures stop at this point. One interesting thing to note, is yesterday, the ntbackup command failed with this error: "Error returned while creating the volume shadow copy:Not enough storage is available to complete this operation." and I didnt' get any failed login attempts. Not sure if this points to a larger memory issue? Any help would be greatly appreciated.

Doley
New Member
New Member

--
24 Nov 2008 06:45 AM
I should also mention that we see this behavior on a couple of other servers as well.
Doley
New Member
New Member

--
24 Nov 2008 11:32 AM
Hi RM,
That is the entire event log entry other than the server name and user name. Here's the entry from the sql server log:

Date 11/24/2008 2:18:21 AM
Log SQL Server (Current - 11/24/2008 3:00:00 PM)
Source Logon
Message
Error: 18456, Severity: 14, State: 10.


Date 11/24/2008 2:18:21 AM
Log SQL Server (Current - 11/24/2008 3:00:00 PM)
Source Logon
Message
Login failed for user 'usera'. [CLIENT: xxx.xxx.xxx.xxx]
Doley
New Member
New Member

--
24 Nov 2008 12:30 PM
yes, the login is a valid sql login that can login 99% of the time, except when ntbackup is initializing. It is connecting through an ASP site on the IIS server. It uses sql authentication, not windows.
Doley
New Member
New Member

--
25 Nov 2008 12:27 PM
not really, just "Connection failed to database" which is a handled/friendly error.
SQLUSA
New Member
New Member

--
25 Nov 2008 11:21 PM
If this is a dedicated web-support database server, you can just schedule nightly full database backup to a disk device and have it moved to tape after the backup operation completes. You should backup the master, msdb and model databases as well.

You would have to make sure that you have all other files covered backup-wise if any, and then ask the ISP to stop doing NT backups.

Kalman Toth, SQL Server 2008 Training
http://www.sqlusa.com/sql-server-2008-training

Doley
New Member
New Member

--
26 Nov 2008 04:12 AM
Hi SQLUSA,
Thanks for the post, but I don't quite follow you. I'm set with my database backups, but the ISP is doing a systemstate backup (registry, windows OS files, etc), so short of buying a third party app, I don't know how I can tell them to stop doing NTbackups and still have my key OS files backed up?

I'm thinking my issue is resource related (most likely memory as available MB is generally 150 MB or less). I was just hoping someone else had seen a similar issue and had a resolution. Thanks
Doley
New Member
New Member

--
26 Nov 2008 07:55 AM
no, the services are still running and I'm guessing there are still some connections successfully made while the systemstate backup initializes, but I can't say for sure b/c I haven't run a trace and we don't have successful login auditing enabled. It's been 2 days since I've moved the ntbackup to 4:15 and so far that has resolved the issue.
dtimbro
New Member
New Member

--
20 Apr 2009 02:12 PM
Hi there,

Have you tried excluding the Data folder that houses the SQL databases ?

Just a thought ... I do my backups like that (NT Backup for local drives and System State + SQL Databases backup with SQL) and never had an issue ...
You are not authorized to post a reply.

Acceptable Use Policy