Transaction Log Has Filled Up My Servers Hard Drive 1

[Skittle]

Came in to work today to find our SQL server has a problem.
When anybody tries to access the data via a web page we get the error:-

The log file for database 'Main' is full. Back up the transaction log for the database to free up some log space.

We've had a look at the server and found a transaction log 133gig in size has eaten up the hard drive. My understanding is that the log is used to hold transactions waiting to be applied to the database proper. It is in effect transactions that have not yet been 'commited' and that could be rolled back.

Anybody have any ideas what could have caused this to occur?
Anybody any ideas on how I should get rid of the log? The browser text says back it up and delete but that will take a few hours even though its almost certainly what I will do.

 

[tlbroadbent]

The log will be emptied if backups are done. Is the database being backed up? Are transaction log backups being done? Backups will remove committed transactions.

In order to recover the disk space you'll need to remove the committed transactions. You can use BACKUP LOG ... WITH TRUNCATE_ONLY to remove the transactions.

BACKUP LOG YourDatabaseName WITH TRUNCATE_ONLY

Then you'll need to shrink the file. See faq183-1534 - Shrinking Databases and Logs - SQL 7 and Higher.

You may also want to review faq183-1784 - How Do I Develop a Backup and Restore Strategy?


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Skittle]

Thanks. Your posting put me on to the solution.
Much appreciated.