PAE problem question (KB834628)

This question is related to
http://support.microsoft.com/defaul...b;en-us;834628.
We had a database crash about a month ago that was non-recoverable. This was
a subscriber database run in a continuous transactional environment. Server
is W2K3, /PAE and /3GB, 8GB ram, SQL Server gets 6655mb.
I ran a dbcc checkdb and came back with a large number of errors starting
with Errors 1 (see "Errors 1" below).
There are quite few more errors. The checkdb ends with
--CHECKDB found 0 allocation errors and 150 consistency errors in
database 'DB_rpt1'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (DB_rpt1 ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.--
---
At the time we saw absolutely no errors at ANY level (SAN, OS, etc) except
when the SQL Server errorlog start screaming about --
2004-02-18 16:48:32.10 spid52 Error: 823, Severity: 24, State: 2
2004-02-18 16:48:32.10 spid52 I/O error (bad page ID) detected during
read at offset 0x00000004a66000 in file
'w:\DBdata\DB_rpt1_data2.NDF'..--
We had an HBA hardware failure 6 days later, we semi-attributed the
corruption to the bad HBA.
Could this be the 834628 problem? If so how can I tell - the KB is fairly
non-specific about how to spot the error.
Also would the fn_dblog function be of any use and what would I look for -
the article seems to say that one line would be all zero's but this isn't
that clear.
---
Errors 1 --(this is the very beginning of the checkdb
dump)--
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (4:9523). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (4:9524). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (4:9525). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (4:9526). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (4:9527). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (4:9528). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (4:9529). The PageId in the
page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (4:9530). The PageId in the
page header = (0:0).
DBCC results for 'DB_rpt1'.
CHECKDB found 0 allocation errors and 8 consistency errors not associated
with any single object.That is very unlikely a PAE problem. Errors like that are almost always
attributable to bad blocks on disk. With an HBA on the blink, it is likely
that some bad data got written to the disk array, possibly where data was
deleted since the pages are not owned by a specific object.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
news:uHA$AJOEEHA.3472@.TK2MSFTNGP09.phx.gbl...
> This question is related to
> http://support.microsoft.com/defaul...b;en-us;834628.
> We had a database crash about a month ago that was non-recoverable. This
was
> a subscriber database run in a continuous transactional environment.
Server
> is W2K3, /PAE and /3GB, 8GB ram, SQL Server gets 6655mb.
> I ran a dbcc checkdb and came back with a large number of errors starting
> with Errors 1 (see "Errors 1" below).
> There are quite few more errors. The checkdb ends with
> --CHECKDB found 0 allocation errors and 150 consistency errors in
> database 'DB_rpt1'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (DB_rpt1 ).
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.--
> ---
> At the time we saw absolutely no errors at ANY level (SAN, OS, etc) except
> when the SQL Server errorlog start screaming about --
> 2004-02-18 16:48:32.10 spid52 Error: 823, Severity: 24, State: 2
> 2004-02-18 16:48:32.10 spid52 I/O error (bad page ID) detected during
> read at offset 0x00000004a66000 in file
> 'w:\DBdata\DB_rpt1_data2.NDF'..--
> We had an HBA hardware failure 6 days later, we semi-attributed the
> corruption to the bad HBA.
> Could this be the 834628 problem? If so how can I tell - the KB is fairly
> non-specific about how to spot the error.
> Also would the fn_dblog function be of any use and what would I look for -
> the article seems to say that one line would be all zero's but this isn't
> that clear.
> ---
> Errors 1 --(this is the very beginning of the checkdb
> dump)--
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (4:9523). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (4:9524). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (4:9525). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (4:9526). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (4:9527). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (4:9528). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (4:9529). The PageId in the
> page header = (0:0).
> Server: Msg 8909, Level 16, State 1, Line 1
> Table error: Object ID 0, index ID 0, page ID (4:9530). The PageId in the
> page header = (0:0).
> DBCC results for 'DB_rpt1'.
> CHECKDB found 0 allocation errors and 8 consistency errors not associated
> with any single object.
>|||My original theory still looks good - that the hardware was on the way out
and "glitched" writing bad data to disk. The article wasn't very clear on
cause and effect - so I had to ask.
Thank You !
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:u3mbNcOEEHA.1368@.TK2MSFTNGP11.phx.gbl...
> That is very unlikely a PAE problem. Errors like that are almost always
> attributable to bad blocks on disk. With an HBA on the blink, it is
likely
> that some bad data got written to the disk array, possibly where data was
> deleted since the pages are not owned by a specific object.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "frankm" <frank@.nxspxm.mallardcentral.com> wrote in message
> news:uHA$AJOEEHA.3472@.TK2MSFTNGP09.phx.gbl...
> was
> Server
starting
by
except
fairly
for -
isn't
the
the
the
the
the
the
the
the
associated
>|||Hi Frankm,
I agreed with Geoff N that it seems this problem is not mostly related the
problem in KB834628. Based on my experience, 823 errors are mostly hardware
related. Specially this error 823 on bad page ID is raised when the page ID
is invalid. When the error occurred, you can try to perform the following
steps to troubleshoot.
1. Check for error in the system and application from NT even viewer
2. Run DBCC checkdb on databse
a. IF it's sql 7.0, use "-y823" to start sql server and capture the dump
when the error is raised again
b. IF it's sql 2k, user DBCC Dumptrigger('SET', 823) to enable the dump
(this will NOT require the sql to be recycled!)
When the error 823 happens, it will catch the dump on it in sql errorlog,
3. Please also contact hardware vendor to run hardware diagnostic.
If the problem is very complex and you are unable to narrow down, you can
contact our PSS with the above information to continue working with a
dedicated Support Professional.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Thanks...
I have not used the DumpTrigger before - sounds really useful...
As I mentioned - there were absolutely NO errors at any other level - SAN,
OS, hardware -nothing.
The SAN people were in and could find no problem, keep in mind that the lun
was spread across 54 disks.
So the likelihood of it being a SAN problem was remote at best.
I had to ask the question -- Just in case it may be related...
Thanks for your help...
""Yuan Shao"" <v-yshao@.online.microsoft.com> wrote in message
news:PgR3kzUEEHA.616@.cpmsftngxa06.phx.gbl...
> Hi Frankm,
> I agreed with Geoff N that it seems this problem is not mostly related the
> problem in KB834628. Based on my experience, 823 errors are mostly
hardware
> related. Specially this error 823 on bad page ID is raised when the page
ID
> is invalid. When the error occurred, you can try to perform the following
> steps to troubleshoot.
> 1. Check for error in the system and application from NT even viewer
> 2. Run DBCC checkdb on databse
> a. IF it's sql 7.0, use "-y823" to start sql server and capture the dump
> when the error is raised again
> b. IF it's sql 2k, user DBCC Dumptrigger('SET', 823) to enable the dump
> (this will NOT require the sql to be recycled!)
> When the error 823 happens, it will catch the dump on it in sql errorlog,
> 3. Please also contact hardware vendor to run hardware diagnostic.
> If the problem is very complex and you are unable to narrow down, you can
> contact our PSS with the above information to continue working with a
> dedicated Support Professional.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>

pad the generated flat file with 0

Hi Guys,

Related to my last questions on SSIS work i'm doing, Is there a way to pad 0 on my generated flat file dynamically.

I'm getting the data from 1 table and then generating the file. The file i need to generate would have data at the desired location as the file is being used by another system.

Depedning on the data I want to put the padding of "0" and "3" inc certain fields. How am i suppose to do it.

Apart from this I would need to megre 2 or more column and before the merge do an airthmatic operation.

What would be the best component to use script component or derived column?

Ta

Gemma

Gemma,

Not sure if this will help or not, but it is how i've padded zero's before...

Use a derived column:

@.LENGTH is the length you would like to pad to)

@.MyStr is the character expression or string you would like to pad

SUBSTRING(REPLICATE("0", @.LENGTH), 1, (@.LENGTH + 1) - LEN(TRIM(@.MyStr))) + TRIM(@.MyStr)

|||

Here's another - using the same variables as above:

RIGHT(REPLICATE("0",@.Length) + @.MyStr, @.Length)

|||

Hi Guys,

I don't know whether this is completely what I asked.

I would need to pad the values dynamically. By this I mean the padding would be done depending on the actual length of data in the columns. So when the file is generated I would have to check each column and if certain column's data length is less then fixed length then pad it with zero.

Other then that how would I suppose to use these variables?

That is why I asked Script or derived column which one.

Just a quick note, the file I'm generating is going to be fixed width as i guess it and i'm generating a dummy file but with fixed width it's not putting the records in each line. Instead file is having all the records at the same line. Why? I've already checked. There are 2 columns which i'm writing. The first i'm putting in as 20 and the 2nd one as 35 width still i'm getting this problem.

Ta

Gamma

|||

The above expressions do exactly what you asked. The variables are simply placeholders in the code. If you know that your column is 20 wide and you want it to pad out the non filled data to 20 places (to the right) you would simply put your column name in the @.MyStr and 20 in the @.Length.

for example we have a membership number that needs to be padded out to 9 digits. The column's name is Member.

SUBSTRING(REPLICATE("0", 9), 1, (9+ 1) - LEN(TRIM([Member]))) + TRIM([Member])

So, what that does is takes a string of 9 zero's (Replicate("0", 9) and substrings out the 1, to 9 - trimmed column length and then adds the trimmed column information.

i.e. member number of "12345 " would yeild substring(replicate("0", 9), 1, 10 - 5) + "12345" > "0000" + "12345" > "000012345"

You would need to set this up for each column seperately.

hope this helps.

sql

Packages related with its jobs...

Hi,

I have diferent jobs scheduled in the system but I cant find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)

DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28

I would like to know the Package name related to.

Any help would be very appreciated.

RegardsHi,

I have diferent jobs scheduled in the system but I cant find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)

DTSRun /~Z0x9D852D31537078274085C85BE05756CCE0CA78671EC12A 4BDFFEC4E5E6017E4841EE5F41C492CCAA7F5746CA894011BB 376479B6E679EC3C6045C328D1EF1CDA7CF28B6EEFE9DFE923 7DF5662AE09BD6215C35AA4121BD2DE4433C7BABEE42EC87E7 0F47EA7C01FB44CB28

I would like to know the Package name related to.

Any help would be very appreciated.

Regards

Try:

select * from sysdtspackages where id = '[package id]'

[edit: d'oh, I should read SQL BOL first! The /~Z is an indicator that the string that follows is encrypted. I do not know SQL's encrption algorithm so I'm afraid I can't really help here.]|||thanks. I have tested the sysdtspackages table but I cant find any useful information to relate a job with the package that is running.

Meanwhile I will try SQL BOL.

Regards|||Is the dts packagae writing a log file in the logs folder of the mssql folder? If not, modify the dts package to write the log. Then, aftter the dts packagees execute, check the log. It will hold the clear text name of the package and the results of each step in the package. You should be able to correlate the run time and the associated package. And if you have to delete old logs, the dts package will recreate them on the next execution.

Package Time Outs

Okay, we have are running our Master Package (and therefore all related Child packages) through a .bat file. The .bat file is scripted using the following logic for an entire month of daily runs:

Code Snippet

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SET \Package.Variables[ReportingDate].Value;"2/01/2007" > E:\ETL\ErrorLogs\Processing\etl_20070201log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
mkdir E:\ETL\ErrorLogs\Archive\20070201
move E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070201

DTExec /FILE E:\ETL\FinancialDataMart\Master.dtsx /DECRYPT masterpwd /SU /SET \Package.Variables[ReportingDate].Value;"2/02/2007" > E:\ETL\ErrorLogs\Processing\etl_20070202log.txt
IF NOT %ERRORLEVEL%==0 GOTO ERROR%ERRORLEVEL%
mkdir E:\ETL\ErrorLogs\Archive\20070202
move E:\ETL\ErrorLogs\Processing\*.txt E:\ETL\ErrorLogs\Archive\20070202

etc...

Generally it takes about 40-45 minutes to run one days worth of data. However, we have found unpredictable instances where the job will take 3 hours or even 6 hours and appear to hang....

The weirdness sets in when we kill the job and rerun it. In all instances of a rerun, the job will execute in the normal 40-45 minute time frame. Obviously, we would like to institute some sort of logging, monitoring and error handling....including if need be a method to timeout a process and restart it.

I am reviewing the WMI (Windows Management Instrumentation) Task but I'm not entirely convinced that it's the right tool for the job.

Questions:

Has anyone else experienced the type of processing behavior that I described? Has anyone been successful at using WMI or another process to monitor and timeout packages? If so, are there sample packages or a good tutorial that maps it out? Unrelated to this issue, we also have instances incomplete processing logs. The logs don't finish writing and the weird part is that they all end at the same point, does anyone have experience with incomplete job logs?:

Code Snippet

Progress: 2007-06-20 12:46:49.87
Source: Update factFinancial Data Flow
Cleanup: 11% complete

Thanks in advance!

Sounds like you're encountering some sort of deadlock. It's not clear to me whether or not you've enabled logging in all the packages; have you?

Have you installed SP2? We added some more logging in SP2 around calls to external databases.

You shouldn't need to kill the packages, but if you really, really want to go that way, you could use a Execute Process tasks (calling DTExec) instead of the Execute Package tasks, and set the TimeOut TerminateProcessAfterTimeout properties.