admin 发表于 2017-10-14 16:04:23

The transaction log for database is full

ProblemWhen running some transactions on a database the following error appears:Msg 9002, Level 17, State 4, Line 2The 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
SolutionAfter 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, etcTo 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:http://msdn.microsoft.com/en-us/library/ms345414.aspxThe 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.http://blog.sql-assistance.com/media/blogs/DBABlog/Post_0/The_transaction_log_for_database_Database_Name_is_full_1.jpg?mtime=1386950108
http://blog.sql-assistance.com/media/blogs/DBABlog/Post_0/The_transaction_log_for_database_Database_Name_is_full_2.jpg?mtime=1386950108

[*]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.http://blog.sql-assistance.com/media/blogs/DBABlog/Post_0/The_transaction_log_for_database_Database_Name_is_full_3.jpg?mtime=1386950108
http://blog.sql-assistance.com/media/blogs/DBABlog/Post_0/The_transaction_log_for_database_Database_Name_is_full_4.jpg?mtime=1386950108
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.http://blog.sql-assistance.com/media/blogs/DBABlog/Post_0/The_transaction_log_for_database_Database_Name_is_full_5.jpg?mtime=1386950108
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.http://blog.sql-assistance.com/media/blogs/DBABlog/Post_0/The_transaction_log_for_database_Database_Name_is_full_6.jpg?mtime=1386950108
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.http://blog.sql-assistance.com/media/blogs/DBABlog/Post_0/The_transaction_log_for_database_Database_Name_is_full_7.jpg?mtime=1386950108
The same thing can be accomplished by running the bellow command after replacing database name and the transaction log file name (use sp_helpdb command to find out the log file name).view plaincopy to clipboardprint?


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



页: [1]
查看完整版本: The transaction log for database is full