March 25, 2009 at 11:08 pm
My DB actual backup file size is 10GB and per day my differential backup size will be around 3500KB or max 4MB . but when i restore ( as said above posts) my DB it is ( ldf & mdf ) taking 44GB on disk.
is this is the behavior or am i doing some thing wrong, because it is almost taking 400% than the actual size.
i have tried with the backups taken manually and schedule auto backups (using agent jobs )
Please help me out on this
Thank You
March 26, 2009 at 1:43 am
what are the sizes of mdf and ldf seperately?
Also, are you taking tran log backups?
March 26, 2009 at 3:22 am
Hi PS,
Here the details
My Actual BKP file - 10.2GB
ldf - 32GB
mdf - 13GB
My BKP agent jobs script is
Full Bkp Script:
===========
SET @filename = ''E:\Backup\DB_Date.bkp''
BACKUP DATABASE [DB]
TO DISK = @filename
WITH INIT, NOUNLOAD, NAME = N''DB',
NOSKIP, STATS = 10, NOFORMAT
RESTORE VERIFYONLY FROM DISK = @filename',
Diff Bkp Script:
===========
DECLARE @filename VARCHAR(255)
SET @filename = ''E:\Backup\DB_Date.bkp''
BACKUP DATABASE [DB] TO DISK = @filename WITH
DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N''DB-Differential Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO'
and i am restoring by using SQL 2005 "DB:->Tasks:->Restore DB" through wizard i am restoring the DB
Let me know is there any modification require at any level(DB creation / Taking Backup / Restoring Backup ).
Thank You
March 26, 2009 at 3:38 am
Problem is that your log file is 32 GB .
Try shrinking your log before you take backup
Tanx 😀
March 26, 2009 at 5:04 am
I have two agents jobs for backups, sunday full bkp and rest of the days differential bkp, if i do this 'Shrink' task, will it have any impact on my backup task ?
i am taking a single file for full & diff backups. let me know what should i do to fix up this problem
Thank You
March 26, 2009 at 5:55 am
raju.tanneeru (3/26/2009)
I have two agents jobs for backups, sunday full bkp and rest of the days differential bkp, if i do this 'Shrink' task, will it have any impact on my backup task ?i am taking a single file for full & diff backups. let me know what should i do to fix up this problem
Thank You
What recovery model is your database in? What are type of transactions that you are running?
There's no point in shrinking unless you are sure that the log file is not going to grow again?
If you have such a small change in the database then I would recommend to put the database in Simple Recovery model.
March 26, 2009 at 8:48 am
In SSMS, right-click on the DB -> Tasks -> Shrink -> File
change dropdown to "Log", and see the free % in there of 32GB
You can do a BACK UP LOG, then shrink the log as well
do take a FULL backup before and after, just in case
March 26, 2009 at 9:00 am
You should read about how logs work in SQL Server so that you understand what is wrong.
A full or diff backup does not clear space from the log. Only a log backup does this. If you are in full recovery mode, then you are constantly growing your log file with old transactions.
You need to set up regular log backups. Once you see the size of those backups, you'll have an idea of how large to set your log files.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 27, 2009 at 10:33 am
when i check the free space it is saying 99% is free space out of 32GB for log file
how can i solve this problem
May 27, 2009 at 10:47 am
As mentioned you need to shrink the log, but you also need to set up log backups, figure out how large to leave the log, and monitor things.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 27, 2009 at 10:58 am
Are you now backing up the transaction log on a regular basis?
Verify the recovery model of the database. Is it full recovery or simple?
If simple recovery model, shrink the log file down to an acceptable size. I would recommend 1GB to start and then monitor for additional growth.
If full recovery model, verify that you are indeed backing up the transaction log. If you are, find the largest backup file over the past month. Take that size and round up to the nearest GB/MB (for example, if the largest tlog backup was 900MB - round up to 1000MB). Now, you need to alter the database and put it in simple recovery model, shrink the log file to the above size you calculated, take a full backup and restart your transaction log backups.
You can review the article in my signature for further information.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply