Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We had a total power failure on our 2 node cluster using SQL Server 2008 (10.0.2531) Standard Edition. After recovery, everything is running fine. One issue I discovered is the Table Size and the Data Space Used has increased multiple times the original size (depending on the amount of records).

Examples: Before the power outages (at 01:45 am)

Table 1 had 29 records

Table size was 32 KB and

Data space used was 8 KB

After the power outages (complete recovery at 03:45 am)

Table 1 has 29 records

Table size is 48 KB

Data space used is 16 KB

Before the power outages (at 01:45 am)

Table 2 had 5,109,715 records

Table size was 2,683,533 KB

Data space used was 1,352,527 KB

After the power outages (complete recovery at 03:45 am)

Table 2 has 5,109,715 records

Table size is 9,170,072 KB

Data space used is 4,975,880KB

This is true across all tables… albeit at different percent increases.

I am at a loss for ideas of what happened or how to fix it. Any ideas?

share|improve this question

1 Answer 1

My was that the table size was expanded during the recover process. transactions get rolled back then replayed to ensure integrity. I would expect this to vary depending on the amount and size of transations. You can always reshrink the databases post power recovery. Personally I'd be more worried about having the entire cluster die for the want of a UPS on at least 1 node and drive array.

share|improve this answer
    
Thanks Jim, This was a self-inflicted wound (data space) in the perfect storm (power failure). The amount of free space per page was adjusted from 10% to 75%... the idea was to have 25% free space per page. This is the reason for the growth in the table size and data space. Not sure what I was thinking when I set it. The server finished coming up 1 minute before the re-indexing maintenance job started. I was managing other servers while the maintenance job ran so when I reviewed the servers, all of a sudden the database is 3 times larger. My thought was something was corrupt. –  Big Johnson Aug 10 '12 at 12:24
    
The power situation: we lost street power for 1/10th of a second (love the power company “trying something”) our generator kicked on but didn’t switch over because street came back so fast and froze the generator in deciding what to do, leaving the building dark. The UPS system worked great, 1 hour of time. Unfortunately, at 1:50 a.m. we were not able to get in, figure out the issue, and fix it in 1 hour. Good thing to note in disaster testing, how long it takes to get there and figure something out and take action. It took us 1 hour 5 minutes. –  Big Johnson Aug 10 '12 at 12:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.