Transaction Log file size issue

Question

Post reply

Transaction Log file size issue

rajg

Mr or Mrs. 500

Points: 581
More actions
February 6, 2012 at 2:33 am

#256192

Hi All,
I am not a DBA but need some help regarding the database transactions logs and maintenance. I have PROD database running with data file (6.78 GB size ) and log file (79 GB). I understand that log file size if very large and has already created disk space issue in the past. But that time - we increased disk space rather than thinking about good solution.
At this moment - we do full backups daily at midnight though point in time recovery is critical for us. I understand we need to differnet backup stratergy and I am thinking of weekly full back, daily differntial and hourly log backups. But this is not implemented yet and looking at the problems - we will have to implement it soon. Our database recovery model is FULL.
But my problem at this moment is - log file has is very large and recently it created problem. Last friday - it automatically increased the size since autogrowth property is set to true. But while SQL server increasing log file size - we got serious issue with server slowness causing application down problem and then we had to check with server provider for server issues and all. Our application down for almost 3-4 hrs due to this and other issues it caused. Log file was increased automatically in the past but it didnt create problems at this time (may be due to other factors).
As I said - we are going to implement to improved backup stratergy but it will take another couple of days or a week to test it on locally and we can not do it now in weekdays.
Is there anyway to shrink the log file size immediately and still have point in time recovery when we implement the new plan without losing any logs.
I have noticed that since last Friday I am seeing very high CPU usage contantly (around 80-95%). Could there be any relation of log size with this CPU usage ? I understand CPU usage can have multiple reasons like indexing but we have already proper indexes and it was not problem before.
Please let me know your suggestions/solutions/comments.
Thanks,
Bhimraj

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Bhimraj

So you're not doing any transaction log backups at all at the moment?

John

rajg Mr or Mrs. 500 Points: 581 More actions · Answer 2

Hi John,

No, we are not doing log backups at this moment. Only full backs daily.

-Bhimraj

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Bhimraj

Then you won't be able to release any space from your log file. You have two options:

(1) Implement your new backup strategy as a matter of priority

(2) Switch to Simple recovery mode in the meantime and lose the ability to do point-in-time recovery.

John

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The root of the problem is that you are not taking log backups.

Please read through this - Managing Transaction Logs[/url]

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Matimba SSC Enthusiast Points: 136 More actions · Answer 5

Hi

I suggest you backup your log file and shrink it (although this is not recommended on SQL best practices but i believe its the best solution at the moment)

rajg Mr or Mrs. 500 Points: 581 More actions · Answer 6

Thanks John, Gila and Tee.M for your inputs. Seems like I have to implement new back up plan on priority. GilaMonster - your article is very interesting and informative. I am going through it.

Tee.M - if I backup log file and shrink it - it should free some space right ? Ideally how much time it will take shrink 79 GB file ?

If I try to do it immediately on live server - will it affect live users ( I guess - Yes but still want to confirm)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

rajg (2/6/2012)
Tee.M - if I backup log file and shrink it - it should free some space right ? Ideally how much time it will take shrink 79 GB file ?

Don't shrink it to 0. Shrink to a reasonable size (maybe 4GB). It should be near-instant, but may not release all the space. Do note, you'll need around 79GB for that log backup file. I would suggest instead perhaps switch to simple recovery (not now, wait until the users are offline), checkpoint, shrink log, full recovery, full backup and set up the log backup job at the same time.

If I try to do it immediately on live server - will it affect live users ( I guess - Yes but still want to confirm)

No. Shrink is an online operation. Only time you might have an impact is if you shrink too small and the log has to grow immediately.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233866 More actions · Answer 8

rajg (2/6/2012)
But my problem at this moment is - log file has is very large and recently it created problem. Last friday - it automatically increased the size since autogrowth property is set to true. But while SQL server increasing log file size - we got serious issue with server slowness causing application down problem and then we had to check with server provider for server issues and all. Our application down for almost 3-4 hrs due to this and other issues it caused. Log file was increased automatically in the past but it didnt create problems at this time (may be due to other factors).

what autogrow settings are currently set, value as percentage or in MBs?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

rajg Mr or Mrs. 500 Points: 581 More actions · Answer 9

Thank you all for taking time to help me on this. Finally, I took the backup of log file (Gila was correct on 79 GB 🙂 ) and then shrunk it.

I have implemented new backup strategy in the last week for the Full (weekly) , Diff (daily) and log backups (hourly).

Now with this strategy - I also need to take care disk space. So I am working on the backup file rotation policy where I am creating a scheduled task to keep only two weeks of backup files.

In the last week - since it was first time log was backed up, its size is around 79 GB.

As I understand I wont need this log file after one week (or in the future) - when I will have full backup on this weekend ? I mean even in case of some problem - I will be able to restore the database using the full backup and subsequent Diff, log backups.

I would like to confirm if my understanding is correct so that I can free up the disk space utilized by 79GB log backup file.

Looking for inputs 🙂 Thanks in advance.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Yes, after you take your next full backup, if you delete the 79GB log backup, you will only be able to restore to a point in time after the full backup was taken. If that's acceptable, you're OK to delete the large backup file. Check that new backups are not appending to that file first, though.

John

rajg Mr or Mrs. 500 Points: 581 More actions · Answer 11

Thanks John.

Check that new backups are not appending to that file first, though.

My new hourly log backups are only in MBs. This means its not appending, right ?

In what case this can happen (i.e. new backups are appending to first file) ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

This will happen if you use BACKUP LOG TO DISK = 'x:\MyFolder\MyBigLogBackupFile.TRN' and you don't specify WITH INIT. Use RESTORE HEADERONLY to check how many backups are in the file before you delete it. This applies whether your backups are in Bs, KBs, MBs, GBs or anything else.

John

lozis6947 SSC Rookie Points: 32 More actions · Answer 13

lozis6947

SSC Rookie

Points: 32

February 17, 2012 at 8:12 pm

#1448856

Only full backs daily