May 21, 2012 at 9:19 am
Hi guys
I'm having trouble on finding out what causing this error on one of out sql servers.
Date: 5/21/2012 Source: MSSQLSERVER
Time: 3:00:00 AM Category (6)
Type: Error Event ID 17055
User: NT AUTHORITY\SYSTEM
Computer: 'sqlserver name'
Description
3041:
BACKUP failed to complete the command dump tranXpBL with truncate_only
dump tran PXBL with truncate_only
dump tran CMBL with truncate_only
dump tran RPBL with truncate_only
dump tran WRHBL with truncate_only
dump tran TLBL with truncate_only
dump tran Rep
Do you guys have any idea on what causes this errors and how to resolve them?
I had searched on GOOGLE but there seems to be loads of typical errors but no exact resolution.
Your effort will be highly appreciated.
Regards,
Shani
May 21, 2012 at 9:23 am
shani19831 (5/21/2012)
Hi guysI'm having trouble on finding out what causing this error on one of out sql servers.
Date: 5/21/2012 Source: MSSQLSERVER
Time: 3:00:00 AM Category (6)
Type: Error Event ID 17055
User: NT AUTHORITY\SYSTEM
Computer: 'sqlserver name'
Description
3041:
BACKUP failed to complete the command dump tranXpBL with truncate_only
dump tran PXBL with truncate_only
dump tran CMBL with truncate_only
dump tran RPBL with truncate_only
dump tran WRHBL with truncate_only
dump tran TLBL with truncate_only
dump tran Rep
Do you guys have any idea on what causes this errors and how to resolve them?
I had searched on GOOGLE but there seems to be loads of typical errors but no exact resolution.
Your effort will be highly appreciated.
Regards,
Shani
that syntax is pretty old - but since it's sql 7/2000 its legitimate (although i use backup log with truncate_only)
run the command sp_helpdb and check if your databases are in simple or full recovery mode - if they are in simple this would explain the issue - if they are in full then i'd advise changing them to simple (as you're not using log backups anyway)
MVDBA
May 21, 2012 at 9:40 am
That is old, and it's not something you normally do. I'd agree with Michael's advice.
Can you check the recovery model and post the exact code being used?
May 21, 2012 at 9:57 am
Hi, yes its a SQL 2000
and after running that sp_helpdb this is what i got in return and im now lost
No permission to access database 'PhoenixCBL_Restore'.
name db_size owner dbid created status compatibility_level
---------------------------------------------- ------------- ------ ------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CMBL 587.81 MB SQL01\Administrator 7 Aug 31 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
ETBL 185.75 MB SQL01\Administrator 8 Aug 31 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
ETBL_Restore 2.00 MB SQL01\Administrator 16 Oct 5 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
IX_DB 58.75 MB CB\admin_harmsolll 13 Dec 1 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
LoggingBL 765.44 MB sa 18 Aug 12 2010 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoShrink, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
master 17.69 MB sa 1 Aug 6 2000 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
model 6.75 MB sa 3 Aug 6 2000 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
msdb 338.25 MB sa 4 Aug 6 2000 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
PXBL 39371.13 MB SQL01\Administrator 6 Aug 26 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsRecursiveTriggersEnabled 80
PXBL_Cards_test 9478.88 MB sa 20 Jan 15 2011 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsRecursiveTriggersEnabled 80
ReportServerCB 7312.56 MB SQL01\Administrator 11 Aug 27 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
ReportServerCBTempDB 973.69 MB SQL01\Administrator 12 Aug 27 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=Latin1_General_CI_AS_KS_WS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
tempdb 350.69 MB sa 2 May 19 2012 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
TLBL 3138.81 MB SQL01\Administrator 10 Aug 31 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoShrink, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
WRHBL 34854.44 MB SQL01\Administrator 9 Aug 26 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AI, SQLSortOrder=54, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
WRHBL_Restore 2.00 MB SQL01\Administrator 17 Oct 5 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
XPBL 687.13 MB SQL01\Administrator 5 Aug 26 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
XPCB_Cards_test 158.56 MB sa 19 Jan 11 2011 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
XPCB_Restore 94.19 MB SQL01\Administrator 15 Oct 22 2009 Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=BULK_LOGGED, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics 80
May 21, 2012 at 10:00 am
Is that database in recovering or restoring mode?
May 21, 2012 at 10:04 am
Steve Jones - SSC Editor (5/21/2012)
Is that database in recovering or restoring mode?
Steve that I wouldn't know Honestly, unless you tell me how to see that, but this is the live database that gets backed up everyday, so i presume its recovering but i don't know...
May 21, 2012 at 10:05 am
looking at that you definatly have a couple of DB that are in simple (WRHL for one) and that will cause that statement to potentially fail..
i think you should really look at your strategy before implementing any changes though - your databases that are in full recovery are having the log truncated- which is pretty pointless.... you would be better just getting rid of those commands and switching to Simple recovery mode.. Its a lot safer
MVDBA
May 21, 2012 at 10:10 am
Enterprise Manager should show the status of the database in the tree view. Does it look like all others or is the icon different?
I don't have a 2000 instance handy or I'd test this for you.
May 21, 2012 at 10:14 am
Steve Jones - SSC Editor (5/21/2012)
Is that database in recovering or restoring mode?
from the sp_helpdb it looks as if they are all ONLINE
MVDBA
May 21, 2012 at 10:24 am
michael vessey (5/21/2012)
looking at that you definatly have a couple of DB that are in simple (WRHL for one) and that will cause that statement to potentially fail..i think you should really look at your strategy before implementing any changes though - your databases that are in full recovery are having the log truncated- which is pretty pointless.... you would be better just getting rid of those commands and switching to Simple recovery mode.. Its a lot safer
Micheal Thanks for the prompt but im still lost, if i want to change from full to simple how do i go about it?
-----The icon looks like a normal DB and its not restoring its in full view and is usable.........
May 21, 2012 at 10:30 am
there are 2 ways
right click on the database and choose properties and then in one of the screens there will be a recovery mode box (i don't have sql 2000 installed so i can't screen shot it)
or the easier way is using an SQL SCRIPT
ALTER DATABASE xxx SET RECOVERY SIMPLE
in older versions of sql you could also use the sp_dboption command
be sure to read up in books on-line before you make the change - just so you understand the implications.
MVDBA
May 21, 2012 at 10:44 am
michael vessey (5/21/2012)
there are 2 waysright click on the database and choose properties and then in one of the screens there will be a recovery mode box (i don't have sql 2000 installed so i can't screen shot it)
or the easier way is using an SQL SCRIPT
ALTER DATABASE xxx SET RECOVERY SIMPLE
in older versions of sql you could also use the sp_dboption command
be sure to read up in books on-line before you make the change - just so you understand the implications.
Mike i have had a look at the DB and right click got to see the properties and all the settings.... i will have to talk to the IT manager and the other guys and hear their inputs
Thanks and i will post back an update
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply