one of my co workers wants to use excel with a sql connection and run sys.sysprocesses to run every 10 seconds to check for table lock is this process safe?
This can be achieved by creating SQL connection in excel and execute the query.
Otherwise you can write a excel macro to achieve the task
is this process safe for the SQL server
It is a system view. It is safe to call.
and have it refresh every 10 seconds?
Is it safe to delete columns in excel when your connected to a SQL connection from that sheet?
If it is reading and not trying to update, it is safe. However, when you try to refresh next time, the columns might get restored again, depending on how you are populating the excel sheet.
If you are trying to delete columns in the sys.sysprocesses that attempt will fail, so the macro or connection would throw an error.
ok thanks but will running sys.sysprocesses every ten seconds kill the SQL server?
No, it won't kill your server. Try the following query in an SSMS query window.
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM sys.sysprocesses
The messages window will show you some info on how much time and how much IO it took. You will see perhaps a few milliseconds time and no IO. Even that time is probably used for sending the data to your client. You can test that using the following:
SET FMTONLY ON
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM sys.sysprocesses
When you are done, turn off the diagnostics using the following, or you will not see any outputs in that query window.
SET FMTONLY OFF
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;