ZMEadmin
asked on
Sector size error while creating database in SQL Server 2008 on Windows 2008R2
We're facing an odd issue on a couple of our MS SQL Server machines. They are running SQL Server 2008 (not R2), and Windows 2008R2.
We're using a query to create a database, using H: for the data partition and E: for the transaction logs. When we run the query, we get the following error:
Msg 5177, Level 16, State 1, Line 2
An unexpected error occurred while checking the sector size for file 'E:\SQLData\MonitorDB_1.ld
We tried putting the transaction logs on G: instead (there are a number of partitions on this machine), and G: works fine. Only the E: partition is having the problem. I checked the filesystem permissions, and E: and G: are identical. Both the user who is running the queries and the local SYSTEM account have full NTFS privs to the entire partitions. They are both empty, freshly formatted partitions (no files), and both are the same size. They both use a 64k cluster size in NTFS. The user running the queries can successfully create and delete folders and files manually on the E: partition.
I'm fresh out of ideas on this one. My next step might be to delete and recreate the E: partition entirely, but I'd really like to know what is causing this.
We're using a query to create a database, using H: for the data partition and E: for the transaction logs. When we run the query, we get the following error:
Msg 5177, Level 16, State 1, Line 2
An unexpected error occurred while checking the sector size for file 'E:\SQLData\MonitorDB_1.ld
We tried putting the transaction logs on G: instead (there are a number of partitions on this machine), and G: works fine. Only the E: partition is having the problem. I checked the filesystem permissions, and E: and G: are identical. Both the user who is running the queries and the local SYSTEM account have full NTFS privs to the entire partitions. They are both empty, freshly formatted partitions (no files), and both are the same size. They both use a 64k cluster size in NTFS. The user running the queries can successfully create and delete folders and files manually on the E: partition.
I'm fresh out of ideas on this one. My next step might be to delete and recreate the E: partition entirely, but I'd really like to know what is causing this.
ASKER
I did indeed already see that MS article. The SQL Server services are running as the local SYSTEM account, and SYSTEM has full privs on the partitions in question.
Possible to restart service?
ASKER
Already tried restarting the service. Also tried restarting the entire server. Ran chkdsk on the partition to see if there were problems. None detected. Sorry to keep shooting down the suggestions. :) I really tried to debug it before posting here!
Strange indeed.
You could try changing the drive letters of the partitions but I can't imagine it would change anything. Surely it's a physical problem with the E partition but chkdsk should have found it.
You could try changing the drive letters of the partitions but I can't imagine it would change anything. Surely it's a physical problem with the E partition but chkdsk should have found it.
ASKER
I have just tried:
- Deleting and recreating the entire partition.
- Changing the drive letter.
No luck. I'm starting to think there is something wrong with this Windows or SQL install. The strange thing is that it's been running fine for months. It only today started having this problem. Ugh.
- Deleting and recreating the entire partition.
- Changing the drive letter.
No luck. I'm starting to think there is something wrong with this Windows or SQL install. The strange thing is that it's been running fine for months. It only today started having this problem. Ugh.
Yeah it's suspicious. Try running diagnostics on your server.
You please go through Kb Article issue No 6
http://support.microsoft.com/kb/982018
and
then Disk Partition Alignment Best Practices for SQL Server
http://msdn.microsoft.com/en-us/library/dd758814.aspx
http://support.microsoft.com/kb/982018
and
then Disk Partition Alignment Best Practices for SQL Server
http://msdn.microsoft.com/en-us/library/dd758814.aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I should clarify: This doesn't determine the root cause of the problem, but it gives me a way around it for now. I'll have to see if I can talk to HP and Microsoft about this one at some point.
GPT is a newer standard so probably better for your system. The article below came out before Server 2008.
I would be wary of your drives considering the problems that you have seen. Maybe upgrade your RAID drivers and run extensive disk tests for "peace of mind".
http://www.tomshardware.com/forum/233291-32-what-difference
I would be wary of your drives considering the problems that you have seen. Maybe upgrade your RAID drivers and run extensive disk tests for "peace of mind".
http://www.tomshardware.com/forum/233291-32-what-difference
MBR is the standard partitioning scheme that's been used on hard disks since the PC first came out. It supports 4 primary partitions per hard drive, and a maximum partition size of 2TB.
GPT disks are new, and are readable only by Windows Server 2003 SP1, Windows Vista (all versions), and Windows XP x64 Edition. The GPT disk itself can support a volume up to 2^64 blocks in length. (For 512-byte blocks, this is 9.44 ZB - zettabytes. 1 ZB is 1 billion terabytes). It can also support theoretically unlimited partitions.
GPT disks are new, and are readable only by Windows Server 2003 SP1, Windows Vista (all versions), and Windows XP x64 Edition. The GPT disk itself can support a volume up to 2^64 blocks in length. (For 512-byte blocks, this is 9.44 ZB - zettabytes. 1 ZB is 1 billion terabytes). It can also support theoretically unlimited partitions.
ASKER
I recently read a pretty good FAQ on Microsoft's site about GPT vs MBR, and specifically MS's implementation of GPT, and it does seem like the way to go on new equipment, especially since many of our newer machines are going well beyond 2TB partitions. I do agree with your concern about the raid problems on these machines, though. I did indeed upgrade the drivers, software, and firmware to the latest versions on my test machines, but the problem persists when using MBR.
I'll definitely keep pounding on these to make sure we have no further problems.
I'll definitely keep pounding on these to make sure we have no further problems.
ASKER
After trial and error, I determined on my own that using GPT is an acceptable solution to the problem, although I still don't understand the underlying *reason.*
http://support.microsoft.com/kb/239759
APPLIES TO
•Microsoft SQL Server 7.0 Standard Edition
•Microsoft SQL Server 2000 Standard Edition