Conference Questions Pot-Pourri #10: Shrinking the database before taking a backup

Here’s a question I got from someone who attended our database maintenance workshop at PASS last week (paraphrased):

I attended your pre-conference session on database maintenance and found it to be very informative.  From what you told use though, I think I need to change my nightly backup procedure.  I like to get my databases back to as small of a size as possible before backing them up, so I run the following commands to do this before taking the full database backup: BACKUP LOG [mydbname] WITH NO_LOG and then DBCC SHRINKDATABASE (‘mydbname’).  Could you help me with a better way of doing this? We’re on SQL Server 2005.

And here’s the answer I sent back:

How large is the database? And how long must you keep the backups around? If the cumulative size of the backups takes up a large proportion of your available storage space (and we’re talking more than just a single direct-attached 100+GB drive), then it may be worth compressing the backups – otherwise you’re likely causing yourself more trouble than its worth.

By doing BACKUP LOG WITH NO_LOG you’re effectively throwing away log records and removing the possibility of doing any kind of point-in-time, or up-to-the-second recovery (see BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it). If you’re running in the FULL recovery model, and you don’t care about either of these features, then you should switch to the SIMPLE recovery model. If you really want to be in FULL, don’t ever use WITH NO_LOG.

The amount of transaction log that a full backup requires cannot be changed by you truncating the log. The full backup will backup any log it requires to enable the restored database to be a transactionally consistent copy of the database. See Debunking a couple of myths around full database backups and More on how much transaction log a full backup includes.

Doing a DBCC SHRINKDATABASE (the same exact operation as a database auto-shrink) will cause massive index fragmentation, and cause file-system fragmentation of the data files, as they will likely need to grow again after you’ve squeezed all the space out of them. See Auto-shrink – turn it OFF! for more details on the effects.

If you’re really concerned about backup sizes and space is at a premium, I recommend using a 3rd-party backup compression tool so you’re not affecting the actual database. Remember also that SQL Server 2008+ has native backup compression too – see my blog post here for more details.

Hope this helps.

Conference Questions Pot-Pourri #9: Q&A around compression features

Today's post is based on a bunch of questions I've had around the various compression features in SQL Server 2008.

Does turning on data compression or backup compression compress the transaction log files on disk?

No. The transaction log is not compressed in any way under any circumstances. Rows from tables and indexes that have compression enabled will be logged in their row compressed forms, even if page compression is enabled.

Does data compression compress LOB columns?

No. There is no native compression support for any LOB columns (n/text, image, n/varchar(max), varbinary(max), XML), whether stored in-row or out-of-row. There's also no native compression support for FILESTREAM data.

Does log shipping use compression to compress the logs being shipped?

Log shipping does not ship transaction logs – it ships log *backups*. If backup compression is enabled for the instance hosting the log shipping primary database, or the log shipping job is changed to enable backup compression, then the log backups will be compressed and less data will be sent over the wire to the log shipping secondary(s).

Is backup compression the same as log stream compression with database mirroring?

No. Backup compression compresses backups (see my previous blog post here). Log stream compression with database mirroring compresses transaction log records before sending them between the principal and the mirror (see my previous blog post here).

Should I just turn on backup compression at the instance level?

Not necessarily. It depends whether the majority of database on the instance will benefit from backup compression. Backup compression (and any compression algorithm) uses CPU whether a decent compression ratio is achieved or not. Check what compression ratio is achieved first and then enable backup compression if its worth it. Otherwise, just enable it for individual databases.

Does data compression use the same algorithm as backup compression?

No. Backup compression uses a proprietary block-based compression algorithm that is part of Windows. Data compression uses up to 3 algorithms, depending on the level of compression configured. Row compression just makes all non-LOB columns into variable-length columns. Page compression does row compression, then common-prefix compression for each table columns, then common-value dictionary compression for each page. Details can be found at the following BOL sections: Row Compression Implementation  and Page Compression Implementation.

Hope this helps!

Conference Questions Pot-Pourri #8: How to move constraint indexes?

It's been a long time since the last Conference Questions Pot-Pourri – in fact it was at the last SQL Connections in Orlando in April. Now we're in Las Vegas doing SQL Connections Fall – Kimberly's lecturing for an hour on partitioning so I can get out a quick post.

This is a question that came up yesterday – can CREATE INDEX … WITH DROP_EXISTING be used to move indexes that enforce constraints? Let's check it out.

First up I'm going to create a couple of tables. Table t1 has a unique constraint backed by a nonclustered index. Table t1 has a primary key constraint backed by a clustered index.

CREATE DATABASE ConstraintTest;
GO
USE ConstraintTest;
GO

CREATE TABLE UniqueConstraint (c1 INT UNIQUE NONCLUSTERED);
GO
INSERT INTO UniqueConstraint VALUES (1);
GO

CREATE TABLE PrimaryKeyConstraint (c2 INT PRIMARY KEY CLUSTERED);
GO
INSERT INTO PrimaryKeyConstraint VALUES (1);
GO

(Oops – in the original post I c&p'd the wrong code and had the second table as a unique nonclustered constraint too – sorry for the mixup) 

Now I'll add another filegroup that we'll try to move the indexes into.

ALTER DATABASE ConstraintTest ADD FILEGROUP ExtraFilegroup;
GO

ALTER DATABASE ConstraintTest ADD FILE (
   NAME = ExtraFile1,
   FILENAME = 'C:\SQLskills\ExtraFile1.ndf',
   SIZE = 5MB)
TO FILEGROUP ExtraFilegroup;
GO

Now I'll try moving the nonclustered index enforcing the unique constraint.

SELECT [name], [index_id] FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('UniqueConstraint')
GO

name                           index_id
—————————— ——–
NULL                           0
UQ__UniqueConstraint__7C8480AE 2

The index we want is index ID=2, so we should be able to move it as follows:

CREATE UNIQUE NONCLUSTERED INDEX UQ__UniqueConstraint__7C8480AE
ON UniqueConstraint (c1) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GO

No problem! Now let's try the same thing for the clustered primary key constraint.

SELECT [name], [index_id] FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('PrimaryKeyConstraint')
GO

name                           index_id
—————————— ——–
PK__PrimaryKeyConstr__7E6CC920 1

We only have one choice, so we should be able to rebuild it using:

CREATE CLUSTERED INDEX PK__PrimaryKeyConstr__7E6CC920
ON PrimaryKeyConstraint (c2) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GO

Msg 1907, Level 16, State 1, Line 1
Cannot recreate index 'PK__PrimaryKeyConstr__7E6CC920'. The new index definition does not match the constraint being enforced by the existing index.

Hmm – what am I missing? I'll check sys.indexes again and not filter the columns this time:

SELECT * FROM sys.indexes
WHERE [object_id] = OBJECT_ID ('PrimaryKeyConstraint')
GO

object_id  name                           index_id type type_desc is_unique
———- —————————— ——– —- ——— ———
2105058535 PK__PrimaryKeyConstr__7E6CC920 1        1    CLUSTERED 1

data_space_id ignore_dup_key is_primary_key is_unique_constraint fill_factor
————- ————– ————– ——————– ———–
1             0              1              0                    0

is_padded is_disabled is_hypothetical allow_row_locks allow_page_locks
——— ———– ————— ————— —————-
0
         0           0               1               1

Ah – I was missing the unique attribute on the index. What I need to do is the following:

CREATE UNIQUE CLUSTERED INDEX PK__PrimaryKeyConstr__7E6CC920
ON PrimaryKeyConstraint (c2) WITH (DROP_EXISTING = ON)
ON ExtraFilegroup;
GO

And that works fine. So – the answer is yes, you can use CREATE INDEX … WITH DROP_EXISTING to move indexes that enforce constraints. This is really good, as it allows you to move these indexes without having to do DROP INDEX then CREATE INDEX. That method is bad, as it creates a window of opportunity for someone to enter data that violates the constraint while the index is dropped.