question

franciss avatar image
franciss asked

DB Corruption

Our server goes for frequent shutdowns due to power outages thereby rendering the DB corrupt having consistency errors. Is there any way out of this as a fix needs to be done quite regularly. Is there any measures that can be taken once the server comes up after shutdown to prevent the DB from having consistency errors.
dbcc
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks all....but i am supporting for arnd 100 stores where power outage is very common...it is affectign all sales table with consistency errors...power outage is not in myhands!...the UPS device can backup upto 20 mins i think!!
0 Likes 0 ·
Dave_Green avatar image
Dave_Green answered
As an immediate mitigation - can you put the server on a UPS system? That should help if the outages are short-lived, and in any case allow the system time to shutdown gracefully (thereby avoiding corruption). That doesn't solve the current issue of corruption though - just prevent future power issues causing more. As @sp-lock said, you need to solve the real problem, not just the symptom.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
Firstly I would say 1. Fix the power outages. 2. Depending on the corrupt information from your DBCC would really determine your next step.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

raadee avatar image
raadee answered
Do you have UPS software installed on your servers so that your servers get shut down cleanly after a fixed number of minutes (within the 20)? If not, check with your UPS vendor for software included with the hardware. Install the software and configure it with thresholds, you can probably fire off your custom scripts when UPS shuts down your server to make it as clean as possible. If you have a appserver and sqlserver, you could make the appserver go down first so that there are no connections to the sqlserver and then shutdown the sqlserver.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
I'm going to pile on with everyone else here. The key point is that SQL Server stores information to disk. If that information is only partially written and the server goes away, you get a corrupt database. The answer is that you need to ensure that the UPS keeps the server from failing when the power goes away and then issues a shutdown command to the server so that it can gracefully finish all the writes on the system prior to turning off. The UPS should come with software for this. If not, you'll have to get it installed. There is no other way around it.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.