Troubleshooting for "The transaction log for database 'database1' is full due to 'REPLICATION'"

Problem

I have a dev/test server B on which one database [database1] was restored from the same name database on production server A. [database1] on server A is the published database in a transnational replication. The recovery mode is simple for [database1] on both servers.

Recently I have received an alert from server B:
"Title: SQL Server Alert System: 'server B - Sev:017 - INNSUFIFIENT RESOURCES' occurred on \\server B
DESCRIPTION:   The transaction log for database 'database1' is full due to 'REPLICATION'."

Solution

First I checked the transaction log file size and available disk space, the log is 200GB, and the drive is almost full. The recovery mode of [database1] is simple, which means the transaction log should be truncated automatically.  However, some factors may delay the truction, they are explained in the online book:                           https://msdn.microsoft.com/en-CA/library/ms190925.aspx#FactorsThatDelayTruncation

Run this script (script1) to get the factor:
SELECT [name]
,[database_id]
,[log_reuse_wait_desc]
FROM [sys].[databases]
where name = 'database1'

The result is

The factor delayed the truncation is REPLICATION. But there is no replication setup on this server at all. I verified that by running this script:

SELECT [is_published]
,[is_subscribed]
,[is_cdc_enabled]
FROM [sys].[databases]
where name = 'database1'

Then I checked the transaction log file usage by running the command: "DBCC SQLPERF(logspace)". The result of "log Space Used(%)" is more than 99%, which means it can't be shrunk to a reasonable size.

I tried stored procedure sp_removedbreplication:  

exec sp_removedbreplication 'database1'

It did not remove the replication flag on database. Restoring from a published database may just bring partial information of the replication, enough for SQL Server not to truncate the tranction log, not enough for sp_removedbreplication to find and remove it.

So the solution is to add the database to publication, then remove it. Right click Replication in SSMS, then click Publisher Properties. Click Publication Databases in the Publisher Properties window, and check the Transactional button besides [database1], then OK.

Ran script1 again and found the value of [log_reuse_wait_desc] changed to "NOTHING", which means nothing will delay the truncation of transaction log. Ran "DBCC SQLPERF(logspace)" again, "log Space Used(%)" is 0.09. Then you can just shrink the transaction log file and solve the problem. The last step is to go back to Publisher Properties, and uncheck the Transactional button besides [database1].

Downgrade SQL Server 2012 from Standard Edition to Developer Edition

Problem

We have several dev/test servers with SQL Server 2012 Standard edition installed. Since Developer edition is free for development/test environment, so it make sense to downgrade to Developer edition. Unfortunately, Microsoft does not provided a direct method to do it. According to Microsoft online book: https://msdn.microsoft.com/en-us/library/ms143393(v=sql.110).aspx, it is easy to upgrade from cheaper edition to expensive edition, not the other way around.

Solution

We will have to uninstall SQL Server 2012 Standard edition, then install Developer edition. But before doing that, we can backup the system databases, then migrate them later into the new installation.

Steps

1. Preparation: check the instance properties and system database properties, take a screenshot of the root directory and database default locations.
   
2. Run  SQL Server 2012 Configuration Manager, take a screenshot of accounts used by SQL Server services, then stop the services.
3. Copy system databases (master, model, msdb) and ReportServer database to a new folder.
4. Uninstall SQL Server 2012 Standard edition.
5. Install SQL Server 2012 Developer edition, set the root directory and database default locations the same as the previous installation. Make sure the patch level is the same as before.
6. Run SQL Server 2012 Configuration Manager, stop SQL Server services.
7. Copy the system databases (master, model, msdb) and ReportServer database from the backup folder to the current installation folder.
8. Restart SQL Server services.

Send SMS Notification When You Receive an Important Office 365 Email

Problem

In order to monitor our databases servers, many email alerts have been setup in SQL Server to help us know what's happening on the server. It is really helpful for the monitoring and trouble shooting for the database servers. However, it causes the flood of email to my index. Some email alerts need my immediate attention, for example a production is in critical condition, need me to fix it right away. I may miss it just because I cannot read so many email and tell this one is much more important than the others.

Solution

There are several methods to setup special notifications for important office 365 emails:

Method 1. iPhone VIP email alerts.

The iOS email client allow users to add some contacts (for example your boss) as VIP senders, therefore receive VIP email notifications when an email from VIP sender enters the inbox. My cell phone is an Android phone, so this method does not work for me.

Method 2. Office 365 Email Text Message feature
Office 365 email support text message, which can be easily setup:

Limitations: it only support Bell and Telus in Canada. Since I am with Rogers, neither does this method work for me.

Method 3. Email to Text Feature (Cell Phone Carriers) and Office 365 Email Index Rules

Most cell phone carriers provide email to text feature, which can send you a text message once an email was sent to your phone's email inbox. I am with Rogers, my phone's email address is 10digitphonenumber@pcs.rogers.com. This is how I setup my SMS notifications:

1. Add 10digitphonenumber@pcs.rogers.com as a contact in Office 365
2. Create an inbox rule in Office 365 to forward important emails to 10digitphonenumber@pcs.rogers.com

You will get a SMS every time you have received an important email.

Troubleshooting for SQL Server 2014 RTM TempDB Error "Could not allocate space for object 'dbo.SORT temporary run storage"

Problem

Error Message:

Subject: SQL Server Alert System: 'ServerName - Sev:017 - INNSUFIFIENT RESOURCES' occurred on \\ServerName

DESCRIPTION:    Could not allocate space for object 'dbo.SORT temporary run storage:  150684339732480' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Troubleshooting

1. Check tempdb properties

The version of SQL Server on this server is 2014 RTM CU14. Since the application is not certified for SQL Server 2014 SP1 and above, so we are unable to install more patches. Autogrowth of tempdb is disabled. The error message tells me there is no space in tempdb any more.

The first step is to check the properties of tempdb from SSMS: 

It shows that a lot of space available, strange. If this is true, then we should not have gotten the error message.

2. Check DM view sys.dm_db_files_space_usage

DM view sys.dm_db_files_space_usage returns space usage information for each file in the database. Microsoft online book provides more details here. Run this query will get the space usage information from tempdb:

SELECT *

  FROM [tempdb].[sys].[dm_db_file_space_usage]

The results show that the tempdb has been filled up by internal objects. According to Microsoft online book, the following objects are included in the internal object page counters:

• Work tables for cursor or spool operations and temporary large object (LOB) storage
• Work files for operations such as a hash join
• Sort runs

The internal objects should be deallocated after being used. However, in this case, they are not deallocated, and keep growing until reach the space limit.

3. Reboot server during planned application outage

A reboot will recreate the tempdb, therefore cleanup the internal objects. 

Future Work

The next step will be monitoring the growth of internal objects, and to setup alert for tempdb space usage.