comand line crushing server

AlexG

Hi,
I have sp, daily script all db on domain (1000+) using cursor and sql compare comand line

db ranges from sql version 2000 to to 2008 R2
recently script start crushing server,I could not locate any error logs for redgate sql compare beside "out" files with 0 bytes

bellow part of loops script
OPEN UserDB_Cursor
FETCH NEXT FROM UserDB_Cursor INTO @db_servername, @DBName
@FETCH_STATUS = 0
BEGIN


SET @CMD = 'Exec master..xp_cmdshell ''C:\Tools\SQLCompare8\sqlcompare /server1:'
+ @db_servername + ' /database1:' + @DBName
+ ' /server2:smtmcrsqlxx /database2:Sqlcompare /scriptfile:"\\Server1\DBREP\snapshot\'
+ @db_servername + '_' + @DBName
+ '.sql" /force /exclude:user /exclude:certificate /exclude:Assembly /quiet /options:DoNotOutputCommentHeader /out:"\\Server2\DEV\snapshot_log\'
+ @db_servername + '_' + @DBName
+ '.log"'''
select @CMD
EXEC (@CMD)
FETCH NEXT FROM UserDB_Cursor INTO @db_servername, @DBName
END

Chris Auckland

Thanks for your post.

What are the symptoms of the server being crushed? Is SQL Server causing very high CPU or memory usage, or is the SQL Compare process consuming all the resources?

Does the procedure wait for the previous EXEC (@CMD) to complete before it starts the next one, or are multiple instances of SQL Compare being opened?

Usually SQL Compare will be run from a workstation and not on the server itself. If SQL compare is causing the problems on the server, you could try running SQL Compare on a different machine.

I hope this helps.