Win32 Restart SQLServer.exe (Default Instance VBScript)

Question

Win32 Restart SQLServer.exe (Default Instance VBScript)

Welsh Corgi

SSC Guru

Points: 116520
More actions
March 22, 2012 at 6:55 am

#255175

Several years ago I wrote a VBScript Program that would restart a Service if an Oracle application was not processing Orders.
I believe that I used a couple of Win32 Process Classes.
I have a default instance and two named instances.
I'm doing an initial load to Staging tables from DB2 with several packages and if I restart the SQL Server Service between steps I get better performance.
I know that this seems kind of unorthodox but I'm running out of options.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

SQL Server has some of the most stable and reliable services there are, there's likely to be something else funny going on that restarting happens to solve but could be resolved in a better way.

For example, how are you doing the initial load? SSIS? Have you perhaps overallocated the memory available to SQL Server, so the footprint of the service grows too much and puts SSIS under memory pressure?

What in particular is taking longer? A Bulk Insert, a specific t-SQL statement or something else?

HowardW One Orange Chip Points: 29109 More actions · Answer 2

Having said that, it's not rocket science to restart services, just use the Net Stop and Net Start commands:

http://technet.microsoft.com/en-us/library/cc736564%28v=ws.10%29.aspx#BKMK_cmd

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

HowardW (3/22/2012)
SQL Server has some of the most stable and reliable services there are, there's likely to be something else funny going on that restarting happens to solve but could be resolved in a better way.
For example, how are you doing the initial load? SSIS? Have you perhaps overallocated the memory available to SQL Server, so the footprint of the service grows too much and puts SSIS under memory pressure?
What in particular is taking longer? A Bulk Insert, a specific t-SQL statement or something else?

I have allocated 10GB out of 24 GB. 13% of memory is being used.

I'm performing Data Transformation task (fast load) on most of the 69 tables.

I was getting obscure errors so I had to create a Stored Procedure on about 7 tables that uses a Linked Server. The last table which contains less than 3 million records is taking forever and has not completed.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

HowardW (3/22/2012)
Having said that, it's not rocket science to restart services, just use the Net Stop and Net Start commands:
http://technet.microsoft.com/en-us/library/cc736564%28v=ws.10%29.aspx#BKMK_cmd

I need to do this Programmatically as a job step and I need to make sure that I stop the correct instance.:-D

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/