How to resolve "Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]"?

If SQL database is getting failed leaving the below error message.

Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

Also the database log file reveals the following:

Job 'CBI_FULL_Backup' : Step 1, 'CBI SQL BAckup - Full' : Began Executing 2012-01-04 01:00:00
10 percent backed up. [SQLSTATE 01000]
20 percent backed up. [SQLSTATE 01000]
30 percent backed up. [SQLSTATE 01000]
40 percent backed up. [SQLSTATE 01000]
50 percent backed up. [SQLSTATE 01000]
60 percent backed up. [SQLSTATE 01000]
70 percent backed up. [SQLSTATE 01000]
80 percent backed up. [SQLSTATE 01000]
90 percent backed up. [SQLSTATE 01000]
Processed 3565832 pages for database 'CBI', file 'CBI_Data' on file 1. [SQLSTATE 01000]
100 percent backed up. [SQLSTATE 01000]
Processed 1375 pages for database 'CBI', file 'CBI_Log' on file 1. [SQLSTATE 01000]
Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000]

Root Cause:

The backup file got corrupted

Workaround:

Renamed the backup file / device.

URL: http://mssql-tech.blogspot.com/2012/01/how-to-resolve-msg-3013-sev-16-backup.html

Other References:

http://support.microsoft.com/kb/290787

How to restore SQL Server (32 Bit) performance counters in Win 64Bit?

If we have x64 Windows and X86 SQL.

In this case, for looking into X86 instances of SQL, we need to use the 32 bit equivalent of Performance Monitor. We can find that in C:\Windows\SysWOW64 or open that from RUN Command by typing the following.

perfmon /32

Now how we do we load the Performance Objects and counters back (in case they are not present. I guess that’s not present and that’s what led you to this post in the first place.)

1) First unload the counters, to remove any remnants. We use the unlodctr component(shipped with Windows) to do that.

We need to run the following from the Run command.

unlodctr mssql$<instance name>

--> If we are dealing with a default instance, then we need to run

unlodctr mssqlserver

If it is not loaded, we will get a well self explanatory message.

2) Then we need to identify the proper INI file using which we would load the counters. The actual path for the counter can be located in the following location

HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQL$<instance name>\Performance\

The Key in this Location would be PerfIniFile

(For default instance HKLM\SYSTEM\Services\Currentcontrolset\Services\MSSQLServer\Performance\PerfIniFile)

For Named Instance the file name would be in the format perf-<instancename>sqlctr.ini

For Default instance it would be in the form of sqlctr.ini

The file is present in the Binn Directory for the respective instance. Using the Windows Desktop Search facility we might save ourselves some time, if we are not sure where to find the specific file.

 

3) Now use the lodctr utility (from RUN Command) to load the Objects and counters using the identified INI file from above step. We need to specify the complete path for the INI file as the only option for lodctr.

lodctr <complete path for the ini file identified from the above steps)

On a clustered instance the unlodctr and lodctr works the same way, its only that we might have to failover the SQL to each node and run the same steps on each node.

That's all :)

Note: There may be some other causes as well but here I presented the most typical cause of disappearing SQL performance counters and the steps how to restore them. 

URL: http://mssql-tech.blogspot.com/2011/12/how-to-restore-sql-server-32-bit.html

For further details please refer to the links below:

Other References:

http://www.sqlcoffee.com/Tuning02.htm

http://support.microsoft.com/kb/227662

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/03/18/what-should-we-do-when-the-sql-server-performance-counters-are-missing.aspx

How to resolve 'File system error: A FileStore error from WriteFile occurred. Physical file...' error message?

Cubes processing failed in SSAS 2008 R2

Error:

File system error: A FileStore error from WriteFile occurred. Physical file:  \\?\L:\Microsoft SQL Server\MSSQL.3\OLAP\Data\MSMDCacheRowset_xxx.tmp.
Logical file:

Workaround:

Check the dimensional property "ProcessingGroup" for all the relevant dimensions. If it's set to 'ByTable' try setting it to 'ByAttribute'

 

The reason this can cause processing issues with large dimensions (# of members, # of attributes, etc.) is because when using the ByTable setting, it will try to put the entire dimension into memory.

URL: http://mssql-tech.blogspot.com/2011/11/how-to-resolve-file-system-error.html


References:

http://stackoverflow.com/questions/420193/process-dimension-fails-with-message-a-filestore-error-from-writefile-occurred

http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/709ca808-12b1-49cd-9013-7f354a1b26b0/

http://mssqlbase.blogspot.com/2010/04/process-olap-dimensions-error-file.html

http://blogs.msdn.com/b/sqlcat/archive/2009/04/01/using-processinggroup-dimension-property-option-bytable-vs-byattribute-may-error-with-string-keys.aspx

How to troubleshoot Fatal error 605?

Problem:

While running queries you get the below error message.

Server: Msg 21, Level 21, State 1, Line 1

Warning: Fatal error 605 occurred at ....

Further checking the windows application event logs you find the below error message.

Attempt to fetch logical page %S_PGID in database '%.*ls' belongs to object '%.*ls', not to object '%.*ls'.

Cause:

This error occurs when Microsoft® SQL Server™ detects database corruption. The second object specified in the text not to object '%.*ls' is probably corrupt.

Solution:

Execute DBCC CHECKDB as soon as possible.

1) First try to drop the tables involved in the error message and recreate those tables from scratch.
OR
2) Extract all the data out into a new database.
OR
3) Use DBCC PAGE to try to figure out which tables in the database are affected and drop their statistics.

URL: http://mssql-tech.blogspot.com/2011/11/how-to-troubleshoot-fatal-error-605.html

Other References:

Check out the below links for more details on this topic.

http://msdn.microsoft.com/en-us/library/aa258728(v=sql.80).aspx
http://www.sqlservercentral.com/Forums/Topic967852-266-1.aspx
http://connect.microsoft.com/SQLServer/feedback/details/469811/attempt-to-fetch-logical-page-in-database-2-failed-it-belongs-to-allocation-unit-not-to