Databases
StackOverflow2013
Postsdbo
POT SQL multi thread for launching xp_cmdshell jobs
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POT SQL multi thread for launching xp_cmdshell jobs
    primarykey
    data
    text
    <p>I have a job that takes database backup files and compresses them to .7z files using seven zips command line utility and at the moment it takes about 8 hours to run through all the .bak files because it is doing one at a time. This is running on has about 16 cores and the 7z process only seems to be using 1 core so I would like to be able to run multiply instances of the xp_cmdshell command to have it compress several files at a time. Is there any way to execute a list of commands in T SQL on MSSQL Server 2005?</p> <p>I have post my script below. </p> <p>This is a link to the program I am using to zip the files. [http://downloads.sourceforge.net/sevenzip/7za920.zip][1] </p> <pre><code>-- YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY -- II zip all files in a folder II -- VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV -- Zip and then Delete all files from the backup folder -- List all files in a directory - T-SQL parse string for date and filename DECLARE @PathName VARCHAR(256) , @CMD VARCHAR(512) CREATE TABLE #CommandShell ( Line VARCHAR(512)) -- To use the xp_cmdshell option it has to be enabled. you can use the script bellow to enable it. -- -- -- run exec sp_configure to see if the option exists in this list and you can check if it is enabled. -- EXEC sp_configure -- go -- -- if you don't see xp_cmdshell in the list then you will have to enable advanced options first -- -- before you can enable the xp_cmdshell option. -- EXEC sp_configure 'show advanced options', 1; -- go -- reconfigure -- go -- -- if xp_cmdshell is in the list then you should just need to run this script. -- exec sp_configure 'xp_cmdshell', 1 -- go -- reconfigure SET @PathName = 'D:\FILES\Backups\' SET @CMD = 'DIR ' + @PathName + ' /TC' INSERT INTO #CommandShell EXEC MASTER..xp_cmdshell @CMD -- Delete lines not containing filename DELETE FROM #CommandShell WHERE Line NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %' OR Line LIKE '%&lt;DIR&gt;%' OR Line is null -- SQL reverse string function - charindex string function SELECT ROW_NUMBER() OVER (ORDER BY REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) )) AS ROW_NUM, FileName = REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ), CreateDate = LEFT(Line,10) INTO #tempFileList FROM #CommandShell WHERE REVERSE( LEFT(REVERSE(Line),CHARINDEX(' ',REVERSE(line))-1 ) ) LIKE '%.bak' ORDER BY FileName DROP TABLE #CommandShell DECLARE @FileMaxRownum INT SET @FileMaxRownum = (SELECT MAX(ROW_NUM) FROM #tempFileList) DECLARE @FileIter INT SET @FileIter = (SELECT MIN(ROW_NUM) FROM #tempFileList) WHILE @FileIter &lt;= @FileMaxRownum BEGIN DECLARE @DelFile varchar(200) --@@ DECLARE @cmd2 VARCHAR(1000) SET @cmd2 = null DECLARE @db_bkp_files_dir varchar(100) SET @db_bkp_files_dir = null DECLARE @archive_destination_dir varchar(100) SET @archive_destination_dir = null DECLARE @archive_name varchar(100) SET @archive_name = null DECLARE @7z_path varchar(100) SET @7z_path = null set @archive_destination_dir = @PathName --destination dir set @7z_path = 'D:\FILES' set @db_bkp_files_dir = right(@PathName,1) --db backup files origin SELECT TOP(1) @archive_name = FileName FROM #tempFileList WHERE ROW_NUM = @FileIter SET @cmd2 = @7z_path + '\7za a -t7z -mx5 -ms=off ' + @archive_destination_dir + @archive_name + '.7z ' + @archive_destination_dir + @archive_name print @cmd2 EXEC xp_cmdshell @cmd2 --@@ SELECT TOP(1) @DelFile = 'del ' + @PathName + FileName FROM #tempFileList WHERE ROW_NUM = @FileIter EXEC xp_cmdshell @DelFile SET @FileIter = @FileIter + 1 END DROP TABLE #tempFileList </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser802599
    1. USuser802599
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POT SQL multi thread for launching xp_cmdshell jobs
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload