SQL Server Transaction Log

SQL Server Transaction Log

                                 

                                         SQL Server Transaction Log is main data log file contains in SQL Server Database. If any situation your database crash then SQL Server Transaction Log main recovery option. 

For More details How to recover Delete Data.

• How to check Tr Log Space?

DBCC SQLPERF(LOGSPACE);  GO 

• Is Tr Log Reuse?

SELECT log_reuse_wait_desc FROM sys.databases WHERE [name]='DATABASEUSERGROUP';GO

• Each Database Tr Log Usage detail

SELECT db.[name] AS DBName, db.recovery_model_desc, db.state_desc, db.containment_desc,
db.log_reuse_wait_desc,
CONVERT(decimal(18,2), ls.cntr_value/1024.0) AS LogSizeinMB, CONVERT(decimal(18,2), lu.cntr_value/1024.0) AS LogUsedinMB,
CAST(CAST(lu.cntr_value AS float) / CAST(ls.cntr_value AS float) AS decimal(18,2)) * 100 AS [LogUsed%],
db.[compatibility_level] , db.is_auto_shrink_on
FROM sys.databases AS db WITH (NOLOCK) INNER JOIN
sys.dm_os_performance_counters AS lu WITH (NOLOCK) ON db.name = lu.instance_name INNER JOIN
sys.dm_os_performance_counters AS ls WITH (NOLOCK) ON db.name = ls.instance_name LEFT OUTER JOIN
sys.dm_database_encryption_keys AS de WITH (NOLOCK) ON db.database_id = de.database_id
WHERE lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%';

• Get Number of VLF(virtual Log files)

DBCC LOGINFO

           using this link you can find each database VLF count.

--In SQL Server 2016 and LatestSELECT [name], COUNT(l.database_id) AS 'vlf_count' FROM sys.databases sCROSS APPLY sys.dm_db_log_info(s.database_id) lGROUP BY [name]

;WITH cte_vlf AS (
SELECT ROW_NUMBER() OVER(ORDER BY vlf_begin_offset) AS vlfid, DB_NAME(database_id) AS [Database Name], vlf_sequence_number, vlf_active, vlf_begin_offset, vlf_size_mb
    FROM sys.dm_db_log_info(DEFAULT)),
cte_vlf_cnt AS (SELECT [Database Name], COUNT(vlf_sequence_number) AS vlf_count,
    (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 0) AS vlf_count_inactive,
    (SELECT COUNT(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS vlf_count_active,
    (SELECT MIN(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_min_vlf_active,
    (SELECT MIN(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS min_vlf_active,
    (SELECT MAX(vlfid) FROM cte_vlf WHERE vlf_active = 1) AS ordinal_max_vlf_active,
    (SELECT MAX(vlf_sequence_number) FROM cte_vlf WHERE vlf_active = 1) AS max_vlf_active
    FROM cte_vlf
    GROUP BY [Database Name])
SELECT [Database Name], vlf_count, min_vlf_active, ordinal_min_vlf_active, max_vlf_active, ordinal_max_vlf_active,
((ordinal_min_vlf_active-1)*100.00/vlf_count) AS free_log_pct_before_active_log,
((ordinal_max_vlf_active-(ordinal_min_vlf_active-1))*100.00/vlf_count) AS active_log_pct,
((vlf_count-ordinal_max_vlf_active)*100.00/vlf_count) AS free_log_pct_after_active_log
FROM cte_vlf_cnt
GO 

If database is Recovery mode is FULL then your database Tr Log growth will depend on your database usage. if high usage database then Tr Log growth  will be high.

Then first you need to take full backup of the database as follows,

BACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP3.trn' WITH NOFORMAT, INIT,  NAME = N'DATABASEUSERGROUP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10GODECLARE @backupSetId AS intSELECT @backupSetId = position FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' and backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset WHERE [database_name]=N'DATABASEUSERGROUP' )IF @backupSetId is null BEGIN  RAISERROR(N'Verify failed. Backup information for database ''DATABASEUSERGROUP'' not found.', 16, 1) ENDRESTORE VERIFYONLY FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP3.trn' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND;GO

 or else you can schedule your full backup daily or weekly depend on your need.
next you need to log backup depend on your database usage (Ex: hourly log backups)

BACKUP LOG [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP3.trn' WITH NOFORMAT, NOINIT,  NAME = N'DATABASEUSERGROUP-Log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10GO

If you no need to keep log file then you can truncate Tr Log as follows,
(Not Recommended to used in Production Database if has backup Maintain Plan)

USE [DATABASEUSERGROUP]GODBCC SHRINKFILE (N'DATABASEUSERGROUP_log' , 0, TRUNCATEONLY)GO

Hope you will have good understand of this article.   
Cheers...
and Most welcome your comments and ideas...

PARTITION TABLE

PARTITION TABLE

                                                  

                                     You can create a partitioned table or index in SQL Server.The data in partitioned tables or indexes divided into two different data set for given table.Using following T-SQL commands will help you to  partition tables.

USE [master]

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILEGROUP [SALES];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILEGROUP [ARCHIVE_OLD];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILEGROUP [ARCHIVE_2002];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILEGROUP [ARCHIVE_2003];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILE ( NAME = N'SALES_01', 

  FILENAME = N'C:\MSSQL\2016\Sales_01.ndf' , 

  SIZE = 1024KB , 

  FILEGROWTH = 1024KB ) TO FILEGROUP [SALES];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILE ( NAME = N'ARCHIVE_OLD', 

  FILENAME = N'C:\MSSQL\2016\Archive_Old_01.ndf' , 

  SIZE = 1024KB , 

  FILEGROWTH = 1024KB ) TO FILEGROUP [ARCHIVE_OLD];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILE ( NAME = N'ARCHIVE_2002', 

  FILENAME = N'C:\MSSQL\2016\Archive_2002_01.ndf' , 

  SIZE = 1024KB , 

  FILEGROWTH = 1024KB ) TO FILEGROUP [ARCHIVE_2002];

GO

ALTER DATABASE [DATABASEUSERGROUP] ADD FILE ( NAME = N'ARCHIVE_2003', 

  FILENAME = N'C:\MSSQL\2016\Archive_2003_01.ndf' , 

  SIZE = 1024KB , 

  FILEGROWTH = 1024KB ) TO FILEGROUP [ARCHIVE_2003];

GO

USE [DATABASEUSERGROUP]

GO

IF NOT EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pf_year_archive') BEGIN

CREATE PARTITION FUNCTION [pf_year_archive](tinyint) AS RANGE LEFT FOR VALUES (0, 1, 2, 3)

END

GO

IF NOT EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'ps_year_archive') BEGIN

CREATE PARTITION SCHEME [ps_year_archive] AS PARTITION [pf_year_archive] 

TO ([SALES], [ARCHIVE_OLD], [ARCHIVE_2002], [ARCHIVE_2003], [SALES])

END

GO

ALTER TABLE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] ADD archive tinyint DEFAULT(0) NOT NULL;

--ALTER TABLE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] DROP COLUMN archive ;

GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[SalesOrderDetail]') AND name = N'CIX_SalesOrderDetail_ProductID') BEGIN

CREATE CLUSTERED INDEX [CIX_SalesOrderDetail_ProductID] ON [dbo].[SalesOrderDetail]([ProductID] ASC) ON [ps_year_archive]([archive]);

END

GO

SELECT * FROM [DATABASEUSERGROUP].[dbo].[SalesOrderDetail];

GO

SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.SalesOrderDetail');  

GO  

UPDATE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] SET archive=1 WHERE YEAR(ModifiedDate)<2002;

GO

SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.SalesOrderDetail');  

GO 

UPDATE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] SET archive=2 WHERE YEAR(ModifiedDate)=2002;

GO

SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.SalesOrderDetail');  

GO 

UPDATE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] SET archive=3 WHERE YEAR(ModifiedDate)=2003;

GO

SELECT * FROM sys.dm_db_partition_stats WHERE object_id = OBJECT_ID('dbo.SalesOrderDetail');  

GO 

UPDATE [DATABASEUSERGROUP].[dbo].[SalesOrderDetail] SET archive=4 WHERE SalesOrderID=61184;

GO

Cheers...
and Most welcome your comments and ideas... 

Character Map for Varchar & Nvarchar

Character Map for Varchar & Nvarchar

                                  This post I want to discuss about Character Map supported for SQL Server varchar & nvarchar data types. Everyone knows varchar is variable length, non-Unicode string data. And if we take nvarchar it's variable-length, UNICODE UCS-2 character set. And also ANSI_PADDING is always ON for nvarchar.

Following example will help you find both supported Character

;WITH tblNumbers AS

(

    SELECT 1 AS Number

    UNION ALL

    SELECT Number+1

        FROM tblNumbers

        WHERE Number+1<257

)

SELECT Number AS ASCII_Value,

CAST(CHAR(Number) AS varchar(10)) COLLATE SQL_Latin1_General_CP1_CI_AS AS ASCII_Char_General,

CAST(CHAR(Number) AS varchar(10)) COLLATE Finnish_Swedish_CI_AS AS ASCII_Char_Swedish, 

CAST(CHAR(Number) AS varchar(10)) COLLATE DANISH_NORWEGIAN_CI_AS AS ASCII_Char_DANISH,

CAST(CHAR(Number) AS nvarchar(10)) COLLATE SQL_Latin1_General_CP1_CI_AS AS ASCII_Char_General_nvarchar,

CAST(CHAR(Number) AS nvarchar(10)) COLLATE Finnish_Swedish_CI_AS AS ASCII_Char_Swedish_nvarchar, 

CAST(CHAR(Number) AS nvarchar(10)) COLLATE DANISH_NORWEGIAN_CI_AS AS ASCII_Char_DANISH_nvarchar

FROM tblNumbers

OPTION (MAXRECURSION 257)

Now let me explain which Character not supported varchar.

SELECT CAST(N'ɽ' as varchar(2)) [varchar],CAST(N'ɽ' AS nvarchar(2)) [nvarchar];
SELECT CAST(N'Ȏ ȣ ǧ ǒ ƾ ƛ A ® ¶ Ĥ Ʒ' as varchar(50)) [varchar],CAST(N'Ȏ ȣ ǧ ǒ ƾ ƛ A ® ¶ Ĥ Ʒ' AS nvarchar(50)) [nvarchar];

Hope now you have clear idea what is different between varchar and nvarchar.
Cheers...
and Most welcome your comments and ideas...  
 

SQL Server Data Compression Page vs Row Article 02

SQL Server Data Compression Page vs Row Article 02

                                                    First post I have discuss about DATA_COMPRESSION behavior. Today I have noticed some interesting information about  DATA_COMPRESSION. Normally we already know Unicode data types (nvarchar,nchar) allocate extra space for store data in SQL Server. But with DATA_COMPRESSION is enabled then this extra space NOT allocate. But this only for Page Compression. For example varchar column space allocation and nvarchar column space allocation both same when we specified  “DATA_COMPRESSION=ON”.

For English

use databaseusergroup
GO
--varchar
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblvarchar]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblvarchar];
END
GO
create table tblvarchar(id int IDENTITY(1,1),col1 varchar(100));
GO
INSERT INTO tblvarchar(col1) SELECT 'databas user group';
GO 10000
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblvarchar]( [id] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY];
GO
SELECT 'varchar - ROW'
GO
sp_spaceused 'tblvarchar';
GO
DROP INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [dbo].[tblvarchar] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblvarchar]( [id] ASC) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY];
GO
SELECT 'varchar - PAGE'
GO
sp_spaceused 'tblvarchar';
GO
--nvarchar
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblnvarchar]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblnvarchar];
END
GO
create table tblnvarchar(id int IDENTITY(1,1),col1 nvarchar(100));
GO
INSERT INTO tblnvarchar(col1) SELECT 'databas user group';
GO 10000
CREATE CLUSTERED INDEX [CIX_tblnvarchar_tblnvarchar_id_page] ON [tblnvarchar]([id] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY];
GO
SELECT 'Nvarchar - ROW'
GO
sp_spaceused 'tblnvarchar';
GO
DROP INDEX [CIX_tblnvarchar_tblnvarchar_id_page] ON [dbo].[tblnvarchar];
GO
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblnvarchar]([id] ASC) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY];
GO
SELECT 'Nvarchar - PAGE'
GO
sp_spaceused 'tblnvarchar';
GO

For Sinhala

use databaseusergroup
GO
--varchar
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblvarchar]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblvarchar];
END
GO
create table tblvarchar(id int IDENTITY(1,1),col1 varchar(100));
GO
INSERT INTO tblvarchar(col1) SELECT N'දත්ත සමුදා පරිශීලක කණ්ඩායම';
GO 10000
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblvarchar]( [id] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY];
GO
SELECT 'varchar - ROW'
GO
sp_spaceused 'tblvarchar';
GO
DROP INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [dbo].[tblvarchar] WITH ( ONLINE = OFF )
GO
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblvarchar]( [id] ASC) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY];
GO
SELECT 'varchar - PAGE'
GO
sp_spaceused 'tblvarchar';
GO
--nvarchar
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblnvarchar]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[tblnvarchar];
END
GO
create table tblnvarchar(id int IDENTITY(1,1),col1 nvarchar(100));
GO
INSERT INTO tblnvarchar(col1) SELECT N'දත්ත සමුදා පරිශීලක කණ්ඩායම';
GO 10000
CREATE CLUSTERED INDEX [CIX_tblnvarchar_tblnvarchar_id_page] ON [tblnvarchar]([id] ASC) WITH (DATA_COMPRESSION = ROW) ON [PRIMARY];
GO
SELECT 'Nvarchar - ROW'
GO
sp_spaceused 'tblnvarchar';
GO
DROP INDEX [CIX_tblnvarchar_tblnvarchar_id_page] ON [dbo].[tblnvarchar];
GO
CREATE CLUSTERED INDEX [CIX_tblvarchar_tblvarchar_id_page] ON [tblnvarchar]([id] ASC) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY];
GO
SELECT 'Nvarchar - PAGE'
GO
sp_spaceused 'tblnvarchar';
GO

with above example will give you clear idea about this.
Cheers...
and Most welcome your comments and ideas...