Hi,
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
Thanks
The main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks
|||So Buffer Cache is not reliable if you have 2000 SP2?
>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to[vbcol=seagreen]
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
something
>
>.
>
sql
Showing posts with label advanced. Show all posts
Showing posts with label advanced. Show all posts
Friday, March 23, 2012
Interesting question about memory usage - standard edition
Hi,
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
ThanksThe main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks|||So Buffer Cache is not reliable if you have 2000 SP2'
>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
>> Hi,
>> I have sql server 2000 standard edition (windows 2000
>> server - NOT advanced) on the server which has 2560MB of
>> memory. This edition has restriction of 2048MB of memory
>> it can use (max memory parameter set to this value).
>> I'm making performance analysis of whether we need to
>> upgrade memory on this server (and use /awe) - we're
>> about to add some additional load to the system.
>> I collected statistics using performance monitor. Among
>> many counters, I was monitoring Available Memory (MB).
>> This value varies from 325 to 660.
>> Given that 512MB of memory is totally invisible to the
>> sql server, does this mean that we're hitting the wall
>> regarding memory for sql server? I also noticed
something
>> strange, not aligned with bol, that sql server doesn't
>> release memory at all, because even if the server is
>> idle, available memory doesn't grow higher than 660MB
>> (there is nothing other than sql server that uses this
>> memory - dedicated database server)... Since performance
>> monitor is in this case useless (or not?), how can you
>> determine how much memory database engine really
>> uses/needs?
>> Thanks
>
>.
>
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
ThanksThe main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks|||So Buffer Cache is not reliable if you have 2000 SP2'
>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
>> Hi,
>> I have sql server 2000 standard edition (windows 2000
>> server - NOT advanced) on the server which has 2560MB of
>> memory. This edition has restriction of 2048MB of memory
>> it can use (max memory parameter set to this value).
>> I'm making performance analysis of whether we need to
>> upgrade memory on this server (and use /awe) - we're
>> about to add some additional load to the system.
>> I collected statistics using performance monitor. Among
>> many counters, I was monitoring Available Memory (MB).
>> This value varies from 325 to 660.
>> Given that 512MB of memory is totally invisible to the
>> sql server, does this mean that we're hitting the wall
>> regarding memory for sql server? I also noticed
something
>> strange, not aligned with bol, that sql server doesn't
>> release memory at all, because even if the server is
>> idle, available memory doesn't grow higher than 660MB
>> (there is nothing other than sql server that uses this
>> memory - dedicated database server)... Since performance
>> monitor is in this case useless (or not?), how can you
>> determine how much memory database engine really
>> uses/needs?
>> Thanks
>
>.
>
Wednesday, March 21, 2012
Interesting question about memory usage - standard edition
Hi,
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
ThanksThe main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks|||So Buffer Cache is not reliable if you have 2000 SP2'
>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
something[vbcol=seagreen]
>
>.
>
I have sql server 2000 standard edition (windows 2000
server - NOT advanced) on the server which has 2560MB of
memory. This edition has restriction of 2048MB of memory
it can use (max memory parameter set to this value).
I'm making performance analysis of whether we need to
upgrade memory on this server (and use /awe) - we're
about to add some additional load to the system.
I collected statistics using performance monitor. Among
many counters, I was monitoring Available Memory (MB).
This value varies from 325 to 660.
Given that 512MB of memory is totally invisible to the
sql server, does this mean that we're hitting the wall
regarding memory for sql server? I also noticed something
strange, not aligned with bol, that sql server doesn't
release memory at all, because even if the server is
idle, available memory doesn't grow higher than 660MB
(there is nothing other than sql server that uses this
memory - dedicated database server)... Since performance
monitor is in this case useless (or not?), how can you
determine how much memory database engine really
uses/needs?
ThanksThe main things to look at are still Pages/sec (should be near 0 most of the
time) and Buffer Cache Hit ratio (>95% but depends on what type of workload
and you really need to be on SP3 for the number to mean anything). SQL
Server won't give back memory unless there is competition for it so its
perfectly normal for the Available Memory to stay at 660MB. Page life
expectancy,Memory grants outstanding and free buffers can also help to
narrow down memory contention
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Peja" <peja@.sympatico.ca> wrote in message
news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
> Hi,
> I have sql server 2000 standard edition (windows 2000
> server - NOT advanced) on the server which has 2560MB of
> memory. This edition has restriction of 2048MB of memory
> it can use (max memory parameter set to this value).
> I'm making performance analysis of whether we need to
> upgrade memory on this server (and use /awe) - we're
> about to add some additional load to the system.
> I collected statistics using performance monitor. Among
> many counters, I was monitoring Available Memory (MB).
> This value varies from 325 to 660.
> Given that 512MB of memory is totally invisible to the
> sql server, does this mean that we're hitting the wall
> regarding memory for sql server? I also noticed something
> strange, not aligned with bol, that sql server doesn't
> release memory at all, because even if the server is
> idle, available memory doesn't grow higher than 660MB
> (there is nothing other than sql server that uses this
> memory - dedicated database server)... Since performance
> monitor is in this case useless (or not?), how can you
> determine how much memory database engine really
> uses/needs?
> Thanks|||So Buffer Cache is not reliable if you have 2000 SP2'
>--Original Message--
>The main things to look at are still Pages/sec (should be
near 0 most of the
>time) and Buffer Cache Hit ratio (>95% but depends on
what type of workload
>and you really need to be on SP3 for the number to mean
anything). SQL
>Server won't give back memory unless there is competition
for it so its
>perfectly normal for the Available Memory to stay at
660MB. Page life
>expectancy,Memory grants outstanding and free buffers can
also help to
>narrow down memory contention
>--
>HTH
>Jasper Smith (SQL Server MVP)
>I support PASS - the definitive, global
>community for SQL Server professionals -
>http://www.sqlpass.org
>
>"Peja" <peja@.sympatico.ca> wrote in message
>news:0f4b01c42621$2d719050$a601280a@.phx.gbl...
something[vbcol=seagreen]
>
>.
>
Wednesday, March 7, 2012
Integrity Check Errors
Hello,
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I hav
e
selected the option to check integrity before doing backup. If anything fail
s,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
========================================
====================================
====
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
========================================
====================================
====
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
Message posted via http://www.droptable.comThe most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ========================================
==================================
======
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ========================================
==================================
======
> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:[vbcol=seagreen]
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>
>[quoted text clipped - 59 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200511/1|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:[vbcol=seagreen]
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores t
o
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likel
y
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might wan
t
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200511/1|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on on
e
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOU
T
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via droptable.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...green">
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on
one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption cam
e
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITH
OUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200512/1|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
Message posted via http://www.droptable.com|||Wow, it worked like a charm....
How do I reset it back to multi-user after I finish repairing it ?
Thanks a million.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I hav
e
selected the option to check integrity before doing backup. If anything fail
s,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
========================================
====================================
====
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
========================================
====================================
====
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
Message posted via http://www.droptable.comThe most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ========================================
==================================
======
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ========================================
==================================
======
> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:[vbcol=seagreen]
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>
>[quoted text clipped - 59 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200511/1|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:[vbcol=seagreen]
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores t
o
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likel
y
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might wan
t
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200511/1|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200511/1|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on on
e
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOU
T
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via droptable.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...green">
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on
one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption cam
e
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITH
OUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200512/1|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
Message posted via http://www.droptable.com|||Wow, it worked like a charm....
How do I reset it back to multi-user after I finish repairing it ?
Thanks a million.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200512/1
Integrity Check Errors
Hello,
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I have
selected the option to check integrity before doing backup. If anything fails,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
================================================================================ Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
================================================================================
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
--
Message posted via http://www.sqlmonster.comThe most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ================================================================================> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ================================================================================> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>> Hello,
>[quoted text clipped - 59 lines]
>> Sameer.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
>>The most likely cause of db integrity issues (especially recurring ones)
>>is
>>bad hardware. I would run some intensive hardware tests to find the root
>>cause.
>> Hello,
>>[quoted text clipped - 59 lines]
>> Sameer.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores to
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likely
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might want
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>> Thanks Andrew,
>[quoted text clipped - 17 lines]
>> Sameer.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
>>It still sounds like hardware related. Do you have a UPS and are you
>>confident of it's condition? But I just re-read your original post and
>>missed the part in which you were using Repair Allow data loss. That does
>>exactly what it says. So to fix the problems it has to destroy or loose
>>some data. The problem is that you don't know what data you loose and
>>your
>>data integrity is not lost. Your best bet for a situation such as that is
>>to backup from the last known good backup. Since you are using simple
>>mode
>>you can never do a restore to a point in time. That limits your restores
>>to
>>the last full or differential backup. If you only do that once a night
>>you
>>can loose up to 24 hours worth of work. You should rethink your backup
>>strategy. The fact that you have been doing this for some time now means
>>that you probably do not have a valid Full backup anymore. They most
>>likely
>>all have some form of corruption in them. Either from what is causing the
>>issues or from you doing the repairs and allowing data loss. You might
>>want
>>to consider creating a new db and copying the data out from one and back
>>into another with referential integrity constraints to see where you may
>>have bad data.
>> Thanks Andrew,
>>[quoted text clipped - 17 lines]
>> Sameer.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on one
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOUT
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
--
Message posted via http://www.sqlmonster.com|||ALTER DATABASE dbname SET SINGLE_USER WITH ROILLBACK IMMEDIATE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:5863a80856c4f@.uwe...
> While the database is online and being used, I would like to stop all future
> users from connecting from it, kill all current users logged-in and then
> change the db to a single user mode to be repaired.
> Could you please elaborate it more ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Wow, it worked like a charm....
How do I reset it back to multi-user after I finish repairing it ?
Thanks a million.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||ALTER DATABASE dbname SET MULTI_USER
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:5863d7195c667@.uwe...
> Wow, it worked like a charm....
> How do I reset it back to multi-user after I finish repairing it ?
> Thanks a million.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||Thanks Mr. Karaszi ... I appreciate it !
Now only if this recurring Integrity errors would vanish away after I replace
the RAM, hopefully ....
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||You are not using a RAM disk by any chance are you?
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on
> one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption
> came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away
> WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||Hi Andrew,
Thanks for coming back....what's a RAM disk ?
We're using a simple Primary Hard Disk being mirrored to a Secondary Hard
Disk using Windows Disk Mirroring functionality.
For Memory, we use 2 X 512 MB chips on 2 dimm slots.
Next plan is to buy 1 X 1GB memory chip and put it in 1 dimm slot and if need
be, buy another one and fill the last dimm slot.
--
Message posted via http://www.sqlmonster.com|||If you don't know then you are most likely not using it<g>. But you are
using software raid vs. hardware. I don't know if I would trust the
software mirroring. Maybe it's OK but that would be my guess. You might
want to look into a lug in card to do the mirroring.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:58683e30c7369@.uwe...
> Hi Andrew,
> Thanks for coming back....what's a RAM disk ?
> We're using a simple Primary Hard Disk being mirrored to a Secondary Hard
> Disk using Windows Disk Mirroring functionality.
> For Memory, we use 2 X 512 MB chips on 2 dimm slots.
> Next plan is to buy 1 X 1GB memory chip and put it in 1 dimm slot and if
> need
> be, buy another one and fill the last dimm slot.
> --
> Message posted via http://www.sqlmonster.com|||1. Unfortunately, Hardware Mirroring is out of question for our small company,
atleast for now.
But just to verify if Software Mirroring is the cause, then I will
temporarily disable it and observe what happens.
2. I usually get corruptions in 2 tables (each are over half-a-million
records). I don't have any additional indexes in those tables apart from the
Primary Key [Id] column.
......2.1 Is it possible that indexes get corrupted for heavy tables ? If
so, how do I safely rebuild indexes
3. The 2 tables I mentioned above, each table gets populated by its own
corresponding Client VB App developed by one of the consultatnts we had hired.
However, even when I do inspect the code and design of the app, I don't seem
to find any flaws.
......3.1 Could those client apps causing the trouble ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||It makes sense that the two most heavily used tables are the ones you see
the most issues with. Since they are changing more than the others there is
more opportunity for issues to arise. But the act of Inserting , Updating
and Deleting too much should not be a concern. Indexes don't get corrupted
after you reach a certain change threshold. I still think the hardware or
now maybe the mirroring software is the root cause. Hardware controllers
that do mirroring these days are extremely inexpensive. Most systems have
the capability built into the mother boards now. I think you will find it
is cheaper than you think and worth exploring.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:586f11a2109af@.uwe...
> 1. Unfortunately, Hardware Mirroring is out of question for our small
> company,
> atleast for now.
> But just to verify if Software Mirroring is the cause, then I will
> temporarily disable it and observe what happens.
>
> 2. I usually get corruptions in 2 tables (each are over half-a-million
> records). I don't have any additional indexes in those tables apart from
> the
> Primary Key [Id] column.
> ......2.1 Is it possible that indexes get corrupted for heavy tables ?
> If
> so, how do I safely rebuild indexes
>
> 3. The 2 tables I mentioned above, each table gets populated by its own
> corresponding Client VB App developed by one of the consultatnts we had
> hired.
> However, even when I do inspect the code and design of the app, I don't
> seem
> to find any flaws.
> ......3.1 Could those client apps causing the trouble ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I have
selected the option to check integrity before doing backup. If anything fails,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
================================================================================ Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
================================================================================
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
--
Message posted via http://www.sqlmonster.comThe most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ================================================================================> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ================================================================================> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.sqlmonster.com|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>> Hello,
>[quoted text clipped - 59 lines]
>> Sameer.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
>>The most likely cause of db integrity issues (especially recurring ones)
>>is
>>bad hardware. I would run some intensive hardware tests to find the root
>>cause.
>> Hello,
>>[quoted text clipped - 59 lines]
>> Sameer.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores to
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likely
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might want
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>> Thanks Andrew,
>[quoted text clipped - 17 lines]
>> Sameer.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
>>It still sounds like hardware related. Do you have a UPS and are you
>>confident of it's condition? But I just re-read your original post and
>>missed the part in which you were using Repair Allow data loss. That does
>>exactly what it says. So to fix the problems it has to destroy or loose
>>some data. The problem is that you don't know what data you loose and
>>your
>>data integrity is not lost. Your best bet for a situation such as that is
>>to backup from the last known good backup. Since you are using simple
>>mode
>>you can never do a restore to a point in time. That limits your restores
>>to
>>the last full or differential backup. If you only do that once a night
>>you
>>can loose up to 24 hours worth of work. You should rethink your backup
>>strategy. The fact that you have been doing this for some time now means
>>that you probably do not have a valid Full backup anymore. They most
>>likely
>>all have some form of corruption in them. Either from what is causing the
>>issues or from you doing the repairs and allowing data loss. You might
>>want
>>to consider creating a new db and copying the data out from one and back
>>into another with referential integrity constraints to see where you may
>>have bad data.
>> Thanks Andrew,
>>[quoted text clipped - 17 lines]
>> Sameer.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200511/1|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on one
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOUT
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
--
Message posted via http://www.sqlmonster.com|||ALTER DATABASE dbname SET SINGLE_USER WITH ROILLBACK IMMEDIATE
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:5863a80856c4f@.uwe...
> While the database is online and being used, I would like to stop all future
> users from connecting from it, kill all current users logged-in and then
> change the db to a single user mode to be repaired.
> Could you please elaborate it more ?
> Thanks.
> --
> Message posted via http://www.sqlmonster.com|||Wow, it worked like a charm....
How do I reset it back to multi-user after I finish repairing it ?
Thanks a million.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||ALTER DATABASE dbname SET MULTI_USER
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message news:5863d7195c667@.uwe...
> Wow, it worked like a charm....
> How do I reset it back to multi-user after I finish repairing it ?
> Thanks a million.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||Thanks Mr. Karaszi ... I appreciate it !
Now only if this recurring Integrity errors would vanish away after I replace
the RAM, hopefully ....
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||You are not using a RAM disk by any chance are you?
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on
> one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption
> came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away
> WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||Hi Andrew,
Thanks for coming back....what's a RAM disk ?
We're using a simple Primary Hard Disk being mirrored to a Secondary Hard
Disk using Windows Disk Mirroring functionality.
For Memory, we use 2 X 512 MB chips on 2 dimm slots.
Next plan is to buy 1 X 1GB memory chip and put it in 1 dimm slot and if need
be, buy another one and fill the last dimm slot.
--
Message posted via http://www.sqlmonster.com|||If you don't know then you are most likely not using it<g>. But you are
using software raid vs. hardware. I don't know if I would trust the
software mirroring. Maybe it's OK but that would be my guess. You might
want to look into a lug in card to do the mirroring.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:58683e30c7369@.uwe...
> Hi Andrew,
> Thanks for coming back....what's a RAM disk ?
> We're using a simple Primary Hard Disk being mirrored to a Secondary Hard
> Disk using Windows Disk Mirroring functionality.
> For Memory, we use 2 X 512 MB chips on 2 dimm slots.
> Next plan is to buy 1 X 1GB memory chip and put it in 1 dimm slot and if
> need
> be, buy another one and fill the last dimm slot.
> --
> Message posted via http://www.sqlmonster.com|||1. Unfortunately, Hardware Mirroring is out of question for our small company,
atleast for now.
But just to verify if Software Mirroring is the cause, then I will
temporarily disable it and observe what happens.
2. I usually get corruptions in 2 tables (each are over half-a-million
records). I don't have any additional indexes in those tables apart from the
Primary Key [Id] column.
......2.1 Is it possible that indexes get corrupted for heavy tables ? If
so, how do I safely rebuild indexes
3. The 2 tables I mentioned above, each table gets populated by its own
corresponding Client VB App developed by one of the consultatnts we had hired.
However, even when I do inspect the code and design of the app, I don't seem
to find any flaws.
......3.1 Could those client apps causing the trouble ?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1|||It makes sense that the two most heavily used tables are the ones you see
the most issues with. Since they are changing more than the others there is
more opportunity for issues to arise. But the act of Inserting , Updating
and Deleting too much should not be a concern. Indexes don't get corrupted
after you reach a certain change threshold. I still think the hardware or
now maybe the mirroring software is the root cause. Hardware controllers
that do mirroring these days are extremely inexpensive. Most systems have
the capability built into the mother boards now. I think you will find it
is cheaper than you think and worth exploring.
--
Andrew J. Kelly SQL MVP
"Sameer via SQLMonster.com" <u4996@.uwe> wrote in message
news:586f11a2109af@.uwe...
> 1. Unfortunately, Hardware Mirroring is out of question for our small
> company,
> atleast for now.
> But just to verify if Software Mirroring is the cause, then I will
> temporarily disable it and observe what happens.
>
> 2. I usually get corruptions in 2 tables (each are over half-a-million
> records). I don't have any additional indexes in those tables apart from
> the
> Primary Key [Id] column.
> ......2.1 Is it possible that indexes get corrupted for heavy tables ?
> If
> so, how do I safely rebuild indexes
>
> 3. The 2 tables I mentioned above, each table gets populated by its own
> corresponding Client VB App developed by one of the consultatnts we had
> hired.
> However, even when I do inspect the code and design of the app, I don't
> seem
> to find any flaws.
> ......3.1 Could those client apps causing the trouble ?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200512/1
Integrity Check Errors
Hello,
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I have
selected the option to check integrity before doing backup. If anything fails,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
================================================== ==============================
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
================================================== ==============================
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
Message posted via http://www.droptable.com
The most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ================================================== ==============================
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ================================================== ==============================
> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com
|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:[vbcol=seagreen]
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>[quoted text clipped - 59 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1
|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200511/1
|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:[vbcol=seagreen]
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores to
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likely
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might want
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1
|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200511/1
|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on one
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOUT
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1
|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via droptable.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200512/1
|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
Message posted via http://www.droptable.com
|||ALTER DATABASE dbname SET SINGLE_USER WITH ROILLBACK IMMEDIATE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via droptable.com" <u4996@.uwe> wrote in message news:5863a80856c4f@.uwe...
> While the database is online and being used, I would like to stop all future
> users from connecting from it, kill all current users logged-in and then
> change the db to a single user mode to be repaired.
> Could you please elaborate it more ?
> Thanks.
> --
> Message posted via http://www.droptable.com
I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
patches.
I use Database Maintenance Plans to backup several of my databases and I have
selected the option to check integrity before doing backup. If anything fails,
I get a net send notification.
This has been working extremely well for a year now but only lately,
Integrity checks reveals errors on random tables in random databases.
This morning I got a notification on a database called 'Planning'.
So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
just to make sure.
The following details were revealed: -
================================================== ==============================
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 1310627712, index ID 7: Page (1:3766) could not be processed. See
other errors for details.
Server: Msg 8944, Level 16, State 1, Line 1
Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
(ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
CHECKDB found 0 allocation errors and 2 consistency errors in table
'WorkTransaction' (object ID 1310627712).
CHECKDB found 0 allocation errors and 2 consistency errors in database
'Planning'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (Planning ).
================================================== ==============================
I have been able to repair these errors by doing the following: -
1. Restrict access to Single User on this particular database.
2. In the Query Analyzer, run the statement :
USE Planning
go
DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH NO_INFOMSGS
this seems to correct the problem.
3. Remove Single User restriction.
However, I have been doing the above procedure 2 - 3 times a day on many
other databases and tables that come up with errors.
I was wondering what could be the root cause of all these sudden errors.
I use Simple Database Backup Recovery Model on all my databases.
Thanks,
Sameer.
Message posted via http://www.droptable.com
The most likely cause of db integrity issues (especially recurring ones) is
bad hardware. I would run some intensive hardware tests to find the root
cause.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57da24b5e8956@.uwe...
> Hello,
> I'm using SQL Server 2000 SP4 on Windows Advanced Server 2000 with latest
> patches.
> I use Database Maintenance Plans to backup several of my databases and I
> have
> selected the option to check integrity before doing backup. If anything
> fails,
> I get a net send notification.
> This has been working extremely well for a year now but only lately,
> Integrity checks reveals errors on random tables in random databases.
> This morning I got a notification on a database called 'Planning'.
> So I ran a DBCC CHECKDB('Planning') WITH NO_INFOMSGS in the Query Analyzer
> just to make sure.
> The following details were revealed: -
> ================================================== ==============================
> Server: Msg 8928, Level 16, State 1, Line 1
> Object ID 1310627712, index ID 7: Page (1:3766) could not be processed.
> See
> other errors for details.
> Server: Msg 8944, Level 16, State 1, Line 1
> Table error: Object ID 1310627712, index ID 7, page (1:3766), row 18. Test
> (ColumnOffsets <= (nextRec - pRec)) failed. Values are 26977 and 22.
> CHECKDB found 0 allocation errors and 2 consistency errors in table
> 'WorkTransaction' (object ID 1310627712).
> CHECKDB found 0 allocation errors and 2 consistency errors in database
> 'Planning'.
> repair_allow_data_loss is the minimum repair level for the errors found by
> DBCC CHECKDB (Planning ).
> ================================================== ==============================
> I have been able to repair these errors by doing the following: -
> 1. Restrict access to Single User on this particular database.
> 2. In the Query Analyzer, run the statement :
> USE Planning
> go
> DBCC CHECKTABLE('WorkTransaction', repair_allow_data_loss) WITH
> NO_INFOMSGS
> this seems to correct the problem.
> 3. Remove Single User restriction.
>
> However, I have been doing the above procedure 2 - 3 times a day on many
> other databases and tables that come up with errors.
> I was wondering what could be the root cause of all these sudden errors.
> I use Simple Database Backup Recovery Model on all my databases.
> Thanks,
> Sameer.
> --
> Message posted via http://www.droptable.com
|||Thanks Andrew,
I forgot to mention that I did a comprehensive hardware diagnostics 2 times
on this past Sunday and did tests especially on Memory like 4 time using the
Dell Diagnostics software and no errors were found.
Should I doubt the results and start changing the hardware components
gradually ?
Andrew J. Kelly wrote:[vbcol=seagreen]
>The most likely cause of db integrity issues (especially recurring ones) is
>bad hardware. I would run some intensive hardware tests to find the root
>cause.
>[quoted text clipped - 59 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1
|||It still sounds like hardware related. Do you have a UPS and are you
confident of it's condition? But I just re-read your original post and
missed the part in which you were using Repair Allow data loss. That does
exactly what it says. So to fix the problems it has to destroy or loose
some data. The problem is that you don't know what data you loose and your
data integrity is not lost. Your best bet for a situation such as that is
to backup from the last known good backup. Since you are using simple mode
you can never do a restore to a point in time. That limits your restores to
the last full or differential backup. If you only do that once a night you
can loose up to 24 hours worth of work. You should rethink your backup
strategy. The fact that you have been doing this for some time now means
that you probably do not have a valid Full backup anymore. They most likely
all have some form of corruption in them. Either from what is causing the
issues or from you doing the repairs and allowing data loss. You might want
to consider creating a new db and copying the data out from one and back
into another with referential integrity constraints to see where you may
have bad data.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57db2f1476231@.uwe...
> Thanks Andrew,
> I forgot to mention that I did a comprehensive hardware diagnostics 2
> times
> on this past Sunday and did tests especially on Memory like 4 time using
> the
> Dell Diagnostics software and no errors were found.
> Should I doubt the results and start changing the hardware components
> gradually ?
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200511/1
|||Thanks Andrew,
1. I will check the UPS tomorrow and keep you posted. I'll also have the
batteries in the UPS replaced as soon as I can.
2. I do a backup every hour.
3. If I create a new database, should I use a different name or preferably
keep the same name as the current one with problems ? If I change to a new
name, that would add a lot of tasks for me to change the name wherever it
exists.....all codes, queries, reports, etc.
4. Given I create a new database, regardless of the issue in point 3 above,
how do I copy the data with referential integrity ? use DTS ? or queries ?
Thanks for your guidance....I highly appreciate it.
Sameer.
Andrew J. Kelly wrote:[vbcol=seagreen]
>It still sounds like hardware related. Do you have a UPS and are you
>confident of it's condition? But I just re-read your original post and
>missed the part in which you were using Repair Allow data loss. That does
>exactly what it says. So to fix the problems it has to destroy or loose
>some data. The problem is that you don't know what data you loose and your
>data integrity is not lost. Your best bet for a situation such as that is
>to backup from the last known good backup. Since you are using simple mode
>you can never do a restore to a point in time. That limits your restores to
>the last full or differential backup. If you only do that once a night you
>can loose up to 24 hours worth of work. You should rethink your backup
>strategy. The fact that you have been doing this for some time now means
>that you probably do not have a valid Full backup anymore. They most likely
>all have some form of corruption in them. Either from what is causing the
>issues or from you doing the repairs and allowing data loss. You might want
>to consider creating a new db and copying the data out from one and back
>into another with referential integrity constraints to see where you may
>have bad data.
>[quoted text clipped - 17 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200511/1
|||3. The new db is just so that you can get the data in a know condition.
Once you have it where you are comfortable with it and want to use it in
production you can rename it after dropping the original.
4. What I meant was that if you have parent child relationships you want to
add RI constraints on the tables in the new db before you add the data.
That way you will know when you go to insert the data which rows are orphans
etc. It really wouldn't matter how you imported the data as long as you do
it in the right order. Parents first then children and so on.
Andrew J. Kelly SQL MVP
"Sameer via droptable.com" <u4996@.uwe> wrote in message
news:57dfb788bfc8e@.uwe...
> Thanks Andrew,
> 1. I will check the UPS tomorrow and keep you posted. I'll also have the
> batteries in the UPS replaced as soon as I can.
> 2. I do a backup every hour.
> 3. If I create a new database, should I use a different name or preferably
> keep the same name as the current one with problems ? If I change to a new
> name, that would add a lot of tasks for me to change the name wherever it
> exists.....all codes, queries, reports, etc.
> 4. Given I create a new database, regardless of the issue in point 3
> above,
> how do I copy the data with referential integrity ? use DTS ? or queries ?
> Thanks for your guidance....I highly appreciate it.
> Sameer.
>
> Andrew J. Kelly wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200511/1
|||Hi Andrew,
To give you latest status ...
1. I replaced the UPS batteries and after 4 days, corruption came back on one
table in the production database.
2. I also defragmented the disk and after a couple of days, corruption came
back.
3. My next step is to replace RAM chips and see what happens...
The weird thing is that when I get alerted through the DB Maintenance
Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
However, when I simply stop/start the SQL Server, the error goes away WITHOUT
me even running a repair on that particular table.
Is there a way to flush all the connected users so that I can set the
database to a single user before repairing it ?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200512/1
|||> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
ALTER DATABASE with the ROLLBACK option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via droptable.com" <u4996@.uwe> wrote in message news:58622451c05f7@.uwe...
> Hi Andrew,
> To give you latest status ...
> 1. I replaced the UPS batteries and after 4 days, corruption came back on one
> table in the production database.
> 2. I also defragmented the disk and after a couple of days, corruption came
> back.
> 3. My next step is to replace RAM chips and see what happens...
> The weird thing is that when I get alerted through the DB Maintenance
> Integrity Check failure, I run a DBCC CHECKDB and it does find errors.
> However, when I simply stop/start the SQL Server, the error goes away WITHOUT
> me even running a repair on that particular table.
> Is there a way to flush all the connected users so that I can set the
> database to a single user before repairing it ?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200512/1
|||While the database is online and being used, I would like to stop all future
users from connecting from it, kill all current users logged-in and then
change the db to a single user mode to be repaired.
Could you please elaborate it more ?
Thanks.
Message posted via http://www.droptable.com
|||ALTER DATABASE dbname SET SINGLE_USER WITH ROILLBACK IMMEDIATE
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sameer via droptable.com" <u4996@.uwe> wrote in message news:5863a80856c4f@.uwe...
> While the database is online and being used, I would like to stop all future
> users from connecting from it, kill all current users logged-in and then
> change the db to a single user mode to be repaired.
> Could you please elaborate it more ?
> Thanks.
> --
> Message posted via http://www.droptable.com
Friday, February 24, 2012
Integration service Express/2005
I finally bit the bullet and uninstalled all of Sql Server 2005 and installed Sql Server 2005 Express both Advanced and the Toolkit. I can get in and see everything locally and my production Great Plains server just fine through Management console. The one thing I can't do is open an integration package to process our credit cards.
It's a pretty simple integration, but when I go to open it in SQL Server Business Intelligence Development Studio, the only thing it shows installed was Reporting services, not Integration services. When I try to open the project it gives me:
"The application for project 'c:\pcard...' is not installed. Make sure the application for the project type(.dtproj) is installed."
I had a problem earlier with the gui not coming up but was resolved when I applied SS2005 SVP 2. Some items wasn't on the same version. I re-applied svp 2 after I installed SS express and still got the above. If I try and 'add' Integration Services, either thru Express or just regular ss2005, it says it's already installed.
Do I have to modify the registry so that it comes up on Business Intelligence Dev Studio? I can't run the integration if I can't open the project.
Hi Pete,
SQL Express does not include SSIS and the version of BIDS that installs with the Express Toolkit does not support opening SSIS packages. You will need to install one of the paid Editions of SQL Server 2005 to get SSIS functonality.
Mike
|||Hi Mike,
when you say Express Toolkit does not support opening SSIS packages, do you mean it does not support saving packages or the schedule package?
I want to know if there is any way I can save packages and do the scheduled jobs strictly without the licensed SQL Server.
thanks in advance.
Mike
|||hi Mike,
as Mike already pointed out, the BIDS version provided for SQLExpress does not allow you to open SSIS package..
BTW, SQLExpress does not provide the SQL Server Agent as well, so you can not even "schedule" package execution, and no "runtime" for SSIS is available.. you could, eventually, use DTS, as a runtime for it is provided at http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
regards
|||Thanks Andrea...
I am still wrestling with SQL servers and I was wondering if you could give me a definite answer...
I am using enterprise manager to connect to SQL 2005 Express for snapshot replication. I've created new publications, and then using EXPRESS I created a local subscription to the publication.
But I could see no changes in my database.. Do you know why this is happening?
I tried to start the distribution agent (multiple publications) but some reason it can't connect to subscribers.
is this because 2000 and express can't connect to each other? or am I doing something wrong?
thanks
|||Hi ,
I understand that the BIDS version installed with SQL Express Toolkit does not allow to open the SSIS packages. Does that mean that we cannot even execute an SSIS package from SQL server Express.
Thanks.
Vigya
|||Hi Vigay,
Right, you can not use any part of SSIS in SQL Express, it isn't there. You can't run packages or anything else.
Mike
Integration service Express/2005
I finally bit the bullet and uninstalled all of Sql Server 2005 and installed Sql Server 2005 Express both Advanced and the Toolkit. I can get in and see everything locally and my production Great Plains server just fine through Management console. The one thing I can't do is open an integration package to process our credit cards.
It's a pretty simple integration, but when I go to open it in SQL Server Business Intelligence Development Studio, the only thing it shows installed was Reporting services, not Integration services. When I try to open the project it gives me:
"The application for project 'c:\pcard...' is not installed. Make sure the application for the project type(.dtproj) is installed."
I had a problem earlier with the gui not coming up but was resolved when I applied SS2005 SVP 2. Some items wasn't on the same version. I re-applied svp 2 after I installed SS express and still got the above. If I try and 'add' Integration Services, either thru Express or just regular ss2005, it says it's already installed.
Do I have to modify the registry so that it comes up on Business Intelligence Dev Studio? I can't run the integration if I can't open the project.
Hi Pete,
SQL Express does not include SSIS and the version of BIDS that installs with the Express Toolkit does not support opening SSIS packages. You will need to install one of the paid Editions of SQL Server 2005 to get SSIS functonality.
Mike
|||Hi Mike,
when you say Express Toolkit does not support opening SSIS packages, do you mean it does not support saving packages or the schedule package?
I want to know if there is any way I can save packages and do the scheduled jobs strictly without the licensed SQL Server.
thanks in advance.
Mike
|||hi Mike,
as Mike already pointed out, the BIDS version provided for SQLExpress does not allow you to open SSIS package..
BTW, SQLExpress does not provide the SQL Server Agent as well, so you can not even "schedule" package execution, and no "runtime" for SSIS is available.. you could, eventually, use DTS, as a runtime for it is provided at http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
regards
|||Thanks Andrea...
I am still wrestling with SQL servers and I was wondering if you could give me a definite answer...
I am using enterprise manager to connect to SQL 2005 Express for snapshot replication. I've created new publications, and then using EXPRESS I created a local subscription to the publication.
But I could see no changes in my database.. Do you know why this is happening?
I tried to start the distribution agent (multiple publications) but some reason it can't connect to subscribers.
is this because 2000 and express can't connect to each other? or am I doing something wrong?
thanks
|||Hi ,
I understand that the BIDS version installed with SQL Express Toolkit does not allow to open the SSIS packages. Does that mean that we cannot even execute an SSIS package from SQL server Express.
Thanks.
Vigya
|||Hi Vigay,
Right, you can not use any part of SSIS in SQL Express, it isn't there. You can't run packages or anything else.
Mike
Integration service Express/2005
I finally bit the bullet and uninstalled all of Sql Server 2005 and installed Sql Server 2005 Express both Advanced and the Toolkit. I can get in and see everything locally and my production Great Plains server just fine through Management console. The one thing I can't do is open an integration package to process our credit cards.
It's a pretty simple integration, but when I go to open it in SQL Server Business Intelligence Development Studio, the only thing it shows installed was Reporting services, not Integration services. When I try to open the project it gives me:
"The application for project 'c:\pcard...' is not installed. Make sure the application for the project type(.dtproj) is installed."
I had a problem earlier with the gui not coming up but was resolved when I applied SS2005 SVP 2. Some items wasn't on the same version. I re-applied svp 2 after I installed SS express and still got the above. If I try and 'add' Integration Services, either thru Express or just regular ss2005, it says it's already installed.
Do I have to modify the registry so that it comes up on Business Intelligence Dev Studio? I can't run the integration if I can't open the project.
Hi Pete,
SQL Express does not include SSIS and the version of BIDS that installs with the Express Toolkit does not support opening SSIS packages. You will need to install one of the paid Editions of SQL Server 2005 to get SSIS functonality.
Mike
|||Hi Mike,
when you say Express Toolkit does not support opening SSIS packages, do you mean it does not support saving packages or the schedule package?
I want to know if there is any way I can save packages and do the scheduled jobs strictly without the licensed SQL Server.
thanks in advance.
Mike
|||hi Mike,
as Mike already pointed out, the BIDS version provided for SQLExpress does not allow you to open SSIS package..
BTW, SQLExpress does not provide the SQL Server Agent as well, so you can not even "schedule" package execution, and no "runtime" for SSIS is available.. you could, eventually, use DTS, as a runtime for it is provided at http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
regards
|||Thanks Andrea...
I am still wrestling with SQL servers and I was wondering if you could give me a definite answer...
I am using enterprise manager to connect to SQL 2005 Express for snapshot replication. I've created new publications, and then using EXPRESS I created a local subscription to the publication.
But I could see no changes in my database.. Do you know why this is happening?
I tried to start the distribution agent (multiple publications) but some reason it can't connect to subscribers.
is this because 2000 and express can't connect to each other? or am I doing something wrong?
thanks
|||Hi ,
I understand that the BIDS version installed with SQL Express Toolkit does not allow to open the SSIS packages. Does that mean that we cannot even execute an SSIS package from SQL server Express.
Thanks.
Vigya
|||Hi Vigay,
Right, you can not use any part of SSIS in SQL Express, it isn't there. You can't run packages or anything else.
Mike
Integration service Express/2005
I finally bit the bullet and uninstalled all of Sql Server 2005 and installed Sql Server 2005 Express both Advanced and the Toolkit. I can get in and see everything locally and my production Great Plains server just fine through Management console. The one thing I can't do is open an integration package to process our credit cards.
It's a pretty simple integration, but when I go to open it in SQL Server Business Intelligence Development Studio, the only thing it shows installed was Reporting services, not Integration services. When I try to open the project it gives me:
"The application for project 'c:\pcard...' is not installed. Make sure the application for the project type(.dtproj) is installed."
I had a problem earlier with the gui not coming up but was resolved when I applied SS2005 SVP 2. Some items wasn't on the same version. I re-applied svp 2 after I installed SS express and still got the above. If I try and 'add' Integration Services, either thru Express or just regular ss2005, it says it's already installed.
Do I have to modify the registry so that it comes up on Business Intelligence Dev Studio? I can't run the integration if I can't open the project.
Hi Pete,
SQL Express does not include SSIS and the version of BIDS that installs with the Express Toolkit does not support opening SSIS packages. You will need to install one of the paid Editions of SQL Server 2005 to get SSIS functonality.
Mike
|||Hi Mike,
when you say Express Toolkit does not support opening SSIS packages, do you mean it does not support saving packages or the schedule package?
I want to know if there is any way I can save packages and do the scheduled jobs strictly without the licensed SQL Server.
thanks in advance.
Mike
|||hi Mike,
as Mike already pointed out, the BIDS version provided for SQLExpress does not allow you to open SSIS package..
BTW, SQLExpress does not provide the SQL Server Agent as well, so you can not even "schedule" package execution, and no "runtime" for SSIS is available.. you could, eventually, use DTS, as a runtime for it is provided at http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
regards
|||Thanks Andrea...
I am still wrestling with SQL servers and I was wondering if you could give me a definite answer...
I am using enterprise manager to connect to SQL 2005 Express for snapshot replication. I've created new publications, and then using EXPRESS I created a local subscription to the publication.
But I could see no changes in my database.. Do you know why this is happening?
I tried to start the distribution agent (multiple publications) but some reason it can't connect to subscribers.
is this because 2000 and express can't connect to each other? or am I doing something wrong?
thanks
|||Hi ,
I understand that the BIDS version installed with SQL Express Toolkit does not allow to open the SSIS packages. Does that mean that we cannot even execute an SSIS package from SQL server Express.
Thanks.
Vigya
|||Hi Vigay,
Right, you can not use any part of SSIS in SQL Express, it isn't there. You can't run packages or anything else.
Mike
Integration service Express/2005
I finally bit the bullet and uninstalled all of Sql Server 2005 and installed Sql Server 2005 Express both Advanced and the Toolkit. I can get in and see everything locally and my production Great Plains server just fine through Management console. The one thing I can't do is open an integration package to process our credit cards.
It's a pretty simple integration, but when I go to open it in SQL Server Business Intelligence Development Studio, the only thing it shows installed was Reporting services, not Integration services. When I try to open the project it gives me:
"The application for project 'c:\pcard...' is not installed. Make sure the application for the project type(.dtproj) is installed."
I had a problem earlier with the gui not coming up but was resolved when I applied SS2005 SVP 2. Some items wasn't on the same version. I re-applied svp 2 after I installed SS express and still got the above. If I try and 'add' Integration Services, either thru Express or just regular ss2005, it says it's already installed.
Do I have to modify the registry so that it comes up on Business Intelligence Dev Studio? I can't run the integration if I can't open the project.
Hi Pete,
SQL Express does not include SSIS and the version of BIDS that installs with the Express Toolkit does not support opening SSIS packages. You will need to install one of the paid Editions of SQL Server 2005 to get SSIS functonality.
Mike
|||Hi Mike,
when you say Express Toolkit does not support opening SSIS packages, do you mean it does not support saving packages or the schedule package?
I want to know if there is any way I can save packages and do the scheduled jobs strictly without the licensed SQL Server.
thanks in advance.
Mike
|||hi Mike,
as Mike already pointed out, the BIDS version provided for SQLExpress does not allow you to open SSIS package..
BTW, SQLExpress does not provide the SQL Server Agent as well, so you can not even "schedule" package execution, and no "runtime" for SSIS is available.. you could, eventually, use DTS, as a runtime for it is provided at http://www.microsoft.com/downloads/details.aspx?FamilyID=50b97994-8453-4998-8226-fa42ec403d17&DisplayLang=en
regards
|||Thanks Andrea...
I am still wrestling with SQL servers and I was wondering if you could give me a definite answer...
I am using enterprise manager to connect to SQL 2005 Express for snapshot replication. I've created new publications, and then using EXPRESS I created a local subscription to the publication.
But I could see no changes in my database.. Do you know why this is happening?
I tried to start the distribution agent (multiple publications) but some reason it can't connect to subscribers.
is this because 2000 and express can't connect to each other? or am I doing something wrong?
thanks
|||Hi ,
I understand that the BIDS version installed with SQL Express Toolkit does not allow to open the SSIS packages. Does that mean that we cannot even execute an SSIS package from SQL server Express.
Thanks.
Vigya
|||Hi Vigay,
Right, you can not use any part of SSIS in SQL Express, it isn't there. You can't run packages or anything else.
Mike
Subscribe to:
Posts (Atom)