SQL Server 2000 Log File

Factual Questions
1

OK, I’m in a bit of a spot here at work, and it’s entirely my fault. I’ll avoid boring you with the details, and skip to the meat of the deal.

I need to recover data from a transaction that happened on January 8th. I’ve used Red Gate’s SQL Log Rescue for stuff like this before, and it’s pulled my ass out of the fire many times. But today seems to be the end of my lucky run. It’s only pulling from today at 2:18pm (EST) onwards.

Somewhat less than I would have expected.

Now, this log file weighs in at over a gig (it’s a busy server!) but what gets me is that the first handful of transactions available to me are right before the end of a rather large (unrelated) DTS job.

So my questions are:

  • Did the DTS job do something to the log file? Because it’s set to grow as needed I would assume that the thing would just get bigger and bigger, and not auto-truncate itself.

  • Is there a chance my data from 1/8 is still recoverable? I’m willing to try other programs that you guys recommend, but they’d better show me they work before they start asking for money.

I’ll certainly provide info as requested (at least, as much as I can) but any help is, of course, appreciated.

2

Why not restore your backup of the log file closer to the 8th and recover from that?

3

Yeeeeeaaaaahhhhh… let’s just say… that’s not an option >_>

4

OK, just about everyone in the US should be at work now, so I’ll give it my one bump to see if any DBAs can give me a hand.

5

We’re going to need more info about your setup. Do you regularly back up your transaction logs? Your database? What mode do you run your database in? Full Recovery mode? Something else?

What does the DTS package do? What is the time differential between when the DTS package ran and when you did what you wanted to back out? What is the earliest record in the log?

Your comment seems to suggest that you don’t do regular backups that you can restore from, or something…what exactly IS the situation?

6

(OK, now I’m pissed. My session timed out while I was typing. Is it really that hard on the server to set the session length at an hour?)

All right, crazyjoe, I’m trying again.

  • No backups aside from the mirrored drives on the SQL box itself (which obviously does no good here). At the old place the internal network just wouldn’t have been able to cope with it, and I’d be out of my depth trying to back up a database that is 60GB in size, can’t be brought down, and does 100,000 txns a day.

  • I gotta cop to some ignorance here. The only “modes” I know of in SQL Server are single-user and not-single-user. Tell me what my options are and I may be able to make a guess. (Or, tell me if there’s a way I can find out either through Enterprise Manager or on the server itself.)

  • The DTS was to import address data for the US. We use it in the web app the database drives. We can do without, and would have continued to do so if I’d have known about the problem earlier, but I didn’t learn about the problem until after the data was in.

Now, here’s the details on what happened. You can probably skip at least some of this.

We have devices spread across the lower 48 that report to us from time to time (every 3 minutes when they’re active, every 4 hours when they’re not). This gets stored in a table I’ll call Reports_Current here.

Every night at 4:00 AM EST we have a VB.NET program that churns through the reports and archives anything more than 48 hours old. These go into month-based tables (say, Reports_2008_12) that hold about 3 million rows each. Keeps things manageable.

Since we’re offset from midnight (we want to make sure the west coast has a chance to wind down) sometimes the program will see reports from two different months. Like it will start in November and finish in December. The program is supposed to create a month table if it doesn’t exist. There was a bug (fixed now) that only created the table for the first month it saw.

That means that, on the day you switched from 11/30 to 12/1 the first ping from 12/1 would generate an error trying to write to a table that didn’t exist. November was already created and so was fine, the bug prevented December from being made. The next night, December would be first and get made, and everything would be OK.

Except that there was a try/catch block that was screwed up, and instead of erroring out or gracefully stopping, the program would fail to insert into the monthly table but still delete from the current table. (That’s fixed now, too.)

We never noticed because it was only a few hours while 90% of the devices were inactive. If someone had wanted a device that was active during that time, we would have discovered it sooner. But it just never came up.

When we moved, Murphy allowed the servers to go in no problem, but he took his payment out of my workstation. It took me a while to get the archival stuff out of my own backups and get it installed on a new machine. Specifically, it took until the 7th of this month (not the 8th, like I said above).

Now, that thing tore through December just like it was supposed to, no problem. But then that bug jumped up and bit me in the ass when it failed to make the 2009_01 table, then proceeded to drop four and a half days’ worth of reports on the floor.

And we didn’t notice then, because nobody went looking for reports right away.

But now someone called in yesterday and wanted a report for the first, which I can’t give him. Which is why I went groveling through the logs, only to discover that, apparently DTS does some sort of auto-trunctate that I didn’t know about. (That’s somewhat annoying if true, but otherwise the log should have just grown and grown – it’s already back over 5 gigs.)

Now, I’m not in a great deal of trouble here; the boss knows that only having one VB.NET programmer (and only one DB person, and having them be the same person) is a mistake waiting to happen. But I will be the one who gets to tell the customer that he can’t have his data because it doesn’t exist any more. Which is what I’m assuming will happen, but I figure finding someone here who’s smarter than me won’t be too tough :smiley:

7

Ok, the mode is what you will see if you go into EM and get the DB properties, and go to options, I beleive. You are looking for the recovery mode. And based on your notes above, it sounds like even if you are in full recovery mode, you’re screwed because you’re not backing anything up, just allowing it to accumulate in the transaction log.

Unfortunately, sounds like you are using a lot of “worst practices” here, in terms of not backing up the DB, or at the very least, the transaction log. The internal network should have very little to do with it, since you should back up locally to disk, and then transfer the file to somewhere else on the network, like tape storage. There are several utilities, like SQL Litespeed, RedGate SQL Backup, and Hyperbac, that will both speed up backup times and compress the resulting file for you, reducing the load on the server and the network. Check into them :slight_smile:

Transaction log backups, at the very least, should start now. This will capture and store off everything that is happening at a transaction level, without containing it in one single point of failure, your transaction log device. You’re going to want to do research intot his because there is more to it than I can adequately summarize in a message board post.

Unfortunately this is all “future” stuff. It does sound like your transaction log may have been truncated somehow (you’ll have to inpsect your DTS package to see if that was the source, or if you just ran out of space and someone else decided to truncate it).

You have one possible hope…is anything doing a file-level backup on the server? If so, and if it’s sophisticated enough to backup open files, you may be able to attach the old DB files to your server and pull the data out into a temporary table or file, and then import it into your current DB.

8

Ain’t gotta tell me twice, amigo. Good stuff you gave there, except for the magic wand I was looking for :slight_smile:

The DB is in Full recovery mode, let’s hear it for sane defaults! And I also know that nobody else trunc’ed the log, because I’m the only one here who knows how to do it. (The blessing and curse of a small shop.) That leaves the culprit as either DTS, or maybe some setting somewhere that I missed that lets the DB trunc its own log. Either way, I’m boned, this time at least.

Oh, and the network was an issue with the backups, just 'cause the boss’s (and my, at the time) idea was that backups were for when the machine shit itself, because the logs were supposed to go back to the creation of the DB. So, storing backups locally wouldn’t have accomplished much if the machine roasted itself… that would have just meant two copies of the DB were dead.

I get what you’re saying about backing up locally then copying over the network, but at the new place our gigabit network can actually do (almost) a gigabit per second. Backing up over the network now won’t be a problem, as long as I can find something that, like you said, can back up the open MDF and LDF files. I’ll probably look into Red Gate’s stuff; like I said at first their free log analyzer’s been mostly good to me, and if the data ain’t there it isn’t their fault.

Right now I’m trying to figure out how many copies I want to make – and thus how much network drive space I’ll need. My assumption is that I’ll need a version of the MDF that matches the LDF, is that right? Or is any MDF + any LDF enough to do a re-mount on a dev machine while I paw through things?

9

No, your MDF/LDF files have to match up exactly, which is why I said it MIGHT work. Odds are long against it, it was just a desperate attempt. You can do this if you detach the database files while you are backing them up, but that renders the DB unusable. You really want to use a database backup program, like the one native to SQL server, or one of the options I mentioned in my prior post. These ensure the consistency of the database, and can be done while it’s online.

We use RedGate where I am and I would estimate (based on knowing nothing about your data, etc) that you could compress a 60GB database down to around 10GB or so.

Typical upkeep is something like hourly (or even more granular, if you like) transaction log backups, kept for 7 days (a couple days on the server, the rest on tape or on another server) a once-weekly full backup retained for 4 weeks (many places do even longer, we keep 6 months to 1 year’s worth, I beleive) on tape or file server.

As far as needing a gigabit network to handle your backup traffic, that’s just silly. Your boss’s methodology was obviously unsound, but you can easily do transaction log backups that only back up the recent changes to your DB since the last log backup, and store them offsite. You siad the log has only grown to 5GB, so I would imagine that if you were to do hourly or even daily tran log backups, they would be manageable at 100MB or even less. Easily handleable on a 100Mbit network. Hell if you’re that concerned, jam a thumb drive in the server and store the logs there :slight_smile: