Maximum Request Length Exceeded - ASP.NET

When uploading or downloading a large amount of content, ASP.NET may need some request length adjustments in the web.config file. Example below would allow a request to continue for maximum of 3600 seconds (1 hour) and 1 GB of content.

<configuration>
  ...
  <system.web>
    <httpRuntime targetFramework="4.5" executionTimeout="3600" maxRequestLength="1048576" />
  </system.web>
  ...
  <system.webServer>
    <security>
      <requestFiltering>
        <requestLimits maxAllowedContentLength="1073741824" /> <!-- 1 GB in bytes -->
      </requestFiltering>
    </security>
  </system.webServer>
  ...
</configuration>

SQL Server Database and Log File Details

Quick way to retrieve a database and its log file size, etc...

/*-- Get all databases' mdf and ldf info, including logical names --*/
SELECT * FROM SYS.MASTER_FILES

/*-- Get mdf info (more detail) -- */
SELECT * FROM SYS.DATABASES

/*-- Get mdf info (less detail)  --*/
SELECT * FROM SYS.SYSDATABASES

/*-- Get database info -- */
EXEC sp_helpdb

/*-- Get database info (mdf and ldf), including logical names --*/
EXEC sp_helpdb @dbname = N'Northwind'

Get "exec sp_who2" into a table

Running sp_who2 will quickly display all connections to a SQL Server instance. Below is an enhancement to sp_who2 that allows database-specific connections.

DECLARE @DB_NAME varchar(1000)
SELECT @DB_NAME = 'Northwind_DB'

DECLARE @AllConnections TABLE(
    SPID INT,
    Status VARCHAR(MAX),
    LOGIN VARCHAR(MAX),
    HostName VARCHAR(MAX),
    BlkBy VARCHAR(MAX),
    DBName VARCHAR(MAX),
    Command VARCHAR(MAX),
    CPUTime INT,
    DiskIO INT,
    LastBatch VARCHAR(MAX),
    ProgramName VARCHAR(MAX), 
    SPID_1 INT,
    REQUESTID INT
)

INSERT INTO @AllConnections EXEC sp_who2
SELECT * FROM @AllConnections WHERE DBName = @DB_Name
ORDER BY HostName

ASP.NET MVC Scaffolding Template Files (T4)

The following is where you can find the T4 template files used by ASP.NET MVC Scaffolding:

Visual Studio 2015:
C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\Web\Mvc\Scaffolding\Templates\

Visual Studio 2017:
C:\Program Files\dotnet\sdk\NuGetFallbackFolder\microsoft.visualstudio.web.codegenerators.mvc\

Database Drop takes a long time when deleting backup history option is selected in SQL Server

Here's a workaround to speed up database drop. When there are a long history of backup in MSDB database, dropping a database with "Delete backup history" option could take a while.

Update statistics on MSDB tables and create an index for backupset table for [database_name] column. 

/* Update statistics in [msdb] database */
   
USE msdb; 
GO
UPDATE STATISTICS backupfile; 
GO
UPDATE STATISTICS backupmediafamily; 
GO
UPDATE STATISTICS backupmediaset; 
GO
UPDATE STATISTICS backupset; 
GO
UPDATE STATISTICS restorefile; 
GO
UPDATE STATISTICS restorefilegroup; 
GO
UPDATE STATISTICS restorehistory; 
GO


/* Create an index on [backupset] table in [msdb] database for [database_name] column */

Create index IX_backupset_database_name on backupset(database_name); 
GO

AD Query still returns old user name after user name was changed at domain controller

Symptoms:

Consider the following scenario:

1. On the domain member computer, an application calls the LsaLookupSids function to translate a security identifier (SID) to a user name.
2. The user name has been changed on a domain controller.
3. In this scenario, the LsaLookupSids function may return the old user name instead of the new user name. This behavior may prevent the application from working correctly.

Cause:

The local security authority (LSA) caches the mapping between the SID and the user name in a local cache on the domain member computer. The cached user name is not synchronized with domain controllers. The LSA on the domain member computer first queries the local SID cache. If an existing mapping is already in the local SID cache, the LSA returns the cached user name information instead of querying the domain controllers. This behavior is intended to improve performance.

The cache entries do time out, however chances are that recurring queries by applications keep the existing cache entry alive for the maximum lifetime of the cache entry.


Workaround:

To work around this issue, disable the local SID cache on the domain member computer as follows.

1. In RegEdit, locate HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
2. Create a new DWORD directly under "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa", name it LsaLookupCacheMaxSize and assign value 0.
3. Reboot may not be required.

Note The LsaLookupCacheMaxSize registry entry sets the maximum number of cached mappings that can be saved in the local SID cache. The default maximum number is 128. When the LsaLookupCacheMaxSize registry entry is set to 0, the local SID cache is disabled.


Resources:
https://support.microsoft.com/en-us/help/946358/the-lsalookupsids-function-may-return-the-old-user-name-instead-of-the
https://marclsitinfrablog.wordpress.com/2011/06/25/lsa-lookup-cache/

Steps to add VS solution to Visual Studio Team Services

Steps to add an existing Visual Studio solution to a repository on Visual Studio Team Services (or Team Foundation Server).

1. Install Git on your PC first.
2. Create a project with Git as version control on your Visual Studio Team Services website.
3. Get the URL of the new project after it is created,
   i.e., https://developer2201.visualstudio.com/WebApp1/_git/WebApp1
4. Assuming your Visual Studio solution folder is C:\Dev\VS2017_Eval\WebApp1...
5. Open command line console. Go to "C:\Dev\VS2017_Eval\WebApp1".
6. Run command: git init
7. Run command:
   git remote add origin https://developer2201.visualstudio.com/WebApp1/_git/WebApp1
8. add ".gitignore" file to the folder. You can download it from github at
   https://github.com/github/gitignore/blob/master/VisualStudio.gitignore. 
9. Run command: git add .gitignore
10. Run command: git commit -m ".gitignore file added"
11. Run command: git add *
12. Run command: git commit -m "Initial source codes"
13. Run command: git push -u origin --all

Step 13 can be done in Visual Studio by using Sync and Push in outgoing commit in Visual Studio, too.