0

I am getting below error.

The transaction log for database 'CRM_QA_V6' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases likely created by IncludeExceptionDetailInFaults=true

When I executed the below mentioned query I got the output:

Query: select log_reuse_wait_desc from sys.databases where name ='CRM_QA_V6'

Output: 'NOTHING'

2 Answers2

6

When your transaction log grows too large, the first thing to check if your backups are working. Depending on recovery mode, the transaction log keeps growing until you back it up.

If you actually don't want backups, and your recovery mode is not simple, you can change it to simple with:

alter database YourDb set recovery simple with no_wait
dbcc shrinkfile(YourDbLog, 1)

In simple mode, the backup log does not try to store every change since the last backup. It keeps just enough information to be able to roll back unfinished transactions. That way, the file will stay limited in size even if you never back the database up.

Andomar
  • 943
  • 1
  • 10
  • 23
  • + 1 for the reminder to address the underlying problem (my answer only looked at the immediate resolution to the error) and for including the explanation of the `simple` recovery mode. – Boris Nikolaevich Aug 24 '11 at 07:50
  • I wouldn't shrink it to 1 Meg, but a more useful number like say 8 Gigs or something that makes sense depending on the size of the biggest transactions pushed through the system. Other than that problem, do this. – mrdenny Aug 24 '11 at 14:43
4

Often this occurs when the disk itself is full, and the log—which is probably set to auto-expand—cannot expand because the OS won't allow it.

Check to see whether the disk where the log file is written is full.

Boris Nikolaevich
  • 141
  • 3