Recover Deleted Data

Recover Deleted Data

Today one of my local development database data deleted mistakenly and create same data again little bit painfully because not only the amount of data, it’s has very time taken logic behind it. 
Unluckily I haven’t backup of that database. Normally when I’m doing some critical development test in my local database, I was creating database snapshot. But I forget it :(.

I was bit lucky my database was full recovery mode. By the way I have taken full backup(luckily not copy only backup this time :), normally I was taking copy only backup every time as best practices) few days back.

Well… with in few minute I have manage to recover my deleted table and deleted data.After finish this I was thinking to publish my T-SQL script in my web page with step by step explains.

As a starting we need to create database with full recovery mode and need to take full backup(not copy only backup) as follows,

USE master
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DATABASEUSERGROUP') BEGIN
    ALTER DATABASE [DATABASEUSERGROUP] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [DATABASEUSERGROUP];
END
GO
CREATE DATABASE DATABASEUSERGROUP;
GO
ALTER DATABASE [DATABASEUSERGROUP] SET RECOVERY FULL WITH NO_WAIT;
GO

--you need to change backup file path for you own
BACKUP DATABASE [DATABASEUSERGROUP] TO  DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_FULL_04NOV2014.bak'
WITH    NOFORMAT, NOINIT, 
        NAME = N'DATABASEUSERGROUP-Full Database Backup',
        SKIP, NOREWIND, NOUNLOAD,  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

--you need to change backup file path for you own
RESTORE VERIFYONLY FROM  DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_FULL_04NOV2014.bak' WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND;
GO

Next I’m going to create some data, above created database as follows,

USE DATABASEUSERGROUP
GO
CREATE TABLE tblDeleteRecords(col1 int,col2 varchar(128),col3 datetime);
CREATE TABLE tblDropTable(col1 int,col2 varchar(128),col3 datetime);
GO
--INSERT DATA
INSERT INTO tblDeleteRecords(col1,col2,col3)
SELECT id,name,GETDATE() FROM sys.sysobjects;
GO
INSERT INTO tblDropTable(col1,col2,col3)
SELECT id,name,GETDATE() FROM sys.sysobjects;
GO
SELECT * FROM tblDeleteRecords;
SELECT * FROM tblDropTable;
GO
CHECKPOINT;
SELECT GETDATE()
--2014-11-05 11:41:06.323
GO

OK! Let’s delete and see!!!

--DELETE DATA
DELETE FROM tblDeleteRecords WHERE col1<20;
SELECT GETDATE()
--2014-11-05 11:42:07.487
GO
--DROP TABLE
DROP TABLE tblDropTable;
SELECT GETDATE()
--2014-11-05 11:43:32.153
GO

With this article I was thinking to discuss about SQL Server undocumented “fn_dblog” function. I have no idea why Microsoft will listed this as undocumented. With using above function you can view LSN number. Who is deleted records and more information contains in transaction log file.
 

SELECT  SUSER_SNAME([Transaction SID]) userName,B.[Current LSN],B.Operation,[Transaction ID],B.AllocUnitName,B.[Description]
FROM fn_dblog(NULL, NULL) B
WHERE (AllocUnitName LIKE '%tblDeleteRecords%' OR AllocUnitName LIKE '%tblDropTable%')
AND [Operation] = 'LOP_INSERT_ROWS';

SELECT  SUSER_SNAME([Transaction SID]) Last_record_deleted_user,[Current LSN],Operation,[Transaction ID],AllocUnitName,[Description]
    FROM fn_dblog(NULL, NULL)
    WHERE
        [Transaction ID] = (
            SELECT top 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
            WHERE [Operation] = 'LOP_INSERT_ROWS' AND AllocUnitName LIKE '%tblDeleteRecords%' OR AllocUnitName LIKE '%tblDropTable%'
            order by [Transaction ID] desc
        )
        AND [Operation] = 'LOP_BEGIN_XACT'
   
GO
 
SELECT  SUSER_SNAME([Transaction SID]) userName,B.[Current LSN],B.Operation,[Transaction ID],B.AllocUnitName,B.[Description]
FROM fn_dblog(NULL, NULL) B
WHERE AllocUnitName LIKE '%tblDeleteRecords%'
AND [Operation] = 'LOP_DELETE_ROWS';

SELECT  SUSER_SNAME([Transaction SID]) Last_record_deleted_user,[Current LSN],Operation,[Transaction ID],AllocUnitName,[Description]
    FROM fn_dblog(NULL, NULL)
    WHERE
        [Transaction ID] = (
            SELECT top 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
            WHERE [Operation] = 'LOP_DELETE_ROWS' AND AllocUnitName LIKE '%tblDeleteRecords%'
            order by [Transaction ID] desc
        )
        AND [Operation] = 'LOP_BEGIN_XACT'
   
GO

SELECT  SUSER_SNAME([Transaction SID]) Last_record_deleted_user,[Current LSN],Operation,[Transaction ID],AllocUnitName,[Description]
    FROM fn_dblog(NULL, NULL)
    WHERE
        [Transaction ID] = (
            SELECT top 1 [Transaction ID] FROM fn_dblog(NULL, NULL)
            order by [Transaction ID] desc
        )
        AND [Operation] = 'LOP_BEGIN_XACT'
   
GO

Well! Hope you have view some information contains in Transaction log file. Now is time to recover above deleted data. Initially you after delete records you need to take tail log backup of SQL server database. As follows,

--SQL Server Transaction Log Backups
BACKUP LOG DATABASEUSERGROUP TO DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_TR_LOG_04NOV2014.trn'
GO

Next you need to restore your last full backup with “NORECOVERY” state. After finish this, you will noticed your database is “on recovery” mode. Well finally you need to restore Tail log backup as follows,

--First recovery Deleted Table
USE master
go
RESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_FULL_04NOV2014.bak'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE LOG DATABASEUSERGROUP FROM DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_TR_LOG_04NOV2014.trn' WITH STOPAT = '2014-11-05 11:43:30.000'

--Next recovery all
USE master
go
RESTORE DATABASE [DATABASEUSERGROUP] FROM  DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_FULL_04NOV2014.bak'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10
GO

RESTORE LOG DATABASEUSERGROUP FROM DISK = N'E:\Backups\MSSQL\DATABASEUSERGROUP_TR_LOG_04NOV2014.trn' WITH STOPAT = '2014-11-05 11:41:07.000'

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

Enabling Disk Performance Counters for Windows Task Manager

Enabling Disk Performance Counters for Windows Task Manager

When I was reading Windows server document, I seen some picture in task manager windows tab displayed all hard disk drive partitions in performance tab as following display picture. Normally default windows tab this won’t display.

Well! How can we add this?
With using following steps you can add this facility to your session (login) related task manager.

Step 1: Enabling Disk Performance Counters

1.    Ensure Task Manager is closed.
2.    Launch the Command Prompt using the "Run as Administrator" option.
3.    Enter the following at the Command Prompt:

diskperf -Y

4.     Hit Enter.
5.    Close the Command Prompt.
6.    Re-open the Task Manager.

Nice Article Which I Read...

Nice Article Which I Read...

• IMPLICIT CONVERTION(sqlskills.com by Jonathan Kehayias) -                                                                     While performance tuning investigation I have found very nice article about IMPLICIT CONVERTION. This is publish on sqlskills.com(one of my favourite website) website, written by Jonathan Kehayias.
• Finding a table name from a page ID(SQLskills) -  This is good article written by Paul Randal.
• Deadlock Troubleshooting From Bart Duncan's SQL Weblog
• Deadlock Troubleshooting, Part I
• Deadlock Troubleshooting, Part II
• Deadlock Troubleshooting, Part III

SQL Server Collation

SQL Server Collation

         
                       Sometimes you need to work with different SQL Server collation depend on your customers. If you need to have multiple languages in same database then you must use Unicode supported  data types. If you have different language with different database then, you can have different collation for each database.

• How SQL Server collation works?  

                                In high-level explanation is it will support proper use of characters for either a language. 

• SQL Server Collation with “LIKE” Operator

                              This is very importance when you are using LIKE Operator in different collations. Main consideration is case sensitive, if your database collation is case sensitive then like operator supports only case sensitive data. If accent-insensitive then you can have data with different accent.
Following examples will help you to get more idea,

use tempdb
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblCollate]') AND [type] in (N'U')) BEGIN
    DROP TABLE [dbo].[tblCollate];
END    
GO
CREATE TABLE tblCollate
    (varcharGeneral        varchar(100) COLLATE    SQL_Latin1_General_CP1_CI_AS NOT NULL,
     varcharCollateCIAI varchar(100) COLLATE Finnish_Swedish_CI_AI NOT NULL,
     varcharCollateCSAI varchar(100) COLLATE Finnish_Swedish_CS_AI NOT NULL,
     varcharCollateCSAS varchar(100) COLLATE Finnish_Swedish_CS_AS NOT NULL,
     varcharCollateCIAS varchar(100) COLLATE Finnish_Swedish_CI_AS NOT NULL                        
);
GO
--Insert Record Set 01
INSERT INTO [dbo].[tblCollate]
(varcharGeneral,varcharCollateCIAI,varcharCollateCSAS,varcharCollateCSAI,varcharCollateCIAS)
VALUES    ('Varchar','Varchar','Varchar','Varchar','Varchar'),
        ('varchar','varchar','varchar','varchar','varchar'),
        ('Value','Value','Value','Value','Value'),
        ('value','value','value','value','value'),
        ('Vacation','Vacation','Vacation','Vacation','Vacation'),
        ('vacation','vacation','vacation','vacation','vacation');
GO
--Insert Record Set 02
INSERT INTO [dbo].[tblCollate]
(varcharGeneral,varcharCollateCIAI,varcharCollateCSAS,varcharCollateCSAI,varcharCollateCIAS)
VALUES    ('Wallet','Wallet','Wallet','Wallet','Wallet'),
        ('wallet','wallet','wallet','wallet','wallet'),
        ('Wallpaper','Wallpaper','Wallpaper','Wallpaper','Wallpaper'),
        ('wallpaper','wallpaper','wallpaper','wallpaper','wallpaper'),
        ('Watch','Watch','Watch','Watch','Watch'),
        ('watch','watch','watch','watch','watch');
GO    
    
SELECT * FROM [dbo].[tblCollate]; --All Records

--SQL_Latin1_General_CP1_CI_AS
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Va%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%va%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Wa%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Value%');
SELECT varcharGeneral FROM [dbo].[tblCollate] WHERE (varcharGeneral LIKE '%Wallet%');

--Finnish_Swedish_CI_AI
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Va%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%va%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Wa%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Value%');
SELECT varcharCollateCIAI FROM [dbo].[tblCollate] WHERE (varcharCollateCIAI LIKE '%Wallet%');

--Finnish_Swedish_CS_AI
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Va%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%va%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Wa%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Value%');
SELECT varcharCollateCSAI FROM [dbo].[tblCollate] WHERE (varcharCollateCSAI LIKE '%Wallet%');

--Finnish_Swedish_CS_AS
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Va%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%va%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Wa%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Value%');
SELECT varcharCollateCSAS FROM [dbo].[tblCollate] WHERE (varcharCollateCSAS LIKE '%Wallet%');

--Finnish_Swedish_CI_AS
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Va%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%va%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Wa%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Value%');
SELECT varcharCollateCIAS FROM [dbo].[tblCollate] WHERE (varcharCollateCIAS LIKE '%Wallet%');
GO

Now you will have some good idea about SQL Server collation and how you have to change collation with different languages, 

Hope following Microsoft Reference will help you for further investigation,

• SQL Server Collation Fundamentals
• LIKE (Transact-SQL) 
• Selecting a SQL Server Collation
• COLLATE (Transact-SQL)

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

Table default data display order

Table default data display order

Today one of my friend asking me about default sort order of table. Hope most of them answer is, It was sorted according to clustered index column and with given “Order By condition” it can be “ascending or descending” my answer was same and again he ask me,

Then what happened if we haven’t any clustered index? What do you think? 
It’s good question any way I have two answers for that,

• If you create table without any clustered index and then insert some records to that table. Then default display order is whatever the data inserted order. Example is if you insert “2” first and “1” second then your first row will be “2” and second row will be “1”. 

• If you insert some records to table and then create “clustered index” and later you will be deleted clustered index. Then your data sort order is old clustered index column sort order.If you insert records after drop “clustered index” then, those records inserted after that. 

Seems to be he was very happy with my answers. But after one min he was again asking me,

What happened if we have “clustered index” with duplicate records?  
      I was bit smile and tell him then it will display “clustered index” column order and duplicate records will display when data inserted order. 
After that I was show him following small example,

use tempdb
go
drop table tblOrder;
go
create table tblOrder(col1 int, col2 int, col3 int);
go
insert into tblOrder values(1,2,3);
insert into tblOrder values(3,1,2);
insert into tblOrder values(2,3,1);
insert into tblOrder values(5,6,7);
insert into tblOrder values(5,8,13);
insert into tblOrder values(5,4,14);
insert into tblOrder values(5,5,15);
insert into tblOrder values(4,9,11);
insert into tblOrder values(20,19,311);
go
select * from tblOrder;
go
create clustered index [cix_tblorder_col1] on [dbo].[tblOrder]([col1] asc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col1] on [dbo].[tblOrder] with ( online = off );
go
select * from tblOrder;
go
create clustered index [cix_tblorder_col2] on [dbo].[tblOrder]([col2] asc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col2] on [dbo].[tblOrder] with ( online = off );
go
select * from tblOrder;
go
create clustered index [cix_tblorder_col3] on [dbo].[tblOrder]([col3] asc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col3] on [dbo].[tblOrder] with ( online = off );
go
--with descending
select * from tblOrder;
go
create clustered index [cix_tblorder_col1] on [dbo].[tblOrder]([col1] desc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col1] on [dbo].[tblOrder] with ( online = off );
go
--default ascending
select * from tblOrder;
go
create clustered index [cix_tblorder_col1] on [dbo].[tblOrder]([col1]);
go
select * from tblOrder;
go
drop index [cix_tblorder_col1] on [dbo].[tblOrder] with ( online = off );
go
--nonclustered index
select * from tblOrder;
go
create nonclustered index [cix_tblorder_col1] on [dbo].[tblOrder]([col1] asc);
go
select * from tblOrder;
go
drop index [cix_tblorder_col1] on [dbo].[tblOrder] with ( online = off );
go

I was really happy about his third Question that's why i have decided to publish this post.

hope following tips will help who is reading this post,

• You need to select best column for "clustered index" whenever you are designing table.

• It should be much as small (smallint,int or less than 20 digits) then performance will be good.

• if your system is 99.9% of insert(log table) then very rarely using "SELECT" statements then better to create table without any indexes.(Ex: if you have very high insert rate table then reduce indexes much as possible)

• Try to create small indexes(index contain bytes) much as possible.

• According to index fragmentation try to Rebuild or reorganize indexes.     

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

Change SQL Server HEAP table file group

Change SQL Server HEAP table file group

                         I have a small worker-round for sql server database, HEAP tables move to different file group. Of-cause this is bit critical work before starts I have a two solutions,

• Create CLUSTERED index of all given HEAP tables.
• Move all HEAP table to new file group.   

And every one is know, if you recreate or newly create CLUSTERED index on SQL server table, then index will be created given file group.which means simply move data to newly given file group.

But what will happens if we drop again,

• Will HEAP table again move to old file group?
• Or HEAP table remain as same file group?

 what Microsoft Saying?

Managing Heaps

• To create a heap, create a table without a clustered index. If a table already has a clustered index, drop the clustered index to return the table to a heap.

• To remove a heap, create a clustered index on the heap.

• To rebuild a heap to reclaim wasted space, create a clustered index on the heap, and then drop that clustered index. 

Will Have test this,

USE [MSDN]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND [type] in (N'U')) BEGIN
DROP TABLE [dbo].[DatabaseUserGroup];
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[DatabaseUserGroup](
[colid] [int] IDENTITY(1,1) NOT NULL,
[colname] [varchar](100) NULL
) ON [PRIMARY];
END
GO
INSERT INTO [dbo].[DatabaseUserGroup] ([colname]) SELECT name FROM master.sys.objects;
GO 10
SELECT count(*) cnt FROM [dbo].[DatabaseUserGroup];
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND i.type_desc='HEAP'
AND o.[name]='DatabaseUserGroup';
GO
CREATE UNIQUE CLUSTERED INDEX PK_CIX_DatabaseUserGroup_colid ON [dbo].[DatabaseUserGroup](colid) WITH (ONLINE=ON) ON [MAIN];
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND o.[name]='DatabaseUserGroup';
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[DatabaseUserGroup]') AND name = N'PK_CIX_DatabaseUserGroup_colid') BEGIN
DROP INDEX [PK_CIX_DatabaseUserGroup_colid] ON [dbo].[DatabaseUserGroup] WITH ( ONLINE = ON );
END
GO
SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name],i.type_desc
FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
AND o.[name]='DatabaseUserGroup';

Well.. Well... table remain as newly given file group.When a clustered index is created, SQL Server will temporarily duplicate the data from the heap into the clustered index key order and remove the original pages associated with the heap.But remember this is bit costly operation(depend on how large your table) you need to make sure use this very less server busy time.According to Microsoft,

Creating or dropping clustered indexes requires rewriting the entire table. If the table has nonclustered indexes, all the nonclustered indexes must all be recreated whenever the clustered index is changed. Therefore, changing from a heap to a clustered index structure or back can take a lot of time and require disk space for reordering data in tempdb.    

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

SQL Server Tempdb Full Error

SQL Server "Tempdb" Full Error

Sometimes you have noticed following errors, while you are working in SQL Server database platform,

Well… initially I want to mention, simply you can change “tempdb” file location or shrink “tempdb” files (if available space). But best practice is, you need to monitor your database and identify the low disk space issues before this happens. And initial SQL server installation you need to locate your “tempdb” files in separate disk (apart from default “C” drive)

Following example will explain you how to move “tempdb” files to different location,

USE tempdb
GO
SELECT * FROM sys.database_files;
GO
EXEC sp_helpfile
GO

USE master
GO
DECLARE @ssql                    nvarchar(max);
DECLARE @tempdbnewfilelocation    nvarchar(200);

SET @ssql='';
SET @tempdbnewfilelocation='E:\MSSQL\2005\tempdb';

SELECT @ssql=@ssql+'ALTER DATABASE tempdb MODIFY FILE(NAME = '+name+', FILENAME = '''+@tempdbnewfilelocation+''+REVERSE(LEFT(REVERSE (physical_name),CHARINDEX('\',REVERSE (physical_name))))+''');'+char(13)+char(10) FROM tempdb.sys.database_files;
PRINT @ssql;

EXEC (@ssql);
      

If you need to place "tempdb" files in different location, then you need to change above script according to the drive name.  

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

SQL Server Data Compression Page vs Row

SQL Server Page Data Compression vs Row Data Compression

                                                      

                                                                       Today one of my friend ask me about SQL Server Data Compression, After that I have discuss with him about SQL Server Data Compression such as what are the advantages and disadvantages, how it's working like that... For this article I'm going to discuss about SQL Server Data Compression ,how is working and advantages and disadvantages.

Ok! First we will look, what is SQL Server Data Compression?

                                                                                               Data Compression is available only in the Enterprise Edition of SQL Server(And of course developer edition also) You can use the data compression feature to help compress the data inside a database. And this will support  for tables and indexes only. In addition to saving space, data compression can help to improve performance of I/O because after apply data compression it will be less data pages.

Page compression algorithm is reduce data duplication method which means if some records will duplicate within the page then SQL Server remove such duplicate values within a data page by replacing some kind of special values.

And row compression use remove unused spaced(bytes) in column.At the same time you can not use PAGE and ROW compression both, but if page compression enabled then automatically includes row compression. 

Normal Method,

USE [MSDN]
GO
--Drop Table
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblWithOutDataCompression]') AND [type] in (N'U'))
DROP TABLE [dbo].[tblWithOutDataCompression];
GO
--Create Table
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblWithOutDataCompression]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblWithOutDataCompression](
[rowID] [bigint] NULL,
[colName] [varchar](2000) NULL,
[colcity] [varchar](2000) NULL,
[colAddress] [varchar](4000) NULL
) ON [PRIMARY]
END
GO
--Create Clustered Index
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[tblWithOutDataCompression]') AND name = N'PK_ClusteredIndex_tblWithOutDataCompression')
CREATE CLUSTERED INDEX [PK_ClusteredIndex_tblWithOutDataCompression] ON [dbo].[tblWithOutDataCompression]
(
[rowID] ASC
) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblWithOutDataCompression]([rowID],[colName],[colcity],[colAddress])
SELECT bucketid,cacheobjtype,objtype,'SQL SERVER DATA COMPRESSION' FROM sys.syscacheobjects
WHERE
(LEN([cacheobjtype])<2000)  
AND (LEN([objtype])<2000) 
ORDER BY bucketid;
GO
DBCC IND('MSDN',tblWithOutDataCompression,-1);
GO
EXEC sp_spaceused 'tblWithOutDataCompression';

GO

After execute above SQL Statement you will notice it will returns no of data pages created and table space allocation.

Row compression,

USE [MSDN]
GO
--Drop Table
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionROW]') AND type in (N'U'))
DROP TABLE [dbo].[tblDataCompressionROW];
GO
--Create Table
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionROW]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblDataCompressionROW](
[rowID] [bigint] NULL,
[colName] [varchar](2000) NULL,
[colcity] [varchar](2000) NULL,
[colAddress] [varchar](4000) NULL
) ON [PRIMARY]
END
GO
--Create Clustered Index
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionROW]') AND name = N'PK_ClusteredIndex_tblDataCompressionROW')
CREATE CLUSTERED INDEX [PK_ClusteredIndex_tblDataCompressionROW] ON [dbo].[tblDataCompressionROW]
(
[rowID] ASC
)WITH (DATA_COMPRESSION = ROW) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblDataCompressionROW]([rowID],[colName],[colcity],[colAddress])
SELECT bucketid,cacheobjtype,objtype,'SQL SERVER DATA COMPRESSION' FROM sys.syscacheobjects
WHERE
(LEN([cacheobjtype])<2000)  
AND (LEN([objtype])<2000) 
ORDER BY bucketid;
GO  
DBCC IND('MSDN',tblDataCompressionROW,-1);
GO
EXEC sp_spaceused 'tblDataCompressionROW';

GO

Well! After execute above row compression SQL Statement then you will noticed some small amount of number of data(8K) pages reduce. And allocation size also.(Note : if you have very less amount of data in above tables then you will not noticed any space gain.)

Page compression,

USE [MSDN]
GO
--Drop Table
IF  EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionPAGE]') AND [type] in (N'U'))
DROP TABLE [dbo].[tblDataCompressionPAGE];
GO
--Create Table
IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionPAGE]') AND [type] in (N'U'))
BEGIN
CREATE TABLE [dbo].[tblDataCompressionPAGE](
[rowID] [bigint] NULL,
[colName] [varchar](2000) NULL,
[colcity] [varchar](2000) NULL,
[colAddress] [varchar](4000) NULL
) ON [PRIMARY]
END
GO
--Create Clustered Index
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE [object_id] = OBJECT_ID(N'[dbo].[tblDataCompressionPAGE]') AND name = N'PK_ClusteredIndex_tblDataCompressionPAGE')
CREATE CLUSTERED INDEX [PK_ClusteredIndex_tblDataCompressionPAGE] ON [dbo].[tblDataCompressionPAGE]
(
[rowID] ASC
)WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO
INSERT INTO [dbo].[tblDataCompressionPAGE]([rowID],[colName],[colcity],[colAddress])
SELECT bucketid,cacheobjtype,objtype,'SQL SERVER DATA COMPRESSION' FROM sys.syscacheobjects
WHERE
(LEN([cacheobjtype])<2000)  
AND (LEN([objtype])<2000) 
ORDER BY bucketid;
GO  
DBCC IND('MSDN',tblDataCompressionPAGE,-1);
GO
EXEC sp_spaceused 'tblDataCompressionPAGE';

GO

Hope After execute above page compression SQL Statement then you will noticed some significant amount of number of data(8K) pages reduce. And allocation size also.Following SQL Statements will help you to find more information about  Data Compression.

USE [MSDN]
GO
SELECT 
    t.[name] AS TableName,
    p.[rows] AS RowCounts,
    SUM(u.[total_pages]) AS TotalPages, 
    SUM(u.[used_pages]) AS UsedPages, 
    (SUM(u.[total_pages]) - SUM(u.[used_pages])) AS UnusedPages
FROM sys.tables t
INNER JOIN      
    sys.indexes i ON t.[object_id] = i.[object_id]
INNER JOIN 
    sys.partitions p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
INNER JOIN 
    sys.allocation_units u ON p.[partition_id] = u.[container_id]
GROUP BY t.[name], p.[rows]
ORDER BY t.[name];
GO
EXEC sp_estimate_data_compression_savings 'dbo', 'tblWithOutDataCompression', NULL, NULL, 'ROW';
GO
EXEC sp_estimate_data_compression_savings 'dbo', 'tblWithOutDataCompression', NULL, NULL, 'PAGE';
GO

My personal view is this can be good move if you have more gain for disk IO. If you have limited CPU and memory then, this can be performance issue.Microsoft mention it will be extra CPU resources are required to compress and decompress the data but I hope it will affect tempdb also, depend on the table or index size.Best method is if you test this in your development or test server and then move to production database.
And after apply data compression then if you noticed not gain any page compression then better to remove it.
And I hope this should be monitoring job weekly/monthly/yearly because first 3 months you will notice good IO performance of data compression and after one year you will notice there is no gain, Because your table contains data (last 9 months) can not be compress then this can be disadvantage.
If you need more information hope this Microsoft white paper will help you. - Data Compression: Strategy, Capacity Planning and Best Practices   

     

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

SQL Server Port(Default 1433)

SQL Server Port(Default 1433) 

                                                                       SQL Server Port! Well... some times most of developers Asking "What is SQL Server Port?" If you have multiple instances in same serve, some of developers saying I can't connect this database server(database server connection problems) And some of saying how can I get SQL Server port?(or How to find SQL Server port?). If you are Database Administrator in Software development company, then you will face above these questions most of the time.

First How can I view(or Find) SQL Server port in given Server?        

                                                                                     You can used several method to view this, first I'm going to explain, How to find this in graphical view as follows,

Programs ->  Microsoft SQL Server (2005,2008,2008 R2 or 2012) -> Configuration Tools -> SQL Server Configuration Manager -> SQL Server Network Configuration -> And then Select your SQL Server instance -> TCP/IP 

Hope now you can view or find SQL Server port, which is given SQL Server instances.

Any other Methods - Yes :)

Method 01,
                                Open Task Manager and then Go to the Services Tab as following Picture,

Inside the services tab you will notice PID according to SQL Server service. With using above PID you can view SQL Server port as follows,

 Method 02,
                   If you are not happy with above methods, then you can go with following T-SQL command,

DECLARE @tcp_port nvarchar(5)
EXEC xp_regread@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT
SELECT @tcp_port

And if you already know the SQL Server port of given server then you can use SSMS to connect that as follows,

<Server or instance name>,<port>   Ex : MyServer,1433

Hope above Article will help you.

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