Set offline failed for database 'MyDB'

Question

My program failed by this exception:

System.Data.SqlClient.SqlException: 
The transaction log for database 'MyDB' is full. 
To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I noticed that my tables doesn't load in SQL Management Studio and i cann't open database properties window

Then I try to change my log file to autogrowth by this statement:

 ALTER DATABASE MyDB
 MODIFY FILE
 (NAME=MyDB_Log,MAXSIZE=2TB,FILEGROWTH=20MB);

this statement executed successfully but doesn't help me to recover my database Then i try to set offline MyDB the operation failed by some exception Then i set the database in single user mode but the exception still exists Then i try this statement:

ALTER DATABASE MyDB SET EMERGENCY;
GO
ALTER DATABASE MyDB set single_user
GO
DBCC CHECKDB (MyDB, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE MyDB set multi_user
GO

This statement failed too on first line.

And now i don't know what should I do.

Answer 1

Follow the following steps.

• Open up SQL Management Studio and connect to your database server
• Right-click your database
• Click Properties
• Click the Options link
• Set the Recovery Model to Simple as follows

• Click OK
• Once this is complete, right click on the database again
• Click Tasks>Shrink>Files
• On the Shrink Database window select the file type as 'Log' . The file name appears in the filename drop down as databasename_log as follows:

The space used versus the space allocated displays. After you set the recovery model to Simple, the majority of the space in the transaction log released.

• Ensure that the Release unused space radio button is selected.
• Click OK on this window to shrink the transaction log.

You might also want to read through this short post http://sqlity.net/en/556/t-sql-tuesday-25-%E2%80%93-sql-server-tips-tricks/