Backup and truncate questions/problems. 1

[Ruairi]

I have a SQL Server 6.5 DB that is allocated 1200 MB for data and 1000 MB for the log. It was showing 500 MB free for data and 40 MB free for the log. I needed to import new data (to synch with a dos system for paralell testing). I made a backup of the entire DB. Then I deleted 90% of the data (a few tables data did not change and these tables are not dependent on any other table being there), truncated the transaction log, and recalculated. Now with no data and a transaction log i just truncated i show 0.00 MB of log space and 20.62 MB of data space when i recalculate. What's going on? How do i completely clear out the transaction log? How did i go from 4,000,000 records worth of tables taking up 700 MB to less than 500 records in the entire DB taking up over 1.1 GB? foxdev, TomSark, I would appreciate a short explanation of backup procedures and clearing out logs if you have the time. Thanks in advance. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[harryrich]

I'm far from expert but I'll give it a shot. You don't say, but I assume most of your 1.1GB is in the log file. When you deleted 90% of the database you generated a large number of log entries placing you near the end of the log file. Then some entries which are still active were made. When you truncated the log you purged the inactive entries but the active entries near the the end of the log were not moved.

My guess is that most of you log is available, but you cant' shrink it. There's an SQL 7.0 FAQ that you might be able to adapt.

 

[Ruairi]

harryrich,
Thanks for taking the time to respond. Actually that 1.1 GB is just whats being used by the data (according to enterprise manager anyway). The log file is on a seperate device. What you said about most of it being available is a little reassuring. Maybe the numbers enterpprise manager is reporting are just wrong. I did a full backup right before deleting everything (full meaning i selected the Database option to backup, not trans. log or single table). Shouldn't this allow the transaction log to be totally cleared?
Thanks again,
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[harryrich]

Sorry, I guess I read your post cross-eyed. The key thing is each log file has three different sizes, in descending order: (1) how much is allocated for it, (2) what size you can shrink it to given its current fragmentation, and (3) how much of it is actually in use.

What you might consider, if things are bad and there is no straight-forward fix, is to restore from backup, export the 10% of the table you want to keep, trunecate the table, and import the records you are going to keep back in. This should result in much less activity than deleting 90% of the table.

Luck!

 

[Ruairi]

I looked again this morning and enterprise manager reports 1000 MB free for the log and 1196.61 MB free for data. Maybe the server just had to be restarted? anyway i guess it's fixed. Thanks for the help. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[knabet]

Ruairi:

I've never used SQL Server 6.5, but in 7.0 the log file continues to grow until a transaction log backup, and maybe a complete database backup will do that too. So your log can grow out of control especially if you Database is used a lot during the day. I'd try doing a transaction log backup when it seems to be full and see if that clears it. I do know you can't change the size allocated for a log or database to a lower amount, only bigger.

Hope this helps

 

[jnicho02]

After truncating the transaction log in 6.5 you will need to either restart the server or do a dbcc checkdb as the page allocation will be all screwy. Its a bit like recalculating index statistics... My home ---->

http://www.geocities.com/jnicho02/

My company ->

http://www.firstlightassociates.co.uk/

 

[Ruairi]

OK, that makes sense since the numbers were right after i rebooted the server. It's always nice to know that it's working like its supposed to. Thanks jnicho02, and everyone for your responses. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com