October 6, 2006 at 12:14 pm
Hello all,
I have a database thats about 30MB. The database was dropped and I am currently performing a full restore of a backup from 3 days ago.
RESTORE DATABASE [CNECT_APP] FROM DISK = N'E:\SQLServerData\MSSQL\BACKUP\CNECT_APP\CNECT_APP_db_200610022300.BAK' WITH FILE = 1, NOUNLOAD , STATS = 10, RECOVERY , REPLACE
Again the database and backup file is only 30+mb. But the restore has been running for 2 hours now. Another thing that puzzles me is that I see the LDF being restored but its about 60gig in size ... yes 60 gig! Command restore filelistonly does report that the TRAN is 60gigs.
I guess there's no corelation between the physical backup file to the size of the tranlog?. Does this also explain why the restore is taking hours to complete? Anyone have a clue on what's going on?
Thanks,
SteveV
October 6, 2006 at 1:43 pm
Here's an issue with backups and restores. When transactions occur, the transaction goes into the log file. If there isn't enough room, the log file expands. Every so often, the transactions are cleared. BUT the space is NOT returned to the OS. So there's lots of empty space in the log file. SQL Server believes that you really need it, so it copies that empty space and has to restore it.
That file can also have ALL the transactions that occured since the last full backup if the db is in FULL recovery mode and you never do log backups.
-SQLBill
October 6, 2006 at 1:50 pm
Thanks for the reply. It looks like that the db is/was on FULL recovery mode. I do see that the 60 gig LDF was restored. The restore/recover has been running for a couple of hours now. What is it doing behing the scenes thats taking this long to finish?
October 7, 2006 at 12:11 am
When you take full backup of the database it doesnot take inactive portion of the transaction log on the backup (.bak file) but it is smart enough to remember the size of t-log when you took the backup.
So, when you restore it essentially will create a t-log of 60 GB.
I think you need to wait till it completes...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply