May 1, 2006 at 6:22 am
I have a database that is hit quite heavily. SQL Server is 2000 Standard Edition SP3a running on Windows 2000 SP4. The data file is 20Gb and the database is in SIMPLE recovery mode. The database transaction log file is 14Gb in size. 98% of that is used, not free. At night and on weekends, the database usage goes way down to almost no users. On Monday mornings, I would expect the transaction log used space to decrease and the free space to increase. Can some one explain to me why this does not happen?
May 1, 2006 at 6:35 am
I just used Lumigent's Log Explorer product to look into the current transaction database log. The majority of the log is old, committed transactions. I see dates of February, March, and April. Database backups occur nightly. Why is the transaction log retaining old transactions?
May 1, 2006 at 7:25 am
You should probably schedule maintanence plans on your database during weekends to shrink your log files on a periodical basis using DBCC commands.
Prasad Bhogadi
www.inforaise.com
May 1, 2006 at 7:36 am
When a database is in SIMPLE recovery mode, the transaction log space is suppose to automatically be reclaimed to keep space requirements small. I should not have to shrink the file. I admit that I will probably have to run a DBCC SHRINK. I am looking for a reason of why the log space is not automatically being reclaimed.
May 1, 2006 at 7:53 am
If you have scheduled a regular backup plan, the inactive transaction log is automatically truncated after the backup process is completed in SIMPLE recovery mode.
Prasad Bhogadi
www.inforaise.com
May 2, 2006 at 2:40 am
Check to see what process's are running.
Had a similar problem where a long running process blocked a re-index leaving a large log.
May 2, 2006 at 8:12 am
In Simple recovery mode, the LOGICAL log is truncated regularly whenever a checkpoint is executed. The physical log file is NOT shrunk. So if a very large transaction runs, causing the log to expand, the file will not be physically shrunk afterwards. As was mentioned previously, schedule a DBCC SHRINKFILE or DBCC SHRINKDATABASE command to run once a week or on a schedule that fits your needs.
May 2, 2006 at 9:19 am
This post also has a discussion on similar issue with simple recovery transaction log truncation issue just for reference
http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=275242&p=1
Prasad Bhogadi
www.inforaise.com
May 2, 2006 at 5:32 pm
Quest Capacity Manager has graphs that show used and unused portion of databases and transaction logs. That should help you identify the amount of unused space that is allocated to the transaction log. It also has an interface to shrink databases/datafiles real-time or at a scheduled time.
http://www.quest.com/capacity_manager_for_sql_server/
Hassan
May 3, 2006 at 4:24 am
Hi,
Try this
Check the auto shrink option in the database properties option.
Ramaa
May 3, 2006 at 5:20 am
Here you go, simple solution:
1. Put Database into full recovery mode.
2. Backup up the transaction log (you should then see used space shrink)
3. Put Database back into simple recovery mode.
4. Shrink the file (Enterprise Manager or DBCC) to reclaim the space.
Note: If you find that the transaction log doesn't free up enough space after your first pass through, repeat the steps as there is a minimum overhead based on the size of the log file.
May 3, 2006 at 6:03 am
I think http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=275242&p=1 is exactly what I am experiencing.
My database was in FULL recovery model and sometime ago I changed it to SIMPLE. I thought checkpoints would automatically occur and my log file will manage itself and keep it size relative small. However, that's not the case. My data file is 20Gb and my log is 15Gb and the log continues to grow. Using Lumigent's Log Explorer I can see committed transactions dating back to January 2006. Using Log Explorer, I can also see checkpoints in the database log. Using DBCC OPENTRAN I do not find any open transactions. Thus, I conclude that SQL Server is not reclaiming the commited log records.
After reading CHECKPOINT overview in BOL, it looks like I would have been correct if my database was in "log truncate" mode, which I don't believe it is. According to BOL's CHECKPOINT overview, SQL Server 2000 will automatically checkpoint any database if the database is in log truncate mode and the log becomes 70% full. A database is in log truncate mode when the database is using the SIMPLE recovery model and one of the following occurs:
1) BACKUP LOG WITH NO_LOG or TRUNCATE_ONLY
2) A nonlogged operation is performed.
3) ALTER DATABASE to add or delete a file.
When I changed from FULL to SIMPLE recovery model, I did not perform one of the three additional steps. Therefore, I plan on detaching the database, deleting the log file, and then re-attaching the database, which will force a new log (number 3 above). Then I will size my log to 600Mb and monitor it. I am betting the checkpoints will occur when the log is 70% full, SQL Server will reuse the commited logs, and the file size will stay small.
I plan on peforming the database creating the new log this Sunday. Sometime next week, I will update this posting to let everyone know how the database log is functioning.
May 3, 2006 at 8:21 am
I think there is a misconception. Per BOL Simple Recovery Model
"After the log space is no longer needed for recovery from server failure, it is reused."
This doesn't mean the file is shrunk after the process runs. So if the file grows due to a large transaction it will remain that size. The data is marked as committed and those areas of the file can be reused. Truncation is not the same as shrinking the file. It is marking the area as reusable.
For instance when you issue a truncate table of a table which takes up 400GB only the marker is removed and the pages marked as empty. The data still exists on the pages and the file remains the same size. Later transactions will write over those pages as new objects and data are added. As well you can use BEGIN TRAN and ROLLBACK TRAN around a truncate table statement and it appears as nothing happens. Also doing the truncate table seemingly doesn't do anything in the log file because the size doesn't grow to match the missing data. The fact is the truncate table statement is logged.
As for what tools might see, many use methods of reading the log file directly and therefore will see the committed transactions still contained in the log file that have not been written over. So I wouldn't trust what they say.
From BOL
Virtual Log Files
Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.
The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files. The number and size of the virtual log files in a transaction log increase as the size of the log file increases. A small log file can have a small number of small virtual log files (for example, a 5-MB log file that comprises five 1-MB virtual log files). A large log file can have larger virtual log files (for example, a 500-MB log file that comprises ten 50-MB virtual log files).
Microsoft® SQL Server™ 2000 tries to avoid having many small virtual log files. The number of virtual log files grows much more slowly than the size. If a log file grows in small increments, it tends to have many small virtual log files. If the log file grows in larger increments, SQL Server creates a smaller number of larger virtual log files. For example, if the transaction log is growing by 1-MB increments, the virtual log files are smaller and more numerous compared to a transaction log growing at 50-MB increments. A large number of virtual log files can increase the time taken to perform database recovery.
As records are written to the log, the end of the log grows from one virtual log file to the next. If there is more than one physical log file for a database, the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file. Only when all log files are full will the log begin to grow automatically.
May 3, 2006 at 2:06 pm
DandD,
Looking forward to your results. I saw that you are using the tool from Lumigent. Have you been using long and has it been easy to get up and running? I have looked at LogPI in the past and would be interested if you have any comparison. Reason I ask is Logexplorer seems pretty steep in price. Thanks.
Curtis
May 30, 2006 at 6:04 am
My plan, see my May 3 posting, worked and my database appears to be in log truncate mode, which means the log is maintaining a small size without my assistance. The log has been removing old, committed transactions and has been maintaining about 50Mb size.
My company has had LogExplorer for about 2 years now. It is easy to install and configure. What little use I have used has proven to be a life savor. It is very user friendly.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply