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 following tables.
| Number of Users | Number of Data Files | Size of Each Data File |
|---|---|---|
| Up to 15 | 3 | 50 MB |
| Between 15 and 50 | 3 | 300 MB |
| Between 51 and 100 | 5 | 300 MB |
| Between 101 and 300 | 7 | 500 MB |
| >300 | 7 | 800 MB |
| Number of Users | Number of Log Files | Size of Each Log File* |
|---|---|---|
| Up to 15 | 3 | 50 MB |
| Between 15 and 50 | 3 | 300 MB |
| Between 51 and 100 | 5 | 300 MB |
| Between 101 and 300 | 5 | 500 MB |
| >300 | 6 | 500 MB |
* The transaction log file sizes are relevant only if the transaction log backup is performed frequently.
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.