big problem with sql server 2000 msde sp 4 (stops working by accident)

Hi,
I get the following error-message from sqlserver 2000 (msde edition)
I have no idea what is going wrong.
I was only able to get back to working state by
DBCC CHECKDB ALLOW-DATA-LOSS
(or restoring a backup)
But i do not want the db to crash at my customers ...
In the knowledge-base I found:
"An assertion or Msg 7987 may occur when an operation is performed on an
instance of SQL Server"
without any reason why inconsitencies can occur
This bug is at least known since 5th april 2005 ... and still known for
sql-server 9 (2005 I think)
I hope I oversaw something ... or does SQLServer realy stops to work by
chance?
please help!
2006-07-26 03:43:04.00 spid51 ex_raise2: Exception raised, major=79,
minor=87, severity=22, attempting to create symptom dump
2006-07-26 03:43:04.28 spid51 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 51, PSS = 0x414491a8, EC = 0x414494d8
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 7/26/2006
Time: 5:32:26 AM
User: N/A
Computer: HLX02
Description:
Error: 7987, Severity: 22, State: 3
A possible database consistency problem has been detected on database
'faroer'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database
'faroer'.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 33 1f 00 00 16 00 00 00 3......
0008: 06 00 00 00 48 00 4c 00 ...H.L.
0010: 58 00 30 00 32 00 00 00 X.0.2...
0018: 07 00 00 00 66 00 61 00 ...f.a.
0020: 72 00 6f 00 65 00 72 00 r.o.e.r.
0028: 00 00 ..Sascha Bohnenkamp wrote:
> Hi,
> I get the following error-message from sqlserver 2000 (msde edition)
> I have no idea what is going wrong.
> I was only able to get back to working state by
> DBCC CHECKDB ALLOW-DATA-LOSS
> (or restoring a backup)
> But i do not want the db to crash at my customers ...
> In the knowledge-base I found:
> "An assertion or Msg 7987 may occur when an operation is performed on an
> instance of SQL Server"
> without any reason why inconsitencies can occur
> This bug is at least known since 5th april 2005 ... and still known for
> sql-server 9 (2005 I think)
> I hope I oversaw something ... or does SQLServer realy stops to work by
> chance?
> please help!
> 2006-07-26 03:43:04.00 spid51 ex_raise2: Exception raised, major=79,
> minor=87, severity=22, attempting to create symptom dump
> 2006-07-26 03:43:04.28 spid51 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 51, PSS = 0x414491a8, EC = 0x414494d8
>
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 7/26/2006
> Time: 5:32:26 AM
> User: N/A
> Computer: HLX02
> Description:
> Error: 7987, Severity: 22, State: 3
> A possible database consistency problem has been detected on database
> 'faroer'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database
> 'faroer'.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Data:
> 0000: 33 1f 00 00 16 00 00 00 3......
> 0008: 06 00 00 00 48 00 4c 00 ...H.L.
> 0010: 58 00 30 00 32 00 00 00 X.0.2...
> 0018: 07 00 00 00 66 00 61 00 ...f.a.
> 0020: 72 00 6f 00 65 00 72 00 r.o.e.r.
> 0028: 00 00 ..
No, SQL Server does not "stop working by accident". Most likely you
have flaky hardware that is causing corruption in your database file.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben schrieb:
> No, SQL Server does not "stop working by accident". Most likely you
> have flaky hardware that is causing corruption in your database file.
well ... are there any chance to find someting to help find the problem
in the log files of the server (or in the dumps) ?
From teh stack dump I cannot see any i/o problems ...
* Short Stack Dump
* 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
* 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
* 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
* 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
* 006BA785 Module(sqlservr+002BA785) (SQLExit+00152C8E)
* 004040DA Module(sqlservr+000040DA)
* 0042ADAE Module(sqlservr+0002ADAE)
* 0042A3D3 Module(sqlservr+0002A3D3)
* 0043638B Module(sqlservr+0003638B)
* 0043288B Module(sqlservr+0003288B)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
* 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 004398A5 Module(sqlservr+000398A5)
* 004398A5 Module(sqlservr+000398A5)
* 0041D396 Module(sqlservr+0001D396)
* 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
* 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 004194B9 Module(sqlservr+000194B9)
* 004193E4 Module(sqlservr+000193E4)
* 00429EAA Module(sqlservr+00029EAA)
* 00415D04 Module(sqlservr+00015D04)
* 00416214 Module(sqlservr+00016214)
* 00415F28 Module(sqlservr+00015F28)
* 0076C7D0 Module(sqlservr+0036C7D0) (SQLExit+00204CD9)
* 007704EF Module(sqlservr+003704EF) (SQLExit+002089F8)
* 0077099C Module(sqlservr+0037099C) (SQLExit+00208EA5)
* 006332EC Module(sqlservr+002332EC) (SQLExit+000CB7F5)
* 0043D005 Module(sqlservr+0003D005)
* 0042598D Module(sqlservr+0002598D)
* 41075309 Module(ums+00005309) (UmsThreadScheduler::ExitUser+00000459)|||Sascha Bohnenkamp wrote:
> Tracy McKibben schrieb:
>> No, SQL Server does not "stop working by accident". Most likely you
>> have flaky hardware that is causing corruption in your database file.
> well ... are there any chance to find someting to help find the problem
> in the log files of the server (or in the dumps) ?
> From teh stack dump I cannot see any i/o problems ...
> * Short Stack Dump
> * 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
> * 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
> * 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
> * 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
> * 006BA785 Module(sqlservr+002BA785) (SQLExit+00152C8E)
> * 004040DA Module(sqlservr+000040DA)
> * 0042ADAE Module(sqlservr+0002ADAE)
> * 0042A3D3 Module(sqlservr+0002A3D3)
> * 0043638B Module(sqlservr+0003638B)
> * 0043288B Module(sqlservr+0003288B)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
> * 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 004398A5 Module(sqlservr+000398A5)
> * 004398A5 Module(sqlservr+000398A5)
> * 0041D396 Module(sqlservr+0001D396)
> * 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
> * 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 004194B9 Module(sqlservr+000194B9)
> * 004193E4 Module(sqlservr+000193E4)
> * 00429EAA Module(sqlservr+00029EAA)
> * 00415D04 Module(sqlservr+00015D04)
> * 00416214 Module(sqlservr+00016214)
> * 00415F28 Module(sqlservr+00015F28)
> * 0076C7D0 Module(sqlservr+0036C7D0) (SQLExit+00204CD9)
> * 007704EF Module(sqlservr+003704EF) (SQLExit+002089F8)
> * 0077099C Module(sqlservr+0037099C) (SQLExit+00208EA5)
> * 006332EC Module(sqlservr+002332EC) (SQLExit+000CB7F5)
> * 0043D005 Module(sqlservr+0003D005)
> * 0042598D Module(sqlservr+0002598D)
> * 41075309 Module(ums+00005309) (UmsThreadScheduler::ExitUser+00000459)
Doesn't have to be an I/O problem - it could be bad RAM, a bad CPU, any
number of things. You should run some hardware diagnostics on the
machine, perhaps even open an incident with Microsoft - they can help
you decipher the logs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

BIG PROBLEM : Invalid object name 'ReportServerTempDB.dbo.ExecutionCache'

Anyone know my reports are all suddenly throwing this back :
Invalid object name 'ReportServerTempDB.dbo.ExecutionCache'
?The Report Server uses two databases on SQL Server. One of them is called
ReportServerTempDB. It contains a table called ExecutionCache. Check out
whether this table is still there, and whether it can still be queried. You
have to use Enterprise Manager or Query Analyzer to try and query it.
HTH
Charles Kangak, MCT, MCDBA
"Matt Swift" wrote:
> Anyone know my reports are all suddenly throwing this back :
> Invalid object name 'ReportServerTempDB.dbo.ExecutionCache'
> ?
>
>

Bi-directional Transaction Replication

Besides loopback detection, is there anything else that is needed to prevent the replication command from sending back to the source. Cos i am having problem with the subscriber B sending back the replication to A in the form of B as the publisher and A as the subscriber.
:confused:hi
what is the problem? a error in the sync. Can you writr more information about your problema.

TIA
Abel.|||It gives me an error " violation of primary key constraint..." And when i search the record at the subscriber, it shows that the record is already there. Meaning A send to B...then B send to A...If I'm not wrong in reading about loopback detection, B shouldnt send to A already...correct me if I'm wrong. Tks.|||hi, you have a Transactional replication in a SQL Server? this type of replication not is a "bi-direccional" (by defualt, the data of subscriber be read only, do you can't modified this data), how implement this replication?
In your case use the merge, this type of replication is "bi-directional".

bye.
Abel.

Best way to track who is accessing a record

I have an application that has a SQL back end, and I want to be able to track who is accessing a record so that no one else can access it at the same time. I was going to do this with a table or application state, but how can I avoid keeping files locked when someone abandons a session? Any Ideas?There are third party tools to do it but try the links below for code to track updates and links to the third party tools. Hope this helps.
http://www.aspfaq.com/show.asp?id=2448

Best way to backup a database,

Hi,

Does anyone have a preference on how they back up their database?

Currently I do it via a SQL Server Agent Job, that runs some T-SQL to backup the system database, and backup the user databases and transaction logs (where appropriate).

I was thinking about moving this to a VBScript for the reason that it will allow me to (easily) write to a log and email the relevant people (i.e. if a backup succeeds or fails).

Question is, in SQL (T-SQL), is there an easy way to write to a text file, and send out an email? (does sendmail require outlook to be installed on the server?)

Thanks again!.You can set up your backups through Enterprise Manager. When you enable a schedule for the backup, the backup is added as a job. For that job you can setup notifications eg mail and adding results to the application log.
To send mails, sql server needs a mapi compliant mail program, which can be Outlook.|||I do my backups with a T-SQL script. You can send emails from the script using xp_sendmail. Why write a text file log? You can write to a database table instead, which can provide a lot more functionality for a logviewer GUI.

Instead of doing incremental backups of my larger databases (which get progressively larger), I write a complete backup everyday to a network disk that has seperate folders for each day. I also do a shrink and translog truncate before the backup runs. Here's my script for the backup step:

DECLARE @.day_of_week VARCHAR(15),
@.server_name VARCHAR(25),
@.db_location_string VARCHAR(128),
@.log_location_string VARCHAR(128),
@.database_name VARCHAR(128)

DECLARE database_cursor CURSOR FOR
SELECT [name] as DBNAME FROM sysdatabases
WHERE [name] NOT IN ('master', 'model', 'msdb', 'tempdb')

SET @.day_of_week=DATENAME(dw, GETDATE())
SET @.server_name=@.@.SERVERNAME

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @.database_name

WHILE @.@.FETCH_STATUS=0
BEGIN
SET @.db_location_string='\\myServer\SQL Backups\' + @.day_of_week + '\SQL\' + @.server_name + '\' + @.database_name + '.bak'
SET @.log_location_string='\\myServer\SQL Backups\' + @.day_of_week + '\SQL\' + @.server_name + '\' + @.database_name + '_log.bak'
BACKUP DATABASE @.database_name TO DISK = @.db_location_string WITH NOINIT , NOUNLOAD , NAME = @.database_name, NOSKIP , STATS = 10, NOFORMAT
--BACKUP LOG @.database_name TO DISK = @.log_location_string WITH NOINIT , NOUNLOAD , NAME = @.database_name, NOSKIP , STATS = 10, NOFORMAT
FETCH NEXT FROM database_cursor INTO @.database_name
END
CLOSE database_cursor
DEALLOCATE database_cursor|||Oh, if you don;t have your server set up for SQL Mail, which is, frankly, a pain, you can add a VBScript step to your back job that sends mail using the CDONTS object.

Configure your backup step to on failure, go to the send mail step, otherwise skip it.|||Thanks for the advice.

All suggestions taken on board.|||bpdWork:

Thank you so much for sharing your sql script. I might be able to use that in a new backup plan I am working on. One question, however, and I know this is asking a lot. Do you have another script that will restore all these databases?

Thanks
Tom|||nevermind that last question, it was too easy!

Thanks
Tommy