Moving and shrinking Log file in a different drive

[eggy168]

Hi,

The box is running Win 2003 and SQL Server 2005. I have a transaction log file in a disk D and the main data is in disk C. I had the Data (Primary in 160MB, by 1MB, unrestricted growth in path C; Log, 25 MB, by 10 percent, restricted growth in path D)

I tried to use DBCC Shrinkfile and Truncate_Only statement, it gave me the error message. Can anyone please help?

This is what I wrote in the query,

Use Budget_2008
GO
DBCC ShrinkFile (D:\SQLDat2ServerLog\Budget_2008_log,1)
BACKUP Log D:\SQLData2ServerLog\Budget_2008 WITH TRUNCATE_ONLY
DBCC ShrinkFile (D:\SQLDat2ServerLog\Budget_2008_log,1)

Also, I would like to know is there anyway I can move the D path's log file back to the C path? I tried to use the following, but again, the error message appeared,

1) RUN Alter Database Budget_2008 SET OFFLINE
2) I move the D path's log file to the C path where the Data located.
3) Run ALTER DATABASE Budget_2008 MODIFY FILE(NAME = Budget_2008_Log, FILEName = 'C:\Program File\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Budget_2008_Log.LDF'
4) Run ALTER DATABASE Budget_2008 SET ONLINE

I tried to work on change the data in the file, but it ends up the new Log file is not working, but the Old Log file is still capturing the log. Can anyone help?

Thank you very much

 

[JayKusch]

OK ... first off unless the C: and D: drives are logical drives, you are going to get better performance if you data file is on a seperate physcial disc from your log files.

I do not like having my DB files on the OS disc (which is the C: drive in most cases) but that is another matter.

Now what you need to do, if you want to move the tlog file back to the C: drive is to DETACH the database. Putting it OFFLINE will not permit you to move the underlying DB files.

Once you have detached the DB, move the LDF (log file) back to the C: drive and share and the REATTACH the DB.

Both the ATTACH and REATTACH can be found by right clicking on the DB in Mgt Studio and drilliing into "TASKS".

When you reattach the DB most likely the log file will have a big red X saying it cannot find the log file (since you moved it).

Use the drop down window to drill to the folder you moved the log file too and you should be good to go.




Thanks

J. Kusch