Moving log segment to separate device

We have an SQL Server 6.5 server which we plan to upgrade as soon as our in-house development team updates the application running on it to run on the new versions of SQL Server. The problem we have now is that the log segment shares the space with the data segment, and whenever we run a 'dbcc checkdb' we get the message "Notification of log space used/free cannot be reported because the log segment is not on its own device". Is there a way we can move the log segment to a separate device on this production machine?

    Requires Free Membership to View

    Login

In SQL Server 6.5, devices were used as the means to allocate space on the file system and the databases were created from the devices. Although it was possible, it was never a good option to combine the data and log segments, but rather have a dedicated data device and a dedicated log device to prevent the error messages you are receiving. Based on the scenario, below outlines my recommendation:

  1. Schedule downtime to migrate the data from one database to another with the proper devices and segments.
  2. Backup the current production SQL Server database.
  3. Script out all of the objects in the database (tables, indexes, stored procedures, defaults, etc.) based on object type and save as text files in the file system.
  4. Create a new data device sized appropriately on the SQL Server with a suffix of '_New'.
  5. Create a new log device sized appropriately on the SQL Server with a suffix of '_New'.
  6. Create a new database on the SQL Server with a suffix of '_New' based on the new data and log devices just created.
  7. Execute the contents of the text files in ISQL\W to create the tables, stored procedures, etc. with the exception of indexes.
  8. Set the current production SQL Server database to read-only and dbo use only to prevent any data modifications.
  9. Issue BCP OUT statements from the current production SQL Server database to the file system.
  10. Issue BCP IN statements from the file system to the '_New' SQL Server database.
  11. Issue the CREATE INDEX statements from the text files in the file system.
  12. Check the system tables between the current production SQL Server database and the '_New' to verify all of the object counts are equal between the two databases.
  13. Rename the current production SQL Server database to have a suffix of '_Old'.
  14. Rename the '_New' database to the proper database name.
  15. Request the users test the application.
For additional information on SQL Server 6.5 to 2000 upgrades, check out the following articles:

Good luck!

For More Information

This was first published in June 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top