Failed DB back-up!

Question

Failed DB back-up!

jackcoppen

Valued Member

Points: 51
More actions
May 29, 2013 at 2:35 am

#277996

Failed database back-up
I get the following error message in my maintenainceplan history. Unfortunately I do not have much knowledge of SQL and I would like to ask if someone can further help me.
(Maintenance Plan has been working well for months.)
Type: SQL cluster (version SQl Server 2008 R2)
Taskdetail: Databases: **,**,**,**,**,**,**,**,**,**,**
Type: Full
Append existing
Error number: -1073548784
Error message:
Executing the query "BACKUP DATABASE [LogisP] TO DISK = N'\\\\****\\s..." failed with the following error: "A nonrecoverable I/O error occurred on file "
\\\\******\\sql\\SQLCLU04\\****_backup_2013_05_29_050502_3909779.bak:
" 112(failed to retrieve text for this error. Reason: 15105).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 1

I typically get 112 errors when there is not enough space on the disk for the backup. Have you checked the SQL error log to see if anything else was logged during that time?

Joie Andrew
"Since 1982"

jackcoppen Valued Member Points: 51 More actions · Answer 2

Thanks for the comments!

It was indeed insufficient disk space during the backup process. I am now using perfmon to figure out how much disk space disk space needed during the backup process. minimum free

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 3

Glad that you figured out what it was. Perfmon will not tell you how much space you need for your dbs though. You should either run sp_helpdb against the dbs you are looking to backup to see how much space you are going to need for a full backup.

Joie Andrew
"Since 1982"

jackcoppen Valued Member Points: 51 More actions · Answer 4

When i execute sp_helpdb I get to see a list of all database including size. Should I add up all sizes and keep this as a minimum free disk space?

And I can run this command only in 2005 or later. Is there an alternative to sql2000?

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 5

I would say it depends on a lot of factors. When you run sp_helpdb without specifying a db name it gives you the total size of the data file plus the log file, to include the white space that those files are taking up. I would say this is a good place to start though. Another thing to consider is where your backups are going. If they are residing on the same disks as the database files then the size of the databases plus the estimated size you will need for your backups (ensure you account for how many backups you plan to keep and for how long) plus space for db growth will need to be accounted for.

Joie Andrew
"Since 1982"

sql-lover SSCoach Points: 18530 More actions · Answer 6

jackcoppen (5/29/2013)
When i execute sp_helpdb I get to see a list of all database including size. Should I add up all sizes and keep this as a minimum free disk space?
And I can run this command only in 2005 or later. Is there an alternative to sql2000?

Find how much data (used portion of data, not free space) each database uses. Uncompressed FULL backups require that plus a portion of Tlog to ensure consistency at the time of restore or backup.

So let's say you have a 10GB database but you are only using 4GB of that. More or less, you may need 4GB at least, plus a bit extra for Tlog information, if the backup is uncompressed.

Take my script as example to collect used space:

--COLLECTING INFORMATION

CREATE TABLE #TempDBSize(

[name] [varchar](100) NOT NULL,

[database_id] [int] NOT NULL,

[Size] [decimal](10, 2) NOT NULL,

[UsedSpace] [decimal](10, 2) NOT NULL

)

EXECUTE master.sys.sp_MSforeachdb

'

USE [?];

INSERT INTO #TempDBSize

SELECT

SD.name,

MF.database_id,

SUM( CONVERT(decimal(10,2),(DF.size/128.0)) ) as Size,

SUM( CONVERT(decimal(10,2), (CAST(FILEPROPERTY(DF.name, "SpaceUsed") AS INT)/128.0 ) ) ) AS UsedSpace

FROM sys.master_files MF JOIN sys.databases SD

ON SD.database_id = MF.database_id

JOIN sys.database_files DF

ON DF.physical_name collate DATABASE_DEFAULT = MF.physical_name collate DATABASE_DEFAULT

WHERE MF.type = 0

GROUP BY SD.name, MF.database_id

'

SELECT

SUM (Size)/1000 as TotalSize,

SUM (UsedSpace)/1000 as TotalUsedSpace

FROM

#TempDBSize;

SELECT *

FROM

#TempDBSize;

DROP TABLE #TempDBSize;

Tested on SQL 2012, but I think it should work on SQL 2005 and above.