SQL Server 2005 Database Logfile is HUGE, full

Question

Let me start by saying I am not an SQL or SQL Server expert by ANY means. I know how to create a database and develop a website that uses it. Beyond that, db administration is completely lost on me :(

that being said I'm stuck. My log file for two of my databases has grown ridiculously large. One is 650MB, and the other is 16 GB that's GIGABYTES! the data in both of these databases is less than 10mb, so I can't imagine how in good god this happened...

I did some research on this which led me to believe that doing either a full database backup or a transactional log backup would fix this...

attempting to do a full database backup I received an error telling me the transaction log was full. so I tried the log backup instead

that worked, and now if I do a full database backup, I no longer get that error.

however, the database log files are still the same size as before.

I went into the shrink too which says that 650mb or so of space is free space, but when I told it to free the space it did nothing. I received no error message, it just closed and did nothing. at least I believe it did nothing because my log files are still gimongous...

so first of all, how in the heck did this happen?! all I did was open my database, change a column here and there. and on the other database I didn't even do that!!

finally, how do I fix this so that I can clear out that log?

any help is appreciated, thanks!

Answer 1

USE MyDatabase
GO
Backup Log MyDatabase WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE (MyDatabase_Log, 1)
GO

Note, this will invalid your backup history, so after using TRUNCATE_ONLY you must perform a full backup.

Answer 2

Unless you need point in time restore or need to do frequent backups, it sounds like you can do without the log files. Change to simple recovery mode. That will cause the log to be truncated after each committed transaction instead of waiting for a log file backup.