Backup Slow Throughput

Hi Team,

I have a SQL 2000 instance with 46 Databases (all databases put together will be 15 GB in size). I am running a SQL Backup using a third party software.

My full backup of the SQL instance which backs up 15 GB of data finishes within 30-45 minutes. But, my differential backup of the same instance which backs up only 150 -250 MB of data takes 12 hours to backup.

I found a knowledge article from MS Support site which says, differntial backup would take more time than full in few scenarios.

http://support.microsoft.com/default.aspx?scid=kb;en-us;196658

But the above document is for SQL 7.0. Will it be the same for SQL 2000 and 2005 too? If yes, can you please tell if I can increase the speed of differential backup in my environment. Should I modify any SQL Parameters?

Please let me know your thoughts on this..

Thanks
Santhosh

This really doesn't make sense.

The codepath for differential backups is the same as for fulls. The only difference is that in differential backups, we optionally skip writing some pages to the backup stream. The test to see if we should skip a page is very fast. Generally, worst case for differential backups is that they take the same time as a full backup.

Is there perhaps some other backup activity going on at the same time which could be causing lock contention?

If not, you can try starting SQL with traceflags 3605 and 3004 and sending the log to us (kevin.farlee@.microsoft.com). Those flags will cause more verbose logging about the backup process.

|||

"starting SQL with traceflags 3605 and 3004" - Can you be more specific on how to start SQL with trace flags.

I have not done this before. Also , it would be great if you could say what are we going to achieve by starting SQL with traceflags 3605 and 3004 .

Thank you,

Santhosh

|||

You can enable the trace flags with the command:

DBCC TRACEON(3004, 3605, -1)

What this does is to cause SQL to log much more detailed information about the backup process in the SQL log.

This information will help us to determine what is happening.

After collecting this information, you can use the command

DBCC TRACEOFF(3004, 3605, -1)

to disable the trace flags and go back to normal logging.

Backup Sizes in Taskpad and Best Practice

Quick question, does anyone know how the backup size of a database is
determined? I am trying to calculate particular size of a database and its
backup size.
SQL Server 2000 database has many types of size calculations, for example
A. Under Properties -- General Tab
1. Size: 124800.78MB
2. Space Available: 64001.71 MB
B. Under Properties -- Data Files Tab
1. Space allocated: 104013 MB
2. Space allocated: 20789 MB
C. Under Taskpad -- General
1. Data: 104012.56MB | 60445.12MB (Used)| 43567.44MB (Free)
2. Transaction Log space: 20788.49MB | 69.09MB (Used)| 20719MB (Free)
How do I know the size of the database backup given the info above? What do
people usually practise to minimize the backup size while not impeding the
performance and availability?
Thanks,
C TO
A is the combined physical file sizes of data and log files on teh drive
B is is individual physical file sizes
C shows how much of the lhysical file size is being used by data
I just checked one of my backups and it appears to be the size of my actual
data for both files, not the size of the physical files, which makes sense.
If you want to minimize the size of you backup files as well as the time it
takes, look into a product called SQL Litespeed from Imceda (yes, I am a
reseller, and for good reason)
HTH,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:B28AE393-46E0-49D4-8B59-52DF29ED813A@.microsoft.com...
> Quick question, does anyone know how the backup size of a database is
> determined? I am trying to calculate particular size of a database and its
> backup size.
> SQL Server 2000 database has many types of size calculations, for example
> A. Under Properties -- General Tab
> 1. Size: 124800.78MB
> 2. Space Available: 64001.71 MB
> B. Under Properties -- Data Files Tab
> 1. Space allocated: 104013 MB
> 2. Space allocated: 20789 MB
> C. Under Taskpad -- General
> 1. Data: 104012.56MB | 60445.12MB (Used)| 43567.44MB (Free)
> 2. Transaction Log space: 20788.49MB | 69.09MB (Used)| 20719MB (Free)
> How do I know the size of the database backup given the info above? What
> do
> people usually practise to minimize the backup size while not impeding the
> performance and availability?
> Thanks,
> C TO

Backup Sizes in Taskpad and Best Practice

Quick question, does anyone know how the backup size of a database is
determined? I am trying to calculate particular size of a database and its
backup size.
SQL Server 2000 database has many types of size calculations, for example
A. Under Properties -- General Tab
1. Size: 124800.78MB
2. Space Available: 64001.71 MB
B. Under Properties -- Data Files Tab
1. Space allocated: 104013 MB
2. Space allocated: 20789 MB
C. Under Taskpad -- General
1. Data: 104012.56MB | 60445.12MB (Used)| 43567.44MB (Free)
2. Transaction Log space: 20788.49MB | 69.09MB (Used)| 20719MB (Free)
How do I know the size of the database backup given the info above? What do
people usually practise to minimize the backup size while not impeding the
performance and availability?
Thanks,
C TOA is the combined physical file sizes of data and log files on teh drive
B is is individual physical file sizes
C shows how much of the lhysical file size is being used by data
I just checked one of my backups and it appears to be the size of my actual
data for both files, not the size of the physical files, which makes sense.
If you want to minimize the size of you backup files as well as the time it
takes, look into a product called SQL Litespeed from Imceda (yes, I am a
reseller, and for good reason)
HTH,
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:B28AE393-46E0-49D4-8B59-52DF29ED813A@.microsoft.com...
> Quick question, does anyone know how the backup size of a database is
> determined? I am trying to calculate particular size of a database and its
> backup size.
> SQL Server 2000 database has many types of size calculations, for example
> A. Under Properties -- General Tab
> 1. Size: 124800.78MB
> 2. Space Available: 64001.71 MB
> B. Under Properties -- Data Files Tab
> 1. Space allocated: 104013 MB
> 2. Space allocated: 20789 MB
> C. Under Taskpad -- General
> 1. Data: 104012.56MB | 60445.12MB (Used)| 43567.44MB (Free)
> 2. Transaction Log space: 20788.49MB | 69.09MB (Used)| 20719MB (Free)
> How do I know the size of the database backup given the info above? What
> do
> people usually practise to minimize the backup size while not impeding the
> performance and availability?
> Thanks,
> C TO

Backup Sizes in Taskpad and Best Practice

Quick question, does anyone know how the backup size of a database is
determined? I am trying to calculate particular size of a database and its
backup size.
SQL Server 2000 database has many types of size calculations, for example
A. Under Properties -- General Tab
1. Size: 124800.78MB
2. Space Available: 64001.71 MB
B. Under Properties -- Data Files Tab
1. Space allocated: 104013 MB
2. Space allocated: 20789 MB
C. Under Taskpad -- General
1. Data: 104012.56MB | 60445.12MB (Used)| 43567.44MB (Free)
2. Transaction Log space: 20788.49MB | 69.09MB (Used)| 20719MB (Free)
How do I know the size of the database backup given the info above? What do
people usually practise to minimize the backup size while not impeding the
performance and availability?
Thanks,
C TOA is the combined physical file sizes of data and log files on teh drive
B is is individual physical file sizes
C shows how much of the lhysical file size is being used by data
I just checked one of my backups and it appears to be the size of my actual
data for both files, not the size of the physical files, which makes sense.
If you want to minimize the size of you backup files as well as the time it
takes, look into a product called SQL Litespeed from Imceda (yes, I am a
reseller, and for good reason)
HTH,
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:B28AE393-46E0-49D4-8B59-52DF29ED813A@.microsoft.com...
> Quick question, does anyone know how the backup size of a database is
> determined? I am trying to calculate particular size of a database and its
> backup size.
> SQL Server 2000 database has many types of size calculations, for example
> A. Under Properties -- General Tab
> 1. Size: 124800.78MB
> 2. Space Available: 64001.71 MB
> B. Under Properties -- Data Files Tab
> 1. Space allocated: 104013 MB
> 2. Space allocated: 20789 MB
> C. Under Taskpad -- General
> 1. Data: 104012.56MB | 60445.12MB (Used)| 43567.44MB (Free)
> 2. Transaction Log space: 20788.49MB | 69.09MB (Used)| 20719MB (Free)
> How do I know the size of the database backup given the info above? What
> do
> people usually practise to minimize the backup size while not impeding the
> performance and availability?
> Thanks,
> C TO

Backup sizes

We use LiteSpeed backups and was wondering if there is a way in one of those
msdb backup tables that shows me the size of the compressed backups for each
of my dbs?
If so, whats the query?
All i want to know is name of db and its backup size.Take a look at this script:
http://www.sqlcommunity.com/Default.aspx?grm2id=50&tabid=56
Thank you,
Saleem Hakani (World Wide SQL Server Community)
HTTP://WWW.SQLCOMMUNITY.COM
SQLTips, Scripts, Discussions, Radio, Blogs, Articles and a lot more of SQL
Fun.
"Hassan" wrote:
> We use LiteSpeed backups and was wondering if there is a way in one of those
> msdb backup tables that shows me the size of the compressed backups for each
> of my dbs?
> If so, whats the query?
> All i want to know is name of db and its backup size.
>
>|||Saleem this script shows me the actual database size , but I am using
LiteSpeed backup and when i compare the output of your results to the size
on disk, they are different. Your output gives me the size assuming I do a
native backup and not SQL.
How can i get the sizes of the backups that I am using and in this case
LiteSpeed ? are there special LiteSpeed tables ?
"Saleem Hakani" <SaleemHakani@.discussions.microsoft.com> wrote in message
news:3AE9390E-886F-4CAB-8E3B-F06E0BDB0801@.microsoft.com...
> Take a look at this script:
> http://www.sqlcommunity.com/Default.aspx?grm2id=50&tabid=56
> Thank you,
> Saleem Hakani (World Wide SQL Server Community)
> HTTP://WWW.SQLCOMMUNITY.COM
> SQLTips, Scripts, Discussions, Radio, Blogs, Articles and a lot more of
> SQL
> Fun.
>
> "Hassan" wrote:
>> We use LiteSpeed backups and was wondering if there is a way in one of
>> those
>> msdb backup tables that shows me the size of the compressed backups for
>> each
>> of my dbs?
>> If so, whats the query?
>> All i want to know is name of db and its backup size.
>>sql

backup size and initial database size

SQL server 2000
Hi
How can i find
1. Information about all/current backup files, size of backup file on
server using sql query or a script.
2. Initial size of all databases (when they were installed) and current size
of all databases.
Thanks
ontario, canada
I have information about all database backups for last two years (Including
database size in table Backupset in MSDB.
Does % increase in database backup size reflects the % increase in database
size?
ontario, canada
"db" wrote:
> SQL server 2000
> Hi
> How can i find
> 1. Information about all/current backup files, size of backup file on
> server using sql query or a script.
> 2. Initial size of all databases (when they were installed) and current size
> of all databases.
> Thanks
> ontario, canada
|||I am using
select database_name,database_creation_date,backup_start_ date,backup_size
from backupset order by database_name,backup_size desc
to get the bacup size information. I get the information.
I want to select a subset of records which give me information for backupset
for one day of every month (10-...200?). I.e monthwise backup set
information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING COMMAND
select database_name,database_creation_date,backup_start_ date,backup_size
from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
database_name,backup_size desc
I do not get the desired result. What is correct sql query?
ontario, canada
"db" wrote:
[vbcol=seagreen]
> I have information about all database backups for last two years (Including
> database size in table Backupset in MSDB.
> Does % increase in database backup size reflects the % increase in database
> size?
> --
> ontario, canada
> "db" wrote:
|||Select ...
Where BACKUP_START_DATE >= '20000101' And Day(BACKUP_START_DATE) = 10
Tom
"db" <db@.discussions.microsoft.com> wrote in message
news:BCC341DB-9254-43E5-B4FC-08F90F86A325@.microsoft.com...[vbcol=seagreen]
>I am using
> select database_name,database_creation_date,backup_start_ date,backup_size
> from backupset order by database_name,backup_size desc
> to get the bacup size information. I get the information.
> I want to select a subset of records which give me information for
> backupset
> for one day of every month (10-...200?). I.e monthwise backup set
> information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING
> COMMAND
> select database_name,database_creation_date,backup_start_ date,backup_size
> from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
> database_name,backup_size desc
> I do not get the desired result. What is correct sql query?
>
> --
> ontario, canada
>
> "db" wrote:

backup size and initial database size

SQL server 2000
Hi
How can i find
1. Information about all/current backup files, size of backup file on
server using sql query or a script.
2. Initial size of all databases (when they were installed) and current size
of all databases.
Thanks
--
ontario, canadaI have information about all database backups for last two years (Including
database size in table Backupset in MSDB.
Does % increase in database backup size reflects the % increase in database
size'
--
ontario, canada
"db" wrote:
> SQL server 2000
> Hi
> How can i find
> 1. Information about all/current backup files, size of backup file on
> server using sql query or a script.
> 2. Initial size of all databases (when they were installed) and current size
> of all databases.
> Thanks
> ontario, canada|||I am using
select database_name,database_creation_date,backup_start_date,backup_size
from backupset order by database_name,backup_size desc
to get the bacup size information. I get the information.
I want to select a subset of records which give me information for backupset
for one day of every month (10-...200?). I.e monthwise backup set
information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING COMMAND
select database_name,database_creation_date,backup_start_date,backup_size
from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
database_name,backup_size desc
I do not get the desired result. What is correct sql query?
ontario, canada
"db" wrote:
> I have information about all database backups for last two years (Including
> database size in table Backupset in MSDB.
> Does % increase in database backup size reflects the % increase in database
> size'
> --
> ontario, canada
> "db" wrote:
> > SQL server 2000
> > Hi
> > How can i find
> > 1. Information about all/current backup files, size of backup file on
> > server using sql query or a script.
> > 2. Initial size of all databases (when they were installed) and current size
> > of all databases.
> > Thanks
> > ontario, canada|||Select ...
Where BACKUP_START_DATE >= '20000101' And Day(BACKUP_START_DATE) = 10
Tom
"db" <db@.discussions.microsoft.com> wrote in message
news:BCC341DB-9254-43E5-B4FC-08F90F86A325@.microsoft.com...
>I am using
> select database_name,database_creation_date,backup_start_date,backup_size
> from backupset order by database_name,backup_size desc
> to get the bacup size information. I get the information.
> I want to select a subset of records which give me information for
> backupset
> for one day of every month (10-...200?). I.e monthwise backup set
> information for 10-jan, 10-feb-10-mar...etc). When I issue FOLLOWING
> COMMAND
> select database_name,database_creation_date,backup_start_date,backup_size
> from backupset WHERE BACKUP_START_DATE LIKE '10-...200?) order by
> database_name,backup_size desc
> I do not get the desired result. What is correct sql query?
>
> --
> ontario, canada
>
> "db" wrote:
>> I have information about all database backups for last two years
>> (Including
>> database size in table Backupset in MSDB.
>> Does % increase in database backup size reflects the % increase in
>> database
>> size'
>> --
>> ontario, canada
>> "db" wrote:
>> > SQL server 2000
>> > Hi
>> > How can i find
>> > 1. Information about all/current backup files, size of backup file on
>> > server using sql query or a script.
>> > 2. Initial size of all databases (when they were installed) and current
>> > size
>> > of all databases.
>> > Thanks
>> > ontario, canada

Backup Size - Larger than db size

I have a database which I am backing up nightly ( after a log truncate, dbcc
checkdb ) and the .bak size is a lot larger than the actual database.
I am looking at the table space, and I think it might be the reserved space
also being backed up, I thought that only actual data was backed up.
Any pointers would be appreciated. Any more info require pls let me know.Are you perhaps by mistake doing append to the backup device? You can see wh
at backups are on a
backup device using RESTORE HEADERONLY.
Backup will copy all extents which are in use, so even if only one page are
used on an extent, the
whole extent will be included in the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message news:eSbUf.14129$814.11725@.newsfe5-win.ntl
i.net...
>I have a database which I am backing up nightly ( after a log truncate, dbc
c checkdb ) and the .bak
>size is a lot larger than the actual database.
> I am looking at the table space, and I think it might be the reserved spac
e also being backed up,
> I thought that only actual data was backed up.
> Any pointers would be appreciated. Any more info require pls let me know.
>
>

Backup Size - Larger than db size

I have a database which I am backing up nightly ( after a log truncate, dbcc
checkdb ) and the .bak size is a lot larger than the actual database.
I am looking at the table space, and I think it might be the reserved space
also being backed up, I thought that only actual data was backed up.
Any pointers would be appreciated. Any more info require pls let me know.Are you perhaps by mistake doing append to the backup device? You can see what backups are on a
backup device using RESTORE HEADERONLY.
Backup will copy all extents which are in use, so even if only one page are used on an extent, the
whole extent will be included in the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message news:eSbUf.14129$814.11725@.newsfe5-win.ntli.net...
>I have a database which I am backing up nightly ( after a log truncate, dbcc checkdb ) and the .bak
>size is a lot larger than the actual database.
> I am looking at the table space, and I think it might be the reserved space also being backed up,
> I thought that only actual data was backed up.
> Any pointers would be appreciated. Any more info require pls let me know.
>
>

Backup Size - Larger than db size

I have a database which I am backing up nightly ( after a log truncate, dbcc
checkdb ) and the .bak size is a lot larger than the actual database.
I am looking at the table space, and I think it might be the reserved space
also being backed up, I thought that only actual data was backed up.
Any pointers would be appreciated. Any more info require pls let me know.
Are you perhaps by mistake doing append to the backup device? You can see what backups are on a
backup device using RESTORE HEADERONLY.
Backup will copy all extents which are in use, so even if only one page are used on an extent, the
whole extent will be included in the backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message news:eSbUf.14129$814.11725@.newsfe5-win.ntli.net...
>I have a database which I am backing up nightly ( after a log truncate, dbcc checkdb ) and the .bak
>size is a lot larger than the actual database.
> I am looking at the table space, and I think it might be the reserved space also being backed up,
> I thought that only actual data was backed up.
> Any pointers would be appreciated. Any more info require pls let me know.
>
>
sql

Backup problem at Cluster (SQL Server 2000)

Hello,
SQL Server 2000 sitting on cluster windows2003. Complete backup to local
disk file, file size was about 4G if sometimes it could be finished
successfuly. Most times (80%) it failed.
Successful complete backup running took less than 4 minutes.
If failed, normally stopped at about 23 seconds.
Tansaction log backup is no problem.
job step:
BACKUP DATABASE [dbname] TO DISK = N'E:\Program Files\Microsoft SQL
Server\MSSQL$SQLC02\BACKUP\dbname.bak' WITH INIT , NOUNLOAD , NAME =
N'dbname full backup', NOSKIP , STATS = 10, NOFORMAT
manually run the job get this err:
Executed as user: domainname\user1. ConnectionRead (WrapperRead()).
[SQLSTATE 01000] (Message 258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 10 percent backed up. [SQLSTATE
01000] (Error 3211). The step failed.
Database has 1 data file + 1 log file. (I know this is stupid to be having
only one data file, but nothing I can do to it now.)
I really need some directions. This issue is killing me.
Thanks for help!!!!!!!!!!
Tom
Sorry to ask obvious questions but... How big is your full DB
(sp_helpdb)? About 4GB I assume. How much free disk space do you have
on your E: drive? Does it always fail around the same point (somewhere
between 10% and 20%)? Is E: a local disk, a SAN attached disk or some
network mapped disk?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tom Runzer wrote:

>Hello,
>SQL Server 2000 sitting on cluster windows2003. Complete backup to local
>disk file, file size was about 4G if sometimes it could be finished
>successfuly. Most times (80%) it failed.
>Successful complete backup running took less than 4 minutes.
>If failed, normally stopped at about 23 seconds.
>Tansaction log backup is no problem.
>job step:
>BACKUP DATABASE [dbname] TO DISK = N'E:\Program Files\Microsoft SQL
>Server\MSSQL$SQLC02\BACKUP\dbname.bak' WITH INIT , NOUNLOAD , NAME =
>N'dbname full backup', NOSKIP , STATS = 10, NOFORMAT
>manually run the job get this err:
>Executed as user: domainname\user1. ConnectionRead (WrapperRead()).
>[SQLSTATE 01000] (Message 258) General network error. Check your network
>documentation. [SQLSTATE 08S01] (Error 11) 10 percent backed up. [SQLSTATE
>01000] (Error 3211). The step failed.
>Database has 1 data file + 1 log file. (I know this is stupid to be having
>only one data file, but nothing I can do to it now.)
>I really need some directions. This issue is killing me.
>Thanks for help!!!!!!!!!!
>Tom
>
|||How big is your full DB (sp_helpdb)? About 4GB I assume.
4471 MB
How much free disk space do you have on your E: drive?
55 GB free, also host data file and log file.
Does it always fail around the same point (somewhere > between 10% and 20%)?
yes, after 22 sec, but I couldn't see the percentage, but should be
within 10%
Is E: a local disk, a SAN attached disk or some network mapped disk?
local disk.
Thanks for help!!!!!
"Mike Hodgson" wrote:

> Sorry to ask obvious questions but... How big is your full DB
> (sp_helpdb)? About 4GB I assume. How much free disk space do you have
> on your E: drive? Does it always fail around the same point (somewhere
> between 10% and 20%)? Is E: a local disk, a SAN attached disk or some
> network mapped disk?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Tom Runzer wrote:
>

Backup problem at Cluster (SQL Server 2000)

Hello,
SQL Server 2000 sitting on cluster windows2003. Complete backup to local
disk file, file size was about 4G if sometimes it could be finished
successfuly. Most times (80%) it failed.
Successful complete backup running took less than 4 minutes.
If failed, normally stopped at about 23 seconds.
Tansaction log backup is no problem.
job step:
BACKUP DATABASE [dbname] TO DISK = N'E:\Program Files\Microsoft SQL
Server\MSSQL$SQLC02\BACKUP\dbname.bak' WITH INIT , NOUNLOAD , NAME =
N'dbname full backup', NOSKIP , STATS = 10, NOFORMAT
manually run the job get this err:
Executed as user: domainname\user1. ConnectionRead (WrapperRead()).
[SQLSTATE 01000] (Message 258) General network error. Check your networ
k
documentation. [SQLSTATE 08S01] (Error 11) 10 percent backed up. [S
QLSTATE
01000] (Error 3211). The step failed.
Database has 1 data file + 1 log file. (I know this is stupid to be having
only one data file, but nothing I can do to it now.)
I really need some directions. This issue is killing me.
Thanks for help!!!!!!!!!!
TomSorry to ask obvious questions but... How big is your full DB
(sp_helpdb)? About 4GB I assume. How much free disk space do you have
on your E: drive? Does it always fail around the same point (somewhere
between 10% and 20%)? Is E: a local disk, a SAN attached disk or some
network mapped disk?
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tom Runzer wrote:

>Hello,
>SQL Server 2000 sitting on cluster windows2003. Complete backup to local
>disk file, file size was about 4G if sometimes it could be finished
>successfuly. Most times (80%) it failed.
>Successful complete backup running took less than 4 minutes.
>If failed, normally stopped at about 23 seconds.
>Tansaction log backup is no problem.
>job step:
>BACKUP DATABASE [dbname] TO DISK = N'E:\Program Files\Microsoft SQL
>Server\MSSQL$SQLC02\BACKUP\dbname.bak' WITH INIT , NOUNLOAD , NAME =
>N'dbname full backup', NOSKIP , STATS = 10, NOFORMAT
>manually run the job get this err:
>Executed as user: domainname\user1. ConnectionRead (WrapperRead()).
>[SQLSTATE 01000] (Message 258) General network error. Check your netwo
rk
>documentation. [SQLSTATE 08S01] (Error 11) 10 percent backed up. [
SQLSTATE
>01000] (Error 3211). The step failed.
>Database has 1 data file + 1 log file. (I know this is stupid to be having
>only one data file, but nothing I can do to it now.)
>I really need some directions. This issue is killing me.
>Thanks for help!!!!!!!!!!
>Tom
>|||How big is your full DB (sp_helpdb)? About 4GB I assume.
4471 MB
How much free disk space do you have on your E: drive?
55 GB free, also host data file and log file.
Does it always fail around the same point (somewhere > between 10% and 20%)?
yes, after 22 sec, but I couldn't see the percentage, but should be
within 10%
Is E: a local disk, a SAN attached disk or some network mapped disk?
local disk.
Thanks for help!!!!!
"Mike Hodgson" wrote:

> Sorry to ask obvious questions but... How big is your full DB
> (sp_helpdb)? About 4GB I assume. How much free disk space do you have
> on your E: drive? Does it always fail around the same point (somewhere
> between 10% and 20%)? Is E: a local disk, a SAN attached disk or some
> network mapped disk?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Tom Runzer wrote:
>
>

Backup problem at Cluster (SQL Server 2000)

Hello,
SQL Server 2000 sitting on cluster windows2003. Complete backup to local
disk file, file size was about 4G if sometimes it could be finished
successfuly. Most times (80%) it failed.
Successful complete backup running took less than 4 minutes.
If failed, normally stopped at about 23 seconds.
Tansaction log backup is no problem.
job step:
BACKUP DATABASE [dbname] TO DISK = N'E:\Program Files\Microsoft SQL
Server\MSSQL$SQLC02\BACKUP\dbname.bak' WITH INIT , NOUNLOAD , NAME = N'dbname full backup', NOSKIP , STATS = 10, NOFORMAT
manually run the job get this err:
Executed as user: domainname\user1. ConnectionRead (WrapperRead()).
[SQLSTATE 01000] (Message 258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 10 percent backed up. [SQLSTATE
01000] (Error 3211). The step failed.
Database has 1 data file + 1 log file. (I know this is stupid to be having
only one data file, but nothing I can do to it now.)
I really need some directions. This issue is killing me.
Thanks for help!!!!!!!!!!
TomThis is a multi-part message in MIME format.
--010909050504010908020706
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Sorry to ask obvious questions but... How big is your full DB
(sp_helpdb)? About 4GB I assume. How much free disk space do you have
on your E: drive? Does it always fail around the same point (somewhere
between 10% and 20%)? Is E: a local disk, a SAN attached disk or some
network mapped disk?
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Tom Runzer wrote:
>Hello,
>SQL Server 2000 sitting on cluster windows2003. Complete backup to local
>disk file, file size was about 4G if sometimes it could be finished
>successfuly. Most times (80%) it failed.
>Successful complete backup running took less than 4 minutes.
>If failed, normally stopped at about 23 seconds.
>Tansaction log backup is no problem.
>job step:
>BACKUP DATABASE [dbname] TO DISK = N'E:\Program Files\Microsoft SQL
>Server\MSSQL$SQLC02\BACKUP\dbname.bak' WITH INIT , NOUNLOAD , NAME =>N'dbname full backup', NOSKIP , STATS = 10, NOFORMAT
>manually run the job get this err:
>Executed as user: domainname\user1. ConnectionRead (WrapperRead()).
>[SQLSTATE 01000] (Message 258) General network error. Check your network
>documentation. [SQLSTATE 08S01] (Error 11) 10 percent backed up. [SQLSTATE
>01000] (Error 3211). The step failed.
>Database has 1 data file + 1 log file. (I know this is stupid to be having
>only one data file, but nothing I can do to it now.)
>I really need some directions. This issue is killing me.
>Thanks for help!!!!!!!!!!
>Tom
>
--010909050504010908020706
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Sorry to ask obvious questions but... How big is your full DB
(sp_helpdb)? About 4GB I assume. How much free disk space do you have
on your E: drive? Does it always fail around the same point (somewhere
between 10% and 20%)? Is E: a local disk, a SAN attached disk or some
network mapped disk?</tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Tom Runzer wrote:
<blockquote cite="mid43F74958-2081-4A5C-B5F3-55ECDFD62FB8@.microsoft.com"
type="cite">
<pre wrap="">Hello,
SQL Server 2000 sitting on cluster windows2003. Complete backup to local
disk file, file size was about 4G if sometimes it could be finished
successfuly. Most times (80%) it failed.
Successful complete backup running took less than 4 minutes.
If failed, normally stopped at about 23 seconds.
Tansaction log backup is no problem.
job step:
BACKUP DATABASE [dbname] TO DISK = N'E:\Program Files\Microsoft SQL
Server\MSSQL$SQLC02\BACKUP\dbname.bak' WITH INIT , NOUNLOAD , NAME =N'dbname full backup', NOSKIP , STATS = 10, NOFORMAT
manually run the job get this err:
Executed as user: domainname\user1. ConnectionRead (WrapperRead()).
[SQLSTATE 01000] (Message 258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 10 percent backed up. [SQLSTATE
01000] (Error 3211). The step failed.
Database has 1 data file + 1 log file. (I know this is stupid to be having
only one data file, but nothing I can do to it now.)
I really need some directions. This issue is killing me.
Thanks for help!!!!!!!!!!
Tom
</pre>
</blockquote>
</body>
</html>
--010909050504010908020706--|||How big is your full DB (sp_helpdb)? About 4GB I assume.
4471 MB
How much free disk space do you have on your E: drive?
55 GB free, also host data file and log file.
Does it always fail around the same point (somewhere > between 10% and 20%)?
yes, after 22 sec, but I couldn't see the percentage, but should be
within 10%
Is E: a local disk, a SAN attached disk or some network mapped disk?
local disk.
Thanks for help!!!!!
"Mike Hodgson" wrote:
> Sorry to ask obvious questions but... How big is your full DB
> (sp_helpdb)? About 4GB I assume. How much free disk space do you have
> on your E: drive? Does it always fail around the same point (somewhere
> between 10% and 20%)? Is E: a local disk, a SAN attached disk or some
> network mapped disk?
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Tom Runzer wrote:
> >Hello,
> >
> >SQL Server 2000 sitting on cluster windows2003. Complete backup to local
> >disk file, file size was about 4G if sometimes it could be finished
> >successfuly. Most times (80%) it failed.
> >
> >Successful complete backup running took less than 4 minutes.
> >If failed, normally stopped at about 23 seconds.
> >Tansaction log backup is no problem.
> >
> >job step:
> >BACKUP DATABASE [dbname] TO DISK = N'E:\Program Files\Microsoft SQL
> >Server\MSSQL$SQLC02\BACKUP\dbname.bak' WITH INIT , NOUNLOAD , NAME => >N'dbname full backup', NOSKIP , STATS = 10, NOFORMAT
> >
> >manually run the job get this err:
> >Executed as user: domainname\user1. ConnectionRead (WrapperRead()).
> >[SQLSTATE 01000] (Message 258) General network error. Check your network
> >documentation. [SQLSTATE 08S01] (Error 11) 10 percent backed up. [SQLSTATE
> >01000] (Error 3211). The step failed.
> >
> >Database has 1 data file + 1 log file. (I know this is stupid to be having
> >only one data file, but nothing I can do to it now.)
> >
> >I really need some directions. This issue is killing me.
> >
> >Thanks for help!!!!!!!!!!
> >
> >Tom
> >
> >
>

Backup Problem

I have a database that is around 30 gigs in size. It has always taken a
couple of hours to back up. Now all of a sudden it never stops backing
up...It started around 8:00pm last night and when I came in at 8:00am this
morning it was still backing up. There has been no major size change or
anything like that. I need to be able to back it up, but this is crazy.
I'm backing up to disk and then backing the disk file up to tape.Hi John,
First, take a look at errorlog, and NT System and application log to see if
there is any error related to the backup.
Then, Take a look at master..sysprocesses table to see if there is any
blocking problem. sp_lock output would help as well.
Sincerely,
Yih-Yoon Lee
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Backup on line, incremental or decremental

I have SBS 2000, and Ineed to do the backup on line because we are woriking 7*24, and because of the size of DB, I need that the backup will be incremental, How can I do?
SQL Server backup operations are online by definition. To take incremental
backups, take a look at differential backups in books online.
Carlos E. Rojas
SQL Server MVP
Co-Author: SQL Server 2000 Programming by Example
"Martha esteban" <sistemas@.hilanderiafontibon.com> wrote in message
news:43E83FE9-C74D-4AED-A822-44E2AFE8C386@.microsoft.com...
> I have SBS 2000, and Ineed to do the backup on line because we are
woriking 7*24, and because of the size of DB, I need that the backup will
be incremental, How can I do?
|||Hi,
You can do a differential backup , this backup will perform backup on only
the data pages which is changed after your last backup. Prerequisite for
this is you should have FULL database backup.
BACKUP DATABASE <DBNAME> TO <DEVICE> WITH DIFFERENTIAL
Thanks
Hari
MCDBA
"Martha esteban" <sistemas@.hilanderiafontibon.com> wrote in message
news:43E83FE9-C74D-4AED-A822-44E2AFE8C386@.microsoft.com...
> I have SBS 2000, and Ineed to do the backup on line because we are
woriking 7*24, and because of the size of DB, I need that the backup will
be incremental, How can I do?

backup job can't remove old .BAK from disk

Hello,
I have a full database backup job that I run nightly on large database. The
database size is approx 37 GB. This job was created using the maintenance
plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
days worth of .BAK files on the server.
My problem is that occasionally(not all or the time) this backup job fails.
After taking a closer look I found that the database is actually being backed
up properly but the job fails because it is not able to delete the old backup
from the previous day. This can cause the disk drive to fill to capacity if
not monitored closely.
I have other database backup jobs that were created using maintenance plans
that work just fine.
Is there a reason why this database backup job is sometimes unable to delete
old backup files?
This has been causing me headaches for some time and I'd appreciate any
feedback you may have...
Thanks,
John
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:

> Hello,
> I have a full database backup job that I run nightly on large database. The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job fails.
> After taking a closer look I found that the database is actually being backed
> up properly but the job fails because it is not able to delete the old backup
> from the previous day. This can cause the disk drive to fill to capacity if
> not monitored closely.
> I have other database backup jobs that were created using maintenance plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John
|||Get rid of the maint plan and code it yourself. You only keep 1 copy so
you can just delete it before the backup.
It would be quicker to write the job than to have posted your question
.
I assume you are copying the backup somewhere otherwise if the backup
fails or is corrupt you are left without a backup and can lose the
database.
I try to keep at least 3 backups on disk and will zip them if they are
large.
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas

"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:

> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John
|||"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:%23v0oMCi2EHA.1392@.tk2msftngp13.phx.gbl...
> Get rid of the maint plan and code it yourself. You only keep 1 copy so
> you can just delete it before the backup.
>
Umm, can I recommend deleting it AFTER a successful backup.
Otherwise you risk deleting your one good backup and having the new one
fail. :-)

> It would be quicker to write the job than to have posted your question
> .
> I assume you are copying the backup somewhere otherwise if the backup
> fails or is corrupt you are left without a backup and can lose the
> database.
> I try to keep at least 3 backups on disk and will zip them if they are
> large.
> Nigel Rivett
> www.nigelrivett.net
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

backup job can't remove old .BAK from disk

Hello,
I have a full database backup job that I run nightly on large database. The
database size is approx 37 GB. This job was created using the maintenance
plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
days worth of .BAK files on the server.
My problem is that occasionally(not all or the time) this backup job fails.
After taking a closer look I found that the database is actually being backed
up properly but the job fails because it is not able to delete the old backup
from the previous day. This can cause the disk drive to fill to capacity if
not monitored closely.
I have other database backup jobs that were created using maintenance plans
that work just fine.
Is there a reason why this database backup job is sometimes unable to delete
old backup files?
This has been causing me headaches for some time and I'd appreciate any
feedback you may have...
Thanks,
JohnCould you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database. The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job fails.
> After taking a closer look I found that the database is actually being backed
> up properly but the job fails because it is not able to delete the old backup
> from the previous day. This can cause the disk drive to fill to capacity if
> not monitored closely.
> I have other database backup jobs that were created using maintenance plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||Check your scheduling. Chances are, the Tape Backup client you are using on
this server or the Anti-Virus software is running scheduled jobs around the
same time as your disk database backup. All of these task try and need to
gain exclusive access to the files; so, it is better to have their
respective schedules staggard.
You probably see the problem against this database more often than the
others because of its size: the larger the file the longer the duration so
the higher the probability that there will be a conflict.
Sincerely,
Anthony Thomas
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:836CA9D0-E58B-49E6-A537-F4208EF376B2@.microsoft.com...
Could you please explain how do you delete the old backup file?
Are you using VB Scripts,...?
Ed
"John P" wrote:
> Hello,
> I have a full database backup job that I run nightly on large database.
The
> database size is approx 37 GB. This job was created using the maintenance
> plan wizard in Enterprise Manager. The maintenance plan is set up to
retain 1
> days worth of .BAK files on the server.
> My problem is that occasionally(not all or the time) this backup job
fails.
> After taking a closer look I found that the database is actually being
backed
> up properly but the job fails because it is not able to delete the old
backup
> from the previous day. This can cause the disk drive to fill to capacity
if
> not monitored closely.
> I have other database backup jobs that were created using maintenance
plans
> that work just fine.
> Is there a reason why this database backup job is sometimes unable to
delete
> old backup files?
> This has been causing me headaches for some time and I'd appreciate any
> feedback you may have...
> Thanks,
> John|||I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>|||We have a space problem, but more because of our Storage Administration
team.
The default behaviour of the Maintenance Plans is to backup up and then
verify each database in the plan. Only after all backups are completed,
without error, the file delete portion runs for yesterday's backup. So, you
need space for at least 2 Full backups and any retained transaction log
backups in between.
The way we've coded it is to go ahead and set the Maintenance Plans, but
then code another job that runs several times per day checking the Archive
Bit on the prior backup files. Whenever the tape client runs, it will reset
this bit allowing our job to remove it. This reduces our space requirements
to just 1 set of Full backups and intra-day transaction log backups.
Also, we write our backups to dedicated disk partitions that are compressed.
You might also consider something like SQL Litespeed, which further
compresses the database backup files but integrates nicely withing the
Maintenance Plans already created.
Hope this helps.
Sincerely,
Anthony Thomas
"randy" <randydoub@.yahoo.com> wrote in message
news:1beb01c4dc68$9f66cb00$a301280a@.phx.gbl...
I have a similar situation where my backup area can only
hold one backup file. I use a two step job and avoid the
Maintenance Plan wizard. The first step uses xp_cmdshell
to delete the previous day's backup file, the second backs
up the database with the 'backup database x to disk ='
command. Later in the evening my backup folder is copied
to tape. I build the date into the backup file name. So
I have to create a variable with the delete command and
path and correct file name for xp_cmdshell.
>--Original Message--
>Hello,
>I have a full database backup job that I run nightly on
large database. The
>database size is approx 37 GB. This job was created using
the maintenance
>plan wizard in Enterprise Manager. The maintenance plan
is set up to retain 1
>days worth of .BAK files on the server.
>My problem is that occasionally(not all or the time) this
backup job fails.
>After taking a closer look I found that the database is
actually being backed
>up properly but the job fails because it is not able to
delete the old backup
>from the previous day. This can cause the disk drive to
fill to capacity if
>not monitored closely.
>I have other database backup jobs that were created using
maintenance plans
>that work just fine.
>Is there a reason why this database backup job is
sometimes unable to delete
>old backup files?
>This has been causing me headaches for some time and I'd
appreciate any
>feedback you may have...
>Thanks,
>John
>.
>

backup full-text db

hi,
i try to back up a full-text database
however, the size of database quit big
around 46xxx records need 95XMB
how can i compress the backup size ?
Thanks
is this SQL 2005? How big is your database to begin with?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Q" <q@.q.com> wrote in message
news:%23rJNGDoWFHA.1152@.tk2msftngp13.phx.gbl...
> hi,
> i try to back up a full-text database
> however, the size of database quit big
> around 46xxx records need 95XMB
> how can i compress the backup size ?
> Thanks
>