Have Geek Brain

SQL Server 2005 Database Log File HUGE, Complete

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

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

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

an attempt was made to perform a full database backup. I got an error saying that the transaction log is full. so i tried log backup

and now if I do a full database backup I don't get this error anymore.

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

I also fell into a shrink that says 650mb of space is free space, but when I said to make space, it didn't do anything. I didn't get an error, it just closed and did nothing. at least I believe it didn't do anything because my log files are still creepy ...

so, first of all, how it happened! all i did was open my database, change the column here and there. and in another database i didn't even do that !!

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

any help is appreciated, thanks!

+2


a source to share


2 answers


If you don't need a moment during restore or need frequent backups, it looks like you can do without log files. Switch to simple recovery mode. This will cause the log to be truncated after every committed transaction, rather than waiting for a backup of the log file.



+3


a source 


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


Please note: this will invalidate your backup history, so after use TRUNCATE_ONLY

you must perform a full backup.

+2


a source






More articles:


All Articles