October 5, 2011 at 2:52 am
Hi Guys
I have an issue where a log file of a database is huge +- 350GB whereas the database is 120gb.
The database is in full recovery mode, full backups are being made every night. No scheduled transactional log backups are being made (this was stopped as a request from our client).
Now I knew the log file would grow this large without transactional log backups. So i made a log backup now, (log backup is 349GB) but the log itself is still huge, I have tried shrinking it as well.
Right click - task - shrink - files and chose the log file, but it is not shrinking.
Can anyone shed some light on this?
Regards
October 5, 2011 at 3:15 am
Could you post the output from the following query when executed against your database
DBCC LOGINFO()
run a further transaction log backup to cycle the log and then try shrinking again. With a file that size i would shrink it in stages to get to the ideal size, but it all depends on the output from the above query
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2011 at 3:23 am
It returned over 800 rows :ermm:
So I guess I need to schedule the transactional log backups to run let's say every 3 hours?
October 5, 2011 at 4:00 am
ruan.keyser (10/5/2011)
It returned over 800 rows :ermm:So I guess I need to schedule the transactional log backups to run let's say every 3 hours?
:w00t:
start the log backups running as frequently as needed. Shrink the log in stages to avoid swamping the system, re run DBCC LOGINFO() to see how many log sequences exist. You need to know how large the log is required to be for the frequency you are implementing.
If you don't need point in time restores you would probably be better off switching to Simple recovery
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2011 at 4:03 am
Thanks.
But unfortunately simple recovery won't be possible as the database is mirrored.
I am running the log backups every hour now. I have also managed to shrink the database to 39GB.
Thanks a lot
October 5, 2011 at 4:28 am
ruan.keyser (10/5/2011)
But unfortunately simple recovery won't be possible as the database is mirrored.
OK, in that case you absolutely must backup the transaction log as frequently as is required to avoid abnormal growths.
ruan.keyser (10/5/2011)
I have also managed to shrink the database to 39GB.Thanks a lot
shrunk the database or individual files?
The log is probably the only one you should be touching at present
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2011 at 5:06 am
I tried shrinking just the log but nothing happened (the screen literally just vanishes).
So I shrunk the db, no errors came forth and the client now informed me that the performance is better than ever.
October 5, 2011 at 5:10 am
so what does DBCC LOGINFO() show now
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2011 at 5:20 am
It now returns 451 rows as to 817 earlier.
This should probably be decreasing the more t-log backups are made?
October 5, 2011 at 5:28 am
Factors That Can Delay Log Truncation:
http://msdn.microsoft.com/en-us/library/ms345414.aspx
Database mirroring requires that each log record remain active until the principal server instance receives notification from the mirror server instance that the record has been written to disk on the mirror server. If the mirror server instance falls behind the principal server instance, the amount of active log space grows accordingly. In this case, you may need to stop database mirroring, take a log backup that truncates the log, apply that log backup to the mirror database (using WITH NORECOVERY), and restart mirroring.
October 5, 2011 at 5:36 am
Thank you!
How will I know if the mirrored server fell behind?
October 5, 2011 at 6:24 am
Okay, this is how I see it - The log file grew on the principal server and the mirror accordingly. I made a backup on the of the log on the principal and did a shrink on the log. Now the log file is the same size on both the principal and mirror. So that means that the mirror did write the latest data to the log?
How would I know if the mirror is in any way behind the principal?
October 5, 2011 at 6:25 am
ruan.keyser (10/5/2011)
It now returns 451 rows as to 817 earlier.This should probably be decreasing the more t-log backups are made?
no, this decreases with the shrinking of the log, what size is it now?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 5, 2011 at 6:26 am
650mb? :pinch:
The script only returned 2 rows
October 5, 2011 at 6:27 am
ruan.keyser (10/5/2011)
How would I know if the mirror is in any way behind the principal?
by the mirroring status synchronised or synchronising, disconnected, etc
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply