Friday, May 15, 2015

One of the misconceptions that some people have about change data capture (CDC) in SQL Server is that it needs the database to be set up with full recovery mode. If we look at the following TechNet article, it mentioned:

It should be noted that change data capture works with all recovery models. But when change data capture is enabled, operations that would normally be minimally logged in simple or bulk-logged recovery models are fully logged to enable change data capture to capture all changes.

So Change Data Capture can be enabled on database with simple recovery mode. This can be helpful as typically in some development environment, the databases are being set with simple recovery mode. One thing to be careful is that if we have a database with simple recovery mode and have CDC enabled, but it doesn’t have the CDC capture job running, as the database process transactions, the database’s transaction log would use more space. This is because those transactions will not get release until the CDC capture process those transactions. Let me try to demonstrate this.

By default, when CDC is enabled, it would create 2 SQL Agent jobs, one is to capture the changes and another one is to clean up the CDC data older than 3 days (default, but it can be modify). For this testing, if you have SQL Agent running, you might want to stop and disabled the CDC capture job. You can do that using the following script:

The database's transaction log is now using about 116 MB of disk space, much higher than the 0.8 MB of disk space in the first test. If we look at the log_reuse_wait_desc column on sys.databases table for CdcTest database:

The reason that the database could not reuse the log space is because "replication", which actually make sense since under the hood CDC is using the same technology as replication. Basically, the database will not release the transactions in the transaction log until they are processed by the CDC capture job/process.

Now, let's try to process the CDC changes, do a checkpoint and check the database's transaction log space usage:

Additional thing to note is that, on the above example, we did those transactions on a table that is being tracked with CDC. This would not happen if the transactions are run against any table that is not being tracked by CDC on CDC enabled database. The database transaction log will reuse the log space after a checkpoint. The following script can help to illustrate this:

Clean Up

Take Away

Some take away points:

CDC can be enabled on database with any recovery models, including database with simple recovery mode.

Make sure that the CDC capture job is running, as transactions against CDC tracked table(s) will be preserved in the transaction log until they are processed by CDC capture job/process. This is especially true if you restore CDC enabled database with KEEP_CDC option, as the CDC capture and clean up jobs would not get created by default

If you have CDC tracked tables and you don't have the CDC capture job running, you can potentially see your database's transaction log grow even if it is in a simple recovery mode.

Tuesday, May 5, 2015

Below is a SQL script that can be used to list recent auto grow and/or auto shrink events that happened on your SQL Server. It queries the SQL Server default trace. By default, default trace is enabled (you can check this by running: sp_configure 'default trace enabled').

By the way, you can also get the information on recent auto grow and/or auto shrink events in SQL Server Management Studio, by doing the following:

Open SQL Server Management Studio (SSMS)

On the Object Explorer (you can press F8 to view Object Explorer), right click on the database that you want to get the auto grow and/or auto shrink events on

On the context menu, select Reports and then Standard Reports. Then select Disk Usage.

It would open a Disk Usage report for that particular database. If there is any recent auto grow and/or autoshrink event on that database, you should be able to see the “Data/Log Files Autogrow/Autoshrink Events” section.

Hopefully, you don’t have the auto shrink feature enabled on any of your database. Thus, you don’t have any auto shrink events to worry about.

As for auto grow events, if you notice a lot of them, you might want to consider pre-grow (increase the size of) the data/log file during period of low activity. Another thing that you might consider if you are noticing a lot of auto grow events in your database is that you can make the auto growth size to be bigger.