设为首页收藏本站

SAP Best Business Solution

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 156|回复: 0

The transaction log for database is full

[复制链接]

13

主题

13

帖子

144

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
144
发表于 2017-10-14 16:04:23 | 显示全部楼层 |阅读模式
Problem
When running some transactions on a database the following error appears:
Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'Database_Name' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Solution
After the transactions are committed and the data pages are written on the disk, SQL Server tries to clear the transaction log data that is not needed anymore. By doing, it tries to reuse the transaction log for future operations. There are several possible reasons why the transaction log cannot be reused: open transactions, replication, a transactional backup is necessary, etc
To find out which one applies to your case execute the following statement:
view plaincopy to clipboardprint?

  • SELECT name, log_reuse_wait_desc FROM sys.databases  

Keep in mind that it is possible to have more than one reason preventing log reuse. The log_reuse_wait_desc column will show only one of the reasons and, after fixing it and query the sys.databases view again, you can see a different log_reuse_wait reason.
To better interpret the log_reuse_wait_desc column you can access the following link:
The above error appears because the transaction log is full(has reached the maximum file size).


...



To solve this issue you have the following options:
  • Increase the maximum transaction log file size
To increase the maximum transaction log file size, right click in SSMS on the database and choose Properties from the drop down menu. After the Properties window is displayed, go to Files section and click on both the data and log Autogrowth / Maxsize column and increase the maximum transaction log file size or set it to Unlimited.


  • Backup transaction log
If it is a production database you probably have the Recovery Model set to Full and perform transactional log backups, to be able to recover your database to a point in time.
In this case, to avoid the ?The transaction log is full? error, you must back up your transaction log file. While performing the backup of your transaction log file, the SQL Server automatically truncates the inactive part of the transaction log. The inactive part contains the completed transactions, and is no longer used by SQL Server during the recovery process. In this way, SQL Server reuses this inactive space in the transaction log, instead of allowing the transaction log to continue to grow and to use more space.
To back up the transaction log right click in SSMS on the database and choose Properties from the drop down menu and choose Task -> Back Up.
In the Back Up Database window, verify the database name (the Recovery model must be either FULL or BULK_LOGGED) and choose Transaction Log in the drop down menu of Backup type.
Go to Destination and choose the location on the disk where you want to store the backup and then click OK button.


If you have to do this often is a good idea to increase the frequency of the Full or Transaction log backups.
  • Truncate the transaction log
If it is a test database that is not important and it doesn?t need to be back up, you set the recovery model to Simple and truncate the log file.
To change the Database Recovery Model from Full to Simple, right click in SSMS on the database and choose Properties from the drop down menu. After the Properties window is displayed, go to Options and change the Recovery Model to Simple.

After changing the Recovery Model,you need to shrink the transactional log to free the space. To do this right click again in SSMS on the database and choose Task -> Shrink -> Files.

In the Shrink File window select the File type as 'Log' and the name of the transactional log file will be displayed in the File name drop down list.
In Shrink action choose Release unused space option (radio button) and then click OK button.

The same thing can be accomplished by running the bellow command after replacing database name and the transaction log file name (use sp_helpdb [Database_Name] command to find out the log file name).
view plaincopy to clipboardprint?

  • USE [Database_Name]  
  • GO  
  • DBCC SHRINKFILE (N'Database_Name_log' , 0, TRUNCATEONLY)  
  • GO  



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则



QQ|Archiver|手机版|小黑屋|www.sapbbs.com    

GMT+8, 2018-2-18 05:40 , Processed in 0.429618 second(s), 28 queries .

声明:本站严禁任何人以任何形式在本论坛发表与中华人民共和国法律相抵触的言论!

本站内容由网友原创或转载,如果侵犯了您的合法权益,请及时联系处理!© admin@sapbbs.com

快速回复 返回顶部 返回列表