RE: Possible Data Integrety Issues???

[allyne]

Hi Everyone,

I have a database That I use for reporting purposes thats on SQL Server 2005 and is used as a Report Server. This database is Updated Nightly so I have a fresh copy of the data from the day before so the database is static.

I have many reports that have always worked and are very stable but now I can run a report, lets say at 9:00 am and the results of the report is incorrect. I can also run the same report an hour later and the results are correct.

Some of these reports are stored procedures that are used with Crystal Report XI.

The others I run straight from the Query Analyzer. It doesn't seem to matter if its Crystal reports or If I run them from the Query Analyzer. The biggiest problem is that it's very speratic and doesn't happened all the time.

I also ran just a Select * from Table and every once in a great while I'll get bad results.

Any clue as to what might be causing this?

thanks for all your help!

 

[calico]

Hi,

How big is you DB ?
How long does it take to the job to complete ?
May be the job is not "completed" early in the morning ?

May be some problem with your store-proc (ex: "execution plan", "procedure cache", etc)

Did you check if your DB are corrupted (both DB) ?

May be problem with your CR reports ?

When a problem is very speratic and doesn't happened all the time, it is very difficult to find the problem.

Can you tell me more info ?

Calico

 

[allyne]

Hello,

Thanks for your response! The copy of the database that gets refreshed (NGProd). each night is 186 Gigs and I have 47 gigs of space available.

I have another database on the same server that I use for my reports which is called NGReports. I use this database for all my stored procedures which uses that tables in NGProd. So for example I have a stored procedure that looks something like this (simplified version). I had a request for just this part of the data and was getting inconsistent data (Only happened once so far). This report is an example of one that I have ran straight from the report server in query analyser and got wrong results the first time and I ran it again a few minutes later and got correct results. I also created a crystal report just to see what would happen and so far I am getting correct results.

This is seems to be happening more and more even with report that have always been correct.

Can you tell me how to check for DB corruption? I usually just write reports but I'm the only Database person here.

ALTER Procedure [dbo].DiabetesTemplateUtilization_Billing] as

Select distinct p.Person_Nbr, p.Last_Name,p.First_Name,lm.Location_Name,pe.Enc_Nbr,
Convert(Varchar,d.EncounterDate,101)as EncDate,
Provider =
Case
When pm.[Description]like 'Nurse%'
Then 'RNs'
Else pm.[Description]
End,
Convert(Varchar,HGA1Cdate,101) as HGA1CDate,
HGA1CPercent

from NGProd.dbo.Diabetes_Flwsheet_ d
Join NGProd.dbo.person p on p.Person_ID = d.Person_ID
Join NGProd.dbo.Patient_Encounter pe on pe.Person_ID = d.Person_ID and pe.Enc_Id = d.Enc_Id
Join NGProd.dbo.Provider_Mstr pm on pm.Provider_ID = pe.rendering_Provider_ID
Join NGProd.dbo.Location_Mstr lm on lm.Location_ID = pe.Location_ID
Where HGA1CStatus = 'Completed'
and pm.[Description] not like 'Medical Asst%'
and lm.Location_Name not like '%Dental%'
and Convert(Varchar,Convert(DateTime,d.EncounterDate),112) between '20070101' and '20080430'
order by Location_Name,p.Last_Name, p.First_Name


Thanks for your help!

 

[calico]

Hi,

186 + 47 = 233 gigs is a very big database

How database is refreshed every night ?
- Backup/Restore ?
- Replication ?
- Other method ?

At what time the job starts in the night and how long does it take to the job to complete ?
- 2 hours ?
- 12 hours ?

It look like the job is not "completed" early in the morning ?

Does the problem only appear in the morning ?
Does the problem appear in the afternoon ?

We must eliminate all the possilities one by one.

----------------------------------------------------------
If you want to check if the database is corrupted and you are not a DBA, I suggest to you to backup the database and restore it on an other machine (TEST machine). On the TEST machine, execute this commande with Query Analyzer:

use NGProd
go
DBCC CheckDB
go
---------------------------------------------------------

Calico

 

[SQLBill]

You need to answer the most important part of Calico's response. How long does the restore/refresh take?

I'll bet there isn't any corruption. It's just that the restore/refresh hasn't finished by the time you run your report.

How do you do the restore/refresh? Is it by a scheduled job? If so, open SSMS, expand SQL Server Agent. Expand Jobs. Right Click on the job and select View Job History. See how long it took each time. Add the duration to the time the job starts and see what time it ended.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[allyne]

Sorry I haven't gotten back to you sooner. I've been out of town. The answer to this question is there was a major software upgrade that was causing the problems. I'm currently up and running!!!

Thanks for all your time and help.

 

[calico]

Hi,

Thank you for the news

By the way, it is important that "your guys" informe you when they do things (ex. Upgrade, maintenance, etc) that can affect your work.

A +

Calico