Solved

Database Down

Posted on 2014-10-12
4
101 Views
Last Modified: 2014-11-03
Dear Expert,

We have Database in SQL server 2008 R2 having size approximately 490 GB,
after four and five hours our database down so that our website stopped, then we restart the Database server its again working then after some hours  again database down.
Kindly please let us know the reason so solve the problem.

Thanks in Advance
0
Comment
Question by:Asrar Azmi
4 Comments
 

Expert Comment

by:Ravi Kamagond
ID: 40375633
Database maintenance can cause downtime if an operation is performed that needs to place a lock on a table for an extended period of time. Examples of such operations include:
1. Creating or rebuilding a non-clustered index (can prevent table modifications)
2. Creating, dropping, or rebuilding a clustered index (can prevent table reads and modifications)
3. Data center failure: This category of failures takes the entire data center offline, rendering any local redundant copies of the data useless. Examples include natural disasters, fire, power loss, or failed network connectivity.
4. Server failure: This category of failures takes the server hosting one or more SQL Server instances offline. Examples include failed power supply, failed CPU, failed memory, or operating system crashes.
5. I/O subsystem failure: This category of failures involves the hardware used to physically store the data and thus directly affects the data itself—usually causing data loss (which then may lead to downtime to perform disaster recovery). Examples include a drive failure, a RAID controller failure, or an I/O subsystem software bug causing corruption.
6.Human error: This category of failures involves someone (a DBA, a regular user, or an application programmer introducing an application software bug) making a mistake that damages data, usually causing data loss (and then potentially downtime to recover the data). Examples include dropping a table, deleting or updating data in a table without specifying a predicate, setting a database offline, or shutting down a SQL Server instance. Human errors are usually accidental, but they can also be malicious.

In all cases, data loss can be prevented and downtime can be minimized using the technologies in SQL Server 2008.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 40379979
What do you mean by "our database down"?
What are the Event Viewer messages/errors on the server where "our website stopped"?
What are the Event Viewer and SQL Log messages/errors on the SQL server where "our database down"?

Is this maybe a resources related issue on the SQL box like a disk full due to excessive T-log growth or maybe is running out of memory because the SQL Server does NOT have the MAX memory properly configured? Maybe CPU peaks due to missing indexes and the Performance Dashboard Missing Index report should help with that. http://blogs.msdn.com/b/tommills/archive/2008/05/30/performance-dashboard-missing-indexes-report.aspx


Did anyone managed to connect to the SQL Server were the database is and check its status? Check if there are any locks/blocks by running a command like SP_WHO2 in a SSMS query or look at the Activity Monitor?
0
 

Author Comment

by:Asrar Azmi
ID: 40382010
I received following message in EventViewer  

Autogrow of file 'gpssystem1_log' in database 'gpssystem' was cancelled by user or timed out after 32479 milliseconds.  Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.


Please check and reply soon
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 40391166
Your Auto grow is set to the default 10%.  It needs to be changed to a fixed size such as 500MB.  Let me know if you need more details.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

773 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