January 6, 2004 at 2:33 am
I have a SQLServer 2k database with a 4Gb log file size. Recovery mode is full and the tranlog is dumping every few minutes. I am however seeing a constant amount ,about 500Mb, of used log space.
Usual transactions are short and the usual output of DBCC OPENTRAN is 'No active open transactions'.
I'm assuming that overheads don't amount to this sort of usage, so I'm looking for suggestions as to why this is happening please.
Regards
Rick
January 6, 2004 at 5:48 am
Are you using replication?
January 6, 2004 at 6:41 am
No replication that I'm aware of. (I'm new to the environment and still finding my feet). My understanding of 'DBCC OPENTRAN' is that it would indicate if there was a Replication Trunc marker in the log ( though I could be wrong).
January 7, 2004 at 5:21 pm
Related Question:
How can you backup transaction logs onto tape and delete logs weekly so your hard drive does not get full ?
January 8, 2004 at 1:34 pm
When you say that the log is 4Gb in size, but there is about 500 Mb in use, do you mean that you are looking at the output of DBCC SQLPERF(LOGSPACE) and seeing approximately 12.5% in the "Log Space Used (%)" column? Or do you mean that the log file is declared so that it can grow up to 4Gb in size, but is currently filling up 500Mb of space?
If you're talking about the size of the physical log file, then log backups won't shrink the file. They're not supposed to. A log backup will (of course) make a backup of the log, and it also truncates the log (that is, it marks the inactive portions as available for reuse within the physical log file). It does not, however, shrink the log files.
If you want to shrink a log file, you should use DBCC SHRINKFILE.
Hope this helps!
Chris
January 9, 2004 at 4:00 pm
I'm refering to the output of DBCC SQLPERF(LOGSPACE). I'm not concerned with the physical size, just the amount used. As an aside, I will be shrinking shortly in order to reduce the volume of Virtual Log files as the growth rate of 2mb has caused rather a lot of Virtual Logs to be created.
Rick
January 9, 2004 at 4:05 pm
>>Related Question:
>>How can you backup transaction logs onto tape and delete logs weekly so >>your hard drive does not get full ?
Maintenance plans can delete backups which are over a particular age. If you're dumping to disk then at some points tapes are backing up the disks then simply set the 'remove files older than..' dialog box to a suitable value.
Hope this helps.
(NB this should probably have been a separate thread to get the largest audience)
Rick.
January 9, 2004 at 4:07 pm
Hi there
What do you see when you run DBCC LOGINFO ? how many of the virtual logs are of status 2 (aka inuse by the engine, either checkpointing or writing to).
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
January 9, 2004 at 5:36 pm
The status 2 records at the time I ran totalled to 16Mb. There were about 9000 virtual Log files. At this time usage was around the 500Mb figure.
Cheers
Rick.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply