SQL Server problem *sigh*

[drebo]

So, my predecessor before me some years ago decided it would be down-right neato to set up a SQL Server 2000 database to log hits to one of the websites my company runs. Well, needless to say, a database entry for every single file access is going to grow very large. After five years, the database has grown to a whopping 9gb. To make it better, he was using Full recovery mode...our transaction log was approaching 10gb yesterday.

Normally, this would be a drop in the bucket as far as storage goes...however, this guy, in his infinite wisdom, created a server hosting ~20 websites on a Pentium 3 800mhz box with 512mb RAM and two 36gb drives in a software mirror. Needless to say, the server is far from robust. It's not fast. It doesn't work very well. I've been migrating everything that I can off of it.

Now to the problem... This over-burdened piece of shit is out of space. I've switched it over to Simple recovery mode and shrunk the log file, and that freed up about 10gb of space. However, I need to get rid of this database, or at least delete some of the data in it.

Problem with that is that the server lacks the processing power to do anything about the data in it. I've tried deleting rows one at a time, everything before a specific date, as well as just plain deleting EVERYTHING. It always times out.

I'm fresh out of ideas. If anyone has an idea of how to remove data from this database, I'm listening.

 

[Kntx]

Deleting records from a table will (at least temporarily) increase the size of the database. What I'd do if I really didn't care about any of data in the table is this:

TRUNCATE TABLE the_table_name

This will remove all data from the table without writing any entries to the transaction log. You might want to grab a backup first in case you ever want it back or to restore the database to another server.

 

[drebo]

You know, I can't stand it when the solution is staring me in the face...

TRUNCATE...uhg. I'd forgotten about that one.

And, the likelihood of us needing website logs from 2003 is pretty slim.

Thanks for the suggestion.

Edit: Ahh, it's a beautiful thing... TRUNCATE TABLE InternetLog; DBCC SHRINKDATABASE bvn_weblog; 9gb goes poof!

 

[imported_Dhaval00]

By the way, why are your logs on the same machine? Bad, bad practice. They should be on a tertiary drive... if the machine crashes, what's the point of full recovery mode.

Ignore the post if I misinterpreted your original post.

 

[drebo]

Originally posted by: Dhaval00
By the way, why are your logs on the same machine? Bad, bad practice. They should be on a tertiary drive... if the machine crashes, what's the point of full recovery mode.

Ignore the post if I misinterpreted your original post.

One would tend to agree with you. However, nobody ever accused my predecessor of doing things the right way.

Of course, that's besides the point. If it were up to me, our two cabinets full of servers would be a single cabinet with our routers and an Intel Modular Server running all of our servers off of its integrated SAN-type-dealie. It would be clean and a hell of a lot better than what we've currently got. But, hey, it's not up to me and I gotta go with what they're giving me.

 

[techfuzz]

Dhaval00, the standard practice is to put the OS on one drive (RAID 1), the logs on another drive (RAID 1), and database on another drive (RAID 5). With a decent backup solution in place that does either partial or full backups on a daily basis, you'll greatly limit your risk to catastrophic failure. By catastrophic failure I mean two drives simultaneously failing in any of the RAID sets or the entire server going up in smoke (act of God).

If you really want to lesson your risks further, you can employ database mirroring or log file shipping. Of course now you're talking about 2 duplicate systems which costs a lot more $$$.

And if you're crazy and have money to burn, toss in some RAID 6 or RAID 5+1 on top of your mirroring/shipping.

I guess my point is, unless you dealing with a mission critical system that would put your business on hold indefinitely OR cause a substantial momentary loss to the company, you have to balance risk vs. cost. In the OP's situation, unless his website is an ecommerce or similar site that is critical to his business (which I highly doubt), his database is about as non-mission critical as you can get.

techfuzz