gyans
asked on
Cannot dump the transaction log
The log got full and is in suspended state.
I cannot dump the log it is giving me the following error:
1> dump tran report_data with truncate_only
2> go
DUMP TRANSACTION for database 'report_data' could not truncate the log. Either
extend the log using ALTER DATABASE ... LOG ON command or eliminate the oldest
active transaction in database 'report_data' shown in syslogshold table.
I got the oldest active transaction log:
1> select * from syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- --------------
--------------------------
--------------------------
-----------
6 0 193 763512 0x000ba6780003 0x000000000000
May 7 2005 12:27PM
$queryplans_insert
386
(1 row affected)
1>
How do I correct this ,thank you.
I cannot dump the log it is giving me the following error:
1> dump tran report_data with truncate_only
2> go
DUMP TRANSACTION for database 'report_data' could not truncate the log. Either
extend the log using ALTER DATABASE ... LOG ON command or eliminate the oldest
active transaction in database 'report_data' shown in syslogshold table.
I got the oldest active transaction log:
1> select * from syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
xloid
------ ----------- ------ ----------- -------------- --------------
--------------------------
--------------------------
-----------
6 0 193 763512 0x000ba6780003 0x000000000000
May 7 2005 12:27PM
$queryplans_insert
386
(1 row affected)
1>
How do I correct this ,thank you.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
We should add to Waleed's comment that the "trunc log on chkpt" option means it's no longer possible to do any regular transaction dumps (ie incremental backups). Both DUMP TRAN ... WITH TRUNCATE_ONLY and WITH NO_LOG also disable incremental backups until the next full database dump is done.