none
Move Log files to separate drive

    Question

  • Hi 

    The DBA has setup the Server where the .mdf and .ldf are on the same drive - I would like to move all the .ldf to separate drive and also set the same correct permission on the new folder on that new drive

    I found this script - but that's failing on the user:  owner_sid  0x01

    there's a specific admin user (not the SA) that are owner of most of the DB's

    can someone plz assist?    

    USE [master]
    GO
    
    CREATE proc [dbo].[MoveLog]
    as
    
    --enabling xp_cmdshell will be required to do the file move
    exec sp_configure 'xp_cmdshell', 1;
    reconfigure;
    
    
    declare @DBName as varchar(128);
    declare @LogicalName as varchar(128);
    declare @FileLocation as varchar(128);
    declare @FileName as varchar(128);
    declare @CMD as nvarchar(256);
    declare @SourceFolder as varchar(128);
    declare @DestinationFolder as varchar(128);
    
    SET @DestinationFolder = 'E:\MSSQL\LOG'; -- Change this to the correct destination folder
    
    BEGIN TRY
    
    declare Db_cursor Cursor forward_only for
       select name
       from sys.databases
       where owner_sid  0x01;--user dbs only
    
    OPEN Db_cursor;
    
    FETCH NEXT FROM Db_cursor 
    INTO @DBName;
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    
        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK immediate';
        Exec sp_executesql @CMD
        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT ;
    
        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET OFFLINE ';
        Exec sp_executesql @CMD
        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;
    
        SELECT @LogicalName =name, @FileLocation=physical_name, @FileName = reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))
        FROM sys.master_files
        WHERE type_desc = 'LOG' and database_id = DB_ID(@DBName);
    
        --move the log file
        set @CMD = 'exec xp_cmdshell N''move "' + @FileLocation + '" "' + @DestinationFolder + '\' + @FileName + '"''';
        Exec sp_executesql @CMD
        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;
    
        SET @SourceFolder = REPLACE(@FileLocation,@Filename,'');
    
        /* Update the system catalog */
        set @CMD = 'ALTER DATABASE [' + @DBName + '] MODIFY FILE ( NAME = [' + @LogicalName + '], FILENAME = ''' + @DestinationFolder + '\' + @FileName + ''')';
        Exec sp_executesql @CMD
        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;
    
        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET MULTI_USER';
        Exec sp_executesql @CMD
        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;
    
        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET ONLINE';
        Exec sp_executesql @CMD 
        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;
    
        FETCH NEXT FROM Db_cursor 
        INTO @DBName;
    END
    
    CLOSE Db_cursor;
    DEALLOCATE Db_cursor;
    --GO
    END TRY
    
    BEGIN CATCH
       IF (SELECT CURSOR_STATUS('global','Db_cursor')) >=0 
       BEGIN
          DEALLOCATE Db_cursor;
          RAISERROR ('Db_cursor Deallocated in catch',0,1) WITH NOWAIT;
       END
    
       SELECT ERROR_LINE(),ERROR_MESSAGE();
    
    END CATCH
    
    exec sp_configure 'xp_cmdshell', 0;
    reconfigure;



    • Edited by JmATK Tuesday, February 14, 2017 6:07 AM
    Tuesday, February 14, 2017 6:06 AM

Answers

  • This us an updated sp


    alter proc [dbo].[MoveLog]
    as





    declare @DBName as varchar(128);
    declare @LogicalName as varchar(128);
    declare @FileLocation as varchar(128);
    declare @FileName as varchar(128);
    declare @CMD as nvarchar(256);
    declare @SourceFolder as varchar(128);
    declare @DestinationFolder as varchar(128);

    SET @DestinationFolder = 'E:'; -- Change this to the correct destination folder

    BEGIN TRY

    declare Db_cursor Cursor forward_only for
       select name
       from sys.databases
        where owner_sid = 0x01;--user dbs only

    OPEN Db_cursor;

    FETCH NEXT FROM Db_cursor 
    INTO @DBName;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK immediate';
          Exec sp_executesql @CMD 
     
        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT ;

        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET OFFLINE ';
       Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

        SELECT @LogicalName =name, @FileLocation=physical_name, @FileName = reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))
        FROM sys.master_files
        WHERE type_desc = 'LOG' and database_id = DB_ID(@DBName);

        --move the log file
        set @CMD = 'exec xp_cmdshell N''move "' + @FileLocation + '" "' + @DestinationFolder + '\' + @FileName + '"''';
         Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

        SET @SourceFolder = REPLACE(@FileLocation,@Filename,'');

        /* Update the system catalog */
        set @CMD = 'ALTER DATABASE [' + @DBName + '] MODIFY FILE ( NAME = [' + @LogicalName + '], FILENAME = ''' + @DestinationFolder + '\' + @FileName + ''')';
         Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET MULTI_USER';
        Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET ONLINE';
         Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

     FETCH NEXT FROM Db_cursor 
        INTO @DBName;
     
    END

    CLOSE Db_cursor;
    DEALLOCATE Db_cursor;
    --GO
    END TRY

    BEGIN CATCH
       IF (SELECT CURSOR_STATUS('global','Db_cursor')) >=0 
       BEGIN
          DEALLOCATE Db_cursor;
          RAISERROR ('Db_cursor Deallocated in catch',0,1) WITH NOWAIT;
       END

       SELECT ERROR_LINE(),ERROR_MESSAGE();

    END CATCH


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by JmATK Tuesday, February 14, 2017 10:50 AM
    • Unmarked as answer by JmATK Wednesday, February 15, 2017 3:57 PM
    • Marked as answer by JmATK Friday, February 17, 2017 9:16 AM
    Tuesday, February 14, 2017 10:25 AM

All replies

  • At first glance it looks OK, I would PRINT @cmd to see the script generated by the dynamic SQL and test it on the dev server first


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 14, 2017 6:29 AM
  • Hi Uri 

    Ok - I'm getting this error: 

    Msg 102, Level 15, State 1, Procedure MoveLog, Line 25
    Incorrect syntax near '0x01'.

    what about the folder permission - so SQL can use this correct ? 


    • Edited by JmATK Tuesday, February 14, 2017 9:37 AM
    Tuesday, February 14, 2017 9:34 AM
  • You missed = 

     select name
       from sys.databases
       where owner_sid = 0x01;--user dbs only


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 14, 2017 9:42 AM
  • Grant all appropriate permissions to the account you run this script and it should be working 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 14, 2017 9:43 AM
  • ahh ...I read that it should run a few times ...(nothing seems to happen, no files is moved) - I've also restarted the SQL   

    Msg 2714, Level 16, State 3, Procedure MoveLog, Line 2

    There is already an object named 'MoveLog' in the database.

    The owner_sid  0x01 is SA account - correct?  - how do I find the similar on another account?



    • Edited by JmATK Tuesday, February 14, 2017 10:17 AM
    Tuesday, February 14, 2017 9:55 AM
  • Run first this 

    -- To allow advanced options to be changed.  
    EXEC sp_configure 'show advanced options', 1;  
    GO  
    -- To update the currently configured value for advanced options.  
    RECONFIGURE;  
    GO  
    -- To enable the feature.  
    EXEC sp_configure 'xp_cmdshell', 1;  
    GO  
    -- To update the currently configured value for this feature.  
    RECONFIGURE;  
    GO  


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 14, 2017 10:24 AM
  • This us an updated sp


    alter proc [dbo].[MoveLog]
    as





    declare @DBName as varchar(128);
    declare @LogicalName as varchar(128);
    declare @FileLocation as varchar(128);
    declare @FileName as varchar(128);
    declare @CMD as nvarchar(256);
    declare @SourceFolder as varchar(128);
    declare @DestinationFolder as varchar(128);

    SET @DestinationFolder = 'E:'; -- Change this to the correct destination folder

    BEGIN TRY

    declare Db_cursor Cursor forward_only for
       select name
       from sys.databases
        where owner_sid = 0x01;--user dbs only

    OPEN Db_cursor;

    FETCH NEXT FROM Db_cursor 
    INTO @DBName;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET SINGLE_USER WITH ROLLBACK immediate';
          Exec sp_executesql @CMD 
     
        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT ;

        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET OFFLINE ';
       Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

        SELECT @LogicalName =name, @FileLocation=physical_name, @FileName = reverse(left(reverse(physical_name), charindex('\', reverse(physical_name)) -1))
        FROM sys.master_files
        WHERE type_desc = 'LOG' and database_id = DB_ID(@DBName);

        --move the log file
        set @CMD = 'exec xp_cmdshell N''move "' + @FileLocation + '" "' + @DestinationFolder + '\' + @FileName + '"''';
         Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

        SET @SourceFolder = REPLACE(@FileLocation,@Filename,'');

        /* Update the system catalog */
        set @CMD = 'ALTER DATABASE [' + @DBName + '] MODIFY FILE ( NAME = [' + @LogicalName + '], FILENAME = ''' + @DestinationFolder + '\' + @FileName + ''')';
         Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET MULTI_USER';
        Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

        set @CMD = 'ALTER DATABASE [' + @DBName + '] SET ONLINE';
         Exec sp_executesql @CMD 

        RAISERROR ('Executed command: %s',0,1,@CMD) WITH NOWAIT;

     FETCH NEXT FROM Db_cursor 
        INTO @DBName;
     
    END

    CLOSE Db_cursor;
    DEALLOCATE Db_cursor;
    --GO
    END TRY

    BEGIN CATCH
       IF (SELECT CURSOR_STATUS('global','Db_cursor')) >=0 
       BEGIN
          DEALLOCATE Db_cursor;
          RAISERROR ('Db_cursor Deallocated in catch',0,1) WITH NOWAIT;
       END

       SELECT ERROR_LINE(),ERROR_MESSAGE();

    END CATCH


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by JmATK Tuesday, February 14, 2017 10:50 AM
    • Unmarked as answer by JmATK Wednesday, February 15, 2017 3:57 PM
    • Marked as answer by JmATK Friday, February 17, 2017 9:16 AM
    Tuesday, February 14, 2017 10:25 AM
  • Hi Uri 

    can't seem to get it to work - maybe my permissions :(  ... - closing this

    thank you for you time and effort (y) :)   

    Tuesday, February 14, 2017 10:50 AM
  • Comment out Exec sp_executesql @CMD 

    and type print @cmd

    Now, run line by line the script and see where is the error. Probably the error occurs in the MOVE file to the separate drive, make sure that SQL Server account is running under domain account which has an appropriate permissions


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, February 14, 2017 11:41 AM
  • Hi Uri 

    I'm facing colleagues that say/argues that this "split" of .mdf and .ldf on 2 separate drives, doesn't matter, as it's all happens on the same SAN? - do you concur in this?

    it's a virtual machine (hyper-v) - only this SQL on the Host

    - everything I read - this "splitting" of .mdf and .ldf is considered Best Practice!      


    • Edited by JmATK Wednesday, February 15, 2017 4:03 PM
    Wednesday, February 15, 2017 4:01 PM