Set up: SQL Server 2005 on Win 2003 server (prod machine) and same on Win XP (development machine) server
I have a rather large table with almost 2 million record in it. When I try to
select * from that_table
in Managment Studio, text mode new query window, I am getting this error after approximately half of all records in the table are displayed:
An error occurred while executing batch. Error message is: Couldn't replace text
This happens on both prod and dev server. I think it is out memory type error but, I do not think it is normal. Or, is it? I tried to pull next record from that error
select * from that_table where pk_field = 520000
tried to pull all records after the error.
select * from that_table where pk_field >= 520000
It seems to fail approximately after pulling the same amount of records, plus-minus 10,000 - 20,000.
It is a good question. I dont really need but, performance of this table is a bit on a slow side so, I thought this is a good indicator... or, not?
This is billing table and each bill run I get to insert about 20,000 new records for each company and I have a handful and, this is a real bottleneck. If I trancate the table to about 100,000 records (I can remove bills that are more than 2 years old but, this becomes more problematic as I get more records for each bill now per year).
May be the structure of the table is the problem? I would appreciate if someone could advice.
Here is ddl
[empbillstatement_id] [int]IDENTITY(1,1) NOT NULL,
[billstatementbenefit_id] [int]NOT NULL,
[billstatementvalue_id] [int]NOT NULL,
[coverage_amt] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_coverage_amt] DEFAULT ((0)),
[coverage_category] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[coverage_type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_EmpBillStatement_coverage_type] DEFAULT ('e'),
[employer_premium] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_employer_premium] DEFAULT ((0)),
[employee_premium] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_employee_premium] DEFAULT ((0)),
[employer_tax] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_employer_tax] DEFAULT ((0)),
[employee_tax] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_employee_tax] DEFAULT ((0)),
[paymethod_id] [int]NOT NULL,
[ee_tax_fed] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_ee_tax_fed] DEFAULT ((0)),
[ee_tax_prov] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_ee_tax_prov] DEFAULT ((0)),
[er_tax_fed] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_er_tax_fed] DEFAULT ((0)),
[er_tax_prov] [decimal](18, 5) NOT NULL CONSTRAINT [DF_EmpBillStatement_er_tax_prov] DEFAULT ((0)),
[coverage_category_fr] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sp_age] [int]NULL, CONSTRAINT [PK_EmpBillStatement] PRIMARY KEY CLUSTERED
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
How much memory did you dedicate to SQL Server ?
Jens K. Suessmeyer
- Edited by Jens K. Suessmeyer -Microsoft employee, Moderator Friday, February 13, 2009 10:48 AM Ugly typo, see the "memory" changes instead of erros, sorry for the confusion.
Do you mean error logs? How much do I need and if not error logs then what? Another problem I noticed, if I run a bill for 500 people company, it takes 5 min to complete but 1700 takes more than 3 hours. The structure of the bill details is similar and from what I see this table takes longest to insert.