March 19, 2008 at 5:38 am
:hehe:SQL Server 2000
In my database directory these are all the files belonging to
_tlog 32.827 KB (32 Mb)
_full_backup 58.555.027 (58 GB)
but the actual size of is 3.500 MB :hehe: ( of which 1.800 MB is free space )
how is this possible ??
(....Mark the db_full_backup of 58 GB.. :cool:) !!!!
can i shrink the with the DBCC SHRINKDATABASE command , so the FULL BACKUP may come smaller....
!!!.. and when the Database Maintenace Plan is done for this database, the database BAK file is at its normal size: 1,7 GB used space ) : This is the command: in the Maintenance plan :
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 3BE4B63D-8B6A-4B63-9AA9-267C4AEC4901 -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\DB Maintenance SPGLPROD4.txt" -DelTxtRpt 4WEEKS -WriteHistory -BkUpMedia DISK -BkUpDB "G:\Database_backup" -DelBkUps 1WEEKS -BkExt "BAK"'
March 19, 2008 at 6:39 am
It sounds like the backup file is being appended to instead of overwritten during each backup. Look in the maintenance plan to see how it's setup.
You could also run a RESTORE HEADERONLY in query analyzer to confirm that's what's happening; 1 row for each backup would be returned.
RESTORE HEADERONLY FROM DISK = 'g:\database_backup\_full_backup.bak'
March 19, 2008 at 7:05 am
the BLOW-up is in the FULL_backup. ALso other related DB have same problem, BUT are not so BIG.
this is the FULL Backup syntax:
BACKUP DATABASE [SPGLPROD] TO DISK = N'G:\Database_backup\SPGLPROD_Full_backup' WITH NOINIT , NOUNLOAD , NAME = N'BACKUP SPGLPROD Full backup', NOSKIP , STATS = 10, NOFORMAT
March 19, 2008 at 7:23 am
BACKUP DATABASE [SPGLPROD] TO DISK = N'G:\Database_backup\SPGLPROD_Full_backup' WITH NOINIT , NOUNLOAD , NAME = N'BACKUP SPGLPROD Full backup', NOSKIP , STATS = 10, NOFORMAT
"WITH NOINIT" means the backup set is appended to. If it said "WITH INIT" then it would overwrite.
RESTORE HEADERONLY is run from within Query Analyzer. Where are you running it from?
March 19, 2008 at 7:25 am
hi TOdd....
you where right.
i found 41 rows iwth this command:
RESTORE HEADERONLY FROM DISK = 'g:\Database_backup\SPGLPROD_Full_backup'
so that's 40 too many..
where in my datab.mainten.plan OR FULL Backup job can i skip this ( i cannot found any opton that may lead to this..))
March 19, 2008 at 9:03 am
Under the maintenance plan, "Remove Files older than", set whatever works for you (1 day, 1 week, etc.). A maintenance plan should be creating a separate file with a date/time stamp at the end. If it's a TSQL job within a scheduled job, use "WITH INIT" and it'll overwrite each time. HTH.
-- You can't be late until you show up.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply