December 7, 2012 at 6:11 am
Hi all!
This table has developed over 15 years, came originally from a ISAM based system, and is today used in my Clarion based program.
We have quite some GPF's, and some Closedowns.
We have found, that if we do not open this table, the program runs trouble free, and if we use it, it gives theese problems now and again.
Question:
Can any see something here, that is definately wrong, like a column I removed some years ago called månedsløn - (Montly fee) - A naming like that is born to make troubles!
Something like that.
CREATE TABLE [dbo].[PERSONAL](
[per_tlf_privat] [char](20) NULL,
[per_mobil_privat] [char](20) NULL,
[per_e_mail_privat] [char](60) NULL,
[per_noter] [varchar](4999) NULL,
[per_personal_id] [int] IDENTITY(1,1) NOT NULL,
[per_bestyrelse] [tinyint] NULL,
[per_fornavn] [char](20) NULL,
[per_efternavn] [char](20) NULL,
[per_gade] [char](35) NULL,
[per_postnr] [char](15) NULL,
[per_bynavn] [char](20) NULL,
[per_cpr] [char](14) NULL,
[per_cpr_ja_nej] [char](4) NULL,
[per_gruppe] [smallint] NULL,
[per_kategori] [char](20) NULL,
[per_underkat] [char](20) NULL,
[per_timer] [decimal](7, 2) NULL,
[per_faste] [tinyint] NULL,
[per_ansat] [int] NULL,
[per_fratraadt] [int] NULL,
[per_tlf] [char](20) NULL,
[per_mobil] [char](20) NULL,
[per_e_mail] [char](60) NULL,
[per_hyre] [decimal](7, 2) NULL,
[per_departments_id] [int] NULL,
[per_magtanvendelse] [tinyint] NULL,
[per_color] [int] NULL,
[payscale] [nvarchar](10) NULL,
[weeklyhours] [int] NULL,
[tempstart] [datetime] NULL,
[tempend] [datetime] NULL,
[trainingstart] [datetime] NULL,
[trainingend] [datetime] NULL,
[trainingcoach] [nvarchar](50) NULL,
[onleavestart] [datetime] NULL,
[onleaveend] [datetime] NULL,
[picture] [image] NULL,
[istemp] [bit] NULL,
[isonleave] [bit] NULL,
[isintraining] [bit] NULL,
[filetype] [nvarchar](50) NULL,
[vacationwithpay] [float] NULL,
[vacationwithoutpay] [float] NULL,
[specialvacation] [float] NULL,
[lastvacationyear] [float] NULL,
[lastwithoutpay] [float] NULL,
[lastspecial] [float] NULL,
[initials] [nvarchar](25) NULL,
[positiontypechoice] [tinyint] NULL,
[accountnumber] [nvarchar](50) NULL,
[cpr2] [nvarchar](20) NULL,
[temphasended] [bit] NULL,
[payscaleid] [int] NULL,
[supplementpay] [float] NULL,
[childcaredaysleft] [decimal](7, 2) NULL,
[vacation_as_duty] [tinyint] NULL,
[Person_ID] [numeric](18, 0) NULL,
[CPR_encrypted] [varbinary](256) NULL,
CONSTRAINT [per_vis_personal_id] PRIMARY KEY CLUSTERED
(
[per_personal_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
December 7, 2012 at 6:17 am
Can you be any more specific about the kind of problems it's causing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 7, 2012 at 6:29 am
Try an integrity check on the table.
If that's ok then it could be that the application has the structure of the table from before you updated the column and it needs to be refreshed.
Cursors never.
DTS - only when needed and never to control.
December 7, 2012 at 6:30 am
My real problem is, that i can't be specific.
My program GPF's out of the blue, but unly iin procedures, which has this table open.
On my developement PC, it happens 'Monthly'
On some other PC's, it happens 'Very often'.
Last example was a program, DMC, which i use to script table content into text INSERTS.
It worked on +100 tables, but ended in a loop on this table - And only this table.
So, I am looking for something which is out of the ordinary, something thats known to be troublesome (Like my cloumn name "månedsløn") -
Best regards
Edvard Korsbæk
December 7, 2012 at 6:32 am
Its so, that i am using this program on app 100 different DB's
Some SQL2005, some SQL 2008, and different versions.
But all gives the same set of problems with GPF etc.
Edvard Korsbæk
December 7, 2012 at 8:17 am
Try running this query to see how many rows and how big the table is, could this one table be too big for the client program to handle?
SELECT s.name AS schema_name, t.name AS table_name, i.name AS index_name,
SUM(ps.row_count) as row_count,
SUM(in_row_used_page_count) AS rowused_pages,
SUM(in_row_used_page_count) * 8 AS rowused_KB,
SUM(lob_used_page_count) AS LOBused_pages,
SUM(lob_used_page_count) * 8 AS LOBused_KB,
SUM(row_overflow_used_page_count) AS overflow_pages,
SUM(row_overflow_used_page_count) * 8 AS overflow_KB
FROM sys.tables t
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.dm_db_partition_stats ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.index_id IN (0,1)
AND t.name = 'PERSONAL'
GROUP BY s.name, t.name, i.name
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply