Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-19
13
Medium Priority
?
2,375 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 30

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 30

Expert Comment

by:Randy Downs
ID: 36563608
Possible to restart service?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 30

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
 
LVL 30

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 30

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

609 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