ERRORLOG Growth Issue

Steps to resolve continuous error message filling up SQL Log

This is a known Windows error that can happen when:

•  When a database is moved from one server to another or if the database is no longer owned by the same user or otherwise has a permission issue because of being “reattached” (DETACH/ATTACH) to SQL Server.  

• May also happen if the database is restored via a backup.  It is likely caused by an Orphaned User attached to the database.

Temporary Resolution

To temporarily solve this issue, delete the ERRORLOG's (they will be large if this is the issue).  This will free up space so the user can begin working again.

1. In Windows File Explorer, navigate to: C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.TCLI\MSSQL\Log
2. Delete the ERRORLOGs.  You may not be able to delete the first one since it's in use but you can delete the other ones.  Highlight them and delete them.  Shown below:
   

This allows your user to get back in right away, however, the issue of the ERRORLOG growth will continue until the fix below is implemented.

Resolution

You can either use a Command Prompt or Scripts in Microsoft SQL Server Management Studio (you don't need to do both).  Both resolutions are below:

Command Prompt

Run Command Prompt as Administrator.

C:\Windows\System32>sqlcmd -S .\tcli -U sa
password: tcli4333%
1>ALTER AUTHORIZATION ON DATABASE::TCLIdb TO [sa];
2>GO
1>exit

C:\Windows\System32>net stop MSSQL$TCLI
The SQL Server (TCLI) service is stopping....
The SQL Server (TCLI) service was stopped successfully.

C:\Windows\System32>net start MSSQL$TCLI
The SQL Server (TCLI) service is starting.
The SQL Server (TCLI) service was started successfully.

C:\Windows\System32>sqlcmd -S .\tcli -U sa
password: tcli4333%
1> ALTER DATABASE TCLIdb SET DISABLE_BROKER;
2> GO
1> ALTER DATABASE TCLIdb SET NEW_BROKER;
2> GO

1>exec sp_cycle_errorlog;

2>go 7

1>exit

Microsoft SQL Server Management Studio

Sample Error Message:

Resolution

Run the following SQL statements withing Microsoft SQL Server management Studio:

1. ALTER AUTHORIZATION ON DATABASE::TCLIdb TO [sa];

   1. Example:
      1. 
2. STOP AND START SQL SERVICE - OTHERWISE THE FOLLOWING STATEMENTS WILL JUST HANG.
   1.  Example:
      1. 
      2. Stop/Start the SQL Server service.  This can also be done by right clicking on the server within Management Studio and selecting STOP – waiting for the service to stop and then right clicking on the service again and selecting START.

3. ALTER DATABASE TCLIdb SET DISABLE_BROKER; 

   ALTER DATABASE TCLIdb SET NEW_BROKER;

   1. Example:
      1.