Solved

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

Posted on 2011-09-19
13
1,879 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

744 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

11 Experts available now in Live!

Get 1:1 Help Now