February 12, 2020 at 6:44 am
I have created a stored procudure SP2 which creates the csv file. This store procedure is called within another stored procedure SP1 but when i run stored procedure SP1 which contains execute statement to SP2 from .net application, My table gets locked and i get a deadlock. the csv file is created but is locked and and it doesn't write anything to it and sits there. Everytime this happens, we have to restart the server. i get the following error when i run the query to see what has happened:
BlockingSessionID VictimSessionID BlockingQuery VictimQuery
82 96 xp_cmdshell SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp
WaitDurationSecond WaitType BlockingQueryCompletePercent
1154 LCK_M_SCH_S 0
Don't know how to resolve this issue.
February 12, 2020 at 10:05 am
you don't have to restart the server, you can just run "kill 1154" (in this example)
we would need to see both proc calls, but I suspect that since you are using global temp tables then this is the issue. can you switch to using a TVP?
it looks like proc 1 is holding a lock and proc 2 is fighting it.
MVDBA
February 12, 2020 at 9:32 pm
It looks like your xp_cmdshell is hanging - and you can't kill that as it spawns a windows process external to SQL Server. You would need to restart the server (as you did) or restart the SQL Server service or kill the PID in Windows - cmd.exe from xp_cmdshell and often any of the processes that xp_cmdshell invoked.
Without seeing the code it's hard to say what the issues may be but I've seen quite a few posts with the same issue . Try splitting out the xp_cmdshell is about all I can suggest without seeing the code.
Sue
February 12, 2020 at 11:47 pm
##csvtemp only contains 6 lines.. so it is not big...
set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ****** -P ******** -T'
exec master..xp_cmdshell @sql
February 13, 2020 at 8:40 am
##csvtemp only contains 6 lines.. so it is not big...
set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ****** -P ******** -T'
exec master..xp_cmdshell @sql
deadlocks are not about the size of the table, it is about 2 competing queries for one resource - in this instance a ## table - your proc2 is competing with proc1 in the same transaction for access to the ## table.
MVDBA
February 13, 2020 at 5:43 pm
##csvtemp only contains 6 lines.. so it is not big...
set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ****** -P ******** -T'
exec master..xp_cmdshell @sql
If you can run the bcp command alone without issues then you may want to look at the security context in place when it's run in the stored procedure. It's weird that it creates the file and can't write to it though - maybe make sure the file doesn't already exist.
Sue
February 13, 2020 at 10:50 pm
##csvtemp only contains 6 lines.. so it is not big...
set @sql = 'bcp "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20,Col21,Col22 FROM ##csvtemp" queryout '+@processedFolderLocation+'/"'+@csvFileName+'" -c -d ' + DB_NAME() + ' -t, -S '+ @@servername+ ' -U ****** -P ******** -T'
exec master..xp_cmdshell @sql
Good lord! Be careful here! This is a classic reason why people think that xp_CmdShell is a security violation and, in the case of how you've written the code with a hard-coded user name and password, they are incredibly correct (in this case). You absolutely need to learn to do this with a "Trusted Connection" and without any individual user having the privs to execute xp_CmdShell directly. They should only have the privs to execute a stored procedure that does such a thing and not even be able to see the content of the stored procedure.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply