How to Disable CDC( Change Data Capture) for tables
Change Data Capture (cdc) property is disabled as default. here I will Query you how to Disable CDC for the already enabled table .
USE YourDataBase;
GO
EXECUTE sys.sp_cdc_disable_table
@source_schema = 'dbo',
@source_name = N'TableName',
@capture_instance = N'dbo_tableName';
Step-by-Step Process to Upgrade SQL 2008 to SQL 2016
There are generally two manual methods to upgrade to SQL 2016, which involve:
Performing side-by-side upgrade – It involves creating a full backup of existing SQL Server database and a clean installation of SQL Server on a new operating system
In-place upgrade of the earlier SQL database
Both of these methods have their own advantages and disadvantages. The side-by-side upgrade helps in eliminating the registry and system corruption that often sneaks into Windows installations. It offers a more stable installation of newer SQL Server. Also, you can avail the database on the original system while installing on a new computer.
But, side-by-side upgrade of SQL 2008 is safest and takes little time to upgrade the Server but eliminate long downtime if any unwanted issue occurred. In the side-by-side upgrade, you’ll have to backup and restore all the database. Many times, you’ll have to implement a new version of Windows Server, which often requires a new license for the operating system. Also, you’ll have to restore the customized settings and properties of SQL server that are changed during migration, and then reapply them to the new SQL server.
1. New server should build with latest OS
2. Document environment pre-requisite
3. Study deprecated features and discontinued features
3.Run upgrade advisor to know Breaking changes and Behavior Changes because some of the keywords\functionality or other things are deprecate in the upper versions you have to make sure your application should run after migrate to higher version
4.Identify SQL Server services - Database engine, SSIS, SSAS, SSRS, etc if any
5.Database collation upgrade considerations
6.Application connection requirement
7.Plan to manage other technology features - log shipping, clustering, database mirroring, replication, full-text search, DQS, etc, if any
8.Manage Linked Server availability if any
9.Plan database backups – Full backup, differential backup and transactional log backup
10.Manage required disk size
11.Manage separate service accounts
12.Check data consistency
13.Pre upgrade - performance metrics to compare after upgrade performance
14.Estimate downtime
15.Finalize upgrade approach
16.Upgrade validation criteria
17.Final acceptance criteria
18.Rollback plan and testing
19.Notification to all involved and impacted stakeholders
20.Post deployment steps
21.Ready with new/modified database maintenance plan
Based on your requirement you can add the steps..
Running this script will tell you which instance of a process is using lots of CPU on the database server. You can use the loginame or hostname columns to identify the person.
SELECT TOP 25
spid
,blocked
,convert(varchar(10),db_name(dbid)) as DBName
,cpu
,datediff(second,login_time, getdate()) as Secs
,convert(float, cpu / datediff(second,login_time, getdate())) as PScore
,convert(varchar(16), hostname) as Host
,convert(varchar(50), program_name) as Program
,convert(varchar(20), loginame) as Login
FROM master..sysprocesses
WHERE datediff(second,login_time, getdate()) > 0 and spid > 50
ORDER BY pscore desc
To rebuild a index, the below query will use full to estimate the approximate time(Not accurate but it will rough estimated time either +\- 30%).
use dbname
go
SELECT IndexName = name,
IndexSizeGB = CEILING(1.0 * dpages / 128 / 1024),
ETA_Min = CEILING(1.0 * dpages / 128 / 1024) * 1.5,
LogSpaceGBRequired = CEILING(1.0 * dpages / 128 / 1024) * 2,
DataSpaceGBRequired = CEILING(1.0 * dpages / 128 / 1024)
FROM sysindexes
WHERE NAME LIKE ('indexname')
--OR NAME LIKE ('indexname')--If you
want use multiple indexes use OR condition
Find the restore date and time.
SELECT @@servername as
Destination_server_Name,[rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[bs].[server_name] as Source_Server_Name,
[bs].[database_name]
as [source_database_name],
[bmf].[physical_device_name]
as [backup_file_used_for_restore],
[rs].[user_name] as User_Name
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily
bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC
How to take copy only backup for all backups
declare @OutputDir varchar(255)='<path>'--Give the path
DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @path = @OutputDir
--SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),104)
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','')
PRINT 'Starting Backups'
DECLARE db_cursor CURSOR FOR
SELECT name FROM MASTER.dbo.sysdatabases
--WHERE name like ('')--Change the db’s according to your requirements
--NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0 BEGIN
SET @fileName = @path + @name + '_'+'0000'+'_' + @fileDate + '.BAK'
PRINT 'Starting Backup For ' + @name
BACKUP DATABASE @name TO DISK = @fileName WITH FORMAT, copy_only, compression
print @name + 'backup complete'
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
PRINT 'Backups Finished'
Use below T-Sql command to find the database size in GBs,TB's
--Database Sizes
select
'server' = @@servername,d.NAME
, 'total size in megabytes'= convert(decimal(10,2),(sum(size * 8.00) / 1024.00 ))
, 'total size in gigabytes' = convert(decimal(10,2),(sum(size * 8.00) / 1024.00 / 1024.00))
, 'total size in terabytes' = convert(decimal(10,2),(sum(size * 8.00) / 1024.00 / 1024.00 / 1024.00)) from
sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4
and d.is_read_only<>1
GROUP BY d.NAME
ORDER BY d.NAME
