Backup Database hangs

SQL Server 2000

Backup database command hangs. The command I am using is:

BACKUP DATABASE @.DBName TO DISK = @.filename WITH INIT

Size of the database is 140GB and we have 210 GB free disk space.
sp_who status shows runnable.

Any help would be appreciated.

Thanks.How do you execute the command (analyzer?) and what do you mean when you say "Backup database command hangs."? How did you find out it hangs?|||Command is execeuted thru SQL Server Agent as..

BACKUP DATABASE [Assentor] TO DISK = 'D:\DB_BAK\AssentorDB2.BAK' WITH INIT , NOUNLOAD , NAME = N'Assentor backup', SKIP , STATS = 10, DESCRIPTION = 'Daily Assentor DB Backup', FORMAT , MEDIANAME = N'Assentor DB Backup', MEDIADESCRIPTION = N'Assentor DB Backup'

Generally it takes 2 hrs. to complete the backup, but since last few days it just hangs and sits there for hours. I tried to kill the job, it shows killed/rollback and takes hours to release the spid.

Thanks.|||Any ideas? Last backup job which kicked of at 12:00 noon is still in runnable state. I can provide more info. if someone needs to help me out.

Thanks|||Find the spid of the backup process, and see if anything is blocking the backup. If nothing is blocking the backup, see if the CPU and PHYSICAL_IO numbers are going up. That would indicate that the process is running, if slowly. If he numbers are not going up, check the waittype, waitresource, and lastwaittype columns for any resource contention problems.|||Four options (that I know of):

1. Create multiple filegroups and start backing up on a filegroup level;
2. Move to a third-party backup solution (SQLLiteSpeed)
3. Move to SAN
4. Combination of the three above|||When the spid appears to hang, substitute the spid for the ?? then run:SELECT cpu, physical_io FROM master.dbo.sysprocesses WHERE ?? = spid
WAITFOR DELAY '1:00'
SELECT cpu, physical_io FROM master.dbo.sysprocesses WHERE ?? = spidIf the numbers change (at all), then the spid is still running.

If the numbers don't change, you need to check the disk to see if the backup is growing. Sometimes the spid doing the backup can hang for a long time if the database dump is undergoing a "grow" operation.

-PatP|||Is it 1 hour or 1 minute?|||That should be only one minute, but maybe I goofed. I don't have any easy way to test it at the moment.

If the counters don't change for a minute, then I'd be willing to consider the task "hung" for most purposes.

-PatP|||The whole problem is the backup file is not created at all in this whole process. OR let me put it this way, IF a backup file already exists, ABCbackup.bak with date and time stamp of 10/19/2004 7:30 AM, IT SHOULD CHANGE as soon as I start another backup on 10/20/2004 7:30 AM and the size should start growing. BUT it doesn't happend that way.

Now that I killed the backup job, it shows KILLED/ROLLBACK state for almost 14 hrs. and is blocking the new backup job which kicked of today morning at 7:30 AM.

Thanks.|||The whole problem is the backup file is not created at all in this whole process. OR let me put it this way, IF a backup file already exists, ABCbackup.bak with date and time stamp of 10/19/2004 7:30 AM, IT SHOULD CHANGE as soon as I start another backup on 10/20/2004 7:30 AM and the size should start growing. BUT it doesn't happend that way.

Now that I killed the backup job, it shows KILLED/ROLLBACK state for almost 14 hrs. and is blocking the new backup job which kicked of today morning at 7:30 AM.

80 0 runnable ASSENTOR\MSSQL_Srv_Agent NYC-SRA-02 0 Assentor KILLED/ROLLBACK
80 3 runnable ASSENTOR\MSSQL_Srv_Agent NYC-SRA-02 0 NULL KILLED/ROLLBACK

Thanks.|||I opened a case with Microsoft. Will keep you updated on this issue...

Backup Database at different place

Hi All!

I know that if is want to backup my db on the c: drive i do this
BACKUP DATABASE DBName
TO DISK = 'c:\isl_fs1\DBName.bak'

BUT HOW do I take this to the network. I tried
BACKUP DATABASE DBName
TO DISK = '\\isl_fs1\DBName.bak'
it doesn't work please helpIt'll be longer, slower, at risk of network blips, not portable

Dump locally, then copy...

What, you're drive is out of space?

That's a different problem...|||I agree
typically i would back up to disk and then use network backup software to come get it out of the shared folder that i backed it up into

however to directly backup to a network address
One method is to map a drive to a network location
ex
NET USE X: \\Server\Share

and then use sql backup.
Backup database northwind to disk=x:\backup.bkf

you should however use sp_adddumpdevice to map a backup location to a permanent device it cleans up the backup processes and gives you a fixed location to find all of your backups instead of temporary ones

Backup and restore scripts

Any one have implemented backup, recovery stored procedures which
takes the uments for dbname, date, time ime and restores till that
time?
Look at http://www.winnetmag.com/Article/Art...09/42009.html. If
that's not enough, I can send you my script.
Quentin
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407080734.94bd55f@.posting.google.co m...
> Any one have implemented backup, recovery stored procedures which
> takes the uments for dbname, date, time ime and restores till that
> time?
|||Thanks for the reply. I am looking for centralized scripts where we
should able to able to restore any database by giving arguments as DB
name, time, standby restore/normal restore. We should able to run it
from centralized server. Any ideas?
"Quentin Ran" <ab@.who.com> wrote in message news:<uulGraTZEHA.2388@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> Look at http://www.winnetmag.com/Article/Art...09/42009.html. If
> that's not enough, I can send you my script.
> Quentin
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407080734.94bd55f@.posting.google.co m...

Backup and restore scripts

Any one have implemented backup, recovery stored procedures which
takes the uments for dbname, date, time ime and restores till that
time?Look at http://www.winnetmag.com/Article/ArticleID/42009/42009.html. If
that's not enough, I can send you my script.
Quentin
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407080734.94bd55f@.posting.google.com...
> Any one have implemented backup, recovery stored procedures which
> takes the uments for dbname, date, time ime and restores till that
> time?|||Thanks for the reply. I am looking for centralized scripts where we
should able to able to restore any database by giving arguments as DB
name, time, standby restore/normal restore. We should able to run it
from centralized server. Any ideas?
"Quentin Ran" <ab@.who.com> wrote in message news:<uulGraTZEHA.2388@.TK2MSFTNGP11.phx.gbl>...
> Look at http://www.winnetmag.com/Article/ArticleID/42009/42009.html. If
> that's not enough, I can send you my script.
> Quentin
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407080734.94bd55f@.posting.google.com...
> > Any one have implemented backup, recovery stored procedures which
> > takes the uments for dbname, date, time ime and restores till that
> > time?

Backup and restore scripts

Any one have implemented backup, recovery stored procedures which
takes the uments for dbname, date, time ime and restores till that
time?Look at http://www.winnetmag.com/Article/Ar...009/42009.html. If
that's not enough, I can send you my script.
Quentin
"tram" <tram_e@.hotmail.com> wrote in message
news:26ee1067.0407080734.94bd55f@.posting.google.com...
> Any one have implemented backup, recovery stored procedures which
> takes the uments for dbname, date, time ime and restores till that
> time?|||Thanks for the reply. I am looking for centralized scripts where we
should able to able to restore any database by giving arguments as DB
name, time, standby restore/normal restore. We should able to run it
from centralized server. Any ideas?
"Quentin Ran" <ab@.who.com> wrote in message news:<uulGraTZEHA.2388@.TK2MSFTNGP11.phx.gbl>...[
vbcol=seagreen]
> Look at http://www.winnetmag.com/Article/Ar...009/42009.html. If
> that's not enough, I can send you my script.
> Quentin
>
> "tram" <tram_e@.hotmail.com> wrote in message
> news:26ee1067.0407080734.94bd55f@.posting.google.com...