June 23, 2009 at 9:00 am
Log files for certain DBs are getting too big >10GB. I backup the database files every 4 hours, but not the transaction log files, is this the reason for its growth?
I know this conversation comes about quite a lot, as I have seen a few posts regarding it and how to overcome the problem by using something like:
BACKUP LOG [dbname]
WITH TRUNCATE_ONLY
dbcc shrinkfile ([dbname], 10);
However, does this mean I am theoretically getting of rid of recovery points?
Other than backing up DBs currently, I do not run any other scripts for DB health/performance. Can you please advise what else I could be doing to help performance etc?
What does the 'restrict file growth' do if you set it to 10mb for a transaction log? Would it not let the log file go over 10mb? If so, why would you not want to set this as so always? For better disaster recovery?
Read so many Microsoft articles today my head is spinning. Look forward to any help anyone can provide.
June 23, 2009 at 9:03 am
should have done better search, found this article which is far better than Microsoft help: http://www.sqlservercentral.com/articles/64582/
However some of my questions still remain..
June 23, 2009 at 9:10 am
will (6/23/2009)
However some of my questions still remain..
Specifically....
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
June 23, 2009 at 9:22 am
GilaMonster (6/23/2009)
will (6/23/2009)
However some of my questions still remain..Specifically....
Other than backing up DBs currently, I do not run any other scripts for DB health/performance. Can you please advise what else I could be doing to help performance etc?
What does the 'restrict file growth' do if you set it to 10mb for a transaction log? Would it not let the log file go over 10mb? If so, why would you not want to set this as so always? For better disaster recovery?
and also...
I just did a full back up of DB files (log and mdf) but the log file size stayed the same. Will it be the next backup where the log file is shrunk?
Can you truncate the mdf file of a database and recover hard disk space?
June 23, 2009 at 9:36 am
will (6/23/2009)
What does the 'restrict file growth' do if you set it to 10mb for a transaction log? Would it not let the log file go over 10mb?
Correct, the max size of the log would be 10MB
I just did a full back up of DB files (log and mdf) but the log file size stayed the same. Will it be the next backup where the log file is shrunk?
Log backups never shrink the log. They truncate the log and mark the space inside for reuse, but they won't affect the size of the file.
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
June 23, 2009 at 9:38 am
will (6/23/2009)
GilaMonster (6/23/2009)
will (6/23/2009)
However some of my questions still remain..Specifically....
Other than backing up DBs currently, I do not run any other scripts for DB health/performance. Can you please advise what else I could be doing to help performance etc?
DBCC CheckDB on all databases to check the health. This should be run as often (daily?) as you get get the outage for the process to run. Rebuilding indexes for performance. I run this weekly but that works, for me.
What does the 'restrict file growth' do if you set it to 10mb for a transaction log? Would it not let the log file go over 10mb? If so, why would you not want to set this as so always? For better disaster recovery?
I don't restrict growth. If the log hits a maximum and cannot grow, the database will not be able to have inserts/updates/deletes issued against it. Monitor the logs to ensure you have sufficient disk space for growth, set them large enough initially so that there isn't a lot of "autogrow" occurring and do log backups regualry if in "full: mode to keep the log file size manageable.
and also...
I just did a full back up of DB files (log and mdf) but the log file size stayed the same. Will it be the next backup where the log file is shrunk?
The log file doesn't shrink on it's own. A log backup will free up space in the current log file but will not shrink it.
Can you truncate the mdf file of a database and recover hard disk space?
Not sure what you're going for here. There is no truncate for the mdf that I am aware of. You can shrink it but that causes other issues, especially indexes becoming fragmented. If you've done a massive, one-off delete, I might consider that as an option but not usually. Search this site for pros/cons about this topic.
-- You can't be late until you show up.
June 23, 2009 at 10:02 am
OK thanks for the replies..
So if the backup of the transaction log does not reduce its size, how do others cope with log size? Do you do a full back up, then truncate the log file? Let it grow, back up, truncate the log file etc?
If so... what would the TSQL code look like to acheive this in one job?
June 23, 2009 at 1:57 pm
Set the log to a size that it needs to be for the volume of transactions and frequency of log backups and then leave it alone. Repeated shrink/grow can cause file-system fragmentation and hence is not recommended. Truncating the log breaks the log chain and hence is a bad idea.
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
June 23, 2009 at 1:59 pm
GilaMonster (6/23/2009)
Set the log to a size that it needs to be for the volume of transactions and frequency of log backups and then leave it alone. Repeated shrink/grow can cause file-system fragmentation and hence is not recommended. Truncating the log breaks the log chain and hence is a bad idea.
how do you know what size the log file needs to be?
June 23, 2009 at 2:05 pm
You watch the database for a few weeks and see how big it grows. If it's too big (in your opinion) make your log backups more frequent, do a once-off shrink and then watch it again.
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
June 23, 2009 at 2:06 pm
will (6/23/2009)
GilaMonster (6/23/2009)
Set the log to a size that it needs to be for the volume of transactions and frequency of log backups and then leave it alone. Repeated shrink/grow can cause file-system fragmentation and hence is not recommended. Truncating the log breaks the log chain and hence is a bad idea.how do you know what size the log file needs to be?
No magic here. Monitor the size over a period of time, with appropriate log backups taken at regular intervals. Determine what is the largest it grows to, through all your daily activity and maintenance jobs. This should be a fairly accurate assessment but will need to occur over a portion of time that reflects true server usage.
Edit: Dang - Gail beat me AGAIN.....:-D
-- You can't be late until you show up.
June 23, 2009 at 2:22 pm
ahh now i get it! (i think)
each time the log file is backed up it reorganised the virtual files with in the log. Whilst not reducing the physical size on the disk it frees up space to be reused within the log file.
therefore leaving it to grow, it will automatically dertermine its own size...
that right? thanks boys and girls.
June 23, 2009 at 2:35 pm
Close.
A log consists internally of lots of VLFs (virtual log files). When a log backup occurs all VLFs that do not contain active portions of the log (portions still possibly needed for rollback) are marked as reusable and will be overwritten as needed.
The log's circular. Once SQL reaches the end of the file, it starts overwriting the VLFs at the beginning, providing they have been marked as reusable
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
June 23, 2009 at 2:52 pm
ah ok...
we are getting there, thanks for all the help gail and others. its much appreciated.
so in order for me to 'start again' i should back up all files, truncate then shink logs, then let them grow backing them up every 4-6 hours?
June 23, 2009 at 2:58 pm
will (6/23/2009)
ah ok...we are getting there, thanks for all the help gail and others. its much appreciated.
so in order for me to 'start again' i should back up all files, truncate then shink logs, then let them grow backing them up every 4-6 hours?
That depends on your user's tolerance for data loss. If they can only afford one hour of data loss, then backup the log, at minimum, every hour. This, of course, is in addition to you regular full backups, and possibly differentials.
-- You can't be late until you show up.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply