March 16, 2011 at 2:26 am
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.
March 16, 2011 at 2:36 am
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
March 16, 2011 at 3:41 am
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.
March 16, 2011 at 4:40 am
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
March 16, 2011 at 7:22 am
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.
March 16, 2011 at 12:10 pm
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.
March 17, 2011 at 5:40 am
Thanks All.
The problem is resolved.
We have taken a local full backup rather than a network backup. It fixed our problem.
Thanks again.
March 17, 2011 at 5:52 am
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.
March 17, 2011 at 1:32 pm
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