Solved

MS SQL Server 2012 keeps restarting its databases every couple of minutes

Posted on 2014-02-26
16
1,626 Views
Last Modified: 2014-02-27
ColdFusion 9
MS Windows Server 2008 Standard R2
MS SQL Server 2012

Hi. I am having a distressing problem that affects my entire server. This problem began when I migrated all of my web sites from a remote MS SQL Server 2005 server (at hostmysite.com) to a local MS SQL Server 2012 server at viviotech.net.

After the migration from MSSQL2005 (remote) to MSSQL2012 (local), the web sites all seemed to be working fine. Then, over the past few days, I noticed that sometimes web sites would go offline -- because they could not find the data tables inside their datasources. Odd!

I doublechecked all of the datasources in ColdFusion administrator. Everything looks normal to me.

I investigated in the server event logs. There, I see that MS SQL Server 2012 keeps restarting its databases -- often! -- maybe every time someone loads a web site?

Has anybody there seen anything like this?

Could there be something in my ColdFusion code that is causing this? I attach a sample application.cfc -- is there anything deleterious in that file?

MS SQL Server 2012 keeps restarting its databases every couple of minutes. I include a few log entries from the server events log, below.

Has anyone ever seen anything like this? This was not a problem when the remote MS SQL Server 2005 at hostmysite.com was in use. This is an urgent problem. Thank you for any advice.

Eric


Server Event logs:
Log Name:      Application
Source:        MSSQL$SQLEXPRESS
Date:          2/26/2014 7:54:16 AM
Event ID:      17137
Task Category: Server
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      ram.viviotech.net
Description:
Starting up database 'yogisark'.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQL$SQLEXPRESS" />
    <EventID Qualifiers="16384">17137</EventID>
    <Level>4</Level>
    <Task>2</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2014-02-26T15:54:16.000000000Z" />
    <EventRecordID>31989</EventRecordID>
    <Channel>Application</Channel>
    <Computer>ram.viviotech.net</Computer>
    <Security />
  </System>
  <EventData>
    <Data>yogisark</Data>
    <Binary>F14200000A0000000F000000520041004D005C00530051004C0045005800500052004500530053000000070000006D00610073007400650072000000</Binary>
  </EventData>
</Event>



Log Name:      Application
Source:        MSSQL$SQLEXPRESS
Date:          2/26/2014 8:05:33 AM
Event ID:      17137
Task Category: Server
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      ram.viviotech.net
Description:
Starting up database 'nnvawi'.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQL$SQLEXPRESS" />
    <EventID Qualifiers="16384">17137</EventID>
    <Level>4</Level>
    <Task>2</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2014-02-26T16:05:33.000000000Z" />
    <EventRecordID>32008</EventRecordID>
    <Channel>Application</Channel>
    <Computer>ram.viviotech.net</Computer>
    <Security />
  </System>
  <EventData>
    <Data>nnvawi</Data>
    <Binary>F14200000A0000000F000000520041004D005C00530051004C0045005800500052004500530053000000070000006D00610073007400650072000000</Binary>
  </EventData>
</Event>

Open in new window


Sample application.cfc
<!--- Filename: Application.cfc
 Created by: Raymond Camden (ray@camdenfamily.com)
 Modified by: Eric B, gdemaria, July 2010 --->

<cfcomponent output="false">

  <!--- Name the application. --->
  <cfset this.name="Tricia McCauley">
  <cfset this.scriptProtect = "No">
  
  <cfset this.applicationTimeout = CreateTimeSpan(0,0,360,0)>
  
  <!--- Turn on session management. --->
  <cfset this.sessionManagement="true">
  
  <!--- Set session timeout period --->
  <cfset this.sessionTimeout = CreateTimeSpan(0,0,360,0)>

  <cfset this.clientManagement = "false">

<!--- set path to cfform.js --->
<cfparam name="request.CFFORM_JS_Lib" type="string" default="/CFIDE/scripts/cfform.js" />
  
  
<!--- function: onApplicationStart --->
  <cffunction name="onApplicationStart" output="false" returnType="void">

    <!--- Any variables set here can be used by all of the application's pages --->
    <cfset APPLICATION.dataSource = "tmc">
   
    
<!--- Set up Application variables. Locking the Application scope is not necessary in this method. --->
		<cfset Application.configured = 1>
		<cfset Application.datetimeConfigured = TimeFormat(Now(), "hh:mm tt") & "  " & DateFormat(Now(), "mm.dd.yyyy")>
		<cfset Application.currentSessions = 0>
  
  </cffunction> 
  
   
    <cffunction name="clearSessionVariables" returntype="void">
      <!--- defined all session variables, so they will always exist ---->
      <cfset session.auth = structNew()>
      <cfset session.auth.isLoggedIn  = false>
      <cfset session.auth.UserID  = "">
      <cfset session.auth.Title   = "">
      <cfset session.auth.FirstName   = "">
      <cfset session.auth.MiddleInitial   = "">
      <cfset session.auth.LastName    = "">
      <cfset session.auth.Address    = "">
      <cfset session.auth.City    = "">
      <cfset session.auth.State    = "">
      <cfset session.auth.ZIP    = "">
      <cfset session.auth.Telephone   = "">
      <cfset session.auth.UserEmail    = "">
      <cfset session.auth.UserPassword    = "">
      <cfset session.auth.UserRoleID  = "">
      <cfset session.auth.lastError  = "">
  </cffunction>
  
  <cffunction name="onSessionStart" returntype="void">
      <!--- defined all session variables, so they will always exist ---->
      <cfset clearSessionVariables()>
  </cffunction>
  

<!--- function: onRequestStart ---> 

<cffunction name="onRequestStart">
<cfargument type="String" name="targetPage" required="true" /> 

    <!--- All these folders/top level files require a login, specific roles are addressed below ---->  
    <cfset var securefolders = "admin,manage">  
    <cfset var currentFolder = listFirst(cgi.script_name,"/")> <!--- the user's current location ---->  
    
<cfset REQUEST.contentTable = "tmccNavigation">    
<cfset REQUEST.companyName = "Tricia McCauley">
<cfset REQUEST.companyURL = "www.triciamccauley.com">
<cfset REQUEST.companyDomain = "www.triciamccauley.com">
<cfset REQUEST.usersTable = "tmccUsers">
<cfset REQUEST.blogTable = "tmccBlog">
<cfset REQUEST.newsTable = "tmccNews">
<cfset REQUEST.footerTable = "tmccFooter">
<cfset REQUEST.MailingListsTable = "tmccMailingLists">
<cfset REQUEST.NewslettersTable = "tmccNewsletters">
<cfset REQUEST.TestNewslettersTable = "tmccTestEmailAddresses">




<!--- process login credentials --->

 <!--- begin cfif isDefined("form.userEmail") and isDefined("form.userPassword") ---> 
    <cfif isDefined("form.userEmail") and isDefined("form.userPassword") and isDefined("form.doLogin")>
     
   
         <!--- check box to remember UserEmail was checked, so make a cookie for it ---> 
                <cfif isDefined("form.SaveUserEmail") and form.SaveUserEmail is "Yes"> 
          <cfcookie name="SaveUserEmail" value="#form.UserEmail#" expires="7"> 
        </cfif> 
         
        <!--- user is attempting to log in, so process the login request ----> 
        <cfif NOT checkLogin(form.userEmail, form.userPassword)> 
           <cfinclude template="LoginForm.cfm"> <!--- login failed, so show login form ----> 
           <cfreturn false>  
           <!--- close cfif NOT checkLogin(form.userEmail, form.userPassword) ---> 
        </cfif> 
    <!--- close cfif isDefined("form.userEmail") and isDefined("form.userPassword") and isDefined("form.doLogin") ---> 
    </cfif> 
 
<!--- /process login credentials --->


<cftry>

<!--- test for access to secureFolders --->
     <cfif listFindNoCase(secureFolders, currentFolder)>  <!---- are we in a secure area? --->  
       <cfif session.auth.isLoggedIn is False> <!--- This is a secure area, if the user is not logged in, go to login page ---->  
           <cfinclude template="LoginForm.cfm">
            <cfthrow message="Please log in with proper credentials to access this area.">
           <cfabort>  
       <cfelse> <!--- the user is logged in, then check roles ---->  
           <cfswitch expression="#currentFolder#">  
              <cfcase value="admin">  
                  <cfif listFind("1",session.auth.UserRoleID) eq 0> <!---- role 1 has access to admin --->  
                      <cfinclude template="LoginError.cfm">
                      <cfabort>  
                  </cfif>  
              </cfcase>  
              <cfcase value="manage">  
                  <cfif listFind("1,7",session.auth.UserRoleID) eq 0>  <!---- roles 1, 3 have access to liaison --->  
                      <cfinclude template="LoginError.cfm">
                      <cfabort>  
                  </cfif>  
              </cfcase>  
              <cfdefaultcase> <!---- all other secure folders ---->  
              </cfdefaultcase>  
           </cfswitch>  
       </cfif> <!---- end if user is logged in or not ---->  
    </cfif>  <!---- end if user is in a secure area or not ---->  
    
    <!--- /test for access to secureFolders --->
         
      <cfcatch>
      <cfset clearSessionVariables()>
      <cfset SESSION.auth.lastError  = cfcatch.message>
      <cfreturn false>
  </cfcatch>
  </cftry>

    
    
         
         
             <!--- if query_string contains cast(, then abort! --->                                              
    <cfif cgi.query_string contains "cast(">
      <cfabort>
    </cfif>
    
              <!--- if query_string contains replace(, then abort! --->                                              
    <cfif cgi.query_string contains "replace(">
      <cfabort>
    </cfif>

   </cffunction>
  <!--- close function: onRequestStart --->
 
 
 <!--- begin cfif isDefined("form.doLogin") --->
    <cfif isDefined("form.doLogin")>
    
     
<!--- begin function checkLogin --->
<cffunction name="checkLogin">

  <cfargument name="p_UserEmail" required=false default="" />
  <cfargument name="p_password" required=false default="" />

  <cfset var UserPassword = trim(arguments.p_password)>
  <cfset var UserEmail     = trim(arguments.p_UserEmail)>
  <cfset var getUser = "">

  <cftry>
      <cfif len(UserPassword) eq 0 or len(UserEmail) eq 0>
         <cfthrow message="Please enter UserEmail and password">
      </cfif> 
    
      <cfquery name="getUser" datasource="#APPLICATION.dataSource#">
       SELECT UserID, FirstName, UserRoleID, UserEmail, UserPassword
        FROM #REQUEST.usersTable#
       WHERE UserEmail = <cfqueryparam cfsqltype="cf_sql_varchar" value="#UserEmail#" maxlength="255"> 
      </cfquery>
      <cfif getuser.recordCount eq 0>
        <cfthrow message="Incorrect email address and/or password. Be sure to enter the correct, original email address with which you registered. Please type your password carefully.">
      <cfelseif getUser.UserPassword is not UserPassword>
        <cfthrow message="Invalid Password.">
       </cfif>
    
      <cfset clearSessionVariables()>
      <cfset SESSION.auth.isLoggedIn = "Yes">
      <cfset SESSION.auth.UserID     = getUser.UserID>
      <cfset SESSION.auth.FirstName  = getUser.firstName>
      <cfset SESSION.auth.UserRoleID = getUser.UserRoleID>
      <cfset SESSION.auth.UserEmail  = getUser.UserEmail>
      <cfset SESSION.auth.lastError  = "">
      

 <!--- Now that user is logged in, send her to web root --->

 <cflocation url="/admin/managePages.cfm" addtoken="no">

      
      <cfreturn true>
      
      
  <cfcatch>
      <cfset clearSessionVariables()>
      <cfset SESSION.auth.lastError  = cfcatch.message>
      <cfreturn false>
  </cfcatch>
  </cftry>
    
</cffunction>
<!--- close function checkLogin --->

      <!--- close cfif isDefined("form.doLogin") --->
    </cfif>


</cfcomponent>

Open in new window


p.s. And apparently, one symptom is, when I restart ColdFusion, either of these two web sites goes offline:

www.triciamccauley.com
www.thezeromarginalsociety.com

So, one strange rule of this problem is, both of these web sites cannot be online at the same time. (?)

I tried disabling both sites in IIS, but, still, MSSQL Server 2012 keeps restarting its databases.
0
Comment
Question by:Eric Bourland
  • 9
  • 4
  • 3
16 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
What did viviotech.net support say?  Given this worked under your other hosting and just started after migration, I suspect there was corruption in the transfer, differences in versions (creating an issue), or problem in the new configuration/systems.  SQL Server restarting may be an indication of a server issue like one setup with both antivirus and backup software that clash.  Again, the support staff for the hosting company could better advise on the particulars of their environment unless you own the instance and administrate Windows and SQL yourself.  If so, are there any other changes you made during the migration?
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I wonder if this is just an Event 17137 situation; I am looking into that:

http://sqlserverteacher.blogspot.com/2013/04/sql-server-event-id-17137-starting-up.html

In the MS SQL Server 2012 Management Studio, is there an easy way to set the AUTO_CLOSE option to OFF in my databases?

That might solve the recurrent database restarts.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
Comment Utility
Yes.  I do not have AUTO CLOSE on any databases, so I never experienced that problem.  Sounds like it may be the culprit as it would explain why this is happening as you installed new SQL server.

As stated in the article, you can use the SQL shown in SSMS.
ALTER DATABASE <DATABASE_NAME>
SET AUTO_CLOSE OFF

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Kevin,

I made sure to keep all data tables the same for all web sites -- same datatypes, table structure, and so on.

One change I did make is this:

1) On old host, there was only ONE database, with maybe three hundred data tables total. About thirty web sites requested data from this one database and its hundreds of tables. That database name was 'ebwebwork'. I do not know how often it started or restarted.

2) On new host, there are about thirty databases -- one database for each web site. In Events Viewer, I can see that these databases start and restart very often -- I suspect (do not know for sure) that each database starts and restarts when a person views a web site.

I am standing by to hear back from Viviotech tech support, to get their opinion about the Event 17137 AUTO_CLOSE option I mentioned above. My goal is to confirm that AUTO_CLOSE is set to OFF -- which will at least rule out one problem.

Thank you for your helpful reply. I will come back here when I get more news. =)

Eric
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
>>>As stated in the article, you can use the SQL shown in SSMS.

ALTER DATABASE <DATABASE_NAME>
SET AUTO_CLOSE OFF
                         

Got it. I will try it out!
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
To confirm the issue, you can run this query:
-- http://technet.microsoft.com/en-us/library/ms178534.aspx
SELECT [name]
FROM sys.databases
WHERE is_auto_close_on = 1
;

Open in new window

0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Yep. All are set to AUTO_CLOSE = 1.

Working on this now.
0
 
LVL 3

Author Closing Comment

by:Eric Bourland
Comment Utility
This solved the problem of the recurrent database restarting. Kevin, thank you very much!

Eric
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Wow. I'd never heard of that setting. Sorry you had troubles with it Eric - but very good to know!
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
I am having one other very weird problem related to ColdFusion -- which I am going to post about soon.

For now -- lesson learned: MS SQL Server 2012, by default, sets new databases to AUTO_CLOSE ON.

Solution:
ALTER <DATABASE>
SET AUTO_CLOSE OFF

Result: Improved server performance. =)
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
(.. That's a crazy default to have on a database frequently used for web apps, but ...good to know there's a simple fix)
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Question. Windows Server Events Viewer reports that two other databases -- system databases -- keep restarting. The restarting databases are:

ReportServer$SQLEXPRESS
ReportServer$SQLEXPRESSTempDB.

Is it safe to set these databases to AUTO_CLOSE OFF?

Log Name:      Application
Source:        MSSQL$SQLEXPRESS
Date:          2/26/2014 11:25:26 AM
Event ID:      17137
Task Category: Server
Level:         Information
Keywords:      Classic
User:          NT SERVICE\ReportServer$SQLEXPRESS
Computer:      ram.viviotech.net
Description:
Starting up database 'ReportServer$SQLEXPRESSTempDB'.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
  <System>
    <Provider Name="MSSQL$SQLEXPRESS" />
    <EventID Qualifiers="16384">17137</EventID>
    <Level>4</Level>
    <Task>2</Task>
    <Keywords>0x80000000000000</Keywords>
    <TimeCreated SystemTime="2014-02-26T19:25:26.000000000Z" />
    <EventRecordID>32388</EventRecordID>
    <Channel>Application</Channel>
    <Computer>ram.viviotech.net</Computer>
    <Security UserID="S-1-5-80-425977601-1203083412-1631309457-2457533047-3321749933" />
  </System>
  <EventData>
    <Data>ReportServer$SQLEXPRESSTempDB</Data>
    <Binary>F14200000A0000000F000000520041004D005C00530051004C0045005800500052004500530053000000180000005200650070006F00720074005300650072007600650072002400530051004C0045005800500052004500530053000000</Binary>
  </EventData>
</Event>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
I'll defer to Kevin on that one, but I think so.  This thread says it's reporting services and mentions you could also just disable it - if you're not using it.

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bff29a0-4315-4f7a-af07-0b1c18ff0d2b/numerous-every-10-minutes-event-log-entries?forum=sqlexpress
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Got it. I just disabled it. Looks like everything is OK so far.

Thank you, _agx_! Hope you have been well.

Eric
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Eric,

Sorry I missed your follow-up questions.  Yes, it is safe to disable AUTO_CLOSE for the reporting services databases.  I guess I did not realize the new default because I have the developer edition of SQL 2012 on my laptop.  Apparently, the express edition uses the AUTO_CLOSE default...they must have assumed it would save resources for local use databases.  For a web application, you definitely want it in server mode.

Kevin
0
 
LVL 3

Author Comment

by:Eric Bourland
Comment Utility
Kevin, thank you. It's working very smoothly now. Fast database, fast web sites. I really appreciate your help. Take care.  =) Eric
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now