Hello,

Yes, it is normal.

If you do a FULL database backup, it takes the entire MDF and LDF file into
consideration and while restoration it will create both files.

Thanks
Hari

[quoted text, click to view]

[quoted text, click to view]
Hi,
Whn I do a full backup on the database, the *.bak (backup file of size
500Mb) is about the same size as the *.mdf. However, whn I restore it
gives me the *.mdf (500Mb) as well as the *.ldf (10Gb). What I'm curious

I backed up (full backup) a table service.mdf without the service.ldf
(size 10Gb) from the live server. However, whn I restore the table to a
test server it also give me a service.ldf (same size, 10Gb) together
with the service.mdf. Is this normal? Does the contents of the

Hello,

When you backup the database even if you LDF is not utilizing the definition
of the database (MDF and LDF) will copied into the BAK file.
So when restore the RESTORE DATABASE operation creates the MDF and LDF with
the same size in source even if the file is
not utilized.

Thanks
Hari

[quoted text, click to view]

[quoted text, click to view]
Hi,

Thanks for the confirmation. If that is the case, would I be able to
utilize the *.ldf then. How do I make sure the contents of the *.ldf is

Saving the statement "File size 100 GB" takes only 16 bytes to describe, but
when used to create can create a file 100 GB large. Backing that statement
up only take 16 bytes, but the result on a restore can be enormous.

Now content can be a different story. A full backup only backs up the
currently allocated extents and any committed transactions not already
applied to the data file.

You need to understand how a transacted file system works. Check out the
SQL Server IO white paper. SQL Server follows the WAL (Write Ahead Logging)
protocol. This means that outstanding transactions are committed to the
transaction log file first. A lazy writer thread asynchronously applies
these changes to the database data pages, as time permits.

Transactions remain in the transaction log file until it is backed up or
truncated. If the database is in SIMPLE recovery mode, this truncation
happens automatically, for the other recovery models, you have to do it
manually (schedule a transaction log backup separate from any full backups).

Also, if you create a database, load it, but then delete most of the data
and rebuild the indexes, then although the data files remain the same size,
there can still be only a few allocated extents in the file.

The transaction log file works the same way. Although it has grown, some of
the changes may have been written to the data pages, while others have not.

When you do a full backup, only the allocated pages will be backed up, even
if they are empty, but not the empty unallocated space in the file. The
full backup will then include any uncommitted transactions from the
transaction file necessary to perform database recovery. This may be the
entire transaction log file, or only a small portion of it.

Read both of these documents, and you will better understand how SQL Server
uses these two different file types, and why you may need to do several
kinds of backups.

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/iobasics.mspx

Unless your database is very, very large, it seems unreasonable to have a
transaction log file that is 10 GB. This suggests that you are running in
FULL or BULK_LOGGED recovery mode, but you are not running transaction log
backups. If the recovery models are used correctly, and you are performing
all the necessary backups, there shouldn't be any reason the transaction log
should be any larger than 10% to 35% of the total database size.

If we switch to Bulk Logged during index reorg processes, we typically size
the tlog to about 10% of the total (or 11% of the data file sizes--10%/90%).
If we leave the database in Full recovery during the reorgs, we size the
tlog to about 25% to 35% of the total (or 33% to 54% of the data file
sizes--25%/75% to 35%/65%).

So, if you have a 500 MB data files size, your tlog should be between 56 MB
and 270 MB, at most. If it is bigger, you are not switching to Bulk Logged
for bulk operations, or you are not backing up the tlog frequently enough.

Sincerely,


Anthony Thomas


--

[quoted text, click to view]