General discussion

Locked

SQL Transaction File

By Papa Smurf ·
I have a transaction log that has 438 megs in it. I tried to reduce the size but I don't think I did. I did "DBCC shrinkfile/emptyfile". I then tried to "alter database and remove file" but the system will not allow me to remove a primary data file or transaction log which is a good thing. I went back into explorer and the size of the log had not changed. How do I reduce the size to the transaction log, I need the disk space? Also, in SQL Enterprise Manager the transaction log space used is 13.7mb and the free space is 414.1mb. What does this mean?

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Transaction File

by pVp In reply to SQL Transaction File

First, I have to strongly recommend that you take a course in SQL Server administration. Otherwise, you're going to be very dangerous.

The log file is on a "device" that was created at 438 MB and the filename in question was created at that time, at that size (so it's not possible for the file system not to have that much space later, if the file were treated conventionally and growing). According to your figures, so far only 13.7 MB has been used of 438, so 414.1 remains. (Small change goes to rounding errors.)

If you need a smaller logfile, the easiest thing to do is to create new devices and create a new database, re-create all the tables, defaults, rules, stored procs, whatever you have. Then delete the old db, rename the newone to the old name, and delete the old devices as well. Hopefully you have enough disk to do this.

Otherwise, moving a log file is possible but not real easy. Again, create a new, smaller device.

Rule of thumb: your log should be a minimum of 25% of your database size; bigger if you have a lot of activity, especially large, long-running transactions.

Collapse -

SQL Transaction File

by Papa Smurf In reply to SQL Transaction File

I didn't appreciate the sarcasm but the answer was helpful.

Collapse -

SQL Transaction File

by Papa Smurf In reply to SQL Transaction File

This question was closed by the author

Back to Web Development Forum
3 total posts (Page 1 of 1)  

Software Forums