May 29, 2008 at 9:05 pm
Hi all,
Here's the scenario:
-mssql 2k5
-5Gb data file
-15Gb transaction log file
-full backup once per day
-trans log backups every four hours
-very little activity on the database
The problem:
The transaction log is almost full all the time (less than 50Mb free). Transaction log backups do not appear to be freeing up any space in the log (the TL backup files are only around 1Mb). I don't wish to shrink the physical file, just free more space inside it. The log_reuse_wait column says 0 and log_reuse_wait_desc says 'Nothing'. DBCC opentran says there are no open transactions. Transaction log and Data file are set to autogrow with no limit and there is plenty of space on the volume. The files are not growing regularly / at all.
Any info would be appreciated as to why the log remains so full...
thanks
May 29, 2008 at 9:47 pm
The answer is: Yes, I did miss something completely obvious! I got % used stats from the database before moving it to a different server. At that time, the value aligned with the 'space available' value given by the Management Studio. From then on, I took the 'space available' value to mean the total space available between the data file and log file when in fact it's just the data file. After checking the log space with dbcc sqlperf, it seems that the % log space used is actually very small, as it should be...
Thanks to everyone who had a think about it...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply