Solved

Database Down

Posted on 2014-10-12
4
103 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 40

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Search query matching words 20 38
SQL Query help 3 24
Finding Where Clause Value in SQL Views and SP 21 30
Trouble installing msi file with msiexe.exe 2 14
In this article I will describe the Detach & Attach 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.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 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