Windows file server quota reporting incorrect usage

Occasionally, I have a user report that a network drive they are working on is reporting that it is full and will no longer let them save documents.  When looking at the drive they calculate that the space used is much lower than what the network drive is reporting.  I'm not sure what causes this, but seems to happen more often when large amounts of data are deleted and Windows has a quota on the drive.

In order to fix, you can initiate a quota scan using the following command on the server:

dirquota quota scan /path:c:\path

Note the path c:\path should be replaced with the actual path to the folder on the server, not the UNC path.  This command will return immediately, but it will start a background task tell windows to rescan the quotas defined on the path provided.  Once complete it will update the quota with the correct usage.  The time it takes will vary depending on the size of the drive and the number of files.

It appears that in Server 2019 this is now deprecated and you need to use the commands in the FileServerResourceManager Powershell module.  To initiate a scan you need to start using the following command:

update-fsrmquota -path "C:\path"

To get quota information you can use get-fsrmquota.  example:

get-fsrmquota -path "C:\path"

Cant open SQL Server Configuration manager

I have run into this error a couple times now.  I'm not sure what causes it.  When trying to open SQL Server Configuration manager you get the following message:

There is a support article that covers how to fix this.

https://support.microsoft.com/en-us/help/956013/error-message-when-you-open-sql-server-configuration-manager-in-sql-se

This article only covers 2005 to 2012, but I can confirm it works with 2017.  You just need to use the correct path.

Basically you run the following command.

mofcomp .\sqlmgmproviderxpsp2up.mof

At this point you should be able to open SQL Server Configuration Manger. Yay!

Installing a certificate for SQL Server

I am going to assume that you have a certificate already loaded into the certificate store.  If not that will be a blog post for another day.  If you are running SQL Server as a local system or the service account is in the administrators group, you do not need to worry about granting permissions to the keys.

1. Start by opening mmc.exe and add the certificate plugin for the local system.  
2. Once loaded, find the certificate that you want SQL Server to use.  Right-click on the certificate and select "All Tasks-> Manage Private Keys..." Now you want to give the SQL Server service account read access to the keys. 
3. If your hostname does not match the common name of the certificate you will need to perform an extra step later on. While you have the certificate screen open in mmc, right-click on the certificate and copy down the certificate hash.  You may need it later on.  
4. Next add the sql server configuration manager snapin to the mmc console.  Expand SQL Server Network Configuration.  Right click on protocols for MSSQLServer.  Click on the certificate tab and select the certificate you just loaded. 
   If you do not see your certificate you will need to assign it manually by modifying a registry key. To set the certificate you need to modify the following key:  HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.SQL2014\MSSQLServer\SuperSocketNetLib.  You will set the Certificate value to the certificate hash that you recorded in step 3 above.
8. Press OK.  Now click on SQL Server Services and restart the Sql Server service.  If everything works out the service should start up.  If it fails to start check the logs here: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log
9. Open up SQL Server Configuration Manager and expand SQL Server Network Configuration and right click on protocols for MSSQLServer.  Change the box from Force Encryption No to Yes.
10. Verify that connections are being made using ssl by running the following query:
    If the column encrypt_option is true, then the session is encrypted
11. If you don't want to force all users to use ssl, then the client can add the following to their connection string:  Encrypt=True;TrustServerCertificate=True;

Searching for a SPN

There are various reasons that you may need to search for an alias.  Maybe you want to find which service account an SPN is added to, or if any SPNs have been created. You can do this by using the setspn command and adding the -Q switch .  For example, if you want to find all SPNs with the string "davey" in them you would type:

setspn -Q */*davey*

Note that the format of the string needs to be in the format of a SPN.  In other words a forward slash is required.  The text before the slash is the Service Class and the text after is the host, port and service name. You can then use the "*" character as a wildcard in the rest of the string. 

As an example, if you want to search for all SQL Server SPNs with "davey" in them you could use the following:

setspn -Q MSSQLSvc/*davey*

If you are adding a new SPN remember to use the -S switch as that will search and verify the SPN doesn't already exist.