October 16, 2010 at 1:36 am
Hi
I have a small (personal) database that is used very rarely
however the daily log file backup is around 90 MiB
just now I did this:
backup log Accounts to disk = 'AccountsLogA.bak'
-- no database activity here
backup log Accounts to disk = 'AccountsLogB.bak'
AccountsLogA.bak is 182 KiB
AccountsLogB.bak is 92 MiB !
dbcc loginfo is also giving strange results
before backing up the log it had a single in-use vlf out of 37
after backing up the log it had 22 in-use vlfs
This is a personal database - all my production and development databases are behaving normally
This database alone will not play ball! and I have used every dba trick I know ...
I am just curious as to what it could be
October 17, 2010 at 5:00 am
Kimberly tripp has a series of articles on log files. This is maybe related to vlf size?
start here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
---------------------------------------------------------------------
October 17, 2010 at 1:40 pm
george sibbald (10/17/2010)
Kimberly tripp has a series of articles on log files. This is maybe related to vlf size?start here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
I am ashamed to admit this database had "autoshrink" enabled (and has wrong collation - I must have created it many many years ago)
this seems to have the side effect of marking many vlfs as dirty thus creating very large log file backups
To reduce my vlf count I tried:
alter to simple recovery when the first in-use vlf was lower-numbered
shrink the log file as small as possible (4 vlfs)
grow the log file to 1000mb in one go (12 vlfs)
shrink it back to 100mb (8 vlfs)
alter back to full recovery
Perform full backup
Perform first log backup (80k)
Perform second log backup (20k) // problem solved
Perform third log backup (20k) // really solved
Another bug bites the dust, thanks for your post 🙂
October 17, 2010 at 2:16 pm
thanks for the feedback. Like any other non-read activity, shrinks are logged.
---------------------------------------------------------------------
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply