LocalDB Cannot Start, Strange Error in Log contains "sector size 4096 and is now on a volume with sector size 3072"

Question

Hello,

I'm running Win 8, upgraded from Win 7, and I first noticed that I could not run LocalDB when I tried to create my first LightSwitch app in VS 2012. The error reported there is that it cannot start, but later I found in the error logs the following output:

2012-09-04 12:28:02.43 spid4s      Cannot use file 'C:\Users\UserName\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector size 3072. Move the file to a volume with a sector size that is the same as or smaller than the original sector size.
2012-09-04 12:28:02.43 spid4s      Error: 5178, Severity: 16, State: 1.

I've checked and verified that the sector size on this drive is indeed 4096, and some research seems to suggest that some Dell bios's may have to do with the incorrect reporting of the sector size to some applications.

Does anybody have any suggestions on what I can do to get this thing running?

Thanks in advance!

--Jon

Answer 1

Hello,

I'm running Win 8, upgraded from Win 7, and I first noticed that I could not run LocalDB when I tried to create my first LightSwitch app in VS 2012. The error reported there is that it cannot start, but later I found in the error logs the following output:

2012-09-04 12:28:02.43 spid4s      Cannot use file 'C:\Users\UserName\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector size 3072. Move the file to a volume with a sector size that is the same as or smaller than the original sector size.
2012-09-04 12:28:02.43 spid4s      Error: 5178, Severity: 16, State: 1.

I've checked and verified that the sector size on this drive is indeed 4096, and some research seems to suggest that some Dell bios's may have to do with the incorrect reporting of the sector size to some applications.

Does anybody have any suggestions on what I can do to get this thing running?

Thanks in advance!

--Jon

Answer 2

Hi Jon,

Thanks for the post.

Base on the error message, Microsoft confirmed that they had worked with Windows and with Dell to find the root cause of this error. The compellent array is reporting an incorrect value as their physical sector size. SQL cannot support storage with the sector size which they report, and guarantee data integrity. And Dell had agreed to ship an update to their firmware, which will correct the value which they report as physical sector size. When this firmware is loaded, SQL will be able to support this storage. For more details, please refer to this thread.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

 

Best Regards,
Ray Chen

Answer 3

Hello,

I remembered that i have discovered problems about the difference of sector size thanks to articles posted on the CSS SQL Server Enginers Blog :

http://blogs.msdn.com/b/psssql/archive/2011/09/27/after-applying-sql-server-2008-r2-sp1-error-9013-is-logged-the-tail-of-the-log-for-database-ls-is-being-rewritten-to-match-the-new-sector-size-of-d-bytes.aspx

http://blogs.msdn.com/b/psssql/archive/2011/01/13/sql-server-new-drives-use-4k-sector-size.aspx

I am wandering whether these articles are related to the problem asked by this thread.

Have a nice day

PS : I may have been going wrong with this post as i am far to be a specialist of this kind of questions , i am only curious and , moreover , i have always found very useful informations ( always presented in an understandatable way for not-specialists , a good point for the persons who are hiding behind the pssql name )

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 4

Hello,

I have a Dell XPS computer, now running Windows 8, and I cannot get MSSQL Express LocalDB to run, as it complains "sector size 4096 and is now on a volume with sector size 3072"; The research that I've done seems to indicate that this is due to the replacement C drive that I used some time back which is a 1 TB Advanced Format drive, and there is some incompatibility... It seems a little unclear, but some folks are suggesting that this is a Dell problem, and others are focusing on Windows 8.

I don't care which is at fault, though, I just need to get SQL up and running... Can anybody shed some more light on this, please?

Thanks in advance,

--Jon

Answer 5

The actual error message from the log file looks like this:

2012-09-04 12:28:02.43 spid4s      Cannot use file 'C:\Users\UserName\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances\v11.0\master.mdf' because it was originally formatted with sector size 4096 and is now on a volume with sector size 3072. Move the file to a volume with a sector size that is the same as or smaller than the original sector size.
2012-09-04 12:28:02.43 spid4s      Error: 5178, Severity: 16, State: 1.

Answer 6

Hi,

The workaround in this connect item may be of help: http://connect.microsoft.com/SQLServer/feedback/details/733652/service-inicialization-failled-by-corrupted-system-resource-in-windows-8-client

The connect item responded to by Microsoft: https://connect.microsoft.com/SQLServer/feedback/details/736454/sql-server-rtm-windows-8-consumer-preview-failed-to-install#tabs

---

Thanks, Andrew

Answer 7

I already saw that workaround, actually, but I get stuck on these steps:

6. Rename the old data folder (C:\Program Files\Microsoft SQL Server\MSSQL11.YourInstance\MSSQL\DATA\ to 
something like .\DATA_Original
7. Create a new empty .\DATA folder in the original location (this will become our mount point).
8. Mount a newly-created volume to the newly created folder, and copy the original .mdf, .ldf files into it.
9. Make sure NT SERVICE\MSSQL$YourInstance has permission on the files... remember, they don't natively 
inherit across mount points.
10. You'll need to use the same approach on your personal databases (that's why I created that other volume 
on my .vhd... I can mount it somewhere else.

That's a very abbreviated listing of steps, and does not address how do you tell sql server that the files have moved? Furthermore, other workarounds talk about deleting that data folder and using the sqllocaldb utility to recreate it, but that utility does not shed any light on how to move the database files.

Finally -- this is all pretty shabby. Is SQL server really so behind the times that it cannot be run on a brand new harddrive?

Answer 8

Hi Jon,

Thanks for the post.

Base on the error message, Microsoft confirmed that they had worked with Windows and with Dell to find the root cause of this error. The compellent array is reporting an incorrect value as their physical sector size. SQL cannot support storage with the sector size which they report, and guarantee data integrity. And Dell had agreed to ship an update to their firmware, which will correct the value which they report as physical sector size. When this firmware is loaded, SQL will be able to support this storage. For more details, please refer to this thread.

TechNet Subscriber Support

If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.

 

Best Regards,
Ray Chen

Hello,

Thanks for the reply, but I respectfully wouldn't want to suggest to anyone that this is an "answer" or a solution, as it is not.

I am indeed posting on the other forums, and in fact already have, but mostly all I get is finger pointing, and people referring me back to all the same articles that I've already read, with workarounds that are incomplete and somewhat inappropriate, such as creating virtual drives to store databases on.

A solution for this problem would probably have to include links to actual software updates and/or hotfixes that correct the incompatibilities directly, but it appears that such a fix does not yet exist, unless it's being hidden, but that doesn't make any sense. Probably more to the truth of the matter is that it must be a very small number of people affected, but I also suspect that there are many more who could be affected, but who simply never use development tools.

--Jon

Answer 9

Hello,

Thank you for your post.  I've researched this issue and from the details of the connect report, it does appear that a firmware update should be coming from Dell Computers to correct an issue where the sector size is being incorrectly reported.

In response to why the sector size is important to SQL Server, it concerns the overall architecture of how SQL Server works. 

A page of data in SQL Server is 8k in size.  If the sector size is 4096, a single page data will evenly span 2 sectors.  If the sector size is 3072, despite the software cannot support that sector size please take into consideration that now our pages do not evenly fit across the sectors and space is potentially wasted as it would now take 2.5 sectors for a single page.  The space not occupied in the last sector is unusable because the sector would be flagged as used even though not actually full.  Having an evenly sized sector size will help with data retrieval and efficient use of the disk.

For more information on what sector sizes are supported by SQL Server, please see knowledge base article http://support.microsoft.com/kb/926930.

I hope this information helps.

Sincerely,

Rob Beene, MSFT

Answer 10

That's all well and good to know, but more importantly, isn't there some way to move the master db over to another harddrive using SqlLocalDB command line utility? That would be a big help!

Answer 11

I am having a similar problem on a HP Proliant Server. The message in this case is:

Cannot use file 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\Template Data\master.mdf'
because it was originally formatted with sector size 4096 and is now on a volume with sector size 1048576.
Move the file to a volume with a sector size that is the same as or smaller than the original sector size.

The server is a HP ProLiant ML350 G5 running Windows Server 2012. FSUTIL reports 4096 as the sector size. (FSUTIL FSINFO NTFSINFO).

This is for a customer of mine. I want to recommend to the client that they purchase a multi-user license for SQL Server but they need to know this will work. I could ask them to add a second hard drive and use a "mount point" workaround but this will not handle user databases which are stored in other locations.

Is there any solution from SQL Server support?

Regards,

Jerry Metcoff

York Group BI LLC

Answer 12

Hey Jerry,

The only real solution to this is to install a new harddrive in the system that is to be used as the C drive, and then re-install the OS to that new drive. The new drive probably has to be less than 1TB, but certainly cannot be the type that uses the new "advanced format" technology.

As a "solution" this is terrible and mostly "unacceptable", but as I understand it the problem lies in SQL server itself, which writes data in 4096 bit blocks in order to reduce reads and writes to the physical media which must use that value as the sector size.

Therefore, the FAULT lies in these new advanced format drives, which I guess you could say, are simply incompatible with SQL server.

Hope this helps.

--Jon