SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Maintenance plan won't stop running...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

muxdaddy
Starting Member

5 Posts

Posted - 10/13/2006 :  08:37:07  Show Profile  Send muxdaddy a Yahoo! Message  Reply with Quote
About a year and a half ago, I set up a maintenance plan through the wizard to backup a database on a schedule. problem is that the project has ended about 6 months ago and although I deleted the maintenance plan, deleted the database, somehow, when other maintenance plans run, this job comes back. It doesn't come back in the sense that you can see it to delete it again through EM, it comes back in the form of a .BAK file that keeps growing. Someone has to delete this file once a day (plan is now running every 3 minutes piggy backing on other maintenance plans), as it can grow to a 1GB file in about a day's time.

I'm not sure what to do.

Help me someone. You're my only hope.

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/13/2006 :  09:16:50  Show Profile  Reply with Quote
If the database isn't there it can't be making a backup file of it

Maybe its making a backup of something else with the name of the now-deleted-backup?

Is it possible that its make a backup of something you actually want, but with the wrong name mimicking your deleted database?

The maintenance plan creates some "jobs". I presume these are deleted when you delete the maintenance plan (but I haven't checked) so it would be worth checking if you have some redundant jobs (but even if there are I can't see how they can be backing up a database that doesn't exist any more!)

"as it can grow to a 1GB file in about a day's time."

So: the first time the backup appears it is small, and then grows and gets to 1GB in a day, or so?

If so sounds like things are APPENDING to that file. I suggest you have a look whats in that file, e.g.

RESTORE FILELISTONLY FROM DISK = 'x:\MyPath\MyBackupFile.BAK'

Kristen
Go to Top of Page

muxdaddy
Starting Member

5 Posts

Posted - 10/13/2006 :  09:36:23  Show Profile  Send muxdaddy a Yahoo! Message  Reply with Quote
Well, not sure what to say here. I restored it and it has current backups of a database that doesn't exist in Sql. The database was deleteda long time ago, along with it's backup job. There's only one maintenance plan on the server that's backing up one database that resides on the server. There's only one job listed in the Agent and that's the job to backup the current database on there.

I'm as thrown as you. I don't see how a job that doesn't exist keeps backing up a database that doesn't exist. Is there anything else that I can check? The backups that are supposed to be running show up in the sql logs, but not the phantom backup. I can guarantee the file is there though, and keeps getting appended to. When restoring, there's a long list of backups for the phantom database within the phantom file.

weird, eh?
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/13/2006 :  10:12:17  Show Profile  Reply with Quote
"weird, eh?"

Yup, agree on that!

Coming from some other server?

File's "owner" tell you anything?

Did you check for JOBS (rather than Maintenance Plans)?

Kristen
Go to Top of Page

muxdaddy
Starting Member

5 Posts

Posted - 10/13/2006 :  10:25:33  Show Profile  Send muxdaddy a Yahoo! Message  Reply with Quote
Nope, this is only Sql box on the network.

Nothing out of the ordinary.

Yep, one job listed that matches the one maintenance plan.

Is there somewhere else i can check. Somewhere, something lingering in some Agent registry setting or something. Somewhere, on the hard drive, database crumbs have to be somewhere. It's backing it up from somewhere. The agent has to have something outside of EM listing out the jobs, cause it's running something.

AAAAAAAARRRRRRRRRRRRGGGGGGGGHHHHHHHHHHHHH
Go to Top of Page

muxdaddy
Starting Member

5 Posts

Posted - 10/13/2006 :  10:30:06  Show Profile  Send muxdaddy a Yahoo! Message  Reply with Quote
Quick question. I just searched the whole server for any file containing the text of the "phantom" database name and came up with one hit. Administrator\Local Settings\Temp\MMC9.tmp What is this file?
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/13/2006 :  10:46:08  Show Profile  Reply with Quote
"What is this file?"

I don't know, but you can delete it (unless its still in use). .TMP files in xxx\Local Settings\Temp\ get killed in short-order here ...

Might be worth searching the disk for a data string taken from the backup file which is unique to the original database (keep it short as the column data is likely to be pre/suffixed by binary stuff in the DB).

I can believe that something might be relying on some scheduling somewhere to do the backup that is outside the scope of MaintPlan/SQLJobs (Windows Scheduler, a Batch file calling OSQL for example). BUT if the database isn't there SQL Server isn't going to be able to make a backup file, of that I am sure

I presume you are an SA "god"? (otherwise maybe the DB is there but you don't have permission to see it!!)

"File's "owner" tell you anything?"

I meant for the BAK file - does that tell you anything? (maybe you already looked)

Kristen
Go to Top of Page

muxdaddy
Starting Member

5 Posts

Posted - 10/14/2006 :  08:00:25  Show Profile  Send muxdaddy a Yahoo! Message  Reply with Quote
Not sure why, but when i deleted that file, the problem went away. wtf.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 10/14/2006 :  09:01:08  Show Profile  Reply with Quote
Its a bit scary, isn't it. Don't have any suggestions though I'm afraid. Perhaps keep an eye on it in case it resumes after the next reboot, or some other related event!

Kristen
Go to Top of Page

josepabloucr
Starting Member

2 Posts

Posted - 03/02/2009 :  06:10:22  Show Profile  Reply with Quote
We have the same problem, there is a phanthon backup task (no in jobs nor in maintenance plans) that make a backup every day at 0:00 hours in the backup location of a previewsly delete maintenace plan,
I looked at the msdb database, but found nothing.
There is a temp file on the location you mention, I'm deleting it just to see if that helps.
Does some one knows what's the nature of those temp files with backup info inside them?
Go to Top of Page

josepabloucr
Starting Member

2 Posts

Posted - 03/03/2009 :  04:59:57  Show Profile  Reply with Quote
After 2 days looking for a solution, the deletion of the temp file have solved the problem, what is thet tmp file???
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000