Planning to reformat SQL drives - comments please

[QCumber]

Hi there,

I'd like a sanity check on some work I'm planning for this weekend:

We have a 2 node active passive SQL 2005 cluster running on HP MSA1000 storage. For reasons I won't go into here, we believe the data and log drives are severely fragmented, and may also have some corrupt sectors.

My plan is as follows:

1) Install & configure an additional temporary disk volume (3 x 300Gb RAID5)

2) Shutdown the passive server node to prevent failover

3) Take SQL offline on the active node

4) Robocopy the entire contents of the Data and Log drives to the new temporary disk volume

5) Run full format (not quick) on Data and Log Drives

6) Robocopy the data back to the original locations

7) Restart SQL services

8) Check databases are OK

9) Restart passive node

10) Decommission temporary disk volume


So, is this a sensible plan? Comments greatly accepted.

Cheers,

Q.

 

[Crowley16]

why do you want to prevent failover? surely the idea would be to manually failover to your 2nd server, sort your 1st one out and then failover back to your 1st server...

also, if you think you have corrupt sectors, then it'd be best to change out the hdd, once the first bad sector exists, more tends to creep in quite quickly...

before doing this, you could try a defrag and chkdsk to confirm you have a dodgy hdd...

--------------------
Procrastinate Now!

 

[QCumber]

Hi Crowley16,

I don't want fail over because the suspected disk problem is on the shared storage, so I need to take both nodes offline to move the data off.

There are 6 physical disks making up the data array and none show any problems in Array diags or MSA diags, so unfortunately, it's not as simple as swapping a faulty disk.

Cheers,

Q.

 

[mrdenny]

Yeah, that plan looks like a good one to do what you are trying to do.

If the MSA isn't reporting any bad sectors on the disk then there aren't any.

When you are doing this I would recommend realigning the initial sector on the disk (

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1262122,00.html#monitoring).

This will improve performance and the disk has to be reformatted to do this.

What leads to you think there is a faulty disk in the array?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[QCumber]

Thanks for the tip MrDenny. I'm just about to take the services offline, so I'll use your sector alignment suggestion.

As to the cause; our Dev team have been using this server for the past few months and have filled the disks with all kinds of junk, and by filling I mean to 90% or more of the available capacity. I've now buit them their own dedicated dev server to mess about with and have shifted all their data off. But we're seeing occasional corruption in our transaction logs and our friendly neighbourhood SQL guru thinks it could be down to severe fragmentstion of the disk(s) or possible surface corruption. I figure the reformat will cure both issues.

Anyway, thanks again for the sector alignment info.

Cheers,

Q.

 

[mrdenny]

I've rarly seen a transaction log get corrupted. I've never seen a SQL file become corrupt due to disk fragmentation. Usually it's caused by a problem on the fibre network; bad cable, faulty g-bic, crimpted cable, bent cable, etc.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog