Post reply

Changing SQL Log location

  • February 10, 2004 at 1:57 am

    #65929

    Is it possible to change the location of the log file on SQL Server 2000

    I have ran out of space on my server and have just added a new drive. If I can get the log file on this new partication all of my space prolems will be stopped

     

    thanks Paul

  • February 10, 2004 at 7:21 am

    #493372

    Hi Paul, The way i do this ( may not be the best way, but it works ! ), is to backup the database & then use a script to restore it, with some extras options on the restore command that alters where the data and/or log file are located e.g; RESTORE DATABASE dbname FROM DISK='disk_location\dbname.dmp' WITH RECOVERY, MOVE 'dbname_Data' TO 'disk_location\dbname_Data.mdf', MOVE 'dbname_Log' TO 'disk_location\dbname_Log.ldf'. Hope that helps!. Any questions let me know. Regards, j

  • February 10, 2004 at 7:41 am

    #493376

    You can do as Jima suggests or just detach the database, move the .ldf to where you want it to be and then when you attach the database just tell it the log files new location.

     

    -SQLBill

  • February 10, 2004 at 9:04 am

    #493405

    If you want to change the location of SQL Server error log file, change it from properties --> general tab --> startup patameters.

  • February 10, 2004 at 9:20 am

    #493414

    Here is the steps and the code I use for years... Hope it helps...

    USE DB_Name

    go

    sp_helpfile

    go

    --it'll give you files locations like:

    E:\datafolder\MSSQL\Data\ENT_DATA_Data.MDF                                                                                                                                                                                                                

                                                                                                                                                                                                                                                                                               

    E:\datafolder\MSSQL\Data\ENT_DATA_Log.LDF                                                                                                                                                                                                                       

      --detach theDB                                                                                                                                                                                                       

    USE MASTER

    go

    SP_DETACH_DB 'DB_Name'

    GO

    --Minimize QA and copy AND PASTE THE FILES TO THE NEW LOCATION

    --Copy and paste the path bellow

    USE MASTER

    go

    SP_ATTACH_DB 'dbname', 'E:\MRE-CCA-SQL_Data\MSSQL\Data\ENT_DATA_DATA.mdf',

               'G:\MRE-CCA-SQL_LOG\MSSQL\LOG\ENT_DATA_log.ldf'   

    GO

    --Check for the new location

    USE ENT_DATA

    go

    sp_helpfile

    go

    --Check if everything was OK and the Db is on-line - status field

    USE MASTER

    go

    sp_helpDB 'dbname'

    go

     

  • February 10, 2004 at 9:36 am

    #493418

    Hi Paul, Allen's reply got me thinking, did you mean you wanted to relocate a Db log file or the Sql Error Log file ????. j

  • February 10, 2004 at 9:39 am

    #493420

    I understood - SQL transaction log files. To change them in the Server property will reflect only newly created Dbs. The script I posted shows how to move user DBs logs of already existing Dbs.

    MJ

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply