Hi everyone,
I think this is the 3rd time I have posted about transaction logs, here I go again.
Our .ldf file is exploding due to a couple of failed maintenance tasks. If I understand correctly, I have to shrink this by….
Change from Full to Simple recovery mode, then Database>Tasks>Shrink>Files then choose the file type as ‘Log’ and choose ‘Release unused space’ as the shrink action.
Am I correct in thinking this will have absolutely no effect on the availability of the db to users etc. and therefore running it during business hours will be fine? Are there dangers associated with doing this without a fresh full backup? I have only ever done this is Test where I am of course far more adventurous…
Also, our current maintenance plan for FULL Backups looks like this: Check DB Integrity > Shrink DB > Backup All db’s. This happens nightly and we have a transaction log back up that happens hourly.
Through allot of reading today, I see that Shrinking the db every night is generally considered a bad thing to do and can cause performance issues.
I wondered what you guys thought about this? I’m thinking I should get rid of it.
Thanks as always!
Tash
Hi Tash,
While there will be no impact to the availability of the db to users, you will be breaking your restore chain by changing the db from full to simple recovery and back again. Breaking the restore chain would invalidate all of the transaction log backups between your last full backup to the present. If you are going to do this, I would definitely wait until after hours so you can take a full backup immediately before (and after) undertaking that procedure.
You are correct that you shouldn't be shrinking your db. There is generally no need to do this except in quite rare situations. Shrinking a database leads to a high degree of fragmentation, which definitely effects performance.
Hope this helps.
Jeff
Thanks Jeff, very helpful!