March 17, 2014 at 6:29 am
I have a database that was causing a bit of trouble based on the web code hitting it, basically doing excess web hit counts so that when a web crawler hits it, it was merrily logging all kinds of crap for months.
That has been fixed, and a vast amount of useless records (1.4 million) have been deleted down to a hundred thousand or so and I'm guessing the guy did a standard DELETE as he is fairly database clueless. As a result, I have a predictably large log file that is 99% empty, but refuses to TRUNCATE thus far on transaction log backup for 3 days now. The database is set to the full recovery model and is backing up otherwise normally. Anyone know what could be the hold up? Is there any way short of truncating the log directly to get that to happen? :unsure:
March 17, 2014 at 6:39 am
If it's 99% empty, then that suggests that the log backups are successfully truncating the log. If they weren't, your log would be a lot fuller. Truncate means 'mark log records as inactive and mark that space within the log as reusable'. Since the log is 99% free (reusable), your log truncations are occurring properly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2014 at 7:10 am
GilaMonster (3/17/2014)
If it's 99% empty, then that suggests that the log backups are successfully truncating the log. If they weren't, your log would be a lot fuller. Truncate means 'mark log records as inactive and mark that space within the log as reusable'. Since the log is 99% free (reusable), your log truncations are occurring properly.
I haven't yet finished the Transaction Log stairway PDF lol...In any event, if this is normal, then is there a way to reduce the size of the log that won't cause issues in the database? I know I can use "Shrink -> Files -> Log". My other log files are not this large (6GB+) so I guess I assumed the log was truncated as part of the log backup procedure. Still learning stuff here...
March 17, 2014 at 7:15 am
Siberian Khatru (3/17/2014)
assumed the log was truncated as part of the log backup procedure.
It is. Truncate means to mark portions of the log as reusable. It happens on checkpoint in simple recovery and log backup on full recovery. That's why you have 99% free space in the log file, because the log is being truncated every time a log backup runs.
Truncate, as applied to the log file, does not and never has meant 'shrink the file'. If you want the file shrunk, then run a shrink file operation on it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 17, 2014 at 7:17 am
GilaMonster (3/17/2014)
Siberian Khatru (3/17/2014)
assumed the log was truncated as part of the log backup procedure.It is. Truncate means to mark portions of the log as reusable. It happens on checkpoint in simple recovery and log backup on full recovery. That's why you have 99% free space in the log file, because the log is being truncated every time a log backup runs.
Truncate, as applied to the log file, does not and never has meant 'shrink the file'. If you want the file shrunk, then run a shrink file operation on it.
Man, I have gone years thinking I actually know something about this stuff...Thanks Gail.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply