Delete duplicate from 140 GB SQL server table where 30% records are duplicate without causing overheads like transaction logs full?

sql serversql-server-2008

How to delete duplicate from 140 GB SQL server table where 30% records are duplicate without causing overheads like transaction logs full?

table doesn't have primary key.

Best Answer

If you have no Primary Key then you have no Foreign Keys referencing it.
That leaves you in the privileged position of being able to remove the table and completely recreate it or, rather, create a new table with only the rows you want, then drop the original table and rename the new table, something like this.

create table t2 
( a ... 
, b ... 
, c ... 
) 
primary key ( a, b, c ); 

insert into t2 ( a, b, c ) 
select a, b, c 
from t1 
where ... 
; 

drop table t1 ; 

EXEC sp_rename 't2', 't1' ;

Of course, how you identify the rows that you want to take across is up to you.

Related Solutions

Sql-server – How to create a SQL Server 2008 backup without logs with mirroring

Backing up a database (BACKUP DATABASE ...) backs up the pages from all of the database files (data and log) and upon restore those files will be recreated as a result.

A full database backup does just that: It backs up the database in its entirety. There is no way to get around not backing up the transaction log file. If you are in full recovery mode, then you should ensure you are doing the proper transaction log backups so the log doesn't grow out of control. Outside of that, you are constrained with a full backup. A database consists of data file(s) and log file(s).

A workaround to this could be just to generate scripts from you database. When you right-click your database to generate scripts, you can script out the entire database. There is an option to script out both Schema and Data. Then on your other machine/instance you can run this script. It's not a traditional "backup" by any means, but it should get you what you want and you'll just have a script file as a result. Mind you, when the CREATE DATABASE portion of the script runs, a log file will be created. You can't have an RDBMS database without a log file (or at least not on SQL Server that I know of).

Sql-server – SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

A full backup in SQL Server 2008 does not break the log chain. It only resets the differential base-lsn.

You also can restore log backups after restoring from a copy only. The following script demos that:

CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13 
TO DISK = 'BakTst13_Log_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13 
TO DISK = 'BakTst13_Log_2' WITH INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

It creates a database and a table and inserts 50 rows into that table. In between those inserts several backups are taken in this order:

Full
Log
Full
Full Copy_Only
Log

Next the database is dropped and restored like this:

1st Full
1st Log
2nd Log

The following SELECT demonstrates that the restore was successful.

This shows that neither a COP_ONLY nor a normal Full Backup break the log chain.

Then the database is dropped again and restored like this:

Copy_Only Full
2nd Log

Afterwards the SELECT demonstrates success again.

This demonstrates that you can use a COPY_ONLY full backup as the base of your Log Restore.

Differential tests

I created a DIFFERENTIAL version too:

CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_1' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_2' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13 
TO DISK = 'BakTst13_Diff_3' WITH DIFFERENTIAL,INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F1, D1, D2',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY; 
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore FC, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F2, D2, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_2' WITH NORECOVERY; 
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

This takes backups in this order:

1st Full
1st Differential
2nd Full
2nd Differential
Copy_Only Diff
3rd Differential

It then tries this restore route:

1st Full
1st Differential
2nd Differential

Step 3 fails with this error:

Msg 3136, Level 16, State 1, Line 4
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

This shows that a normal full backup breaks the differential chain.

Next the database is dropped and this restore flow is attempted:

Copy_Only Full
3rd Differential

Step 2 fails with the same error as step 3 above. This shows that a copy only backup cannot be used as the base for a differential restore.

Then the database is dropped again and the following restore is executed:

2nd Full
2nd Differential
3rd Differential

The following select proves that this restore succeeded. This shows that a COPY_ONLY Full backup does not interrupt the differential chain.