February 4, 2011 at 6:50 am
Hi there,
I have a pretty large database for one of my customers. The customer has a lot of transactions during the day (approx 35 people for 14 hours per day concurrent, doing telemarketing).
c.mdf -> 9.4 GB
c_log.ldf -> 6.8 GB
Every night the database gets a full backup; both database and transaction log are backupped.
As I understand, the transaction log is emptied after a full backup.... or not? Even in the morning (before they start working after the last full backup) the c_log.ldf is still huge. Am I overseeing something?
Thanks!
Ray
February 4, 2011 at 9:50 am
After a full or log backup, the transaction log will be emptied out, but the log size remains. This is so the database does not need to grow the file again when it needs that space. Growing the file in the middle of writing a new transaction is a big performance hit.
Instead of just doing a full backup at night, do something like this:
Nightly Full backup
Every four hours do a Differential backup
Every 30 min or hour do a log backup
The Full and Log backups will reduce the logical size of the transaction log, but will not reduce the litteral size. That means the backup will clear the log, and make the space usable again. It will not shrink it.
Once you've got the above backup / restore strategy in place, you can look at the logical size of your log just before it's backed up to see the actual disk size needed. Here's a query you can use to see how much logical space the log is using:
If you use this strategy, you can probably shrink your log file to something like 2gig? You'll have to check and see.
Here are a couple of good MSDN articles on these subjects:
http://msdn.microsoft.com/en-us/library/ms181092.aspx
http://technet.microsoft.com/en-us/library/ms189085.aspx
-------------------------------------------------------------------------------------------------
My SQL Server Blog
February 4, 2011 at 10:00 am
amenjonathan (2/4/2011)
After a full or log backup, the transaction log will be emptied out, but the log size remains.
The log file is only emptied after a log backup.
So:
1. Schedule log backups.
2. Once a log backup is done:
Check the size of the log file:
Use YourDB
GO
SELECT fileid, FileSizeMB, SpaceUsedMB, FreeSpaceMB
,CAST(ROUND((FreeSpaceMB/FileSizeMB) * 100, 2) AS decimal(5, 2)) AS FreeSpacePercent
,name, [filename]
FROM
(
SELECT fileid
,CAST(ROUND(size/128.0,2) AS decimal(12, 2)) AS FileSizeMB
,CAST(ROUND(FILEPROPERTY(name,'SpaceUsed')/128.0,2) AS decimal(12, 2)) AS SpaceUsedMB
,CAST(round((size - FILEPROPERTY(name,'SpaceUsed'))/128.000,2) AS decimal(12, 2)) AS FreeSpaceMB
,name
,[filename]
FROM dbo.sysfiles
) D
GO
Then adust the log file to the max size you think you need. eg:
ALTER DATABASE YourDB
MODIFY FILE (NAME = YourDB_log, SIZE = 1536MB)
February 4, 2011 at 10:13 am
amenjonathan (2/4/2011)
After a full or log backup, the transaction log will be emptied out, but the log size remains.
Please refer below links for good understanding of full backup and log backup.
http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx
http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
M&M
February 4, 2011 at 10:19 am
Ah thanks for the correction Ken.
So the log will truncate at the first backup after the Full back. Why it doesn't do it at the Full seems a bit weird, although Paul Randal does a good job explaining it here:
-------------------------------------------------------------------------------------------------
My SQL Server Blog
February 4, 2011 at 11:09 am
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
February 7, 2011 at 3:40 am
Thanks for all your replies. I checked out article from Paul Randal and I believe I have a better understanding of the matter. Still I have no practical answer how to shrink the logfile, instead I read some warnings never to do it. So I'm more confused now if I should want to shrink it at all.
It's not that I don't have the diskspace; when I run short I can order extra GB's and get them almost instantaneously. But they are expensive. More important, I fear for performance issues (I don't have them yet though)
As far as I understand, I am doing the right thing. I backup both the database (Full Backup) and the transaction log every night. Meaning, the transaction log itself gets truncated.
When I run DBCC LOGINFO I see that I have 179 VLFs (Virtual Logfiles). Is that too many for a 9.4GB database? Too few? As I understand this doesn't have an effect on the physical filesize, only performance which as I said earlier is still good.
It boils down to these questions: should I want to shrink the logfile and if yes, what is the best way?
Thanks,
February 7, 2011 at 3:58 am
r.vanlaake-1086273 (2/7/2011)
As far as I understand, I am doing the right thing. I backup both the database (Full Backup) and the transaction log every night. Meaning, the transaction log itself gets truncated.
Once a day for a transaction log backup? Is the business willing to lose up to 24 hours of data in the case of a disaster?
Hourly is far more common for transaction log backups, or even as often as every 10 minutes.
It boils down to these questions: should I want to shrink the logfile and if yes, what is the best way?
It depends. What you need to find out is what the maximum space utilised by the log is. Check the log percentage (DBCC SQLPERF(LogSpace)) before you run log backups and work out how what the maximum amount of space that the log needs is. Compare that to the size on disk. If they are radically different, you can consider a once-off shrink (DBCC SHRINKFILE) and shrink the log to a size 10-15% larger than the max space that it needs.
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
February 7, 2011 at 9:31 am
Plus if you change the log backups to once per hour, you will not need as much space and should be able to shrink the log at that point.
-------------------------------------------------------------------------------------------------
My SQL Server Blog
February 8, 2011 at 3:22 am
Thanks again! I will increase the number of times I backup the transaction logs.... sounds logical, and better if the transaction log doesn't grow that fast.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply