Record Locks: records

Showing posts with label records. Show all posts

Friday, March 30, 2012

Recovery of the SQL Server 2000 Database.

Situation:
SQL S2000 DB with some 6 mil. records, 100 fields for each record - was indexed at first on just few fields, then some more and when everything seemed to be OK and we had partially indexed good DB we stetted up indexing (single for each field) for the remaining 60 or so fields and left for the weekend.

Upon returning 2 days later we found SQL Server unusually busy and non-responding to Enterprise Manager at all.

Finally we have stopped SQL Server, with proper and uneventful shutdown - restarted the machine and at the moment all that is happening after lots of the activity at first (from current activity log) is that: "Recovery of database 'OurDB' (10) is 64% complete (approximately 240 more seconds) (Phase 2 of 3)."
No clue in previous messages in the current logs, no clue on previous logs.
All databases are inaccessible from Enterprise Manager.

What could be happening and now what?
:confused: :confused:When you say, "proper and uneventful shutdown " do you mean, sp_who showed that nothing was running? Could it be that indexes were still being build? And are you really creating (100) indexes for each field in every table?!?

-jfp|||jfp,
after some additional investigation I have discovered, that indeed, SQL Server was shut down with Windows "End Task" which unfortunately means, that - yes, it could very well still have been in the middle of indexing - and therefore ultimate price was paid for not having DB backed up BEFORE indexing.

BTW indexing of this sort on DB on just dual Xeon 500 box with 1 GB of Ram could go on for days.

Yes, we are indexing all the fields in order to query them and get the idea what the heck we need to do with all this wonderful data.
This is a boring trial run only to help prepare criteria for the real app with less junk and therefore less indexed junk.

Recovery of Suspect Database

I have a large database (>150 million records) that is marked "Suspect" and I would like to recover this if possible. Our IT group did an unexpected reboot of the server during a data import process implemented in multiple nested DTS packages. After the
reboot, the database was marked as Suspect.
SQL Server won't let me dbcc checkdb, restore datbase with recovery or even back up the log file (DB recovery mode is SIMPLE). After running sp_resetstatus and restarting SQL Server, a new error log and dump file are generated and the database is reset to
Suspect. A two-day-old backup is available and I can replace all the data by reimporting the missing days, but this will take days and I'm hoping to be able to recover the existing DB.
A couple of snippets from the error log are below. I also have a 18+ MB dump file. What's the best approach to fixing this?
<snip />
2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-05-04 13:51:46.54 server SQL Server is ready for client connections
2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQLDump0005.txt
* ************************************************** *****************************
*
* BEGIN STACK DUMP:
* 05/04/04 13:52:06 spid 11
*
<snip />
* ----
2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is 0x37EAFC8F
2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File: <pageref.cpp>, line=4454
Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 11
Process ID: 2180
2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in database 'IDD'. Error at log record ID (7480:19322:383)..
2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not recover. Contact Technical Support..
2004-05-04 13:52:42.95 spid3 Recovery complete.
Some good info can be found here.
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>
|||Yeah, I saw that site. Those steps aren't working. If I can't find a way to recover otherwise, I may use emergency mode and bcc the data out as a fail-safe before attempting a restore from backup. Not being able to even force it out of Suspect mode preclu
des a number of things I might try. Thanks Jeff! -- Scott
-- Jeff Duncan wrote: --
Some good info can be found here.
http://www.karaszi.com/sqlserver/inf...suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.[vbcol=seagreen]
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.[vbcol=seagreen]
dump file. What's the best approach to fixing this?
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
><snip />>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>
|||You should open a case with PSS. This definitely sounds like it is worth
more than $250.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
************************************************** **************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>

Recovery of Suspect Database

I have a large database (>150 million records) that is marked "Suspect" and
I would like to recover this if possible. Our IT group did an unexpected re
boot of the server during a data import process implemented in multiple nest
ed DTS packages. After the
reboot, the database was marked as Suspect.
SQL Server won't let me dbcc checkdb, restore datbase with recovery or even
back up the log file (DB recovery mode is SIMPLE). After running sp_resetsta
tus and restarting SQL Server, a new error log and dump file are generated a
nd the database is reset to
Suspect. A two-day-old backup is available and I can replace all the data by
reimporting the missing days, but this will take days and I'm hoping to be
able to recover the existing DB.
A couple of snippets from the error log are below. I also have a 18+ MB dump
file. What's the best approach to fixing this?
<snip />
2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100% c
omplete (approximately 0 more seconds)
2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared Memory,
Named Pipes.
2004-05-04 13:51:46.54 server SQL Server is ready for client connections
2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
*Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL\log\SQ
LDump0005.txt
* ****************************************
**********************************
*****
*
* BEGIN STACK DUMP:
* 05/04/04 13:52:06 spid 11
*
<snip />
* ----
--
2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is 0x37EAFC8F
2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File: <pageref.cpp>,
line=4454
Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 11
Process ID: 2180
2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in dat
abase 'IDD'. Error at log record ID (7480:19322:383)..
2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not re
cover. Contact Technical Support..
2004-05-04 13:52:42.95 spid3 Recovery complete.Some good info can be found here.
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
****************************************
************************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>|||Yeah, I saw that site. Those steps aren't working. If I can't find a way to
recover otherwise, I may use emergency mode and bcc the data out as a fail-s
afe before attempting a restore from backup. Not being able to even force it
out of Suspect mode preclu
des a number of things I might try. Thanks Jeff! -- Scott
-- Jeff Duncan wrote: --
Some good info can be found here.
http://www.karaszi.com/sqlserver/in..._suspect_db.asp
You can set the status of your DB to -32768 which is emergency mode. This
should allow you to see the database unless the mdf file is actually gone.
Jeff Duncan
MCDBA, MCSE+I
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.[vbcol=seagreen]
dump file. What's the best approach to fixing this?[vbcol=seagreen]
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
****************************************
************************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
><snip />>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>|||You should open a case with PSS. This definitely sounds like it is worth
more than $250.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Scott" <anonymous@.discussions.microsoft.com> wrote in message
news:B2C25106-BA97-42D1-910B-3E76CF72EDEB@.microsoft.com...
> I have a large database (>150 million records) that is marked "Suspect"
and I would like to recover this if possible. Our IT group did an
unexpected reboot of the server during a data import process implemented in
multiple nested DTS packages. After the reboot, the database was marked as
Suspect.
> SQL Server won't let me dbcc checkdb, restore datbase with recovery or
even back up the log file (DB recovery mode is SIMPLE). After running
sp_resetstatus and restarting SQL Server, a new error log and dump file are
generated and the database is reset to Suspect. A two-day-old backup is
available and I can replace all the data by reimporting the missing days,
but this will take days and I'm hoping to be able to recover the existing
DB.
> A couple of snippets from the error log are below. I also have a 18+ MB
dump file. What's the best approach to fixing this?
> <snip />
> 2004-05-04 13:51:46.07 spid5 Clearing tempdb database.
> 2004-05-04 13:51:46.38 spid5 Starting up database 'tempdb'.
> 2004-05-04 13:51:46.41 spid5 Analysis of database 'tempdb' (2) is 100%
complete (approximately 0 more seconds)
> 2004-05-04 13:51:46.54 server SQL server listening on TCP, Shared
Memory, Named Pipes.
> 2004-05-04 13:51:46.54 server SQL Server is ready for client
connections
> 2004-05-04 13:52:06.49 spid11 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 11, PSS = 0x42dc2098, EC = 0x42dc23c0
> *Stack Dump being sent to C:\Program Files\Microsoft SQL
Server\MSSQL\log\SQLDump0005.txt
> *
****************************************
************************************
***
> *
> * BEGIN STACK DUMP:
> * 05/04/04 13:52:06 spid 11
> *
> <snip />
>
* ----
--
> 2004-05-04 13:52:07.80 spid11 Stack Signature for the dump is
0x37EAFC8F
> 2004-05-04 13:52:07.82 spid11 SQL Server Assertion: File:
<pageref.cpp>, line=4454
> Failed Assertion = 'rowLog.RowCount () == 1 || pPage->IsEmpty ()'.
> 2004-05-04 13:52:07.82 spid11 Location: pageref.cpp:4454
> Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
> SPID: 11
> Process ID: 2180
> 2004-05-04 13:52:07.82 spid11 Error: 3313, Severity: 21, State: 2
> 2004-05-04 13:52:07.82 spid11 Error while redoing logged operation in
database 'IDD'. Error at log record ID (7480:19322:383)..
> 2004-05-04 13:52:22.91 spid11 Error: 3414, Severity: 21, State: 1
> 2004-05-04 13:52:22.91 spid11 Database 'IDD' (database ID 7) could not
recover. Contact Technical Support..
> 2004-05-04 13:52:42.95 spid3 Recovery complete.
>|||Hi Scott
Did you manage to sort this problem out, because I have the same problem.
SQL Error:
2004-05-10 08:41:31.07 spid10 Error: 3313, Severity: 21, State: 2
2004-05-10 08:41:31.07 spid10 Error while redoing logged operation in data
base 'CBPRD'. Error at log record ID (90312:130277:138)..
2004-05-10 08:41:35.21 spid7 Using 'xpstar.dll' version '2000.28.09' to e
xecute extended stored procedure 'xp_regread'.
2004-05-10 08:42:01.78 spid10 Your transaction (process ID #10) was deadlo
cked with another process and has been chosen as the deadlock victim. Rerun
your transaction.
2004-05-10 08:42:01.78 spid10 Your transaction (process ID #10) was deadlo
cked with another process and has been chosen as the deadlock victim. Rerun
your transaction.
2004-05-10 08:42:01.78 spid10 Error: 3413, Severity: 21, State: 1
2004-05-10 08:42:01.78 spid10 Database ID 8. Could not mark database as su
spect. Getnext NC scan on sysdatabases.dbid failed..
2004-05-10 08:42:01.78 spid10 Process 10 unlocking unowned resource: KEY:
1:30:2 (df0082738c65)
2004-05-10 08:42:01.78 spid10 Error: 1203, Severity: 20, State: 1
2004-05-10 08:42:01.78 spid10 Process ID 10 attempting to unlock unowned r
esource KEY: 1:30:2 (df0082738c65)..
2004-05-10 08:42:01.78 server Using 'sqlimage.dll' version '4.0.5'
Stack Dump being sent to F:\MSSQL7\log\SQL00012.dmp
****************************************
************************************
***
*
* BEGIN STACK DUMP:
* 05/10/04 08:42:02 spid 0
*
* Exception Address = 77F1D642 (RaiseException + 6a)
* Exception Code = 400042ac P
****************************************
************************************
***
----
--
Short Stack Dump
0x77f1d642 Module(KERNEL32+1d642) (RaiseException+6a)
0x006fe3f0 Module(sqlservr+2fe3f0) (stackTraceException+53)
0x006fe3ad Module(sqlservr+2fe3ad) (stackTrace+255)
0x0078d41e Module(sqlservr+38d41e) (utassert_fail+1a0)
0x005bc618 Module(sqlservr+1bc618) (ExecutionContext::Cleanup+9e)
0x00501daf Module(sqlservr+101daf) (PSS::~PSS+111)
0x00501c9b Module(sqlservr+101c9b) (destroyPssMemory+19)
0x00501a9c Module(sqlservr+101a9c) (freepss+132)
0x004d2f0a Module(sqlservr+d2f0a) (StartDBsInParallel+1b0)
0x41092a60 Module(ums+2a60) (ProcessWorkRequests+102)
0x41093316 Module(ums+3316) (ThreadStartRoutine+139)
0x7800b995 Module(MSVCRT+b995) (beginthread+ce)
0x77f04ef0 Module(KERNEL32+4ef0) (lstrcmpiW+be)
----
--
Dump thread - spid = 1, PSS = 0x411da084, EC = 0x411da23c
Stack Dump being sent to F:\MSSQL7\log\SQL00012.dmp
Please advise'

Friday, March 9, 2012

Recover data after using delete statement

Help.. I Just deleted some records a table in sql 2000. Tell me how to
recover the records.
ThanksWhat types of backups do you have?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Le Thuan" <LeThuan@.discussions.microsoft.com> wrote in message
news:51E07AEF-9549-415A-B098-F9C36E7778EA@.microsoft.com...
> Help.. I Just deleted some records a table in sql 2000. Tell me how to
> recover the records.
> Thanks|||Hi,
If the recovery model is other that simple you could recover your data by
using an utility called Log Analyzer (google it). If you have some backup
strategy which imply log backup than do a restore process with stop at clause.
Tibor Karaszi wrote:
>What types of backups do you have?
>> Help.. I Just deleted some records a table in sql 2000. Tell me how to
>> recover the records.
>> Thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1|||I don't have any backups
"Tibor Karaszi" wrote:
> What types of backups do you have?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Le Thuan" <LeThuan@.discussions.microsoft.com> wrote in message
> news:51E07AEF-9549-415A-B098-F9C36E7778EA@.microsoft.com...
> > Help.. I Just deleted some records a table in sql 2000. Tell me how to
> > recover the records.
> > Thanks
>|||Then the data is most probably gone. You could try a log reader program, but I doubt that the
information is still in the transaction log.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Le Thuan" <LeThuan@.discussions.microsoft.com> wrote in message
news:711D861E-95F9-458A-A79F-295C7FA3C14C@.microsoft.com...
>I don't have any backups
> "Tibor Karaszi" wrote:
>> What types of backups do you have?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Le Thuan" <LeThuan@.discussions.microsoft.com> wrote in message
>> news:51E07AEF-9549-415A-B098-F9C36E7778EA@.microsoft.com...
>> > Help.. I Just deleted some records a table in sql 2000. Tell me how to
>> > recover the records.
>> > Thanks
>>

Wednesday, March 7, 2012

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.
See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan
|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

Recordset to Array

Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith

Recordset Destination and foreach loop

I have a csv file in which I am reading into a recordset destination and want to than use a foreach loop to cycle through those records and do some things. The problem I am having is after defining the variable name of the records set as results, and than going into the foreach loop, choosing collection, using the foreach ado enumerator, i dont see anything in the dropdown under ado object source varable? I am new to SSIS but I basically want to parse through this file, change some columns in each line and than either update or insert data in a sql table.See the following post for a tutorial on shredding a recordset: http://www.sqlis.com/59.aspx|||

appreciate the quick response, although like I said

i dont see anything in the dropdown under ado object source varable?

|||What scope is your recordset variable at? Is it a package scope or a task / transform scope?

Recordset Counts

I am trying to count the number of records in a recordset I have created. It always returns a value of -1 nomatter how many records are actually there. I have other operations working fine using similar code, but this one will not work correctly. Can anyone help. Here is the code.

Set ashcontentcountcmd = New ADODB.Command
ashcontentcountcmd.ActiveConnection = ashconn
Set ashcontentcountrs = New ADODB.Recordset
ashcontentcountrs.ActiveConnection = ashconn

ashsource = lstash.List(lstash.ListIndex)

ashcontentcount = "select ID from Flyash where Source ='" + ashsource + "'"

ashcontentcountcmd.CommandText = ashcontentcount ashcontentcountrs.Open ashcontentcountcmd

Debug.Print ashcontentcountrs.RecordCount

Thank you in advance for any help.

MichaelYou need to make it a client-side cursor to return the recordcount or your cursor is the wrong type.|||I am new to this stuff. I don't know what you mean by a client-side cursor. This is an Access database I am connecting to.

RecordSelectionFormula not appearing in the Query

I am using Crystal Reports 7 with VB6.
I am sending some user defined parameters from the application to filter the records and its getting set to the CRPEAuto.Report.RecordSelectionFormula, along with a hardcoded condition in the Report-Edit Selection Formula at design time. The design time and runtime parameter values are getting concatenated properly with the required relational operator.

1.
But the method CRPEAuto.Report.SQLQueryString, retrieves the query of the report(Show SQL Query), without the condition set in the RecordSelectionFormula above.

2.
The query returned above, when executed in the SQL Navigator, runs in 6mins and records are fetched. But the report preview from the application which displays the records on Crystal Report runs indefinetly and no output is got.

Any inputs on this would be appreciated.
Many Thanks.You can use stored procedure with input parameters
Did the query returned is valid?

records wont delete

I have some records that will not delete, whenever I run a delete
statement in the Query analyzer, it never completes the statement, and
I am only deleting one record at a time. Can anyone tell me why a
record wouldn't delete?[posted and mailed, please reply in news]

alloowishus (alloowishus@.yahoo.com) writes:
> I have some records that will not delete, whenever I run a delete
> statement in the Query analyzer, it never completes the statement, and
> I am only deleting one record at a time. Can anyone tell me why a
> record wouldn't delete?

There are several possible reasons:

o The row you are trying to delete is locked by another process.
o There is a foreign-key constraint from a referencing table, and that
table is large, but the referencing column is not indexed, so
checking whether the row is deletable takes a very long time.
o A variation of the above, but the foreign-key relationship uses
cascading updates.
o The table has a DELETE trigger which takes a very long time to
execute.

To check for locking, issue an sp_who from another connection. If you see
a non-zero value in the Blk column, the process on that row is blocked by
the process in the Blk column.

To check the other, use sp_helpconstraint and sp_helptrigger to look
for constraints and triggers.

The above list is not necessarily exhaustive.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:
> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
--
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegroups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...
No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/
|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:

> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>
|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegro ups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

records with <NULL> aren't returned?

I have a table (TempTable) with one field (Field1)
and three records:
2
3
<NULL>
if I do " Select * from TempTable where Field1 <> '2' "
I only get returned :
3
why didn't the <NULL> record show up? Can anything be done to the
table properties without having to modify the query (by adding 'OR
Field1 IS NULL')
Thanks...No, Not as far as I know
SELECT * FROM TempTable
WHERE field1 <>1
OR field1IS NULL
or
SELECT * FROM TempTable
WHERE COALESCE(field1,0) <> 1
and take a look at these 2 queries
SELECT COUNT(*) FROM TempTable --3
SELECT count(field1) FROM TempTable --2
http://sqlservercode.blogspot.com/|||The reason for this is that both = null and <> null are undefined. SQL will
return false in both cases, as the convention for undefined.
For example, null = null is undefined as thus returns false.
There is no way that I know to change this behavior.
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Eych" wrote:

> I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>|||The behavior you are seeing is the ANSI standard. It is recommended that
you use this, but if you need to turn it off , you can do so at the
connection level.
SET ANSI_NULLS OFF
Phil Lavene
"Eych" <eycheych@.hotmail.com> wrote in message
news:1137085275.394772.87080@.g47g2000cwa.googlegroups.com...
>I have a table (TempTable) with one field (Field1)
> and three records:
> 2
> 3
> <NULL>
> if I do " Select * from TempTable where Field1 <> '2' "
> I only get returned :
> 3
> why didn't the <NULL> record show up? Can anything be done to the
> table properties without having to modify the query (by adding 'OR
> Field1 IS NULL')
> Thanks...
>

Saturday, February 25, 2012

Records Versioning/Auditing

Howdy,
Forgive me if this is the wrong group. I'm writing a ASP.Net application and
i need to track every change to a table's records. In SQL 2005, is there a
way to easily keep a record change history? Something with rules or triggers
or something? Or am I stuck comparing every field manually before i perform
the UPDATE?
Thanks a lot!
--
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.netA trigger can do what you need (i.e. putting a changed record into an audit
table prior to or after an update).
I recommend checking out ApexSQL's Audit. It can set up everything for you
with a mostly point-and-click interface.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
> Howdy,
> Forgive me if this is the wrong group. I'm writing a ASP.Net application
> and
> i need to track every change to a table's records. In SQL 2005, is there a
> way to easily keep a record change history? Something with rules or
> triggers
> or something? Or am I stuck comparing every field manually before i
> perform
> the UPDATE?
> Thanks a lot!
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net|||Yeah, i saw them but at $600 per license, i'll figure it out myself.
thanks,
--
David Lozzi
Delphi Technology Solutions
Blog: www.lozzi.net
"TheSQLGuru" wrote:
> A trigger can do what you need (i.e. putting a changed record into an audit
> table prior to or after an update).
> I recommend checking out ApexSQL's Audit. It can set up everything for you
> with a mostly point-and-click interface.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
> news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
> > Howdy,
> >
> > Forgive me if this is the wrong group. I'm writing a ASP.Net application
> > and
> > i need to track every change to a table's records. In SQL 2005, is there a
> > way to easily keep a record change history? Something with rules or
> > triggers
> > or something? Or am I stuck comparing every field manually before i
> > perform
> > the UPDATE?
> >
> > Thanks a lot!
> >
> > --
> > David Lozzi
> > Delphi Technology Solutions
> > Blog: www.lozzi.net
>
>|||$600 is a real bargain for what you get in the package. If you get paid $20
per hour that is only 30 hours before you hit the same amount. No way you
can build out a fully functional system like Audit can do for you in that
time frame.
Note too that the license is just for the client that sets up the auditing.
You can audit unlimited servers from that one purchase. See here,
http://www.apexsql.com/sql_tools_audit.asp, 4th bullet point from the top.
And they have a viewer to report on the audited information that is only $50
per client.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
news:F7CA333A-71F4-4B8A-BC90-FD23D60CF3DA@.microsoft.com...
> Yeah, i saw them but at $600 per license, i'll figure it out myself.
> thanks,
> --
> David Lozzi
> Delphi Technology Solutions
> Blog: www.lozzi.net
>
> "TheSQLGuru" wrote:
>> A trigger can do what you need (i.e. putting a changed record into an
>> audit
>> table prior to or after an update).
>> I recommend checking out ApexSQL's Audit. It can set up everything for
>> you
>> with a mostly point-and-click interface.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "David Lozzi" <dlozzi(remove-this)@.delphi-ts.com> wrote in message
>> news:136C1D21-F4AD-4F15-B193-732576A972EA@.microsoft.com...
>> > Howdy,
>> >
>> > Forgive me if this is the wrong group. I'm writing a ASP.Net
>> > application
>> > and
>> > i need to track every change to a table's records. In SQL 2005, is
>> > there a
>> > way to easily keep a record change history? Something with rules or
>> > triggers
>> > or something? Or am I stuck comparing every field manually before i
>> > perform
>> > the UPDATE?
>> >
>> > Thanks a lot!
>> >
>> > --
>> > David Lozzi
>> > Delphi Technology Solutions
>> > Blog: www.lozzi.net
>>

Records Versioning/Auditing

records per page in ReportViewer

Hi Everyone,

I can't find this to save my life! Is there a way to set the number of records that show per page on the ReportViewer? I'd like to return more per page then returns with the default setting.

thanks,

jekerry

You have to use expressions for that purpose. Have a look at this article:

http://msdn2.microsoft.com/en-us/library/ms251668(VS.80).aspx

Look for Page Breaks section in this article.

|||

Awesome!!! Thanks!

jekerry

Records per page

Hi.

I want to know how many records there are per page.

For example there may be a textbox showing the count of records per page.

What is the expression for this?

Any help would be appreciated.

I don't know if there is anything predefined in SQL for this. You may have to calculate it yourself by knowing the size of the report viewer and the size of your textbox fields.

Friday, March 30, 2012

Recovery of the SQL Server 2000 Database.

Recovery of Suspect Database

Recovery of Suspect Database

Recovery of Suspect Database

Friday, March 9, 2012

Recover data after using delete statement

Wednesday, March 7, 2012

Recordset to Array

Recordset to Array

Recordset to Array

Recordset Destination and foreach loop

Recordset Counts

RecordSelectionFormula not appearing in the Query

records wont delete

records with <NULL> aren't returned?

records with <NULL> aren't returned?

records with <NULL> aren't returned?

Saturday, February 25, 2012

Records Versioning/Auditing

Records Versioning/Auditing

Records Versioning/Auditing

records per page in ReportViewer

Records per page

Record Locks

Blog Archive

About Me