SQL Server Performance Forum – Threads Archive
T-Log full and simple recovery
Why am I getting a message "The log file for database ??? is full. Back up the transaction log for the database to free up some log space" when I have the database set to Simple Recovery? You cannot backup the log in Simple Recovery, so what’s up?Do you have automatic grow in log?
Also, do you have disk space?
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
quote:Originally posted by LuisMartin
Do you have automatic grow in log?
Also, do you have disk space?
Also, do you have disk space?
It turns out there is a disk space issue and I do have auto grow turned on. Which leads to the question, why is there tran log growth in simple recovery? What is being logged or why is any logging taking place, period?
You still use the transaction log for single operations. So, for example when you reindex a large table, you could potentially use a large amount of space temporarily. If you are doing several of these at once, then it will grow even more. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
You can shrink the Transaction log using DBCC SHRINKFILE and ensure to monitor the growth of Tlog to avoid such situation. And also refer to the actions specified in the books online for the error 9002. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
You have huge long running transaction. It may be (re)indexing or something application is doing. Log can’t be trancated untill long runing transaction finishes.
quote:Originally posted by derrickleggett
single operations
What do you mean by "single operations"? So even if you have the database set to Simple Recovery, the transaction log is still used for "single operations"?
From BOL: Transaction Log Architecture
Every Microsoft® SQL Server™ 2000 database has a transaction log that records all transactions and the database modifications made by each transaction. This record of transactions and their modifications supports three operations: Recovery of individual transactions.
If an application issues a ROLLBACK statement, or if SQL Server detects an error such as the loss of communication with a client, the log records are used to roll back the modifications made by an incomplete transaction. Recovery of all incomplete transactions when SQL Server is started.
If a server running SQL Server fails, the databases may be left in a state where some modifications were never written from the buffer cache to the data files, and there may be some modifications from incomplete transactions in the data files. When a copy of SQL Server is started, it runs a recovery of each database. Every modification recorded in the log which may not have been written to the data files is rolled forward. Every incomplete transaction found in the transaction log is then rolled back to ensure the integrity of the database is preserved. Rolling a restored database forward to the point of failure.
After the loss of a database, as is possible if a hard drive fails on a server that does not have RAID drives, you can restore the database to the point of failure. You first restore the last full or differential database backup, and then restore the sequence of transaction log backups to the point of failure. As you restore each log backup, SQL Server reapplies all the modifications recorded in the log to roll forward all the transactions. When the last log backup is restored, SQL Server then uses the log information to roll back all transactions that were not complete at that point. The characteristics of the SQL Server 2000 transaction log are: The transaction log is not implemented as a table but as a separate file or set of files in the database. The log cache is managed separately from the buffer cache for data pages, resulting in simple, fast, and robust code within the database engine.
The format of log records and pages is not constrained to follow the format of data pages.
The transaction log can be implemented on several files. The files can be defined to autogrow as required. This reduces the potential of running out of space in the transaction log, while at the same time reducing administrative overhead.
The mechanism to truncate unused parts of the log is quick and has minimal effect on transaction throughput.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
quote:Originally posted by LuisMartin
From BOL:
Transaction Log Architecture
Every Microsoft® SQL Server™ 2000 database has a transaction log that records all transactions and the database modifications made by each transaction.
Every Microsoft® SQL Server™ 2000 database has a transaction log that records all transactions and the database modifications made by each transaction.
According to your response, even in Simple Recovery "Every Microsoft® SQL Server™ 2000 database has a transaction log that records all transactions and the database modifications made by each transaction". Sounds like there is no difference in the transaction log when it comes to Simple vs. Full Recovery?
As far I know the difference is only for backup. I mean, with full you can backup transactions log, etc. With simple you have to full backup.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS†with no warranties for accuracy.
quote:Originally posted by LuisMartin
As far I know the difference is only for backup. I mean, with full you can backup transactions log, etc. With simple you have to full backup.
In that scenario wouldn’t the transaction log grow, grow, grow, and since you are in Simple Recovery you could not back it up to reduce size?
The difference is the way the log file retains the data. Each single operation (really each single transaction) requires the use of the log space to guarantee consistency of the transaction and provide room for the data modification of the operation. When the transaction has completed, the space is freed up in the log file IF you are in simple recovery mode. If the recovery mode is full or bulk, you retain the history of the transaction so you can back them up and recovery to a certain point in time. <br /><br />In other words, the log file can get fairly big, depending on what’s going on. We had a developer recently who created a cartesian join against millions of records. The log file off that one operation grew to 120GB. <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /> Plan, plan, plan. Processes and planning exist for a reason. The disk subsystem needs to have sufficient size and capacity to handle the max concurrent amount of transactions on the server. If it doesn’t, you will have wait times, or worse, the breakdown of the entire environment from bad planning.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
]]>