Share via

SQL Server Upsizing Wizard: Step 7 – Specify Log Properties

  • Article

In this step you can specify the size and device to store a transaction log. A transaction log is created for your database by SQL Server, and can be used to reconstruct the database in the event of a serious system problem.

  • Log Device
    Displays a list of log devices on the remote server. Select the device you want to use for the transaction log.

    Ideally, a database and its corresponding log should be placed on devices that are on separate physical disks. These devices must be created before you start the SQL Server Upsizing Wizard, as the wizard creates all new devices on the same physical disk — the disk where the master database device resides.

    If you have only one physical disk, you should place the database and its log on separate devices, so that you can use the SQL server DUMP TRANSACTION command.

  • Size
    Displays the size in megabytes of the database device selected in the adjoining Log Device list box.

  • Free Space
    Displays the amount of free space remaining on the log device selected in the Log Device list box.

  • Log Size
    Enter the amount of space, in megabytes, you want to allocate for the transaction log. For more information on how to determine the log size you need, see "Specifying Log Size" in this topic.

  • Database Size
    Displays the amount of space allocated for the new database in Step 6.

Specifying Log Size

As a general guideline, you allocate 10 to 20 percent of the size of the new SQL Server database; this guideline is dependent upon a number of considerations that are discussed in your SQL Server documentation.

You can enter a value of 0 for the log size, which causes the wizard to place the log (a table called syslogs) on the same device as the database. When the log size is set to 0, the log consumes the minimum amount of space on the server, but also fills up very quickly.

When you upsize to a new database, the SQL Server Upsizing Wizard dumps the transaction log every time it fills up. The dump makes a backup copy of a database and its transaction log in a form that can be read in with LOAD DATABASE. For more information on the SQL Server DUMP TRANSACTION command and on estimating the amount of space to allocate for a transaction log, see your SQL Server documentation.

Note   You can increase the size of a log or move it to a new device. For details, see your SQL Server documentation for a description of the ALTER DATABASE command and the sp_logdevice system procedure.

See Also

SQL Server Upsizing Wizard | SQL Server Upsizing Wizard: Step 1 - Select Local Database | SQL Server Upsizing Wizard: Step 2 - Select Data Source | SQL Server Upsizing Wizard: Step 3 - Choose Tables | SQL Server Upsizing Wizard: Step 4 - Map Field Data Types | SQL Server Upsizing Wizard: Step 5 - Select Target Database | SQL Server Upsizing Wizard: Step 6 - Set Database Properties | SQL Server Upsizing Wizard: Step 8 - Set Upsizing Options | SQL Server Upsizing Wizard: Step 9 - Finish | Wizards Overview | Wizards Overview