Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2002-01-23 : 23:07:19
|
I am in a bit of a bind here. For the past few weeks I have been trying different ways of ultimately getting my 25gig SQL Log file down to a decent size. This morning my log file tried to autogrow by 10% but my hard drive was too full and my site was down for a few hours, so right now I let it grow 2% and I've got a few hours to figure out what to do!! .. I read several articles to no prevail. I know you have to truncate and then shrink. I followed the steps with no errors but nothing seemed to happen?? What happens if I just go and physically delete the file?? Can anybody give me a dummy-proof way on how to get this file down in size so I can keep my site up ? Your my hero if you can  Edited by - mike123 on 01/23/2002 23:08:02 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2002-01-23 : 23:09:45
|
| PS: I have read all the msgs in the forum but I still cant do it. Will I totally mess my system by deleting the physical file from windows explorer?Thx again |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-24 : 00:51:10
|
| Are you on 7.0 or 2000?If you truncate the log, then use DBCC Shrinkfile (See BOL)in 2000 this alone will work, if you are on 7.0 follow the steps in this article:http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256650BTW, on log files this size, you should set the autogrow to a certain amount (Like 1Gb or somthing). Growing by percent leads to this type of problem. HTH-Chad |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-01-24 : 08:53:08
|
With SQL 7 I have done this to shrink the log several times; however, it is a bit dangerous...Detach the db.Delete the log.Reattach the db.SQL 7 will recreate a new log file with the default size.I say its a bit dangerous because one out of the 20 or so times that I have successfully done this the db would not reattach  (about 98% of the time I can shrink it without having to come to this method)So this isnt something that I would recommend unless you have a complete backup and are in a emergency.chadmat: Thanks for the KB link. If it works then that would be pretty cool...DanielSQL Server DBA |
|
|
|
MuadDBA
628 Posts |
Posted - 2002-01-24 : 12:28:24
|
| There's also an SP on SWNK.COM's site called SP_FORCE_SHRINK_LOG that shrinks the log file without making you delete it. Try that and see if it works |
|
|
|
|
|
|
nasty situation - log file overflow