I am getting a bit concerned with the size of my log file and my understanding of backups and how the log file should be getting reduced in size. I have a production database that is 12 GB and the log file is 275 GB. The database file is set to autogrow at 1 MB and unrestricted file growth. The log file is set to 10% file growth and restricted to 2,097,152 MB file growth. I perform a full database backup each night. I had thought that all transactions in the log file would be rolled into the database file and then the log file auto-truncated in size during the backup process. I have never seen a log file stay larger than the database file. Please advise how I may keep the log file size (growth) down. Thanks!
Forgot to mention that the database is in Full Recovery mode.
|||there is something wrong in you backup stratergy. You need to relook it as soon as possible. You said it is in Full recovery model. So my first question is , do you take regular backup of Transaction Log(TL) . I doubt , you don't and that is the reason of this outgrown log file.
these are few guidelines
(a) first check whether you need Full recovery model , if not change it to Simple
(b) WHat is the Backup policy. if you are taking the TL backup then increase the frequency of the TL backup
(c) in any case this TL log size is not advisable. You will have to truncate and shrink it. After truncating and shrinking the first step should be a full backup. otherwise the backup chain will break and u will not be able to restore from TL bakcup.
Refer :
http://support.microsoft.com/kb/873235.
Madhu
|||Running the T-SQL below shrunk the log file down to 1 MB. I am not doing TL backups at this time. I never did them in SQL2000, however it now looks like I need to re-examime SQL2005 requirements.
(a) It appears from the article you referenced that if I stay in Full Recovery mode then I will need to add maintenance to backup up Transaction logs, truncate transaction logs, and run update statistics daily.
(b) We backup databases to disk daily and these are written to tape the same night
(c) This solved the current large log file issue:
backup log [DSS] with truncate_only
dbcc shrinkfile(DSS_Log)
|||You said you are in full recovery mode but you are not doing any log backups. So why are you in full recovery mode? What are the recovery requirements from the business? I'm guessing you may not be able to meet those requirements. If you can lose whatever data since your last full backup, you should be able to use simple recovery. If not then you need to be doing log backups. And just to clarify one other statement, keep in mind that truncating the log is not physically shrinking the log. It's good to understand that whole concept. Books Online covers it pretty well under Truncating the Transaction Log:
http://msdn2.microsoft.com/en-us/library/ms189085.aspx
Another thing to keep in mind in that regularly performing physical shrinks of your logs is not necessarily a good idea. You should manage the log size with your transacation log backups. You don't want the logs continually growing, shrinking, etc. You want the logs to be at the appropriate size need for the database activities and managed with log backups. Read the following article about shrinking files:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
-Sue
|||Thank you for the clarification Sue. Your comments have helped me to wrap my mind around how backup strategies work in SQL2005. A full database back up to disk is performed each night and then written to tape. In the event of a failure, we would only lose 24 hours which at this time is acceptable to the company. To provide better recovery I believe I will perform full backups several times throughout the day.
With this said, it would appear that a good strategy for us would be to change the recovery mode to simple, to manage the transaction log files size, and perform full database backups every 2 hours.
Thanks again!
No comments:
Post a Comment