Relability? [part 2, 0xC0202093 error]

In my previous thread, we established that my system does not have SP1 applied. Apparently packaging of SQL Server SP1 is not so easy and while SQL Engine was updated SSIS is still in RTM.

Anyway, after months(!) of running we hit following:

An error occurred while initializing the flat file parser.
The PrimeOutput method on component "Flat File Source" (27679) returned error code 0xC0202093. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Thread "SourceThread0" has exited with error code 0xC0047038.
Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Thread "WorkThread0" has exited with error code 0xC0047039.

I could not find anything regarding 0xC0202093, but re-starting SQL & IS services cured the problem. I am putting it here, so others learn from our experience. Probably re-starting IS engine would be enough.

Someone may say that re-starting of the service is a piece of cake. In the developers enviroment - it is. In the enterprise production system, where corporate rules are applied - it is a nightmare: it has to be approved, it has to be documented ("Can you prove that re-starting will cure the problem?") and it has to be done by a DBA - and that together cost real money.

Googling 0xC0202093 comes up with hits, (http://wiki.sqlis.com/default.aspx/SQLISWiki/0xC0202093.html), and the base error message is "An error occurred while initializing the flat file parser." So it relates to the Flat File source you are using, but above that no more infomation is documented, so not entirely helpfull.

Is this a specific file that causes the issue, or perhaps the size of the file? To be honest I would be tempted to open a PSS case, that is just a poor message, and unless there are any other messages from that packag ethat hint at why, it seems like a serious issue. The parser should not just stop working.

|||

Is this a specific file that causes the issue, or perhaps the size of the file? To be honest I would be tempted to open a PSS case, that is just a poor message, and unless there are any other messages from that packag ethat hint at why, it seems like a serious issue. The parser should not just stop working.

Thanks. It is not related to the size (the one of the files has 200 2-column rows) and all packages using flat file connection were affected. Unfortunatelly, I cannot reproduce the problem. Another thing was that files were on the server files. The same packages, reading from the local driver were fine. [We use dynamic connections where file location is read from the database]. So it might be that flat file parser had problems with networking - don't know.

sql

Re-Installation problem (prev installations not removed completely)

I have installed SQL server 2005 on Win SERVER 2003, then uninstalled and then re-installed but I have found out that the previous instances are still listed and the the previous collation too (I have changed reg setting and and language option re-installation).

It looks like that removing program does not remove completely every object, probably registry values

Do you know how to perform a good uninstallation removing everything (add/remove program does not work properly as written before) ?

Any help will be very appreciated.

Thank

After uninstalling SQL Server, check the ARP to confirm that the SQL Server Native Client was also removed. There are a few other support-type components that remain on your box from time to time. This is one of them.

Thanks,
Sam Lester (MSFT)|||

Because some modules are shared among instances on the same machine, I will not be able to unstall the shared modules, like SQL Native Client,etc.

If I had installed several instances of SQL 2005, will I get problems if I uninstall and reinstall some instances later?

NP

reindexing, rebuilding, etc

hi, new to sql server (2000)..in my previous database, we had to do
maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
database is relatively small (8G)but the reindexing in sql server is done in
minutes, whereas in our old system it used to take up to 4 hrs...Is sql
server that fast or am I doing something wrong?
also, what routines are recommended for regular maintenance and should I do
individually or create a plan?
Thanks,
GerryUse the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
first.
As for the other things:
Backup schedule and types is determined by the owner of the system.
I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as
I do db backup. This
was, if I get a corruption, I can do a log backup, restore the most recent d
b backup and all
subsequent log backups and the corruption probably not re-introduced by rest
oring the log backup.
Your mileage will vary, so just put in some common sense into planning these
things.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done
in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I d
o
> individually or create a plan?
> Thanks,
> Gerry|||Thank you very much.
"Tibor Karaszi" wrote:

> Use the code found in Books Online, DBCC SHOWCONTIG for reindexing, and re
ad
> http://www.microsoft.com/technet/pr...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
>|||Gerry M wrote:
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done
in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I d
o
> individually or create a plan?
> Thanks,
> Gerry
Hi Gerry... I have a script that I run that checks the fragmentation of
all my indexes, and rebuilds those that are badly fragmented. My site
is currently "in limbo", but you can obtain the script from Google's
cache. Search for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded"
Tracy McKibben
MCDBA
http://www.realsqlguy.com

reindexing, rebuilding, etc

hi, new to sql server (2000)..in my previous database, we had to do
maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
database is relatively small (8G)but the reindexing in sql server is done in
minutes, whereas in our old system it used to take up to 4 hrs...Is sql
server that fast or am I doing something wrong?
also, what routines are recommended for regular maintenance and should I do
individually or create a plan?
Thanks,
GerryUse the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx first.
As for the other things:
Backup schedule and types is determined by the owner of the system.
I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as I do db backup. This
was, if I get a corruption, I can do a log backup, restore the most recent db backup and all
subsequent log backups and the corruption probably not re-introduced by restoring the log backup.
Your mileage will vary, so just put in some common sense into planning these things.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I do
> individually or create a plan?
> Thanks,
> Gerry|||Thank you very much.
"Tibor Karaszi" wrote:
> Use the code found in Books Online, DBCC SHOWCONTIG for reindexing, and read
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx first.
> As for the other things:
> Backup schedule and types is determined by the owner of the system.
> I prefer to do DBCC CHECKDB as often as I can. I prefer to do it as often as I do db backup. This
> was, if I get a corruption, I can do a log backup, restore the most recent db backup and all
> subsequent log backups and the corruption probably not re-introduced by restoring the log backup.
> Your mileage will vary, so just put in some common sense into planning these things.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:8295C0D3-477A-454C-92E9-51EE95D9E435@.microsoft.com...
> > hi, new to sql server (2000)..in my previous database, we had to do
> > maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> > database is relatively small (8G)but the reindexing in sql server is done in
> > minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> > server that fast or am I doing something wrong?
> >
> > also, what routines are recommended for regular maintenance and should I do
> > individually or create a plan?
> >
> > Thanks,
> > Gerry
>|||Gerry M wrote:
> hi, new to sql server (2000)..in my previous database, we had to do
> maintenance on a regualr basis...reindexing tables, rebuilding, etc...our
> database is relatively small (8G)but the reindexing in sql server is done in
> minutes, whereas in our old system it used to take up to 4 hrs...Is sql
> server that fast or am I doing something wrong?
> also, what routines are recommended for regular maintenance and should I do
> individually or create a plan?
> Thanks,
> Gerry
Hi Gerry... I have a script that I run that checks the fragmentation of
all my indexes, and rebuilds those that are badly fragmented. My site
is currently "in limbo", but you can obtain the script from Google's
cache. Search for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded"
Tracy McKibben
MCDBA
http://www.realsqlguy.com

ReIndex with fillfactor of 100

Our results of reindexing with a fillfactor of 100% for a read only
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank You
Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.
|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegr oups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>

ReIndex with fillfactor of 100

Our results of reindexing with a fillfactor of 100% for a read only
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778
]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262
]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank YouOoops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegroups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>

ReIndex with fillfactor of 100

Our results of reindexing with a fillfactor of 100% for a read only
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank YouOoops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
--
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegroups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>