backup taking long time

  • Hi All,

    Am trying take a full backup of sharepoint database which is of 40 GB size.Am using management studio and sql server 2005 sp3 Enterprise edition.

    OS is Windows server 2003.It worked fine for 20% and from past 2 hours it has been in hung state. I dont really know what is happening in the background.

    Is there any we can observe at OS level or any command is there to check the progress of the backup is happening in the background.

    How to check the progress of this backup? I dont know if i can really KILL the process and if i start again may be i might landup in the same situation.

    Any efficient way to complete this full backup in a timely fashion???

    Thanks in Advance.

  • Can you check for any other processes currently going on in the server.

    Usually, how much time does this backup take during regular time?

    M&M

  • Try the following:

    SELECT a.name, b.total_elapsed_time / 60000 AS [running time], b.estimated_completion_time / 60000 AS [remaining], b.percent_complete as [% complete],

    (SELECT text FROM sys.dm_exec_sql_text(b.sql_handle)) AS command

    FROM master..sysdatabases a

    INNER JOIN sys.dm_exec_requests b ON a.dbid = b.database_id

    WHERE b.command LIKE '%BACKUP%'

    ORDER by b.percent_complete DESC, b.total_elapsed_time / 60000 DESC

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • This is first time taking the backup. Basically it is a sharepoint search database and now we are getting log fulll error and so we want take a full backup followed by txn log backups.

    select log_wait_reuse_desc from sys.databases shows up LOG_BACKUP required.

    dbcc loginfo

  • Try the following. Change the [Database_name] to your database. Also change the logical_file_name.

    Use [Database_name]

    GO

    --Issue a checkpoint

    Checkpoint;

    GO

    --Backup your log file

    backup log [Database_name] to disk = 'C:\Backups\Backup_name.trn' with stats = 1

    GO

    --Shrink the logfile (logical filename of the database can be got from the properties of the database)

    dbcc shrinkfile ('logical_file_name',1)

    GO

    --Backup your log file once again

    backup log Database_name to disk = 'C:\Backups\Backup_name1.trn' with stats = 1

    GO

    --Shrink the logfile once again(logical filename of the database can be got from the properties of the database)

    dbcc shrinkfile ('logical_file_name',1)

    This will shrink your log file.

    Sanz
  • Oracle_91 (3/16/2011)


    This is first time taking the backup. Basically it is a sharepoint search database and now we are getting log fulll error and so we want take a full backup followed by txn log backups.

    Log full errors can be caused by a few things. The user db log file being full OR the tempdb being full. Chances are, the drive (logical or physical) that TempDB or the user db log file are on is maxed out at capacity.

    I do NOT recommend shrinking your transaction log. Especially as you have no previous backups. I do recommend finding out if it is the user db or TempDB taking up your space, then creating addition files on another drive that has free space, then setting those new files as the default / primary files. And if you are attempting to back up to the full drive, try to back up to another drive with free space.

    Delete any unncessary temp files or other server files to gain back enough space to restart your backup process. Shrinking database files should be a measure of absolute last resort.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks All.

    The problem is resolved.

    We have taken a local full backup rather than a network backup. It fixed our problem.

    Thanks again.

  • Ah, yes. The good old network backup. Actually, the bad old network backup.

    Don't use them. Make all your backups locally, then move them over the network. That way, if the network goes down in the middle of your backup, you still have your backup file.

    Also, if you aren't making regular backups of this db, you should start. Even if it's just once a week, make backups and test them. Think of it this way, can you really afford to lose this database?

    Glad you were able to get things working, though.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks All for the timely help.

    🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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