• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2098
  • Last Modified:

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

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
Eric Bourland
Asked:
Eric Bourland
  • 9
  • 4
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
Eric BourlandAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Eric BourlandAuthor Commented:
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
 
Eric BourlandAuthor Commented:
>>>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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Eric BourlandAuthor Commented:
Yep. All are set to AUTO_CLOSE = 1.

Working on this now.
0
 
Eric BourlandAuthor Commented:
This solved the problem of the recurrent database restarting. Kevin, thank you very much!

Eric
0
 
_agx_Commented:
Wow. I'd never heard of that setting. Sorry you had troubles with it Eric - but very good to know!
0
 
Eric BourlandAuthor Commented:
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
 
_agx_Commented:
(.. 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
 
Eric BourlandAuthor Commented:
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
 
_agx_Commented:
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
 
Eric BourlandAuthor Commented:
Got it. I just disabled it. Looks like everything is OK so far.

Thank you, _agx_! Hope you have been well.

Eric
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Eric BourlandAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 9
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now