Solved

Database Down

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
backups - Strategies 1 13
SqlAdvisor 2016 3 11
Syntax using Declare 3 16
DATETIMEOFFSET feature 1 5
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how the fundamental information of how to create a table.

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

15 Experts available now in Live!

Get 1:1 Help Now