Based on the number of users, we suggest the following guidelines for data files and transaction logs. Your needs may be different from those shown in the table below.
| Number of Users | Number of Data Files | Size of Each Data File | Number of Log Files | Size of Each Log File |
|---|---|---|---|---|
| Up to 15 | 3 | 50MB | 3 | 50MB |
| Between 15 and 50 | 3 | 300MB | 3 | 300MB |
| Between 51 and 100 | 5 | 300MB | 5 | 300MB |
| Between 101 and 300 | 7 | 500MB | 5 | 500MB |
| >300 | 7 | 800MB | 6 | 500MB |
Transaction log backups are essential. After a transaction is backed up, Microsoft SQL Server and Microsoft SQL Server Express databases automatically truncate the inactive portion of the transaction log. This inactive portion contains completed transactions and is no longer used during the recovery process. The basic advantage comes with the fact that Microsoft SQL Server reuses this truncated, inactive space in the transaction log instead of allowing the transaction log to continue to grow and use more space. This is a huge plus from a performance standpoint.
Allowing files to grow automatically can cause fragmentation of those files if a large number of files share the same disk. Therefore, it is recommended that files or file groups be created on as many different available local physical disks as possible. Place objects that compete heavily for space in different file groups.