SQL Server compression backup and trace flag 3042

SQL Server 2008 onwards we have a new feature which allows us to generate compressed backup. This feature is a part of Enterprise edition in SQL 2008 and available in SQL 2008 R2 Standard and Enterprise edition.


We will see how compression backup will works, when you run backup database command using compression option SQL server engine will think how much space it is required for backup and occupies that much space on the filesystem, if the backup is not fit with in the allocated space then it will simply extended to file size. If the backup completes and not used the pre-allocated space then it simply shrinks the file in the disk. The pre-allocation reduces the IO overhead rather constantly growing the file size while backup is running.


Most of the times the pre-allocated space is larger than the actual backup size. For example if your disk is low and you initiate a large backup backup may fail with insufficient disk space. If you want to switch off the pre-allocated space by SQL Server then you can use 3042 trace flag, enabling this trace flag will tells the database engine not to pre-allocate file size and just grow the file while backup is running. But there will be a little performance hit when you enable the trace flag

Moving or Restore CDC enabled databases from one instance to another instance

If a database is CDC enabled you need to follow few more steps while migrating to other instance or on the same instance.

As a first step we will create a CDC enabled database and will setup change data capture for a table with the below script

create database TestDB  -- Creates a sample database

use testdb
create table test1 (id int, name varchar(20))

select name, is_cdc_enabled from sys.databases where name='testdb'  -- Verify whether CDC is enabled on the database or not

use TestDB
exec sys.sp_cdc_enable_db            -- This command enables CDC on the database
select name, is_cdc_enabled from sys.databases where name='testdb'  -- Verify CDC flag updated on the table or not

USE TestDB
exec  sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'test1', @role_name = NULL   -- Enable CDC at table level, after executing this command it creates two jobs in the SQL Server Agent (CDC Capture, CDC Cleanup)

To disable the CDC use the below commands

Use TestDB
go
exec sys.sp_cdc_disable_table @source_schema = N'dbo', @source_name   = N'test1', @capture_instance='dbo_test1'
GO


Script I  - Restore CDC enabled database on the same instance

restore database testdb from disk = 'C:\temp\testdb_full.bak' with replace

As the database exists on the same instance so it doesn't require any additional parameters required to restore the database (Just replace parameter is sufficient).

Script II - Restore CDC enabled database to a different instance (with same name or different name)

1. Take full backup of the database
2. Use the below script to restore on the target server

RESTORE DATABASE TestDB FROM 
DISK = N'F:\Testdb_full.bak' WITH  FILE = 1, 
MOVE N'TestDB' TO N'F:\Data1\TestDB_data.mdf', 
MOVE N'TestDB_log' TO N'F:\Log\TestDB_log.ldf', 
NOUNLOAD,  REPLACE,  STATS = 10, KEEP_CDC
GO

If you observe the above command it has a new parameter along with regular parameters called KEEP_CDC which helps to restore database with CDC values

3.We need to run the below commands to create the jobs on the target instance.

Use TestDB
exec sys.sp_cdc_add_job 'capture'
GO
exec sys.sp_cdc_add_job 'cleanup'
GO

 

Error when you select properties of database in SQL Server 2008 (Property Size is not available for Database)

When I am trying to setup mirroring for this database I got the below error message:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Property Size is not available for Database '[DatabaseName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.6000.34+((KJ_SP3).140819-1214+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Size&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I am sysadmin on that server, so the issue is not related to permission.
I checked the database using sp_help and owner is set and the login is exists on the server.

Resolution : Change the database owner to "SA" or some other login in your system and change back to original owner then it works.