Backup/restore rids free space?

Can someone explain this? I'm trying to do a backup and
restore to get rid of the 20GB of free space.
In EM, Databases/Space Allocated..
Used Free Total
10GB 20GB 30GB
If I backup the DB to tape that has a 15GB capacity, it
backs up just fine, i.e., the free space isn't being
backed up or else it wouldn't fit on the tape.
But, if I restore the DB from tape, it restores 30GB.
Not sure I understand how the free space is being treated
by the backup/restore.
Thanks,
Don
SQL 7.0/NT 4.0Hi,
I think Transaction log file will be grown high.
How to check the size:-
DBCC SQLPERF(LOGSPACE)
If the transaction file size is huge then use the below commands to backup
the transaction log and shrink the file:-
Backup log <dbname> to disk='c:\backup\dbname.trn'
go
DBCC SHRINKFILE('LDF_FILE_NAME',truncateonly)
Thanks
Hari
MCDBA
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||Don,
Backup makes some optimization decisions on what it writes to backup. It
does not need to write all the empty space, eating up tape and time, but it
(as I understand it) is recording enough information to rebuild the
freespace. Think of it as an easy compression algorithm.
However, backup / restore will not change the definition of the database and
the backup knows that it is restoring 30 GB.
Hari gives you guidance on shrinking the log file. The data file can also
be shrunk using DBCC SHRINKFILE if it has become bloated.
Russell Fields
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0|||You might want to look here
(http://www.karaszi.com/SQLServer/info_dont_shrink.asp) first for some
caveats when shrinking data and log files.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"don" <anonymous@.discussions.microsoft.com> wrote in message
news:1e6f001c45551$efef8420$a401280a@.phx.gbl...
> Can someone explain this? I'm trying to do a backup and
> restore to get rid of the 20GB of free space.
> In EM, Databases/Space Allocated..
> Used Free Total
> 10GB 20GB 30GB
> If I backup the DB to tape that has a 15GB capacity, it
> backs up just fine, i.e., the free space isn't being
> backed up or else it wouldn't fit on the tape.
> But, if I restore the DB from tape, it restores 30GB.
> Not sure I understand how the free space is being treated
> by the backup/restore.
> Thanks,
> Don
> SQL 7.0/NT 4.0