This table is causing a lot of problems. Any suggestions of why its so?

  • 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]

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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.

  • 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

  • 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

  • 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