Mount point permission issues

Published 06 July 11 09:56 PM | MartinBell

I thought I’d post about something about a problem I encountered the other day, because the error message was misleading and the solution was not very obvious.

When restoring a database back I started getting the error messages like:

Msg 3257, Level 16, State 1, Line 1
There is insufficient free space on disk volume 'C:\' to create the database. The database requires 423457128448 additional free bytes, while only 113891102720 bytes are available.
Msg 3119, Level 16, State 4, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

This message didn’t make a great deal of sense as the volume I was restoring the database onto had plenty of space available, and I wasn’t actually writing anything directly on the C:\ drive. What made the problem more perplexing was that I have restored this database from the backup loads of times previously. As the message was reporting a lack of space I tried using trace flag 3104 which disables the check for space, but this had no effect. I therefore incorrectly concluded that trace flag 3104 was no longer working, so asked fellow MVPs if they had come across this problem.
     
Simon Sabin suggested that the issue may be something to do with permissions of the service account, but how that related to my problem wasn’t clear, so I thought I would check what was different between the current database and the one I was restoring. Using RESTORE FILELISTONLY I could get a list of the file and the sizes of each of these files. Comparing it with the current files revealed only a couple had changed. The most significant difference was in one of the log files, and therefore I compared the mounted volume with the other mounted volumes and found that for the directory where the log file volume was mounted, the service account did not have explicit permissions granted to it, whilst all the other directories for the mounted volumes did. e.g.

If the path that the volume was mounted is C:\data\db\ldffiles then:

cacl C:\data\db\ldffiles

returned

CREATOR OWNER: (OI) (CI) (IO) F
NT AUTHORITY\SYSTEM: (OI) (CI) F
BUILTIN\Administrators: (OI) (CI) F

which was missing

WINDOM\db_sql_svc:(OI)(CI)C

where the domain account WINDOM\db_sql_svc is the SQL Server Service Account

By adding change permission to the service account the problem no longer manifested itself.

This left me wondering why it had not manifested itself before?

I wrote myself a test script that would create log files of a given size, back up the database, drop the second log file and re-create it with a smaller size, it then tried to restore the database. Using this script and altering the initial file sizes and the replaced file size, I tried to find a threshold which caused the problem. Unfortunately I didn't obtain consistent results so I can not give a specific size to look out for. The tests do show that there is some value somewhere between 90 and 100
GB when the problem usually starts to manifest itself. The following is a list of passes for given file sizes against the size of file expansion (values in GB):

Original Size Min Expansion Max Expansion
100 93 99
155 90 99
159 94 99
200 93 100
250 93 100

There does not seem to be an obvious connection to the amount of free space on the volume.

It is possible for the problem to be present and then suddenly manifest itself, as in my case. In these circumstances you will usually look for something that has changed for the source of the problem, but in this case nothing obvious has changed so you can waste a significant amount of time trying to pin down a non-existent difference. It is possible that this problem has been an issue since the original configuration, so if you are currently using mount points you just may want to check the permissions on the mount point directory.

As I could not find feedback on connect that mentions this I added the following bug.

Filed under: ,

Comments

# Wednesday Weekly #sqlserver Links for 2011-28 | sqlmashup said on July 13, 2011 02:50 PM:

Pingback from  Wednesday Weekly #sqlserver Links for 2011-28 | sqlmashup

# Martin Bell UK SQL Server MVP said on July 27, 2011 09:14 PM:

In my last blog post I talked about a problem where the incorrect permissions on a mount point directory

This Blog

SQL Blogs

Syndication