Log Files

Hi

Im having a problem with disk issue, My database is currently 9.5 gb and my transaction log is 28 gb? is there any way i can reset or recreate a transaction log without affecting the integrity of the database (MDF)? Please help

Hi,

First of all you need to do regular backups to keep the size of your transaction log reasonable.

What you can do then is take a full database backup, then switch to simple recovery model and switch back to full recovery model (or just use the deprecating BACKUP LOG WITH TRUNCATE_ONLY).

After this you should be able to use DBCC SHRINKFILE to shrink the log to a smaller size.

Remember to set up a schedule for your database and log backups afterwards.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

You might want to run this query periodically to make sure you don't have any other issues that are preventing the transaction log from being truncated.

-- Find the problem with the transaction log SELECT name, database_id, log_reuse_wait_desc FROM sys.databases

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!509.entry

|||

Also, consider your recovery model. If you have a "Full" recovery model, the transaction log will grow much larger. Depending on your needs (and your backup plan), you can consider using a "Simple" recovery model.

http://msdn2.microsoft.com/en-us/library/ms191253(SQL.90).aspx

-Steve