locked
xp_cmdshell too slow RRS feed

  • Question

  • Hello,

    I am using SQL 2012 SE.

    I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.

    EXEC master.dbo.sp_configure 'show advanced options', 1
    RECONFIGURE WITH OVERRIDE
    GO
    
    EXEC master.dbo.sp_configure 'xp_cmdshell', 1
    RECONFIGURE WITH OVERRIDE
    
    DECLARE @cmd nvarchar(4000)
    
    
    DECLARE @mdfFileDestLocation nvarchar(4000)
    DECLARE @mdfFileName nvarchar(4000)
    
    set @mdfFileDestLocation='\\servername\Backups\'
    set @mdfFileName='DBName.mdf'
    
    
    SET @cmd = 'robocopy D:\Data\ '+@mdfFileDestLocation+' '+@mdfFileName  
    
    print @cmd
    EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT
    
    
    EXEC master.dbo.sp_configure 'xp_cmdshell', 0
    RECONFIGURE WITH OVERRIDE
    GO
    
    EXEC master.dbo.sp_configure 'show advanced options', 0
    RECONFIGURE WITH OVERRIDE
    GO
    

    The only message is I see while the query is executing is :

    Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

    If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing. Where am i doing wrong?

    Thanks

    Wednesday, August 19, 2015 4:52 PM

Answers

  • I changed my plan here. Instead of copying the ldf and mdf I will be doing a backup and remote restore and It is atleast finishing as expected and on time. The restore from disk will be a remote path to the server where backup was taken.
    • Marked as answer by oleolehoohoo Wednesday, August 19, 2015 7:10 PM
    Wednesday, August 19, 2015 7:10 PM

All replies

  • Hi,

    It might be Robocopy that is getting the process slow

    Please check this link

    https://social.technet.microsoft.com/Forums/windowsserver/en-US/105f6692-5de5-49e7-b242-ef75f99c07b3/robocopy-very-bad-performance-on-windows-server-2008-r2?forum=winservergen


    Please Dont forget to mark as answer and Helpful Post. It helps others to find relevant posts to the same question. Milan Das


    • Edited by Milan Das Wednesday, August 19, 2015 5:06 PM
    Wednesday, August 19, 2015 5:04 PM
  • I am using windows 2012 data center edition. Also I dont think its with robocopy or copy commands. I tried to move using command prompt and it took 30 seconds with robocopy and closer to minute with copy command. Whats happening in the my query is its not even going to next step to enable cmdshell.
    Wednesday, August 19, 2015 5:07 PM
  • This has to do with the memory allocated when you run xp_cmdshell.  You have no control over it.  You need to do it a different way.

    Wednesday, August 19, 2015 5:36 PM
  • OK. Thanks for the response.

    I can put the robocopy commands in a batch file and have it run through task scheduler. Is there a way that I can trigger the task in tackscheduler with tsql that i can use in my storedproc or sqlagent job?

    Wednesday, August 19, 2015 5:51 PM
  • I changed my plan here. Instead of copying the ldf and mdf I will be doing a backup and remote restore and It is atleast finishing as expected and on time. The restore from disk will be a remote path to the server where backup was taken.
    • Marked as answer by oleolehoohoo Wednesday, August 19, 2015 7:10 PM
    Wednesday, August 19, 2015 7:10 PM
  • Mu h cleaner solution, IMO. Regarding the file copy, if you will copy files (possibly the backup file), consider toying with XCOPY and the /J switch (doesn't cache the file contencts).

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, August 19, 2015 7:14 PM