Solved

Sector size error while creating database in SQL Server 2008 on Windows 2008R2

Posted on 2011-09-19
13
1,926 Views
Last Modified: 2012-09-15
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.ldf'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information.


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.
0
Comment
Question by:ZMEadmin
  • 7
  • 5
13 Comments
 
LVL 29

Expert Comment

by:Randy Downs
ID: 36563526
Maybe this will help. Older versions of SQL though.

http://support.microsoft.com/kb/239759

The Microsoft Windows NT account under which the MSSQLServer service starts does not have List Folder permissions on the root drive where the new database data or log files are created.

APPLIES TO
•Microsoft SQL Server 7.0 Standard Edition
•Microsoft SQL Server 2000 Standard Edition


0
 

Author Comment

by:ZMEadmin
ID: 36563540
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.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 36563608
Possible to restart service?
0
 

Author Comment

by:ZMEadmin
ID: 36563627
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!
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 36563683
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.
0
 

Author Comment

by:ZMEadmin
ID: 36564008
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Expert Comment

by:Randy Downs
ID: 36566952
Yeah it's suspicious. Try running diagnostics on your server.
0
 
LVL 5

Expert Comment

by:AlokJain0412
ID: 36571563
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 
0
 

Accepted Solution

by:
ZMEadmin earned 0 total points
ID: 36575121
Thanks for the links.  However, after troubleshooting all day yesterday, I believe the problem is somewhere between the raid controllers/software and the OS rather than an issue with SQL Server.  We are using HP servers with HP raid controllers (Smart Array P410i and P812), and I am now able to consistently reproduce the problem.  I've now also found other software and utilities having problems with the same partitions SQL Server has problems with.

In this case, I was running the fsutil command as follows:

fsutil fsinfo ntfsinfo e:

On "good" partitions, I would get information returned.  On the "bad" partitions, fsutil would be unable to find them. (!)  I'm assuming SQL Server is making a similar call using a similar API as fsutil to get its information, so it would make sense SQL Server would fail when trying to get the sector size if fsutil can't get it, either.

The problem appears to occur on the first logical drive I create on each HP raid controller.  If I first create a small 15 meg logical (the smallest I can make), then all subsequent logicals work fine.  So as long as I created a small dummy logical on each raid controller first, that fixed it.

A second solution (which I'm a bit more fond of) is initializing the new disks as GPT rather than MBR.  Doing that fixes the problem, even without the small dummy partition.  It appears that making them Dynamic disks also fixes it, but I haven't tested that thoroughly yet.
0
 

Author Comment

by:ZMEadmin
ID: 36575132
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.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 36575563
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

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.
0
 

Author Comment

by:ZMEadmin
ID: 36575617
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.
0
 

Author Closing Comment

by:ZMEadmin
ID: 38401359
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.*
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 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