multithreading - T SQL multi thread for launching xp_cmdshell jobs -

i have job takes database backup files , compresses them .7z files using 7 zips command line utility , @ moment takes 8 hours run through .bak files because doing 1 @ time. running on has 16 cores , 7z process seems using 1 core able run multiply instances of xp_cmdshell command have compress several files @ time. there way execute list of commands in t sql on mssql server 2005?

i have post script below.

this link program using zip files. [http://downloads.sourceforge.net/sevenzip/7za920.zip][1]

-- yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy -- ii       zip files in folder                       ii -- vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv  -- zip , delete files backup folder -- list files in directory - t-sql parse string date , filename  declare  @pathname       varchar(256) ,           @cmd            varchar(512)   create table #commandshell ( line varchar(512))       -- use xp_cmdshell option has enabled. can use script bellow enable it.     --     -- -- run exec sp_configure see if option exists in list , can check if enabled.     -- exec sp_configure     -- go     -- -- if don't see xp_cmdshell in list have enable advanced options first      -- -- before can enable xp_cmdshell option.     -- exec sp_configure 'show advanced options', 1;     -- go     -- reconfigure     -- go     -- -- if xp_cmdshell in list should need run script.     -- exec sp_configure 'xp_cmdshell', 1     -- go     -- reconfigure  set @pathname = 'd:\files\backups\'   set @cmd = 'dir ' + @pathname + ' /tc'   insert #commandshell  exec master..xp_cmdshell   @cmd   -- delete lines not containing filename delete    #commandshell   line not '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'  or line '%<dir>%' or line null  -- sql reverse string function - charindex string function  select row_number() on (order reverse( left(reverse(line),charindex(' ',reverse(line))-1 ) )) row_num,   filename = reverse( left(reverse(line),charindex(' ',reverse(line))-1 ) ),   createdate = left(line,10) #tempfilelist  #commandshell reverse( left(reverse(line),charindex(' ',reverse(line))-1 ) ) '%.bak' order filename  drop table #commandshell  declare @filemaxrownum int set @filemaxrownum = (select max(row_num) #tempfilelist)  declare @fileiter int set @fileiter = (select min(row_num) #tempfilelist)  while @fileiter <= @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 #tempfilelist row_num = @fileiter     set @cmd2 = @7z_path + '\7za -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 #tempfilelist row_num = @fileiter     exec xp_cmdshell @delfile     set @fileiter = @fileiter + 1 end drop table #tempfilelist 

i don't think issue sql server. if wanted run multiple threads of execution @ same time, scheduled jobs in sql server agent... but, workaround compression algorithm in 7-zip you're using.

the default compression won't use more 2 cores, alternate compression algorithm, such b-zip, make better use of server's available cores.

instead of using -t7z switch, try using -tbzip2, algorithm detailed in blog post above.

set @cmd2 = @7z_path + '\7za -tbzip2 -mx5 -ms=off '    + @archive_destination_dir    + @archive_name + '.7z '    + @archive_destination_dir    + @archive_name