July 2011 - Posts

Volume Freespace
25 July 11 10:08 PM | MartinBell | with no comments

In my last blog post I talked about a problem where the incorrect permissions on a mount point directory causes a spurious error message regarding space on the root drive. Whilst investigating the problem I needed to find out the space available on the mounted drive. Unlike normal directories the information isn’t immediately obtainable when you look at the properties of the mount point directory when you use windows explorer. With mount points you have to take a further step to view the properties of the mounted drive itself.

So instead of having to trudge through each mount property and then view the properties of the mounted drive, I thought I would use powershell instead. There are loads of posting/articles on how to do this, but this is the command I used:

Get-WmiObject Win32_Volume | select @{name="Mount Point";expression={$_.caption}},@{name="Free Space (GB)";e={$_.Freespace/(1024*1024*1024)}} | sort-object -property "Free Space (GB)" -descending | Format-Table –auto

There are many interesting properties for Win32_Volume, to list them use:

Get-WmiObject Win32_Volume | Get-Member -MemberType Properties

Filed under:
Mount point permission issues
06 July 11 09:56 PM | MartinBell | 2 comment(s)

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: ,

This Blog

SQL Blogs

Syndication