Exception of type 'System.OutOfMemoryException' was thrown. ( I am need to see if there are DUPLICATE ROWS in table).

• VoldemarG

  Hall of Fame

  Points: 3883

  More actions

  February 3, 2014 at 10:13 am

  #283117

  How else can I do it? How can I overcome this error?

  I am trying to see if there are any duplicate rows (all columns) in the table (table has 500 million rows) :

  by running this SQL:

  select entryid, wordid,wordpos,categoryid,accountid

  from HekTable

  group by entryid, wordid,wordpos,categoryid,accountid

  having count(*) >1

  Here is the DDL for this table:

  CREATE TABLE [dbo].[HekTable](

  [EntryID] [int] NOT NULL,

  [WordID] [int] NOT NULL,

  [WordPos] [smallint] NOT NULL,

  [CategoryID] [smallint] NULL,

  [AccountID] [int] NOT NULL

  ) ON [PRIMARY]

  and getting this error in less than a minute after EXEC the above SQL:

  An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

  Likes to play Chess

• J Good

  Default port

  Points: 1435

  More actions

  February 3, 2014 at 1:01 pm

  #1685619

  Check out this kb article from microsoft. It may be of some help.

  http://support.microsoft.com/kb/2874903

  Twitter: @SQL_JGood
  Blog: sqljgood.wordpress.com/[/url]

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  February 3, 2014 at 3:28 pm

  #1685661

  The reference in the previous post is just a good pointing. You can use output file or Results to text.

  Additionally you could create a temp table with same structure and then insert the result set from your query

  select entryid, wordid,wordpos,categoryid,accountid

  from HekTable

  group by entryid, wordid,wordpos,categoryid,accountid

  having count(*) >1

  into the temp table, so you can see/analyze the duplicate rows.

  Regards,

  Igor

  Igor Micev,My blog: www.igormicev.com