Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL SERVER EVENT ERROR Expand / Collapse
Author
Message
Posted Monday, May 21, 2012 9:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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
Post #1303453
Posted Monday, May 21, 2012 9:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
shani19831 (5/21/2012)
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


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
Post #1303459
Posted Monday, May 21, 2012 9:40 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:17 AM
Points: 31,278, Visits: 15,731
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?







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1303468
Posted Monday, May 21, 2012 9:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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


Post #1303483
Posted Monday, May 21, 2012 10:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:17 AM
Points: 31,278, Visits: 15,731
Is that database in recovering or restoring mode?






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1303486
Posted Monday, May 21, 2012 10:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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...
Post #1303489
Posted Monday, May 21, 2012 10:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #1303490
Posted Monday, May 21, 2012 10:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:17 AM
Points: 31,278, Visits: 15,731
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1303498
Posted Monday, May 21, 2012 10:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #1303499
Posted Monday, May 21, 2012 10:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 26, 2013 12:26 AM
Points: 47, Visits: 180
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.........
Post #1303509
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse