SSISDB is full

 database 'SSISDB' is full due to 'ACTIVE_TRANSACTION'

                                        You will noticed occasionally  SSIS DB is full in your database server. or you may need to clean up SSIS DB in your SQL Server integration services.  

This is small example of SSIS DB. and sometime you will noticed this error when fail

"SSIS Server Maintenance Job".

Message

Executed as user: ##MS_SSISServerCleanupJobLogin##. The transaction log for database 'SSISDB' is full due to 'ACTIVE_TRANSACTION'. [SQLSTATE 42000] (Error 9002).  NOTE: The step was retried the requested number of times (3) without succeeding.  The step failed.

FAQ,

How to clean up the SSISDB database using the script? 

How to Delete data in SSISDB database?

SSIS Server Maintenance Job Fail. How to fixed?

Solution  

1) Take full backup of SSIS database.

2) next clean some log and session tables as follows,

USE SSISDB

GO

--FK table 

WHILE 1 = 1

BEGIN

   DELETE TOP(1000) FROM [internal].[executions];

   IF @@ROWCOUNT < 1000 BREAK;

END 

GO

TRUNCATE TABLE [internal].[executable_statistics];

GO

TRUNCATE TABLE [internal].[execution_component_phases]; 

GO

TRUNCATE TABLE [internal].[execution_data_statistics]; 

GO

TRUNCATE TABLE [internal].[execution_data_taps]; 

GO

TRUNCATE TABLE [internal].[execution_parameter_values];

GO

TRUNCATE TABLE [internal].[execution_property_override_values];

GO

TRUNCATE TABLE [internal].[extended_operation_info];

GO

--FK table 

WHILE 1 = 1

BEGIN

   DELETE TOP(1000) FROM [internal].[operation_messages];

   IF @@ROWCOUNT < 1000 BREAK;

END 

GO

--FK table 

WHILE 1 = 1

BEGIN

   DELETE TOP(1000) FROM [internal].[event_messages];

   IF @@ROWCOUNT < 1000 BREAK;

END 

GO

TRUNCATE TABLE [internal].[event_message_context];

GO

TRUNCATE TABLE [internal].[operation_os_sys_info];

GO

TRUNCATE TABLE [internal].[operation_permissions];

GO

TRUNCATE TABLE [internal].[validations];

GO

USE msdb ;  

GO  

EXEC dbo.sp_start_job N'SSIS Server Maintenance Job';  

GO  

3)If needed you can shrink SSISDB database.

Missing Indexes for SQL Server

Missing Indexes for SQL Server 

SELECT CONVERT(decimal(18,2), user_seeks * avg_total_user_cost * (avg_user_impact * 0.01)) AS [index_advantage], 

migs.last_user_seek, mid.[statement] AS [Database.Schema.Table],

mid.equality_columns, mid.inequality_columns, mid.included_columns,

migs.unique_compiles, migs.user_seeks, migs.avg_total_user_cost, migs.avg_user_impact

FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)

ON mig.index_handle = mid.index_handle

ORDER BY index_advantage DESC OPTION (RECOMPILE);

Leadership Styles of Project Management

 Leadership Styles of Project Management

There are different types of leadership styles that we can use before starting a project but they will vary depends on the capability and personality of the manager and the prevailing circumstances.

The manager may have a natural preference for a certain style, but to be successful must vary their style to suit the circumstances.

There are 6 types of leadership styles, following them are:- 

1. Coercive -  

• This management style most used when the project manager has to be work with the junior team members and who does not have adequate level of experience level and knowledge. 

• The project manager monitors the entire project from beginning to end and will provide the proper guidance to the junior team members how to complete the project successfully.

2. Authoritative - 

• This management style has a clear vision and share with the team members. The project manager must be a very knowledgeable person and motivating the team members to collaborate on the project.

• The project manager encouraging the team members achieve the vision together successfully. So he /she will share the ideas and knowledge with other the team members to work collaboratively and show the right direction, improving people’s skills, knowledge and strengths to achieve the target together.

3. Affiliative - 

• In this management style the manager will encourage his/her team members to work on their own pace, motivate them to use their own knowledge and skills, creating processes to reduce the stressfulness and building relationships with the team members.

• Organize some events like games, friendly discussion to  reduce the boredness , stressfulness of the work, try to make them happy of their work, encourage them, therefore they will try to work hard can able to deliver successful project on the right time.

4. Democratic -

• In this leadership style the project manager does not guide his/her team members ,they are free to express their ideas, use their own knowledge to achieve goals, collaboration of work and even everyone is try to give some input to the team success.

• Each and every team member has a one goal that is “ How to achieve our goals” . They will keep this phrase in their mind and try to work hard by helping each other to achieve the goal as a team or else as a one family.

    5.  Pacesetting - 

• The project managers  who follows this management style expect high standards from his/her team members and the team members do have to follow the processors, ideas standards of the project manager.

• The team members have to work hard with lots of stressfulness and the people who don’t work hard will terminate from the team.

6. Coaching –

• In this style the project manager should have strong coaching skills, and should be a self-confidence person who should guide, encourage, and motivate the team members to achieve goals and objectives successfully.

• The project manager has the main responsibility of the team success so therefore he/she must be more responsible otherwise that is the end that team.

The definition of project management

The core relationship with project management and quality management

The definition of project management

The term we use to describe project management is the application of knowledge, skills, tools, and techniques which are used for the project activities to meet the specified project objectives and goals.

There are 5 elements in the project management concept regarding the PMBOK (Project Management Body Of Knowledge)

·         *  Initiation

·        * Planning

·        * Executing

·        * Monitoring and Controlling

·        * Closing

The main challenge of project management is to achieve project goals and objectives mainly concerning project scope, time, cost, and quality.

Quality Management

First of all, we need to understand what was defined as Quality?

The quality which was meant to be is it should match with the purpose. The final product should meet the stakeholder's needs and expectations according to their specified design, functionalities, cost, durability.

Quality management is a continuing process until the final product delivered to the customer. Because we must consider delivering the zero defects and fully-functional end product to the customer to make the customer happy with the product.

Quality management is a repetitive cycle of measuring the quality, monitoring, and updating the processes until the desired quality has achieved.

The main idea about project quality management is to ensure that the project will meet the customer requirements in other words the end product should satisfy what the customer actually wants.

The project team must create a good relationship with the stakeholders from that only we can provide a good end product. One of the main causes for poor project results will be the development team is only focuses to meet the written customer requirement, not the customer actually wants.

For the successful project results should combine project management activities with quality management activities.

Because the main challenge of project management is to achieve defined goals and objectives while concerning scope, time cost, and quality.

The project manager will create a plan for the project time , cost, and scope with the predefined goals and objectives which means the project should deliver on the estimated time and within the estimated cost and scope with zero defects and errors and fully-functional product which should meet the customer needs and expectations.

Therefore there's a core relationship with project management and quality management.

Manage Grants MSSQL objects

How to Manage Grants MSSQL objects?

If you need to Manage grants MSSQL table or any objects you can use following method,

ADD GRANTS

USE [DATABASEUSERGROUP]

GO

GRANT SELECT ON [dbo].[mytable] TO [Domain\username]

Go 

REMOVE GRANTS

USE [DATABASEUSERGROUP]

GO

REVOKE SELECT ON [dbo].[mytable] FROM [Domain\username]

GO

Cheers...

and Most welcome your comments and ideas...

Detach and Attach SQL Server Database

Detach and Attach SQL Server Database

how to Detach and Attach SQL Server Database using SQL Server Management studio?

First,

According to the above, picture Detach your Selected database.
Next,

According to the above, picture Select "Update Stats" And Select "OK".
Next,
Move Database File Old Location to New location,
Ex: C:\databaseusergroup.mdf to D:\databaseusergroup.mdf
And Next Select "Attach" like Following Picture,

Next,

Attach Database like Following Pictures,

(If You Have multiple data file then you need to select only main MDF file)

Cheers...

and Most welcome your comments and ideas...

Get Executed SQLs From Database

Get Executed SQLs From Database

If you need to get executed SQL statements from database, then you can used this method to read it,

First we will insert one record,

INSERT INTO [DATABASEUSERGROUP].[dbo].[XX](col2)SELECT 2015GO 

Next you can read it as follows,

SELECT qs.last_execution_time, st.[TEXT] AS [SQLQuery]FROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stWHERE (lower(st.[TEXT]) like '%insert%')AND (lower(st.[TEXT]) like '%xx%')AND (lower(st.[TEXT]) like '%col2%')ORDER BY qs.last_execution_time DESC

I'm not mistaken if you restart MSSQL Server service then this record will be deleted! I didn't try it just add comment after try it...

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

RESTORE FULL and DIFFERENTIAL BACKUPS

RESTORE FULL and DIFFERENTIAL BACKUPS

If you need to restore database given following backups files,

• One Full Database Backup File
• Tow Differential Database Backup Files

First you need to have full database backup file as follows,

use masterGOBACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_FULL.bak' WITH NOFORMAT, NOINIT,  NAME = N'DATABASEUSERGROUP-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10;GODECLARE @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\DATABASEUSERGROUP_FULL.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWINDGO

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 113752 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP' on file 1.
Processed 1416 pages for database 'DATABASEUSERGROUP', file 'SALES_01' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_02' on file 1.
Processed 176 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_OLD' on file 1.
Processed 424 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2002' on file 1.
Processed 944 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2003' on file 1.
100 percent processed.
Processed 2 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP_log' on file 1.
BACKUP DATABASE successfully processed 116722 pages in 3.060 seconds (298.002 MB/sec).
The backup set on file 1 is valid.

 Next you need to have two(You need to execute same script twice after change backup file Ex: DIFF_01.bak, DIFF_02.bak)  Differential database backup file as follows, 

use master
GO
BACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak'
WITH  DIFFERENTIAL , NOFORMAT, NOINIT, 
NAME = N'DATABASEUSERGROUP-Diff Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
DECLARE @backupSetId AS int
SELECT @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);
END
RESTORE VERIFYONLY FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO

29 percent processed.
39 percent processed.
49 percent processed.
59 percent processed.
69 percent processed.
79 percent processed.
89 percent processed.
99 percent processed.
Processed 40 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_01' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'SALES_02' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_OLD' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2002' on file 1.
Processed 8 pages for database 'DATABASEUSERGROUP', file 'ARCHIVE_2003' on file 1.
100 percent processed.
Processed 1 pages for database 'DATABASEUSERGROUP', file 'DATABASEUSERGROUP_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 81 pages in 0.031 seconds (20.208 MB/sec).
The backup set on file 1 is valid.

After Complete your Backup Task You need to restore above backup file as follows,

USE [master]GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_FULL.bak' WITH  FILE = 1,  NORECOVERY,REPLACE, NOUNLOAD,  STATS = 5;GO

You Have to mention NORECOVERY otherwise you can not restore remaining two Differential database backup files.
And After Restore full database backup,

Finally You Need to restore remaining two Differential database backup files as follows,

USE [master]GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_01.bak' WITH  FILE = 1,NORECOVERY, NOUNLOAD,  STATS = 5GORESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'C:\MSSQL\Backups\DATABASEUSERGROUP_DIFF_02.bak' WITH  FILE = 1,RECOVERY, NOUNLOAD,  STATS = 5GO

Last Differential database backup files you need to restore as RECOVERY.

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

Minimum SQL Server version requirements

Minimum SQL Server version requirements

According to Microsoft Minimum SQL Server version requirements for Windows 10 and Windows Server 2016,

1. SQL Server 2008 R2 is not supported on Windows 10 or Windows Server 2016.
2. For SQL Server 2017 on Windows,The release is supported at the SQL Server 2017 on Windows RTM Release version.
3. For SQL Server 2016,The release is supported at the SQL Server 2016 RTM Release version.
4. For SQL Server 2014,You must apply SQL Server 2014 Service Pack 1 or a later update.
5. For SQL Server 2012,You must apply SQL Server 2012 Service Pack 2 or a later update

More Info - Microsoft SQL Server , Hardware and Software Requirements for Installing SQL Server

Cheers...

and Most welcome your comments and ideas...

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...  
 

Log Errors in SQL Server(Management\SQL Server Log)

Log Errors in SQL Server(Management\SQL Server Log)

                                          In SQL Server if you need to log all errors ( Or any given errors ) then there is no easy way to do it using graphical interface. Following SQL script will help you to log all errors in SQL Server log file. This script is MSSQL configuration script installation level. After execute this it will configure all errors log to MSSQL log files. Note: This is not recommended to execute in Production server or very busy low performance test db servers.

(B’cos after install this script it will log all errors, it cost extra CPU and disk space)

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 10
GO
--EXEC sp_altermessage 8134, 'WITH_LOG', 'true';
GO
DECLARE @message_id nvarchar(20);
DECLARE SYS_messages CURSOR FOR 
SELECT CAST(message_id AS nvarchar(20)) FROM sys.messages WHERE language_id=1033 ORDER BY message_id; 
OPEN SYS_messages; 
FETCH NEXT FROM SYS_messages INTO @message_id; 
WHILE @@FETCH_STATUS = 0  BEGIN
PRINT @message_id
EXEC sp_altermessage @message_id, 'WITH_LOG', 'true';
    FETCH NEXT FROM SYS_messages INTO @message_id; 
END
CLOSE SYS_messages; 
DEALLOCATE SYS_messages; 
GO 

SQL Server Trace

SQL Server Trace

In SQL Server you need to find information, then most of DBA's using SQL Server Error Log.But in SQL Server Error Log Bit difficult to filter information and it does not display all information.

SELECT TG.*,TE.name FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\log_993.trc', default) TG INNER JOINsys.trace_events TE ON TG.EventClass=TE.trace_event_idWHERE DatabaseName='<YOUR DB NAME>';GO

Some information you can find table name start with "sys.trace_" but some pieces of information do not contain "trace"  tables. examples like "ObjectType" columns.  "ObjectType Trace Event Column" Microsoft Docs will help you to find more details.

CREATE SCHEMA PL/SQL(Oracle)

CREATE SCHEMA PL/SQL(Oracle)

• First Need to create table space for located all object belong to schema,

CREATE TABLESPACE TBS_DATABASE_USER_GROUP DATAFILE 'C:\ORACLE\tbs_database_user_group_01.dbf' SIZE 256M REUSE AUTOEXTEND OFF; 

• Next Create  "TEMPORARY TABLESPACE" as follows,

CREATE TEMPORARY TABLESPACE TBS_TEMP_DATABASE_USER_GROUP TEMPFILE 'tbs_temp_database_user_group.dbf' SIZE 5M AUTOEXTEND ON;

• Next Create User(or Schema)

CREATE USER Tharindu  IDENTIFIED BY password123
  DEFAULT TABLESPACE TBS_DATABASE_USER_GROUP
  TEMPORARY TABLESPACE TBS_TEMP_DATABASE_USER_GROUP
  QUOTA 20M on TBS_DATABASE_USER_GROUP;

• Finally Grant for above created user 

GRANT create session TO Tharindu;
GRANT create table TO Tharindu;
GRANT create view TO Tharindu;
GRANT create any trigger TO Tharindu;
GRANT create any procedure TO Tharindu;
GRANT create sequence TO Tharindu;
GRANT create synonym TO Tharindu;

With using above steps, allow you to create oracle user!  

char Vs varchar

char Vs varchar

Most common rule for any database systems if you have fixed length column use "char" data type else, (if you have variations) then use "varchar". but after read some forum post, noticed this is not common for less variation columns(less than 10 digits columns). I have created sample script to validate this as follows,

USE tempdb

GO

DECLARE

  @char CHAR(5) = 'ABCD',

  @nchar NCHAR(5) = N'ABCD',

  @varchar VARCHAR(10) = 'ABCD',

  @nvarchar NVARCHAR(10) = N'ABCD';

SELECT

  DATALENGTH(@char) AS CharDataLength,

  DATALENGTH(@nchar) AS NcharDataLength,

  DATALENGTH(@varchar) AS VarcharDataLength,

  DATALENGTH(@nvarchar) AS NvarcharDataLength

GO

-------------------------------------------------------------

CREATE TABLE #char(strchar char(8));

CREATE TABLE #varchar(strvarchar varchar(8));

GO

INSERT INTO #char VALUES('A');

INSERT INTO #char VALUES('AB');

INSERT INTO #char VALUES('ABC');

INSERT INTO #char VALUES('ABCD');

INSERT INTO #char VALUES('ABCDE');

INSERT INTO #char VALUES('ABCDEF');

INSERT INTO #char VALUES('ABCDEFG');

INSERT INTO #char VALUES('ABCDEFGH');

INSERT INTO #varchar VALUES('A');

INSERT INTO #varchar VALUES('AB');

INSERT INTO #varchar VALUES('ABC');

INSERT INTO #varchar VALUES('ABCD');

INSERT INTO #varchar VALUES('ABCDE');

INSERT INTO #varchar VALUES('ABCDEF');

INSERT INTO #varchar VALUES('ABCDEFG');

INSERT INTO #varchar VALUES('ABCDEFGH');

GO 10000

SET STATISTICS IO ON

SELECT * FROM #char;

PRINT '------------------------------------'

SELECT * FROM #varchar;

SET STATISTICS IO OFF

EXEC sp_spaceused '#char';

GO

EXEC sp_spaceused '#varchar';

GO

DROP TABLE #char;

DROP TABLE #varchar;

with above script statistics you will noticed less space used (and less pages) for "char" column apart from "varchar" column.