Long blocked transaction logging

Is there anyone know any feature in SQL server which can detect/log the transaction which has been blocked unusually long? My idea is:

If a transaction has run for over 30 minutes, then log the following information in SQL server log:

1. State of the transaction : Blocked, Running, Sleeping

2. Time happeing

3. Duration

4. Last SQL it run

5. Blocked by which thread and info of the thread : the last SQL it run, the state of the thread.

Possibly it would be run in SQL server agent or SQL profiler has such feature, I am not sure about it. Can anyone suggest? Thanks.

If you are using SQL 2005, refer to the usage of the dynamic management view sys.dm_tran_active_transactions in Books Online.

For SQL 2000, this article may help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271509

|||

Thanks Arnie, I am using 2005 and this link is helpful:

http://msdn2.microsoft.com/en-us/library/ms191168.aspx

It provides the infomation of the Blocked Process Report Event Class object

Long blocked transaction logging

Is there anyone know any feature in SQL server which can detect/log the transaction which has been blocked unusually long? My idea is:

If a transaction has run for over 30 minutes, then log the following information in SQL server log:

1. State of the transaction : Blocked, Running, Sleeping

2. Time happeing

3. Duration

4. Last SQL it run

5. Blocked by which thread and info of the thread : the last SQL it run, the state of the thread.

Possibly it would be run in SQL server agent or SQL profiler has such feature, I am not sure about it. Can anyone suggest? Thanks.

If you are using SQL 2005, refer to the usage of the dynamic management view sys.dm_tran_active_transactions in Books Online.

For SQL 2000, this article may help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271509

|||

Thanks Arnie, I am using 2005 and this link is helpful:

http://msdn2.microsoft.com/en-us/library/ms191168.aspx

It provides the infomation of the Blocked Process Report Event Class object

Long + Complicated IIF expression

Hello, I have a long / complicated IIF expression for opening
subreports from report 1 (navigation feature). If field = 1, open
subreport 1, if field = 2, open subreport 2, etc etc...and this goes
on.
What's an easier way to do this besides the IIF statement in Reporting
Services? I have about 15 conditions so it would be a very long IIF
expression...Thanks for your help.Create a method in the Code section of report properties and use regular If
or even use case statements.
You can then call this method from the same place you were putting your
IIF's
"RSNewbie" <alinka345@.gmail.com> wrote in message
news:1118942105.414500.325850@.g43g2000cwa.googlegroups.com...
> Hello, I have a long / complicated IIF expression for opening
> subreports from report 1 (navigation feature). If field = 1, open
> subreport 1, if field = 2, open subreport 2, etc etc...and this goes
> on.
> What's an easier way to do this besides the IIF statement in Reporting
> Services? I have about 15 conditions so it would be a very long IIF
> expression...Thanks for your help.
>|||what do you mean by method? What kind of code would I put there?

Logshipping ERROR(urgent)

Hi Guys,
I am facing following problem with MSSQL Server 2000.
I am using Microsoft sql server Log Shipping feature to have a standby
server for disaster recovery purposes.
in the logshipping Destination server while restoring the transaction log
the restore process is failling with following error.
"[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
PrevPageLSN: (8122:7374:3)."
Both servers, source and destination were running with SQL SERVER
ENTERPRISE EDITION SP3 .
after this failure i rebuilded the logshipping process but same error
happened again so after one week updated the destination server with sql sp4
and rebuilded the logshipping process . but the same error happened again.
During this error SQL Server is creating a dump file and text file
pls advice
Are you sure that your databases are consistent - run checkdb...
Bojidar Alexandrov
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice
|||Hi Bojidar Alexandrov,
in source or destination?
source is consistent as i am doing dbcc every day and no errors found.
"Bojidar Alexandrov" wrote:

> Are you sure that your databases are consistent - run checkdb...
> Bojidar Alexandrov
> "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> SQL
> sp4
>
|||"bijupg@.hotmail.com" schrieb:

> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice
Did you do transaction log backups in between? Don't forget that they empty
the TA-Log and produce a new sequence number - then the logshipping (being
fully incremental) are missing some data and cannot work any more ...
|||No i am not doing any transaction log backup in between
"Christian Donner" wrote:

> "bijupg@.hotmail.com" schrieb:
>
> Did you do transaction log backups in between? Don't forget that they empty
> the TA-Log and produce a new sequence number - then the logshipping (being
> fully incremental) are missing some data and cannot work any more ...
|||The type of error you are seeing is most likely being caused by corruption
in your transaction logs. Review system event logs on both servers for ANY
hardware-related warnings and errors. Update all your firmware and drivers.
Adrian
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
> sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice
|||Biju.
You have been having trouble with this DB many times in the past. You had
corruption, it showed as fixed, and now it is back. You do not have torn
page detection on. Turn ton page detect ion on.
If your corruption continues, you will soon have a database that all you can
do it delete.
It may be worth your while to open a case with Microsoft PSS.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
> The type of error you are seeing is most likely being caused by corruption
> in your transaction logs. Review system event logs on both servers for ANY
> hardware-related warnings and errors. Update all your firmware and
> drivers.
> Adrian
>
> "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
>
|||Hi Mike,
can you pls advice how to open case with Microsoft PSS.
"Mike Epprecht (SQL MVP)" wrote:

> Biju.
> You have been having trouble with this DB many times in the past. You had
> corruption, it showed as fixed, and now it is back. You do not have torn
> page detection on. Turn ton page detect ion on.
> If your corruption continues, you will soon have a database that all you can
> do it delete.
> It may be worth your while to open a case with Microsoft PSS.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
> news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
>
>
|||Hi guys,
DBCC CHECKDB is not giving any errors or warnings and database is
functioning normally.
there are no hardware related errors in event viewer.
and we are using compaq cluster with compaq smart array controller so is
there any chance to have a torn page?this type of diska should have mechanism
to ensure that data is completely written and also there are no power failure
at all.
i am using transactional replication for reporting and it is working fine if
there was any problem with source database then it should also fail like
logshipping.
what should be the ideal temperature in datacentres/server romms currently
we are keeping 20-22 degree celcius is it ok?
Pls advice.
"Mike Epprecht (SQL MVP)" wrote:

> Biju.
> You have been having trouble with this DB many times in the past. You had
> corruption, it showed as fixed, and now it is back. You do not have torn
> page detection on. Turn ton page detect ion on.
> If your corruption continues, you will soon have a database that all you can
> do it delete.
> It may be worth your while to open a case with Microsoft PSS.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
> news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
>
>

Logshipping ERROR(urgent)

Hi Guys,
I am facing following problem with MSSQL Server 2000.
I am using Microsoft sql server Log Shipping feature to have a standby
server for disaster recovery purposes.
in the logshipping Destination server while restoring the transaction log
the restore process is failling with following error.
"[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft]&
#91;ODBC SQL
Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
PrevPageLSN: (8122:7374:3)."
Both servers, source and destination were running with SQL SERVER
ENTERPRISE EDITION SP3 .
after this failure i rebuilded the logshipping process but same error
happened again so after one week updated the destination server with sql sp
4
and rebuilded the logshipping process . but the same error happened again.
During this error SQL Server is creating a dump file and text file
pls adviceAre you sure that your databases are consistent - run checkdb...
Bojidar Alexandrov
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][OD
BC
SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), fo
r
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice|||Hi Bojidar Alexandrov,
in source or destination?
source is consistent as i am doing dbcc every day and no errors found.
"Bojidar Alexandrov" wrote:

> Are you sure that your databases are consistent - run checkdb...
> Bojidar Alexandrov
> "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> SQL
> sp4
>|||"bijupg@.hotmail.com" schrieb:

> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft
][ODBC SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), fo
r
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice
Did you do transaction log backups in between? Don't forget that they empty
the TA-Log and produce a new sequence number - then the logshipping (being
fully incremental) are missing some data and cannot work any more ...|||No i am not doing any transaction log backup in between
"Christian Donner" wrote:

> "bijupg@.hotmail.com" schrieb:
>
> Did you do transaction log backups in between? Don't forget that they empt
y
> the TA-Log and produce a new sequence number - then the logshipping (being
> fully incremental) are missing some data and cannot work any more ...|||The type of error you are seeing is most likely being caused by corruption
in your transaction logs. Review system event logs on both servers for ANY
hardware-related warnings and errors. Update all your firmware and drivers.
Adrian
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft
][ODBC
> SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), fo
r
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
> sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice|||Biju.
You have been having trouble with this DB many times in the past. You had
corruption, it showed as fixed, and now it is back. You do not have torn
page detection on. Turn ton page detect ion on.
If your corruption continues, you will soon have a database that all you can
do it delete.
It may be worth your while to open a case with Microsoft PSS.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
> The type of error you are seeing is most likely being caused by corruption
> in your transaction logs. Review system event logs on both servers for ANY
> hardware-related warnings and errors. Update all your firmware and
> drivers.
> Adrian
>
> "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
>|||Hi Mike,
can you pls advice how to open case with Microsoft PSS.
"Mike Epprecht (SQL MVP)" wrote:

> Biju.
> You have been having trouble with this DB many times in the past. You had
> corruption, it showed as fixed, and now it is back. You do not have torn
> page detection on. Turn ton page detect ion on.
> If your corruption continues, you will soon have a database that all you c
an
> do it delete.
> It may be worth your while to open a case with Microsoft PSS.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
> news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
>
>|||Hi guys,
DBCC CHECKDB is not giving any errors or warnings and database is
functioning normally.
there are no hardware related errors in event viewer.
and we are using compaq cluster with compaq smart array controller so is
there any chance to have a torn page?this type of diska should have mechanis
m
to ensure that data is completely written and also there are no power failur
e
at all.
i am using transactional replication for reporting and it is working fine if
there was any problem with source database then it should also fail like
logshipping.
what should be the ideal temperature in datacentres/server romms currently
we are keeping 20-22 degree celcius is it ok?
Pls advice.
"Mike Epprecht (SQL MVP)" wrote:

> Biju.
> You have been having trouble with this DB many times in the past. You had
> corruption, it showed as fixed, and now it is back. You do not have torn
> page detection on. Turn ton page detect ion on.
> If your corruption continues, you will soon have a database that all you c
an
> do it delete.
> It may be worth your while to open a case with Microsoft PSS.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
> news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
>
>

Logshipping ERROR(urgent)

Hi Guys,
I am facing following problem with MSSQL Server 2000.
I am using Microsoft sql server Log Shipping feature to have a standby
server for disaster recovery purposes.
in the logshipping Destination server while restoring the transaction log
the restore process is failling with following error.
"[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
PrevPageLSN: (8122:7374:3)."
Both servers, source and destination were running with SQL SERVER
ENTERPRISE EDITION SP3 .
after this failure i rebuilded the logshipping process but same error
happened again so after one week updated the destination server with sql sp4
and rebuilded the logshipping process . but the same error happened again.
During this error SQL Server is creating a dump file and text file
pls adviceAre you sure that your databases are consistent - run checkdb...
Bojidar Alexandrov
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice|||Hi Bojidar Alexandrov,
in source or destination?
source is consistent as i am doing dbcc every day and no errors found.
"Bojidar Alexandrov" wrote:
> Are you sure that your databases are consistent - run checkdb...
> Bojidar Alexandrov
> "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> > Hi Guys,
> > I am facing following problem with MSSQL Server 2000.
> > I am using Microsoft sql server Log Shipping feature to have a standby
> > server for disaster recovery purposes.
> > in the logshipping Destination server while restoring the transaction log
> > the restore process is failling with following error.
> >
> > "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> > transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> > Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> > PrevPageLSN: (8122:7374:3)."
> >
> > Both servers, source and destination were running with SQL SERVER
> > ENTERPRISE EDITION SP3 .
> > after this failure i rebuilded the logshipping process but same error
> > happened again so after one week updated the destination server with sql
> sp4
> > and rebuilded the logshipping process . but the same error happened again.
> > During this error SQL Server is creating a dump file and text file
> >
> > pls advice
>|||"bijupg@.hotmail.com" schrieb:
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice
Did you do transaction log backups in between? Don't forget that they empty
the TA-Log and produce a new sequence number - then the logshipping (being
fully incremental) are missing some data and cannot work any more ...|||No i am not doing any transaction log backup in between
"Christian Donner" wrote:
> "bijupg@.hotmail.com" schrieb:
> > Hi Guys,
> > I am facing following problem with MSSQL Server 2000.
> > I am using Microsoft sql server Log Shipping feature to have a standby
> > server for disaster recovery purposes.
> > in the logshipping Destination server while restoring the transaction log
> > the restore process is failling with following error.
> >
> > "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC SQL
> > Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> > transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> > Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> > PrevPageLSN: (8122:7374:3)."
> >
> > Both servers, source and destination were running with SQL SERVER
> > ENTERPRISE EDITION SP3 .
> > after this failure i rebuilded the logshipping process but same error
> > happened again so after one week updated the destination server with sql sp4
> > and rebuilded the logshipping process . but the same error happened again.
> > During this error SQL Server is creating a dump file and text file
> >
> > pls advice
> Did you do transaction log backups in between? Don't forget that they empty
> the TA-Log and produce a new sequence number - then the logshipping (being
> fully incremental) are missing some data and cannot work any more ...|||The type of error you are seeing is most likely being caused by corruption
in your transaction logs. Review system event logs on both servers for ANY
hardware-related warnings and errors. Update all your firmware and drivers.
Adrian
"bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> Hi Guys,
> I am facing following problem with MSSQL Server 2000.
> I am using Microsoft sql server Log Shipping feature to have a standby
> server for disaster recovery purposes.
> in the logshipping Destination server while restoring the transaction log
> the restore process is failling with following error.
> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> SQL
> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> PrevPageLSN: (8122:7374:3)."
> Both servers, source and destination were running with SQL SERVER
> ENTERPRISE EDITION SP3 .
> after this failure i rebuilded the logshipping process but same error
> happened again so after one week updated the destination server with sql
> sp4
> and rebuilded the logshipping process . but the same error happened again.
> During this error SQL Server is creating a dump file and text file
> pls advice|||Biju.
You have been having trouble with this DB many times in the past. You had
corruption, it showed as fixed, and now it is back. You do not have torn
page detection on. Turn ton page detect ion on.
If your corruption continues, you will soon have a database that all you can
do it delete.
It may be worth your while to open a case with Microsoft PSS.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
> The type of error you are seeing is most likely being caused by corruption
> in your transaction logs. Review system event logs on both servers for ANY
> hardware-related warnings and errors. Update all your firmware and
> drivers.
> Adrian
>
> "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
>> Hi Guys,
>> I am facing following problem with MSSQL Server 2000.
>> I am using Microsoft sql server Log Shipping feature to have a standby
>> server for disaster recovery purposes.
>> in the logshipping Destination server while restoring the transaction log
>> the restore process is failling with following error.
>> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
>> SQL
>> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
>> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
>> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
>> PrevPageLSN: (8122:7374:3)."
>> Both servers, source and destination were running with SQL SERVER
>> ENTERPRISE EDITION SP3 .
>> after this failure i rebuilded the logshipping process but same error
>> happened again so after one week updated the destination server with sql
>> sp4
>> and rebuilded the logshipping process . but the same error happened
>> again.
>> During this error SQL Server is creating a dump file and text file
>> pls advice
>|||Hi Mike,
can you pls advice how to open case with Microsoft PSS.
"Mike Epprecht (SQL MVP)" wrote:
> Biju.
> You have been having trouble with this DB many times in the past. You had
> corruption, it showed as fixed, and now it is back. You do not have torn
> page detection on. Turn ton page detect ion on.
> If your corruption continues, you will soon have a database that all you can
> do it delete.
> It may be worth your while to open a case with Microsoft PSS.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
> news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
> > The type of error you are seeing is most likely being caused by corruption
> > in your transaction logs. Review system event logs on both servers for ANY
> > hardware-related warnings and errors. Update all your firmware and
> > drivers.
> >
> > Adrian
> >
> >
> > "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> > message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> >> Hi Guys,
> >> I am facing following problem with MSSQL Server 2000.
> >> I am using Microsoft sql server Log Shipping feature to have a standby
> >> server for disaster recovery purposes.
> >> in the logshipping Destination server while restoring the transaction log
> >> the restore process is failling with following error.
> >>
> >> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> >> SQL
> >> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> >> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> >> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> >> PrevPageLSN: (8122:7374:3)."
> >>
> >> Both servers, source and destination were running with SQL SERVER
> >> ENTERPRISE EDITION SP3 .
> >> after this failure i rebuilded the logshipping process but same error
> >> happened again so after one week updated the destination server with sql
> >> sp4
> >> and rebuilded the logshipping process . but the same error happened
> >> again.
> >> During this error SQL Server is creating a dump file and text file
> >>
> >> pls advice
> >
> >
>
>|||Hi guys,
DBCC CHECKDB is not giving any errors or warnings and database is
functioning normally.
there are no hardware related errors in event viewer.
and we are using compaq cluster with compaq smart array controller so is
there any chance to have a torn page?this type of diska should have mechanism
to ensure that data is completely written and also there are no power failure
at all.
i am using transactional replication for reporting and it is working fine if
there was any problem with source database then it should also fail like
logshipping.
what should be the ideal temperature in datacentres/server romms currently
we are keeping 20-22 degree celcius is it ok?
Pls advice.
"Mike Epprecht (SQL MVP)" wrote:
> Biju.
> You have been having trouble with this DB many times in the past. You had
> corruption, it showed as fixed, and now it is back. You do not have torn
> page detection on. Turn ton page detect ion on.
> If your corruption continues, you will soon have a database that all you can
> do it delete.
> It may be worth your while to open a case with Microsoft PSS.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Adrian Zajkeskovic" <azajkeskovic@.hotmail.com> wrote in message
> news:tsednVPPk5VQVjPfRVn-3Q@.rogers.com...
> > The type of error you are seeing is most likely being caused by corruption
> > in your transaction logs. Review system event logs on both servers for ANY
> > hardware-related warnings and errors. Update all your firmware and
> > drivers.
> >
> > Adrian
> >
> >
> > "bijupg@.hotmail.com" <bijupghotmailcom@.discussions.microsoft.com> wrote in
> > message news:F6B405F2-0038-418C-8703-BB77E2C8435D@.microsoft.com...
> >> Hi Guys,
> >> I am facing following problem with MSSQL Server 2000.
> >> I am using Microsoft sql server Log Shipping feature to have a standby
> >> server for disaster recovery purposes.
> >> in the logshipping Destination server while restoring the transaction log
> >> the restore process is failling with following error.
> >>
> >> "[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3456: [Microsoft][ODBC
> >> SQL
> >> Server Driver][SQL Server]Could not redo log record (8130:35542:2), for
> >> transaction ID (0:91843227), on page (1:519336), database 'REVENUES' (5).
> >> Page: LSN = (8130:29950:3), type = 1. Log: OpCode = 3, context 2,
> >> PrevPageLSN: (8122:7374:3)."
> >>
> >> Both servers, source and destination were running with SQL SERVER
> >> ENTERPRISE EDITION SP3 .
> >> after this failure i rebuilded the logshipping process but same error
> >> happened again so after one week updated the destination server with sql
> >> sp4
> >> and rebuilded the logshipping process . but the same error happened
> >> again.
> >> During this error SQL Server is creating a dump file and text file
> >>
> >> pls advice
> >
> >
>
>sql

Logs with timestamp

Hi,

I'm using the SSIS log feature. However, I want a timestamp on the file everytime the IS package runs so as to keep track also that this particular log file is for this specific execution of IS.

How do i do that?

thanks a lot

cherriesh

Are you logging to SQL Server? If so, you can add a column to the dbo.sysdtslog90 table with a datetime datatype and set its default to GetDate().

I recommend that you read Jamie Thomson's blog about custom logging: http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Here's a post from Jamie that shows how to set the log file name dynamically through an expression:

http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx

Login Script

Hi,
Is there a feature in SQL Server that serves the same purpose a login
script?
TIA
BDB (bdb@.reply.to.group.com) writes:
> Is there a feature in SQL Server that serves the same purpose a login
> script?
No. There are some defaults you can set for a user - language and
database comes to mind - but that's about it.
On server level, you can define startup procedures that are run when
SQL Server starts, but that's another matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Login Script

Hi,
Is there a feature in SQL Server that serves the same purpose a login
script?
TIABDB (bdb@.reply.to.group.com) writes:
> Is there a feature in SQL Server that serves the same purpose a login
> script?
No. There are some defaults you can set for a user - language and
database comes to mind - but that's about it.
On server level, you can define startup procedures that are run when
SQL Server starts, but that's another matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Login Script

Hi,
Is there a feature in SQL Server that serves the same purpose a login
script?
TIABDB (bdb@.reply.to.group.com) writes:
> Is there a feature in SQL Server that serves the same purpose a login
> script?
No. There are some defaults you can set for a user - language and
database comes to mind - but that's about it.
On server level, you can define startup procedures that are run when
SQL Server starts, but that's another matter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp