When SQL Server is first installed, it sets the default directory for data and transaction log files. Sometimes, when users use Enterprise Manager to create a database, they don't change the default transaction log path in the Database Properties dialog box. The default places the transaction log in the same directory as the data. Regardless of which version of SQL Server you use, you should store the log on a separate physical disk so that you'll still have access to the transaction log if you lose the disk containing the data. In SQL Server 7.0 and earlier releases, you can't permanently change the default location for data and the transaction log. You have to manually select the path in the Database Properties dialog box and change it to the desired location every time you create a new database in Enterprise Manager.
However, in SQL Server 2000, you can permanently change the default. Open Enterprise Manager, right-click the server name, and select the Properties option from the pop-up menu. Next, click the Database Settings tab, and set a new location for the default data and log directory.
—Uttam Parui
uttam_parui@hotmail.com