| Author |
Topic |
|
Dancewav
Starting Member
9 Posts |
 Posted - 2002-05-24 : 11:03:14
|
| whats the best way to remove trailing space from log file , 3 gig in size , only 50mb used. shrink database and truncate will not clear it.madness |
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-05-24 : 11:14:24
|
| dbcc shrinkfile(file_name, target_size). JamesH. |
 |
|
|
Dancewav
Starting Member
9 Posts |
Posted - 2002-05-24 : 11:52:59
|
| that doesn't work I am afraid. |
|
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2002-05-24 : 12:11:30
|
| You need to backup your log file before you can shrink it. You'll need to back it up and shrink it a couple of times before your log file size begins to go down considerably.To do that select all tasks on your DB properties, select backup database and then choose only the transaction log file.After that you can shrink your log fileHope it will works |
|
|
|
Dancewav
Starting Member
9 Posts |
Posted - 2002-05-27 : 04:29:36
|
| I am afraid it doesn't , the log still has 3 gig trailing free space, mad |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-05-27 : 06:03:33
|
| What was the original size of the log file ? (not the free, but original ?) I've seen that truncate and shrink only seems to recreate a log file the original size , so if you created it at 3gb originally, that's all you'll ever get.THE FOLLOWING METHOD IS DANGEROUS, AND NOT RECOMMENDED FOR A PRODUCTION BOX!!!We got around this problem by making a full backup, truncating the log (may be a unnecessary step), then stop SQL server and "losing" the log file (we renamed it). On SQL startup it recovers the db and re-created a new log file, which was 1 mb in size ( as I recall).HTH |
|
|
|
Dancewav
Starting Member
9 Posts |
Posted - 2002-05-27 : 08:22:26
|
| the original log file was only 1.24MB , not sure what to do next really, had this before and usually shrinking and truncating does the trick, but not this time. Any other suggestions as the one above sounds abit scary ? cheers |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-05-27 : 08:44:03
|
| Alright - a slightly less scary version might be...detach_db, then attach only the .mdf (after renaming the old .ldf).SQL should then create a new log file for the database in the default log directory.HTH |
|
|
|
Dancewav
Starting Member
9 Posts |
Posted - 2002-05-27 : 10:38:33
|
| thats the one, cheers |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-05-27 : 10:44:03
|
| ? Do you mean that that worked for you ? |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-05-29 : 01:16:11
|
| Do you have 7.0 or 2000?DBCC Shrinkfile will work for both. A little work is required for 7.0 though.-Chad |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-05-29 : 02:29:11
|
| What aboutBACKUP LOG DBName WITH TRUNCATE_ONLYorBACKUP LOG DBName WITH NO_LOGThen shrink it using DBCC SHRINKFILEDavidM"SQL-3 is an abomination.." |
|
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2002-05-29 : 05:21:36
|
| I believe he already tried that (see the first 3 or 4 posts where that was recommended). Obviously, truncate and shrink is the first choice, but I've experienced the same problem ot if refusing to get really small, hence my "scary" method.I should add that I did this on SQl 7 SP 2- Dancewav doesn't actually mention what version and SP he is on... |
|
|
|
chadmat
The Chadinator
1974 Posts |
|
|
sumwanlah
Starting Member
43 Posts |
Posted - 2002-05-30 : 22:21:06
|
I think what you first need to do is to assess the need to stop the transaction log file growth. Prevent this monster from growing out of hand. Don't worry about the log file running out of space; if you perform regular backups (either full or log backups) the log file should keep within the fixed file size constraints. If you didn't know by now, backups will write committed transactions stored in the logs into the data file, then remove them from the logs.Next, you need to understand that you can almost never get this 3Gig leviathon to shrink to back to 50Megs. The way transaction log files are created physically and logically, it takes a long while before you can find the opportunity to shrink them at all!!!I'd encountered this problem before... In the end, unless in extreme cases, I will enable the option 'Truncate log on checkpoint'. Then fix the file size to a manageable one. This would be good especially if you have a good backup plan.Anyway, you should refer to Books Online. It has a great reference on the intricacies of Transaction Logs. Check this little passage I took from the topic "Shrinking the Transaction Log". It explains why, sometimes, commands like DBCC Shrinkfile and Truncate Logs functions can only do so much. quote:
When any file is shrunk, the space freed must come from the end of the file. When a transaction log file is shrunk, enough virtual logs from the end of the file are freed to reduce the log to the size requested by the user. The target_size specified by the user is rounded to the next highest virtual log boundary. For example, if a user specifies a target_size of 325 MB for our sample 600 MB file with 100 MB virtual log files, the last two virtual log files are removed and the new file size is 400 MB.
Read BOL, really... It's pretty useful. In this case, good topics to refer to are: "Truncating the Transaction Log" and also "Transaction Log Physical Architecture". Helps you to better appreciate the problem.I like to add-on to the remark chadmat made about 2000. It has the flexibility to shrink individual files, data or log, down to the size you want. BUT! The rules and conditions spelt out in the BOL, will kinda limit this flexibility.Finally, if you really need a solution to your problem. I can tell you this. I came across an sp written by someone else, posted on another site, that came up with a slightly less painful method than dropping and recreating new log files. What he did was to create a temp table in that database affected, write new records into that table, and try to shrink the log file as the same time. Of course, when it can't shrink the file anymore, it will stop executing and drop the temp table. By then, you should have a MUCH smaller log file. All you need then is to do house-cleaning, setting file size limits and growth potentials and you're done.I don't think I should freely distribute his codes, just yet. I don't wanna rob him of his due credit for writing that hide-saving sp. If the forum moderators are okay with me posting the URL to the site where you can find this sp, I'll be happy to do so.------------------------What goes around...? Is the belt around my waist! |
|
|
|
|
silly one this