Backup reporting false success

I am experiencing the same behavior as below (see snip)on
my SQL Server 7.0
running on NT (lastest service packs and patched on
both.) I too have witnessed the BAK file created and
then
deleted. I do not have the maintance plan configured to
deleting old .BAK files. I do receive this error message
when I create a backup from the "backup" option (not the
database maintenance plan.) This is the message I get:
10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
percent backed up. [SQLSTATE 01000] (Message 3211)
ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
258) General network error. Check your network
documentation. [SQLSTATE 08S01] (Error 11) 30 percent
backed up. [SQLSTATE 01000] (Message 3211). The step
failed.
NOTE: I am backing up to the server harddrive(NO NETWORK
INVOLVED!!!)with 20 gigs free. This database is only
199mb in size. I suspect the "general network error" is
bogus.
Please help!
snip from other person with same issue
>>I have a server with Windows 2000 SP4+hotfixes, SQL
>>Server
>>2000 SE SP3A+hotfix. The server has 4 user databases >>
(the
largest being around 500 megs). 3 of the databases
backup
>>fine but 1 database will not take a full backup or a
transaction log backup from the maintenance plan. The
job
runs and succeeds but the file is not where it should be
(nor anywhere else).
The development server with the same configuration does
not have this same problem, and it takes about 7 seconds
to backup the database in question.
I have monitored the directory that the file should be
created in while the job is running, and the file does
create and stays there for the 5 to 7 seconds that the
job
is running and then it dissappears.
I have also used File Monitor from sysinternals and
monitored the system while a backup is being run and
sqlservr.exe and sqlmaint.exe are the only things that
touch the .BAK file. Sqlservr.exe reads and writes to
the .BAK file numerous times and the last thing that
happens is sqlservr.exe opens the .BAK file, then Deletes
the .BAK file, and then closes the .BAK file. No other
applications besides sqlservr.exe and sqlmaint.exe touch
the .BAK file. The index numbers in File Monitor do not
skip any numbers.
Here are the lines from sysinternals File Monitor where
the backup is deleted:
1646 6:32:56 PM sqlservr.exe:3688 OPEN
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS Options: Open Access: All
1647 6:32:56 PM sqlservr.exe:3688 DELETE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
1648 6:32:56 PM sqlservr.exe:3688 CLOSE
E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
SUCCESS
The error message in the errorlog is:
BACKUP failed to complete the command BACKUP LOG [t_prod]
TO DISK = N'E:\Microsoft SQL
Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
Disk space is not an issue as there is over 60 gigs free
and all the databases together barely total a 1 gig.
Backups from a script that run through a job (without the
maint plan wizard) succeed about 75% of the time but
about
25% of the time the job fails with the same errors as the
maint plan fails with. I'm still trying to get File
Monitor running at the time a backup from a script fails,
but haven't been luckly enough to catch it at the right
time yet.
Any information or suggestions to resolve this problem
are
greatly appreaciated as I've been trying to figure this
out for too long. If there is any other information that
would be useful to post, please let me know.
Thanks,
Trent.
.Just a thought... Do you have disk compression turned on?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Kathy" <rnurse@.educ.state.ak.us> wrote in message news:799c01c3b458$0ebdd730$a601280a@.phx.gbl...
> I am experiencing the same behavior as below (see snip)on
> my SQL Server 7.0
> running on NT (lastest service packs and patched on
> both.) I too have witnessed the BAK file created and
> then
> deleted. I do not have the maintance plan configured to
> deleting old .BAK files. I do receive this error message
> when I create a backup from the "backup" option (not the
> database maintenance plan.) This is the message I get:
> 10 percent backed up. [SQLSTATE 01000] (Message 3211) 20
> percent backed up. [SQLSTATE 01000] (Message 3211)
> ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message
> 258) General network error. Check your network
> documentation. [SQLSTATE 08S01] (Error 11) 30 percent
> backed up. [SQLSTATE 01000] (Message 3211). The step
> failed.
> NOTE: I am backing up to the server harddrive(NO NETWORK
> INVOLVED!!!)with 20 gigs free. This database is only
> 199mb in size. I suspect the "general network error" is
> bogus.
> Please help!
> snip from other person with same issue
> >>I have a server with Windows 2000 SP4+hotfixes, SQL
> >>Server
> >>2000 SE SP3A+hotfix. The server has 4 user databases >>
> (the
> largest being around 500 megs). 3 of the databases
> backup
> >>fine but 1 database will not take a full backup or a
> transaction log backup from the maintenance plan. The
> job
> runs and succeeds but the file is not where it should be
> (nor anywhere else).
> The development server with the same configuration does
> not have this same problem, and it takes about 7 seconds
> to backup the database in question.
> I have monitored the directory that the file should be
> created in while the job is running, and the file does
> create and stays there for the 5 to 7 seconds that the
> job
> is running and then it dissappears.
> I have also used File Monitor from sysinternals and
> monitored the system while a backup is being run and
> sqlservr.exe and sqlmaint.exe are the only things that
> touch the .BAK file. Sqlservr.exe reads and writes to
> the .BAK file numerous times and the last thing that
> happens is sqlservr.exe opens the .BAK file, then Deletes
> the .BAK file, and then closes the .BAK file. No other
> applications besides sqlservr.exe and sqlmaint.exe touch
> the .BAK file. The index numbers in File Monitor do not
> skip any numbers.
> Here are the lines from sysinternals File Monitor where
> the backup is deleted:
> 1646 6:32:56 PM sqlservr.exe:3688 OPEN
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS Options: Open Access: All
> 1647 6:32:56 PM sqlservr.exe:3688 DELETE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
> 1648 6:32:56 PM sqlservr.exe:3688 CLOSE
> E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK
> SUCCESS
>
> The error message in the errorlog is:
> BACKUP failed to complete the command BACKUP LOG [t_prod]
> TO DISK = N'E:\Microsoft SQL
> Server\MSSQL$TXL\BACKUP\t_prod\t_prod_db_200311241832.BAK'
> WITH INIT , NOUNLOAD , NOSKIP , STATS = 10 , NOFORMAT
> Disk space is not an issue as there is over 60 gigs free
> and all the databases together barely total a 1 gig.
> Backups from a script that run through a job (without the
> maint plan wizard) succeed about 75% of the time but
> about
> 25% of the time the job fails with the same errors as the
> maint plan fails with. I'm still trying to get File
> Monitor running at the time a backup from a script fails,
> but haven't been luckly enough to catch it at the right
> time yet.
> Any information or suggestions to resolve this problem
> are
> greatly appreaciated as I've been trying to figure this
> out for too long. If there is any other information that
> would be useful to post, please let me know.
> Thanks,
> Trent.
> .
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I should give some explanation to the phenomenon you described in the snip.
The backup process in the SQL Server is an automatic process, which means
when backup failed to complete, all the backup process will rollback. This
is why the customer you snipped from used File Monitor and found that "does
create and stays there for the 5 to 7 seconds that the job is running and
then it disappears." As he mentioned later "Backups from a script that run
through a job (without the maintenance plan wizard) succeed about 75% of
the time but about 25% of the time the job fails with the same errors as
the maintenance plan fails with".
For the error message you mentioned, I need more information for analysis.
First, is this error message encountered for all the databases you have, or
just one database when using "Backup" option?
Second, besides using the "backup" option, do you meet the same error
message when using T-SQL or job for back up? You can try the T-SQL code
like this:
use master
exec sp_addumpdevice 'disk','northwindback', 'c:\northwind_back.bak'
backup database northwind to northwindback
go
restore database northwind from northwindback
go
If you do not encounter this error, then please provide information for
every step when using "backup" option to check if anywhere in this process
might involve the network device
If you meet the error message again, please collect information from
SQLDIAG, NT application event logs, system event logs and dbcc checkdb out
put and post here for our further analysis.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thank you for responding!!! :-)
The first point you made really made sense to me. I knew
transactions rolled back but never thought about a backup
job rolling back. I did as you suggested. I created a
backup device and backup a database (and restored it)
using T-SQL code. Of course this worked, because it is
not a job scheduled using the backup wizard interface. I
can always manually backup a database by right clicking,
choicing "All Task" and "Backup Database." But as soon
as I ran it as a scheduled job it will failed, or SQL
would reported it as suceeding with no BAK file.
So, I decided to create a backup job manually. It
worked!!! Then I scheduled it through the job interfaced
and it ran as scheduled.
To clarify this to you:
Backup Job FAILS when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Right click on the new backup device (otg_backupdevice)
and select "Backup a Database"
The SQL Server backup wizard window appears, select the
database (otg) to backup, add the backup device
(otg_backupdevice) and schedule the backup to occur once
a day at 12:00 A.M.
Then ran the job from the jobs window
The job fails.
Backup Job Succeeds when I do the following steps:
Create a backup device, i.e. otg_backupdevice
Go to the job window
Right-click and select new job
Name the job - OTG backup to device
Click on the "Steps" tab and click on "New Step"
Select OTG as the database
In the command window I enter "backup database otg to
otg_backupdevice
Click on the "Schedule" tab and schedule it to backup at
10:00 am
The job suceeds
I suspect there is a problem with the backup wizard'
Thank you for your help. I still wonder why the backups
will not work through maintenance plans or through the
backup wizard.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||I tested restoring a database and the system reports the
database is restore sucessfully but it does not. The
sysdatabase does not show an entry for the restored
database. When I close Enterprise Manager and reopen the
database is gone. The mdf file is still in the data
directory. What is wrong with my sql server.
I can restore the database from the same file on my SQL
2000 server but it fail on every one of the SQL 7.0
servers. The production 7.0 SQL server and the 7.0 SQL
server on my laptop computer.
Kathy
>--Original Message--
>Hi Kathy,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>I should give some explanation to the phenomenon you
described in the snip.
>The backup process in the SQL Server is an automatic
process, which means
>when backup failed to complete, all the backup process
will rollback. This
>is why the customer you snipped from used File Monitor
and found that "does
>create and stays there for the 5 to 7 seconds that the
job is running and
>then it disappears." As he mentioned later "Backups from
a script that run
>through a job (without the maintenance plan wizard)
succeed about 75% of
>the time but about 25% of the time the job fails with
the same errors as
>the maintenance plan fails with".
>For the error message you mentioned, I need more
information for analysis.
>First, is this error message encountered for all the
databases you have, or
>just one database when using "Backup" option?
>Second, besides using the "backup" option, do you meet
the same error
>message when using T-SQL or job for back up? You can try
the T-SQL code
>like this:
>use master
>exec
sp_addumpdevice 'disk','northwindback', 'c:\northwind_back
.bak'
>backup database northwind to northwindback
>go
>restore database northwind from northwindback
>go
>If you do not encounter this error, then please provide
information for
>every step when using "backup" option to check if
anywhere in this process
>might involve the network device
>If you meet the error message again, please collect
information from
>SQLDIAG, NT application event logs, system event logs
and dbcc checkdb out
>put and post here for our further analysis.
>
>Best regards
>Baisong Wei
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>.
>|||Hi Kathy,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
Thank you for your detailed information and it is helpful for us to
analysis.
As you said, when execute the TSQL and JOB schedule, the backup process
will complete fine while when using backup option, it failed, right? If I
misunderstood the information you provided, please feel free to let me know.
As using the "backup" option, because it add many options when carried out
the backup, so please carry out it step by step as I mentioned below:
1) Right clicking, choose "All Task" and "Backup Database."
2) This is very important step! Make sure in the destination, there should
be only one device (When you add the device, there could be some other
devices in it), remove other devices from the destination. If not, you will
encounter the errors.
3) Click "OK" after you assigned the schedule time (You can also run it
immediately by click "ok" to test if this will work first)
Try the above steps and pay attention to the second one, only one
destination could be appeared in the "destination".
I hope this will solve the problem and if there is any problem, feel free
to post message here and I am ready to help! By the way, happy
thanks-giving day to you and your family!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Backup hangs in wait-state MSSEARCH

I'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.

The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.

When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.

I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.

The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.

Kim,

This is very likely a bug. I suggest you contact Microsoft support.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

That wait state indicates problems communicating between SQL and the MSSEARCH service via the COM interface.

There were several known problems that would lead to this situation which have been fixed in SP1, so that might be of help.

The quick solution is to cycle the MSSEARCH service, which should get backups moving.

If you're on SP1, and have this problem frequently, we can work with you to see what's going on. Event Viewer events about COM errors would be the first place to look.

|||

Hi Kevin,

We are on SP1, and the oddest thing is that I've disabled MSSEARCH (stopped the process) on the SharePoint server.

The Occurrence is highly frequent i.e. if there has been any user activity on the database the Backup hangs.

|||

I'm assuming that you have fulltext catalogs in your database?

So, for SQL 2005, the service is msftesql. If it is disabled, the backup should ignore the fulltext catalogs.

If we can get a dump for sqlservr.exe and msftesql.exe process together with all errorlog and SQLFT*.LOG file, I can take a look and see what is the problem. Feel free to contact me offline @. kevin.farlee@.microsoft.com

|||

Hi! Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked :0}

Thanks for the all the good will shown in these forums.

Kim Brandt Jensen

Backup hangs in wait-state MSSEARCH

I'm experiencing a very annoying failure when trying to do a backup - I hope you can help where others (including me) have failed.

The setup is a SharePoint Portal Server 2003 version 11.0.8126.0 running on Windows server 2003 Standard edition Service pack 1; it has a SQL-server 2005 version 9.0.2047 running on Windows server 2003 Standard edition Service pack as a back-end.

When I issue a backup of the SharePoint database XXX_SITE which holds round 4 gb. of data (mainly documents) the backup process hangs with a Wait Type 'MSSEARCH', it makes no difference whether I issue it as a single job, or through a maintenance plan.

I tried to stop the MsSearch service on the SharePoint-server, and disabled the Full-Text search on the database but it makes no difference.

The only way I can get a backup is to reboot the server on which SQL-server resides (restarting the SQL-server makes it rather unstable), and do a manual back-up shortly after.

Kim,

This is very likely a bug. I suggest you contact Microsoft support.

Regards,

Matt Hollingsworth

Sr. Program Manager

Microsoft SQL Server

|||

That wait state indicates problems communicating between SQL and the MSSEARCH service via the COM interface.

There were several known problems that would lead to this situation which have been fixed in SP1, so that might be of help.

The quick solution is to cycle the MSSEARCH service, which should get backups moving.

If you're on SP1, and have this problem frequently, we can work with you to see what's going on. Event Viewer events about COM errors would be the first place to look.

|||

Hi Kevin,

We are on SP1, and the oddest thing is that I've disabled MSSEARCH (stopped the process) on the SharePoint server.

The Occurrence is highly frequent i.e. if there has been any user activity on the database the Backup hangs.

|||

I'm assuming that you have fulltext catalogs in your database?

So, for SQL 2005, the service is msftesql. If it is disabled, the backup should ignore the fulltext catalogs.

If we can get a dump for sqlservr.exe and msftesql.exe process together with all errorlog and SQLFT*.LOG file, I can take a look and see what is the problem. Feel free to contact me offline @. kevin.farlee@.microsoft.com

|||

Hi! Yes we did have full text catalogues in the database, but since I had disabled full text search for the database, and disabled msftesql, I didn't suspect them. I got however an article from Microsoft support, showing me how I could test for catalogues not properly removed. So I discovered that there still existed an old catalogue, which I ,after and only after re-enabling full text search, were able to delete, since then my backup has worked :0}

Thanks for the all the good will shown in these forums.

Kim Brandt Jensen