Transaction Log Issue

Question

Transaction Log Issue

ehlinger

Hall of Fame

Points: 3585
More actions
April 23, 2008 at 8:31 am

#121484

I have a database that is approximately 13G. We don't have a 24x7 business so I have the luxury of running maintenance every night on our databases to rebuild indexes, check integrity, backup databases, etc.
The issue I have is the size of the log file. I run the maintenance every night at 11:00 and then have hourly log files set to run every day from 6:00 AM until 5:00 PM. The backup at 6:00 AM everyday of the log file is no running at about 5G which doesn't surprise me because it probably contains all of the reindexing and other stuff that takes place the previous day at 11:00 PM.
The size of the log backups the rest of the day range in size but typically aren't much bigger than 10MB. However, the size of the log file never seems to shrink at all. Currently it is 5.5G. Shouldn't it truncate and reduce the size significantly after the first hourly backup?
What am I missing?

Viewing 7 posts - 1 through 6 (of 6 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

When you truncate the log, the file stays the same size, it just does not contain much data. You have to shrink it using DBCC SHRINKFILE

http://msdn2.microsoft.com/en-us/library/ms189493.aspx

Also read this:

http://msdn2.microsoft.com/en-us/library/ms178037.aspx

Let us know if you have more questions,

Regards,Yelena Varsha

ehlinger Hall of Fame Points: 3585 More actions · Answer 2

Thanks for your help. I went ahead earlier this morning and ran a shrinkfile command through SSMS and the file shrunk to about 5.1G.

I then ran DBCC SQLPERF(LOGSPACE) and the log size in MB id 4987.305 with the log space used % at only 0.67159.

So it appears that even though the log file stayed relatively large it isn't using much of it. So why would it not shrink down considerably more?

The next question I have is that someone else suggested that if the log grows to that point every night because of maintenance issues then just leave it alone because if I manually schedule a shrink it will just grow again. Consistently resizing it will lead to performance degredation caused by physical fragmentation on the disk array.

This all seems to make sense but it seems over time that the size of that log file just continues to creep up in size so should I just leave it alone or should I be trying to shrink it?

Part of what I'm dealing with is I'm running into some space limitations with backups right now until I get a new Server solution approved and in place. Having said that I want to make sure on the new system I am using best practices and not just ignoring this issue because I have the space to do it.

Your thoughts? Thanks.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

I would agree with the second opinion that if the file will grow again every night, you should leave it the same size so the resources would not be spent to increase the file every night many times.

For example, if the file size will be 100 MB and the grouth rate is 10% in the database file properties, then to grow up to 5 GB how many times it has to be expanded?

If you feel that you have limited space, revise your backup strategy and implement it as a job with the steps:

1. Backup database and logs at the end of the day.

2. Move these backups to the network share.

3. Reset Recovery Model from Full to Simple

4. Do your maintenance

5. Reset recovery model from Simple to Full

6. Backup database before the users come in.

7. Backup logs through the day as you now do.

P.S. - I did have this situation where the log file was empty but would not shrink much because of some virtual logs or something.

I detached the database and re-attached it with sp_attach_single_file_db that created a new nice log.

Regards,Yelena Varsha

ehlinger Hall of Fame Points: 3585 More actions · Answer 4

Thanks. One idea I did have was to do the backups directly to our MSA. However, when setting up the backups in SQL all I can see are the drives on that server and none of the network shares.

Is there a way in SQL when setting up the backups to indicate that you want them to go to a network share?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

Yes, read this:

http://support.microsoft.com/kb/555128

HowTo: Backup to UNC name using Database Maintenance Wizard

You have to use UNC as \\servername\sharename

because the mapped drives exist only for the current user when he logged in. SQL Server or Agent startup accounts or both should be domain accounts -depends how you run the backup - as a statement or as a job. These domain accounts have to have a lot of rights. As this article says:

"RESOLUTION

Backups to a network folder require the following prerequisites:

1) The SQL Server service must be running under a domain-level account.

2) The SQL Server service account must have FULL CONTROL rights to the file system folder and to the share.

3) The remote share should only be accessed via UNC name. Mapped drives may not be consistently visible to the SQL Service.

Once these prerequisites are met, the UNC name can be typed into the 'Use this directory' text field on the 'Complete Backup' and/or the 'Transaction Log Backup' tabs of the Database Maintenance plan. SQL will then write backups to this location. Do not use the '…' button. This button will only enumerate local disk resources.

"

P.S. - are you sure you want to backup the DB this big to the network share directly? How you would know that the connection is lost? I would backup to the local drive and then move as a second step and then verify sizes - local and remote and then delete the local backup

Regards,Yelena Varsha

ehlinger Hall of Fame Points: 3585 More actions · Answer 6

Thanks for all your help. I think this will help to resolve the current issues. Thanks again.