June 23, 2010 at 12:15 pm
Hello all,
Been some time since I have been out here in SSC land.
Came across something interesting with one of my clients. They have a windows Event that seems to be recurring daily and looks like a backup of a database on the machine. While this is not really a bad thing, it appears to be causing issues for the application.
This is SQL Express 2005 with the latest Service Pack running on Windows 2003 Server (32-bit) Service Pack 2.
Source: MSSQL$SQLEXPRESS
Category: (6)
EventID: 18624
Type: Information
User: NT AUTHORITY\SYSTEM
Description:
Database backed up Database: My_DB creation date(time):YYYY/MM/DD(HH:MI:SS) etc.
The event seems to be recurring daily, but the funny thing about it is that there is no scheduled event to make this happen. No SQL Agent is installed and there is not a Windows Scheduled Task to fire this off either.
Is this "normal" and I have never seen it before? Why would SQL Express back anything up? Could this just be events flushing from the Transaction Log?
Any ideas or has anyone else seen this?
Regards, Irish
June 23, 2010 at 12:26 pm
It could be getting done manually, it could be a scheduled job on another SQL Server, a Windows Scheduled Task on another server, or it could be launched by an application on another server.
June 23, 2010 at 12:32 pm
The application is it working with could be firing the backups.
If you query the msdb.dbo.backupset table it should give you the username issuing the backup command.
I attached a script I use to check backup times and who-done-its.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 23, 2010 at 1:30 pm
Is it the same time every day? Have you checked scheduled tasks in Windows?
June 23, 2010 at 1:38 pm
Steve Jones! :w00t:
Actually, I have checked there and nothing seems to point to a task scheduled on the Server. However, because there are a number of other SQL Servers with Agents that this could be run by a 3rd party too.
The search continues!
Regards, Irish
June 23, 2010 at 2:14 pm
Okay, as I continue to review this issue, the mystery continues to confuse.
Ran the scripting in an earlier post (Thank you, Michael) and I get some really fun results
DatabaseName DurationInSeconds UserResponsible backup_name description backup_start_date backup_finish_date BackupType BackupSizeMB physical_device_name DeviceType recovery_model
------------------------------------------------------------------------------------------------------------------------- ----------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------
Application 323 NT AUTHORITY\SYSTEM NULL NULL 2010-06-23 03:30:43.000 2010-06-23 03:36:06.000 Database 0 {C1FE7C8B-A202-4A79-B6C6-2EEF42B6D97A}3 NULL SIMPLE
Replication 323 NT AUTHORITY\SYSTEM NULL NULL 2010-06-23 03:30:43.000 2010-06-23 03:36:06.000 Database 0 {C1FE7C8B-A202-4A79-B6C6-2EEF42B6D97A}4 NULL SIMPLE
The Application Database is static and holds constant values, the Replication Database holds more volatile Data, but neither is critical as it is only accessed by the program running local to the Server. If either of these Databases became corrupt, out-dated, or otherwise unavailable the application fails back to the primary database server where the replicated data would come from.
Since these databases could be dropped and rebuilt very easily we never would have a backup scheduled, but there is clearly one here! To add to this, I am finding zero Maintenance Plans, SQL Agent Jobs, or Windows Scheduled Tasks on this Server or any other Server that would have kicked off this backup.
More ideas welcomed :ermm:
Regards, Irish
June 23, 2010 at 2:21 pm
Just shoot'n from the keyboard here....Could you enable "Successful Logons" for that instance and then correlate the timestamp of the backup with the logon time to see what client/IP it is coming from. That may help narrow it down.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 29, 2010 at 2:56 am
The physical_device_name ‘{C1FE7C8B-A202-4A79-B6C6-2EEF42B6D97A}’ indicates possibly a tape agent?
June 29, 2010 at 6:43 am
I enabled Login Auditing on both failed and successful logins, but I am still not seeing who is kicking this off. All I see is NTAuthority\System.
Perhaps I am looking for the wrong thing? Where would SQL Express write this audit data?
Regards, Irish
June 29, 2010 at 8:00 am
Ok, I figured out where to find the auditing information, Windows Event Viewer Security log.
I looked and I can see that the backups occurred (I'm looking at both the SQL Error Log and the Security Log), but there is no login during the backup. In fact, the only login I see is almost a full hour before the backup runs.
This is still a mystery!
Regards, Irish
June 30, 2010 at 1:58 am
What I find mysterious is that the backup information you listed earlier says NT AUTHORITY\SYSTEM initiated the backup. That's the LocalSystem account, which is only ever used by services, so one of the services on the system must have launched that backup. Which one I have no idea, though.
[EDIT] Just had a thought. It's possible to hide a task so it doesn't appear in the Scheduled Tasks control panel. You can view these hidden tasks if you open Scheduled Tasks and click Advanced->Show Hidden Tasks. Does that make a difference here?
June 30, 2010 at 2:33 am
You could also try looking in the SQL default trace files e.g. below would extract the data from the trace file log_25.trc into a table called trace_log_25 in an existing database called Test. Profiler does not come with Express or could have used that instead to view the files content.
USE Test
SELECT * INTO trace_log_25
FROM::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10_50.I1\MSSQL\Log\log_25.trc',1)
June 30, 2010 at 5:33 am
Symantec Backup Exec is known to do this to SQL instances. The only way I know to prevent it is to remove NT AUTHORITY\SYSTEM (but don't do it!)
It messes up my backup chains. I would love to configure it so that it does its thing without interfering with SQL.
Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS
June 30, 2010 at 8:43 am
If it's an agent like Symmantec, you need to go find the admin and have him stop using the agent. That's the only way to fix this.
Or just let him be responsible, but clarify it with management as to who will handle restores.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply