A personal repository of technical notes. - CSC

Delete SQL SERVER Database Transaction Log

Problem
- Unable to modify table.
The transaction log for database 'MyDatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Solution
For detailed log backup solutions, see links under References below.

To delete an unneeded log on a development server:

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE(MyDatabase_Log, 2)


To determine log file name used as parameter with SHRINKFILE:

In Microsoft SQL Server Management Studio
  1. Right-click Object Explorer/MyDatabase
  2. Click Properties
  3. Click Select a page/Files
  4. See Database files: for name of log file.

References
BACKUP (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186865(SQL.90).aspx

DBCC SHRINKFILE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms189493(SQL.90).aspx

Troubleshooting a Full Transaction Log (Error 9002)
http://msdn.microsoft.com/en-us/library/ms175495(SQL.90).aspx

How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511

SQL SERVER - Shrinking Truncate Log File - Log Full « Journey to SQL Authority with Pinal Dave
http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/

The transaction log for database 'mydatabase' is full. To find out why space in the log cannot be reused, see the log_reuse_wait : Transact-SQL : SQL Server : MSDN Forums
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/f3467f88-8657-439f-b422-00e4a0dd14ab/

No comments:

Post a Comment