can't reduce size of Log File - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

can't reduce size of Log File

Author

Topic

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2004-11-03 : 06:57:13

One the lurrvly databases I've inherited is on SQL7 server, has just 1 table in it of approx 10 million rows. It is only written to once a month, importing another million rows or so. For some unknown reason it was set to full recovery mode, which I've now changed to 'trunc on checkpoint' / simple. Who care's about a transaction log for an import when I already have the data that went in on a seperate file right?

The data itself is currently approx 3gb, but the log file is 4.5gb. Only about 36mb is actually used, the rest is free. I really need to free up that empty space back the disk but can't get it to shrink. Looking at the db properties in EM, the log file size is set to 4.5gb, so when I try a dbcc shrinkfile it only reduces back to the original size of the file (4.5gb), which doesn't help.

I've even tried manually altering the size of the file in EM, but it tells me I can only change it to a size larger than it currently is.

Can I use and ALTER DATABASE? not really clear from BOL whether this will change / reduce the file size, I'm kind of assuming I will have the same problem as when I tried it through EM.

Any suggestions?

Soulweaver
Starting Member

18 Posts

Posted - 2004-11-03 : 07:36:13

greetings

ah, logs, we love 'em, but seriously, its not nice if you can't get rid of them when you need to.

you did the shrinkfile, which didn't help, you probably did a backup log with truncate out of desperation.

now what you need to do is find a stored procedure called 'sp_force_shrink_log' search the web for it, it works almost every time for me.

Just be careful, it will quite probably break your recoverability of the database. When I use it, I always create a full backup afterwards.

Tiaan

-----------------------
Black Holes exist where God divided by Zero
-----------------------

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2004-11-03 : 08:48:28

mmm... searched the web, found some script for creating that proc. Maybe I'm just lacking the confidence to go ahead and do this, but is it really ok? Is there not a standard Microsoft way of doing this? (what am I saying? of course not eh?)

Any other suggestions before I try this proc