Post reply

Restarting SQL Server every night

  • March 4, 2004 at 3:52 am

    #76135

    Hello,

    I've got a question about restarting the MSSQLSERVER service in Windows. I need to do that every night. How could I achieve this goal?

    Thanks in advance

  • March 4, 2004 at 5:51 am

    #497221

    net stop mssqlserver

    net start mssqlserver

    and schedule it at windows level (at command).

  • March 4, 2004 at 6:04 am

    #497223

    Ok, I figured it out already, Actually it is:

    net stop sqlserveragent

    net stop mssqlserver

    net start mssqlserver

    net start sqlserveragent

    Otherwise it will not work automatically, and net stop will ask to stop the services that are dependent of mssqlserver.

    But thanks for your answer.

  • March 4, 2004 at 3:42 pm

    #497434

    I am curious as to why you find it necessary to stop and start SQL Server every night?

    GaryA

     

  • March 4, 2004 at 4:43 pm

    #497437

    me too.

  • March 4, 2004 at 11:15 pm

    #497461

    Because the SQL Server process took over 120 mb from my system memory. At the location our customer is working, they have only a server available with 384 mb of memory. Besides SQL Server, another database server (don't know which one exactly) is running on that server.

  • March 5, 2004 at 3:15 am

    #497500

    We also restart the SQL every night (not only the SQL service, but the complete server).

    We saw a great performance increase after we decided to do this.

  • March 5, 2004 at 3:25 am

    #497502

    I think I know what the problem is. The program that uses the database on the SQL Server is build using data bound controls. They don't work always the way the programmer wants. With ADO (bound recordset), the database is reached and updated and so on. With large recordsets this could be the problem of using that much memory.

    Next project should never, never and never again be programmed with data bound controls!

  • March 8, 2004 at 9:53 am

    #497825

    You can set a limit on the amount of memory the server will use. In EM, right click server, Properties, Memory tab.

  • March 8, 2004 at 10:11 am

    #497838

    You can also kill all active threads flush caches to free up memory inside the SQL Server memory space. I would avoid stopping and starting SQL for one simple reason, tempdb gets rebuild every time you start and stop SQL. This will lead to disk fragmentation over the long run and hurt performance. tempdb will also have to grow every day as it is utilized. You are also flushing away any query plans that can help SQL run faster. I would also consider buying memory, it is cheap and is the best upgrade for the dollars spent. SQL is trying to take the memory it needs to perform as well as possible setting the memory down to a lower size will fix the issue of restarting but may lead to other performance issues down the road.

    Wes

  • March 10, 2004 at 12:30 am

    #498090

    I'm relatively new to SQL Server. What do you mean with 'kill all active threads flush caches to free up memory inside the SQL Server memory space'?

  • March 11, 2004 at 6:03 am

    #498255

    there are scripts here that will issue a kill command and that will remove any connections from users on the server then you can issue dbcc statements that will flush out the caches. That is documented in Books On Line.

     

    Wes

  • March 11, 2004 at 9:21 am

    #498319

    I only restart our SQL Server when it is absolutly needed. It may go a month or two before we restart and we rarely have any problems.

    Thanks,

    Bobby

Viewing 13 posts - 1 through 12 (of 12 total)

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