Problem solve Get help with specific problems with your technologies, process and projects.

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?

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 last published in June 2004

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.