DB Size

DB Size

Post by anonymou » Wed, 03 Mar 2004 06:51:50



when I do a complete backup, the size of DB is abt 1.5
GB.  when I restore the db from same backup, the MDF and
LDF are over 3.5 GB ... why are they so large ?

Also, when the log file (LDF) gets big, how can I shrink
it without loosing any data ?

Thanks for your time.

 
 
 

DB Size

Post by Ray Higdo » Wed, 03 Mar 2004 09:53:20


"Without losing any data" You need to schedule tran log backups regularly if
you are concerned with losing data. to truncate the non-active transactions
you can run

backup log dbname with truncate_only

You can then run a dbcc shrinkfile(logfilename,1), however, don't do this
regularly if your log will just grow back.

--
Ray Higdon MCSE, MCDBA, CCNA
---

Quote:> when I do a complete backup, the size of DB is abt 1.5
> GB.  when I restore the db from same backup, the MDF and
> LDF are over 3.5 GB ... why are they so large ?

> Also, when the log file (LDF) gets big, how can I shrink
> it without loosing any data ?

> Thanks for your time.


 
 
 

DB Size

Post by Carlos Eduardo Roja » Wed, 03 Mar 2004 12:53:00


If you're not taking transaction log backups (don't have to provide point in
time recovery), change the database recovery model to simple instead, and
sql server will manage your log space automatically.

--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example


Quote:> when I do a complete backup, the size of DB is abt 1.5
> GB.  when I restore the db from same backup, the MDF and
> LDF are over 3.5 GB ... why are they so large ?

> Also, when the log file (LDF) gets big, how can I shrink
> it without loosing any data ?

> Thanks for your time.

 
 
 

DB Size

Post by Hari » Wed, 03 Mar 2004 15:12:20


Hi,

1.
when I restore the db from same backup, the MDF and LDF are over 3.5 GB ...
why are they so large ?

Ans: The MDF and LDF file size will be created based up on the size of
source MDF and LDF files not .BAK files. The MDF and LDF
will be created based on the information inside in the .BAK file.

Issue the below command before restore, Based on the column "SIZE " , the
new MDF and LDF will be created.

Restore filelistonly from disk='c:\backup\dbname.bak'

2.

when the log file (LDF) gets big, how can I shrink it without loosing any
data ?

Ans: If the database is not critical then change the Recovery model to
'SIMPLE' , then log file will not grow.
If the information is critical then,

1. Schedule a transaction log backup in frequent intervals (say 1 hour or
sooo)
    Backup log dbname to disk='c:\backup\dbname.tr1
2. THis will ensure that Transaction file will not grow high

Shrink the Log file

1. Perform a Transaction log backup  (    Backup log dbname to
disk='c:\backup\dbname.tr1)
2. DBCC SHRINKFILE('Logfilename','TRUNCATEONLY')

Thanks
Hari
MCDBA

when the log file (LDF) gets big, how can I shrink it without loosing any
data ?

Ans: If the database is not critical then change the Recovery model to
'SIMPLE' , then log file will not grow.
If the information is critical then,

1. Schedule a transaction log backup in frequent intervals (say 1 hour or
sooo)
    Backup log dbname to disk='c:\backup\dbname.tr1)
2. THis will ensure that Transaction file will not grow high

Shrink the Log file

1. Perform a Transaction log backup
2. DBCC SHRINKFILE('log

dbcc shrinkfile(logfilename,1),


Quote:> when I do a complete backup, the size of DB is abt 1.5
> GB.  when I restore the db from same backup, the MDF and
> LDF are over 3.5 GB ... why are they so large ?

> Also, when the log file (LDF) gets big, how can I shrink
> it without loosing any data ?

> Thanks for your time.

 
 
 

1. DB size increased after reducing fields size

Hello, I have this problem:

my DB (SQL2k) is about 5mb (4,4 +0,6 free) with about 6000 records in
a table and a 2000 in another one, plus some other minor tables.

I cleaned the tables, reducing all the nvarchar fields size that I had
previously set to 50 or more to 20, 10 ... ecc ecc
Then compacted the DB.

Now DB is bigger, 5,7 (5,0 +0,7). Tried again and the DB now is even
more bigger.
How can I recompact data? because compact in the Enterprise manager
seems not to work.

I can post the schema of the DB if it can help, but is very simple,
and I'm sure I've only forgot something.

Thanks!

2. Database metrics anyone?

3. Large difference between backup size and db size

4. Last Processed

5. DB Size Info from master db

6. PDOXWIN: design/capability question

7. Transfer Db and Db Size

8. using ip address

9. Disaster Recovery: Calculate DB size based on .dmp files

10. SQL 7 DB Size question

11. Can't recalculate db size / can't rebuild index

12. SQL 2000 DB size reporting

13. DB Sizes: SQL Server 6.5 Admin Question