If you choose to create a new Microsoft SQL Server 6.5 database, the Upsizing Wizard asks you to enter devices and sizes for your database and transaction log. It also asks you to enter a pre-allocated size for the database and transactions. This topic provides reference information about how to:
Enter a database device and size
Enter a transaction log device and size
Enter a database and transaction log size
Return to top
All SQL Server databases and transaction logs are placed on devices. A device is both a logical location for databases and transaction logs and a physical file. When you create a device, SQL Server creates a file and reserves a set amount of disk space for its own use.
If you are a system administrator, the Upsizing Wizard displays the amount of free space available on the existing devices. Select a device that has at least enough free space for your estimated database size.
If no existing devices have enough free space, you can create a new device using the Upsizing Wizard. New devices should be at least as big as your estimated database size. If possible, make the device larger than your database size, so you can expand your database later, or place other databases or transaction logs on the same device.
Return to top
The transaction log is a chronological record of all activity that occurs on the server that SQL Server uses for database backup and recovery.
The transaction log device you select must have at least one megabyte of space available. Ideally, a database and its corresponding transaction log should be placed on devices that are on separate physical disks. These devices must be created before starting the Upsizing Wizard, because the Upsizing Wizard creates all new devices on the same physical disk where the Master database device resides.
If you have only one physical disk, you should place the database and its transaction log on separate devices, so you can recover the database.
The Upsizing Wizard creates the transaction log using the size you specify. The size of your transaction log should be at least as large as the size of all of the upsized tables in your database. The size you specify can't exceed the space available on the transaction log device you selected.
Note For new databases, the Upsizing Wizard truncates the transaction log file on checkpoint if it fills up before the upsizing is complete. However, it's possible for the transaction log to overflow on a single table. If this happens, Microsoft Access doesn't upsize the table and writes the error to the Upsizing Wizard report.
Return to top
When Microsoft SQL Server 6.5 creates a database, it sets aside a fixed amount of space for that database on one or more devices. Not all of this space is necessarily used by the database right away. The database size is the maximum size a database can become before it runs out of space.
To estimate the space needed for your database, look at the size of your Microsoft Access database and estimate the rate at which your new SQL Server database will grow. In general, every megabyte of Microsoft Access data requires at least 1.3 to 1.5 megabytes on SQL Server. Note that the size of a Microsoft Access database (.mdb) file includes all Microsoft Access database objects, not just data.
If you have ample disk space on your server, multiply the size of your Access database by two. This ensures that the Upsizing Wizard has enough space to upsize your database and also leave it some room to grow. If you expect to add a lot of data to the database, make the multiple larger.
The transaction log size should be about 25% of the database size.
Return to top