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

    Pregunta

  • 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

    miércoles, 12 de septiembre de 2012 13:53

Respuestas

  • 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

    sábado, 15 de septiembre de 2012 17:05
    Usuario que responde
  • 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

    • Propuesto como respuesta wayvirgo viernes, 01 de febrero de 2013 21:17
    • Marcado como respuesta Papy NormandModerator miércoles, 20 de febrero de 2013 8:34
    viernes, 01 de febrero de 2013 21:17

Todas las respuestas

  • 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

    martes, 04 de septiembre de 2012 18:40
  • 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

    • Marcado como respuesta Shulei ChenModerator miércoles, 12 de septiembre de 2012 9:18
    • Desmarcado como respuesta CoastalData miércoles, 12 de septiembre de 2012 13:48
    miércoles, 05 de septiembre de 2012 8:42
    Moderador
  • 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.

    miércoles, 05 de septiembre de 2012 11:54
    Moderador
  • 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

    lunes, 10 de septiembre de 2012 19:30
  • 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.

    lunes, 10 de septiembre de 2012 19:31
  • 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?

    lunes, 10 de septiembre de 2012 20:24
  • 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

    miércoles, 12 de septiembre de 2012 14:02
  • 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

    sábado, 15 de septiembre de 2012 17:05
    Usuario que responde
  • 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!
    lunes, 01 de octubre de 2012 14:38
  • 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

    • Propuesto como respuesta wayvirgo viernes, 01 de febrero de 2013 21:17
    • Votado como útil wayvirgo viernes, 01 de febrero de 2013 21:17
    viernes, 01 de febrero de 2013 20:31
  • 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

    • Propuesto como respuesta wayvirgo viernes, 01 de febrero de 2013 21:17
    • Marcado como respuesta Papy NormandModerator miércoles, 20 de febrero de 2013 8:34
    viernes, 01 de febrero de 2013 21:17
  • just chipping in here as well,

    on a new installation of ws 2012 R2 , SQLServer express

    Cannot use file 'c:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\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.

    chkdsk shows that the allocation unit is 4096

    lunes, 24 de febrero de 2014 22:45
  • Getting this issue with the WID.

    Cannot use file 'C:\Windows\WID\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.

    Was trying to install WSUS on Sever 2012 R2 and this, amongst other things kept happening. Fixed the other problems (security rights) but now the WID will not start due to this error and because of this I cant use the add or remove roles or features. So I am rather stuck currently.

    Nothing else running on this server. Its been built to hold backups and run as WSUS.

    Allocation is showing as 4096.

    lunes, 02 de junio de 2014 9:28
  • Hi

    Just my two cents on the matter, I have been pulling my hair for the last day or so.

    "Windows Server 2012"

    So checking foundation for the error I used "fsutil fsinfo ntfsinfo" and to my surprise the file system reported:

    Bytes Per Sector  :               512
    Bytes Per Physical Sector :       4096

    This was bit confusing because server has been up for two years without any major problems.

    So with this in mind I checked the time for the first occurrence of event id 5178, this gave me a timeframen to work on, so firstly I checked installed updates... and there I found KB2845152 (which is a hotfix, for DNS ping problem on Direct Access servers ) installed at similar timeframe so I uninstalled the hotfix rebooted the server and checked "fsutil fsinfo ntfsinfo" which reported:

    Bytes Per Sector  :               512
    Bytes Per Physical Sector :       512

    And Windows Internal Database was up and running again.

    Regards Gudjon


    Regards Gudjon

    jueves, 12 de junio de 2014 14:06