Problem
When restoring a database that uses Change Data Capture (CDC), restoring a backup works differently depending on where the database is restored. In this tip we take a look at different scenarios when restoring a database when CDC is enabled.
Solution
For solution, please check my new article on MSSQLTips.com
http://mssqltips.com/tip.asp?tip=2421
Monday, June 27, 2011
Tuesday, June 14, 2011
How to Move Resource Database?
Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
Resource database will be very useful in doing upgrades or in un-installing up-grades. In the previous versions of SQL Server up-grade needs creating/dropping of the system objects. From the SQL Server 2005 version upgrade is just procedure to copying resource database file to local server.
Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf
Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.
You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.
[sourcecode language="sql"]
SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO
SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO [/sourcecode]
To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.
You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
Named Instance
Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.
[sourcecode language="sql"]
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')[/sourcecode]
Resource database will be very useful in doing upgrades or in un-installing up-grades. In the previous versions of SQL Server up-grade needs creating/dropping of the system objects. From the SQL Server 2005 version upgrade is just procedure to copying resource database file to local server.
Name of Resource database data and log file.
mssqlsystemresource.mdf
mssqlsystemresource.ldf
Resource database data and log file location is same as the Master database location. In case if you are moving Master database you have to move the Resource database as well to the same location.
You can check the Resource database version and last up-grade time using the SERVERPROPERTY function.
[sourcecode language="sql"]
SELECT SERVERPROPERTY(‘RESOURCEVERSION’);
GO
SELECT SERVERPROPERTY(‘RESOURCELASTUPDATEDATETIME’);
GO [/sourcecode]
To move the resource database, you have to start the SQL Server service using either -m (single user mode) or -f (minimal configuration) and using -T3608 trace flag which will skip the recovery of all the databases other than the master database.
You can do it either from the Configuration manager or from the command prompt using below command.
Default Instance
NET START MSSQLSERVER /f /T3608Named Instance
NET START MSSQL$instancename /f /T3608Execute the below ALTER command once you have started the SQL Service by specifying the new location, location should be same as Master database location.
[sourcecode language="sql"]
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=data, FILENAME= '\mssqlsystemresource.mdf')
ALTER DATABASE mssqlsystemresource MODIFY FILE (NAME=log, FILENAME= '\mssqlsystemresource.ldf')[/sourcecode]
Wednesday, June 8, 2011
How to kill all sessions that have open connection in a SQL Server Database?
As SQL Server DBAs, many times we need to KILL all Open Sessions against the SQL Server Database to proceed with Maintenance Task, Restore and more...
You can use below different techniques to KILL all open sessions against the database.
Technique - I
Here we will query the SysProcesses table to get the session running against the user database and prepare the dynamic SQL statement to KILL all the connection.
[sourcecode language="sql"]
DECLARE @DbName nvarchar(50)
SET @DbName = N'Write a DB Name here'
DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''
SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND SPId @@SPId
EXEC(@EXECSQL)
[/sourcecode]
Technique - II
Take the database into Single User Mode and execute all the task needs to perform against the databse.
[sourcecode language="sql"]ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE [/sourcecode]
Once you are finish with all the required task make the database accessible to everyone.
[sourcecode language="sql"]ALTER DATABASE [Database Name] SET MULTI_USER[/sourcecode]
Technique - III
In case of restore the database by replacing existing database, you can take the database OFFLINE and restore it. Restore will bring the database online.
[sourcecode language="sql"]
ALTER DATABASE [Database Name] SET OFFLINE WITH ROLLBACK IMMEDIATE [/sourcecode]
[sourcecode language="sql"]
ALTER DATABASE [Database Name] SET ONLINE
[/sourcecode]
Technique - IV
Go to Activity Monitor, Select the desired database and right click on the database to KILL the process.
You can use below different techniques to KILL all open sessions against the database.
Technique - I
Here we will query the SysProcesses table to get the session running against the user database and prepare the dynamic SQL statement to KILL all the connection.
[sourcecode language="sql"]
DECLARE @DbName nvarchar(50)
SET @DbName = N'Write a DB Name here'
DECLARE @EXECSQL varchar(max)
SET @EXECSQL = ''
SELECT @EXECSQL = @EXECSQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DbName) AND SPId @@SPId
EXEC(@EXECSQL)
[/sourcecode]
Technique - II
Take the database into Single User Mode and execute all the task needs to perform against the databse.
[sourcecode language="sql"]ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE [/sourcecode]
Once you are finish with all the required task make the database accessible to everyone.
[sourcecode language="sql"]ALTER DATABASE [Database Name] SET MULTI_USER[/sourcecode]
Technique - III
In case of restore the database by replacing existing database, you can take the database OFFLINE and restore it. Restore will bring the database online.
[sourcecode language="sql"]
ALTER DATABASE [Database Name] SET OFFLINE WITH ROLLBACK IMMEDIATE [/sourcecode]
[sourcecode language="sql"]
ALTER DATABASE [Database Name] SET ONLINE
[/sourcecode]
Technique - IV
Go to Activity Monitor, Select the desired database and right click on the database to KILL the process.
Wednesday, June 1, 2011
Change Data Capture in SQL Server 2008
Change Data Capture (CDC) is a new feature in SQL Server 2008 which records insert, update and delete activity in SQL Server tables.
CDC is intended to capture insert, update and delete activity on a SQL table and place the information into a separate relational table. It uses an asynchronous capture mechanism that reads the transaction logs and populates the CDC table with the row's data which change. The CDC table mirrors the column structure of the tracked table, together with metadata regarding the change.
To use the CDC feature, first we have to enable it database level. You can use below query to retrieve the CDC enabled databases.
[sourcecode language="sql"]
USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases where is_cdc_enabled <> 0
GO[/sourcecode]
You can use below script to create the sample database and table
[sourcecode language="sql"]
use sqldbpool
create table Employee
(
empID int constraint PK_Employee primary key Identity(1,1)
,empName varchar(20)
,salary int
)
insert into Employee values('Jugal','50000000'),('Abhinav',1000),('Sunil',2000)[/sourcecode]
To enable CDC on database SQLDBPool execute the below query.
[sourcecode language="sql"]
USE SQLDBPool
GO
EXEC sys.sp_cdc_enable_db[/sourcecode]
Once you have enabled the CDC for the database, you can see the CDC schema, CDC User and CDC tables in the database. Please see the below images for more information.
cdc.captured_columns – Returns list of captured column
cdc.change_tables – Returns list of all the CDC enabled tables
cdc.ddl_history – Records history of all the DDL changes since capture data enabled
cdc.index_columns – Contains indexes associated with change table
cdc.lsn_time_mapping – Maps LSN number and time
As CDC feature can be applied at the table-level to any CDC enabled database. You can run below query to enable the CDC on the table.
Please note:
- You must have database owner permission (db_Owner fixed role)
- SQL Agent Service must be running
Using sys.sp_cdc_enable_table procedure we can enable the CDC at the table level. You can specify all the below different options as required.
@source_schema is the schema name of the table that you want to enable for CDC
@source_name is the table name that you want to enable for CDC
@role_name is a database role which will be used to determine whether a user can access the CDC data; the role will be created if it doesn't exist.
@supports_net_changes determines whether you can summarize multiple changes into a single change record; set to 1 to allow, 0 otherwise.
@capture_instance is a name that you assign to this particular CDC instance; you can have up two instances for a given table.
@index_name is the name of a unique index to use to identify rows in the source table; you can specify NULL if the source table has a primary key.
@captured_column_list is a comma-separated list of column names that you want to enable for CDC; you can specify NULL to enable all columns.
@filegroup_name allows you to specify the FILEGROUP to be used to store the CDC change tables.
@partition_switch allows you to specify whether the ALTER TABLE SWITCH PARTITION command is allowed
[sourcecode language="sql"]
USE SQLDBPool
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@role_name = NULL
GO[/sourcecode]
cdc.SQLDBPool_capture – Capture the changes by doing log scan
cdc. SQLDBPool _cleanup –Clean Up the database changes tables.
Once the above query executes successfully, it will create 1 more system table cdc.dbo.Employee_CT for the tracking purpose.
See the result of the SELECT query on both the tables.
Below 5 additional columns are available into cdc.dbo.Employee_CT table.
__$operation and __$update_mask are very important columns. __$operation table contains the value against the DML operations.
1 = Delete Statement
2 = Insert Statement
3 = Value before Update Statement
4 = Value after Update Statement
__$update_mask A bit mask with a bit corresponding to each captured column identified for the capture instance. This value has all defined bits set to 1 when __$operation = 1 or 2. When __$operation = 3 or 4, only those bits corresponding to columns that changed are set to 1.
[sourcecode language="sql"]
insert into Employee values('DJ','10000')
delete Employee where empName = 'DJ'
update Employee set salary = 10 where Empname = 'Sunil'[/sourcecode]
[sourcecode language="sql"]
select * from Employee
select * from cdc.dbo_Employee_CT[/sourcecode]
You can get more information on the CDC configuration by executing sys.sp_cdc_help_change_data_capture stored procedure.
You can disable the CDC either on the table level or the database level. Use below code to disable the CDC on table or database level.
Table Level
[sourcecode language="sql"]
exec sys.sp_cdc_disable_table
@source_schema = 'dbo',
@source_name = 'Employee',
@capture_instance = 'dbo_Employee' [/sourcecode]
Database Level
[sourcecode language="sql"]
use SQLDBPool;
go
sys.sp_cdc_disable_db[/sourcecode]
CleanUp Job
As we checked in the above example that CDC is capturing all the changes at the table level which create the disk space issue. To resolve disk space issue we have clean up job which run every 3 days interval by default. We can schedule it to run as per our requirement.
CDC is intended to capture insert, update and delete activity on a SQL table and place the information into a separate relational table. It uses an asynchronous capture mechanism that reads the transaction logs and populates the CDC table with the row's data which change. The CDC table mirrors the column structure of the tracked table, together with metadata regarding the change.
To use the CDC feature, first we have to enable it database level. You can use below query to retrieve the CDC enabled databases.
Steps to Enable the CDC on database level
[sourcecode language="sql"]
USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases where is_cdc_enabled <> 0
GO[/sourcecode]
You can use below script to create the sample database and table
create database SQLDBPool
goSample DB and Table Creation Script
[sourcecode language="sql"]
use sqldbpool
create table Employee
(
empID int constraint PK_Employee primary key Identity(1,1)
,empName varchar(20)
,salary int
)
insert into Employee values('Jugal','50000000'),('Abhinav',1000),('Sunil',2000)[/sourcecode]
To enable CDC on database SQLDBPool execute the below query.
[sourcecode language="sql"]
USE SQLDBPool
GO
EXEC sys.sp_cdc_enable_db[/sourcecode]
Once you have enabled the CDC for the database, you can see the CDC schema, CDC User and CDC tables in the database. Please see the below images for more information.
CDC Schema, CDC User and CDC system tables
cdc.captured_columns – Returns list of captured column
cdc.change_tables – Returns list of all the CDC enabled tables
cdc.ddl_history – Records history of all the DDL changes since capture data enabled
cdc.index_columns – Contains indexes associated with change table
cdc.lsn_time_mapping – Maps LSN number and time
Enable CDC on Table
As CDC feature can be applied at the table-level to any CDC enabled database. You can run below query to enable the CDC on the table.
Please note:
- You must have database owner permission (db_Owner fixed role)
- SQL Agent Service must be running
Using sys.sp_cdc_enable_table procedure we can enable the CDC at the table level. You can specify all the below different options as required.
@source_schema is the schema name of the table that you want to enable for CDC
@source_name is the table name that you want to enable for CDC
@role_name is a database role which will be used to determine whether a user can access the CDC data; the role will be created if it doesn't exist.
@supports_net_changes determines whether you can summarize multiple changes into a single change record; set to 1 to allow, 0 otherwise.
@capture_instance is a name that you assign to this particular CDC instance; you can have up two instances for a given table.
@index_name is the name of a unique index to use to identify rows in the source table; you can specify NULL if the source table has a primary key.
@captured_column_list is a comma-separated list of column names that you want to enable for CDC; you can specify NULL to enable all columns.
@filegroup_name allows you to specify the FILEGROUP to be used to store the CDC change tables.
@partition_switch allows you to specify whether the ALTER TABLE SWITCH PARTITION command is allowed
[sourcecode language="sql"]
USE SQLDBPool
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@role_name = NULL
GO[/sourcecode]
cdc.SQLDBPool_capture – Capture the changes by doing log scan
cdc. SQLDBPool _cleanup –Clean Up the database changes tables.
Once the above query executes successfully, it will create 1 more system table cdc.dbo.Employee_CT for the tracking purpose.
See the result of the SELECT query on both the tables.
Below 5 additional columns are available into cdc.dbo.Employee_CT table.
__$operation and __$update_mask are very important columns. __$operation table contains the value against the DML operations.
1 = Delete Statement
2 = Insert Statement
3 = Value before Update Statement
4 = Value after Update Statement
__$update_mask A bit mask with a bit corresponding to each captured column identified for the capture instance. This value has all defined bits set to 1 when __$operation = 1 or 2. When __$operation = 3 or 4, only those bits corresponding to columns that changed are set to 1.
Example
Execute the below query on the SQLDBPool database.[sourcecode language="sql"]
insert into Employee values('DJ','10000')
delete Employee where empName = 'DJ'
update Employee set salary = 10 where Empname = 'Sunil'[/sourcecode]
[sourcecode language="sql"]
select * from Employee
select * from cdc.dbo_Employee_CT[/sourcecode]
You can get more information on the CDC configuration by executing sys.sp_cdc_help_change_data_capture stored procedure.
You can disable the CDC either on the table level or the database level. Use below code to disable the CDC on table or database level.
Table Level
[sourcecode language="sql"]
exec sys.sp_cdc_disable_table
@source_schema = 'dbo',
@source_name = 'Employee',
@capture_instance = 'dbo_Employee' [/sourcecode]
Database Level
[sourcecode language="sql"]
use SQLDBPool;
go
sys.sp_cdc_disable_db[/sourcecode]
CleanUp Job
As we checked in the above example that CDC is capturing all the changes at the table level which create the disk space issue. To resolve disk space issue we have clean up job which run every 3 days interval by default. We can schedule it to run as per our requirement.
Subscribe to:
Posts (Atom)