Huge log files

Question

Hello,

 

The disk space of my Team Foundation Server becomes saturated because of the SQL log files. For example, here are the sizes of several TFS database files :

 

- TfsVersionControl.mdf : 5.3 GB - TfsVersionControl_log.ldf : 23.9 GB

- TfsIntegration.mdf : 26.8 MB - TfsIntegration_log.ldf : 1.8 GB

- TfsWareHouse.mdf : 124 MB - TfsWareHouse_log.ldf : 61.8 GB !

 

I have several questions about this :

 

- Is it possible to reduce the size of the log files without risk for the integrity of the TFS data ?

- The log files have the following restricted growth at the installation of TFS : 10% and 2,097,152 MB. Isn't this size an installation bug ? This seems really huge...

- How can I limit the size of the log files ? When I try to modify the limit size of the log files, this has no effect.

 

Thank you for your help.

 

Olivier

Answer 1

Hello Olivier:

 

 You may back up the data -tier and shrink the data base.

 

 Here is how to back up the data – tier:

 

  http://msdn2.microsoft.com/en-us/library/ms253151(VS.80).aspx

 

 

 Shrinking data bases should not affect the integrity of the TFS data. All the data are stored in the tables.

 

 Here are brief introductions about using shrink data bases:

 

    http://msdn2.microsoft.com/en-gb/library/ms188432.aspx

 

    http://msdn2.microsoft.com/en-gb/library/ms180072.aspx

 

 Thanks.

 

Answer 2

Hello,

 

I have been able to shrink the log files of all the databases except TFSWarehouse which is alas the biggest one: it is now 120 GB (compared to 62 GB from my previous post one week earlier...) When I try to do a transaction log backup, I have an error stating that the database is is use.

I have tried to do this backup during the week-end with the TFSServerScheduler service shut down. The backup then stayed hanged at 60% for 40 hours and I have been forced to cancel it.

We are now Monday in the morning with the server running out of space. This afternoon or tomorrow, it will stop working and nearly 30 people won't be able to work anymore. So if you have any solution to shrink that file, please give it to me!

 

Thank you,

 

Olivier

Answer 3

Hello Olivier:

 

  You do not need to back the log file individually. You only need to back the data - tier according to the admin book.

 

You do not need these log any more.  You may try to shrink it to a very small piece. Almost you can shrink it to about 1 GB.

 

You may search the shrink topic in the SQL Server Help book which should be installed with the SQL Server Engine.

 

I am not sure about the detail steps and configurations about shrink.

 

You can feel free to reduce these log files.

 

Can you run the back up steps according to the TFS admin book now?

 

If you have any questions you can post it freely.

 

Good luck.

 

 

Answer 4

Hello Olivier:

 

 From the admin book you may do a full back up. This type does not back up the log file. It just backs up the Databases

• To complete a full backup, select Back Up Database (Full).

When you finish the full back up you can shrink log file freely

 

If you can not run this full back up because of the huge log file

 

you may shrink log files first and immediately do a full back up.

 

Good luck.

Answer 5

The problem is that the log file can be shrinked only after a transaction log backup (as explained in the link you have posted). When I execute a shrink operation on the log file after a full backup of the database, only the free part of the latest allocated part of the file can be freed (with an automatic growth of 10%, that means less than these 10%). To purge and shrink the log file, a transaction log backup is needed. For instance, I have done the test on the database TfsVersionControl :

- I do a full backup of the database.

- 5.8 MB of the log file can be freed on a total of 80.5 MB (i.e. 7%)

- I then do a transaction log backup of the database.

- 71.2 MB of the log file can be freed (i.e. 88%)

 

My problem is that I am able to do a full backup of the TFSWarehouse database, but the transaction log backup of this database fails by hanging at 60% during the backup. Thus, I cannot do an efficient shrink of the log file. Furthermore, if I delete this database and recreate it from the full backup, the log file is recreated with its original size of 120 GB, so I have no idea how I can solve this problem.

 

Olivier

Answer 6

Hello Olivier:
 
  For your scenario:
 
 You may truncate the transaction log first.
 
 Here is the SQL Query Text:
 
  BACKUP LOG …. WITH TRUNCATE_ONLY
  GO
  DBCC SHRINKFILE(logfile_name, 7)
  Go
 
 
  After this, you may do the shrink operation effectively.
 
  After this operation, fully backup the database.

 

Good luck.

Answer 7

Yes! It works.

 

Thank you very much, Hua. You saved the situation.

 

Olivier

Answer 8

Hi,,

I have the same problem,, My transaction Log generated a second file: Database_Log2 (but no extension Type: File) ... how can I shrink it or remove it ?

 

Thanks!

Saltorr

Answer 9

Is it wrong to limit the log file size?

 

Answer 10

The correct action is to set the Recovery model to "Simple" for all databases where you are not performing transaction log backup. "Full" recovery will not truncate log files until they are backed up. "Simple" recovery will truncate logs when a full backup is performed. If you are not backing up transaction logs separately there is no use for the "Full" recovery model.

---

-Aidan