Take the 2-minute tour ×
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's 100% free, no registration required.

I have a database that generate a huge amount of transaction per seconds, something like 50-100 transactions/second. Because of this, the log files grows very quickly. The thing is though, that even though there are a lot of transactions, the actual data is pretty small.

Now the problem I have is that when the log reaches his maximum value, which is pretty big to being with, the database will not accept anymore transactions. I can't really put unrestricted growth since I do have a physical limit to respect.

My initial understanding was that once the log reaches full size , it will simply take the oldest transaction and delete them to make space for the new ones, which would be ok in our case. However, this does not seem to be the case here. I am no expert on SQL so I am at a lost here.

I understand this log is used for backups, but so far we don't have plans to implement backup because we are still on testing phases. Also we won't be the one performing it, so we have to wait for now.

Thanks

share|improve this question

migrated from stackoverflow.com Dec 17 '12 at 16:32

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 4 down vote accepted

A couple of things you can do.

Your database is probably in full recovery mode. This is why the log file does not get re-used. Now if you DON'T need transaction log backups you can set the recovery mode to simple. Your log file will now re-use itself in simple mode. If you DO need transactional log backup i.e. point in time recovery then you will need to actually DO transaction log backups to re-use the log file.

Please be aware that this setting has a MAJOR impact on how you can perform disaster recovery and you need to make sure that the setting matches your disaster recovery needs. If you database is so busy then transactional backups are probably better than simple recovery mode.

Without transaction log backups you will NOT be able to do point in time recovery. Thus you will lose A LOT OF DATA in the event of a disaster.

Go read about transaction logs and recovery modes here: http://msdn.microsoft.com/en-us/library/ms189275.aspx

Now there is something else you need to keep in mind. Virtual Log File fragmentation. It is going to be something very important to understand and master. Kimberly L Tripp has a very very usefull blog entry about this. You can read all about this very helpful topic here: http://www.sqlskills.com/blogs/kimberly/post/transaction-log-vlfs-too-many-or-too-few.aspx

Keep in mind 50-100 transactions a second is not a lot. Also a BIG transaction log file is not always a bad one. There is more to log files than just logging transactions. Go read about it. It is a topic worth mastering.

share|improve this answer

I understand this log is used for backups, but so far we don't have plans to implement backup because we are still on testing phases. Also we won't be the one performing it, so we have to wait for now.

No, log file is not used for backups, but to enable to roll back the transactions to an exact point in time. In case this is of no interest, you can switch to the Simple recovery model and create full database backups. That way, you'll be able to recover your database only to the time when the full database backup was taken (e.g. midnight) and not to any point in time

If you decide to keep the database in Full recovery model, you should create transaction log backups regularly, e.g. every 15-30 minutes. This will keep the size of the online transaction log and you'll have enough data to roll back to a specific point in time (using tlog backups)

More useful info about recovery modes from Paul Randal here: Understanding Logging and Recovery in SQL Server

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.