Tell me more ×
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.
up vote 1 down vote favorite

I've got a database with a data file size of 674 MB and a transaction log of 6.1 GB. The transaction log is about 10 times bigger than the data file.

The transaction log settings can be seen in the image below.

enter image description here

The server is constantly running out of space and this transaction log takes up about 11% of the total disk space. I'd like to make this transaction log file smaller.

Can I simply just change the "Maximum file size" by switching to Restrict file growth [MB]: and set the size to say 80 MB?

share|improve this question

3 Answers

up vote 5 down vote accepted

I prefer restricting to unrestricted.

If some process goes awry, then I don't want the log file to grow and grow and fill the disk up. I'd rather the DB stopped working than the entire Server/SQL Server Instance.

However, in this case you have a different issue: most likely "FULL" recovery and no log backups. Change to simple, shrink the log to, say 500MB, allow growth at 250MB, set a maximum of 2GB.

share|improve this answer
1  
and don't forget to add a log backup job. – SqlACID Oct 27 '11 at 21:49
1  
There's no need for log backups using SIMPLE recovery. – Gonsalu Oct 28 '11 at 15:01
2  
true; i missed the change to simple part. how about add full backup job. – SqlACID Oct 29 '11 at 0:53
up vote 2 down vote

It is not advisable to restrict the transaction log file growth, as that will interfere with the database's ability to record transactions.

If this is a non-production box, or you never plan on doing point-int-time recovery, you could switch the database recoveryoption to "simple". After every checkpoint the log will be automatically truncated, thus preventing growth (unless you are doing a crazy amount of work inside one transaction).

Set the database to simple mode, truncate the log, shrink the log, set auto-growth to 128MB, and you should be on your way.

share|improve this answer
up vote 2 down vote

Sometimes you will not be able to reduce the size of the log files from the database property window. You may get an error message around the lines of: 'The new file size is currently smaller than the current file size...'. However you can try the following steps:

  1. First put your DB recovery to simple mode: ALTER DATABASE Your_DB_Name SET RECOVERY SIMPLE.
  2. Shrink the log file using DBCC or SSMS.
  3. Set you desired file growth settings in the database property window.
  4. Put the DB recovery back to full mode: ALTER DATABASE Your_DB_Name SET RECOVERY FULL.

As @gbn expressed, I also prefer putting the file under a restricted growth. If the file fills up the drive and you do not have any extra space, then you may not be able to shrink the file later on affecting all the DBs that have data/log files on the same drive.

share|improve this answer

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.