Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?
About a month ago I migrated my SQL Server 2008 R2 database to a new schema. I ran scripts to migrate the data in the existing DB to the new DB. When I finished the new database was about 15 GB. A month goes by and my transaction log is 130 GB. Why did this happen and what should I do about it?
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
This question came from our site for professional and enthusiast programmers.
It sounds like your database is in full recovery mode. Under this recovery model your transaction log will continue to grow until it is backed up. Because you're not backing up your transaction log you aren't able to reuse any portion of it. This is all for point-in-time recovery. After you back up your transaction log, the reserved file space for the t-log file will not shrink. You will need to explicitly shrink that file with DBCC SHRINKFIKE. Note: file shrinking should not be a routine operation, but only to do a one-time on demand shrink when you know that file space will not be needed.
Which brings me to my next point. If you need this point-in-time recovery then you need to schedule transaction log backups routinely in order to prevent t-log growth (as you're currently seeing it). If for some chance you only require a recovery point objective that full backups and differential backups can deliver, then you should consider the simple recovery model. Consideration to move to that recovery model needs to be a sound one, as you are losing a certain and import aspect of disaster recovery that most enterprise production databases require.
See this BOL reference on transaction logs and their truncation: http://msdn.microsoft.com/en-us/library/ms190925.aspx#Truncation
