SQL Server Performance Forum – Threads Archive

make physical file smaller

Hi, I have DB in ver 7.0 where the transaction log physical file size is about 2.5 GB
When I truncate log and shrink the DB, it’s size little bit smaller.
But when I use export database to another DB Name the size is only about 160 MB
My question is why the truncate log and shrink database cannot make same effect
as export database? Is there any other way to make physical file smaller other
than export database? Sorry, if My English not good enough. TIA,
Halim

BOL explains:
For each file, SQL Server calculates a target size, which is the size to which the file is to be shrunk. When DBCC SHRINKDATABASE is specified with target_percent, SQL Server calculates target size to be the target_percent amount of space free in the file after shrinking. For log files, SQL Server uses target_percent to calculate the target size for the entire log; therefore, target_percent is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. Unlike data files, the shrinking of log files is not immediate. What was the size defind on the exported database? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

the exported database file size become 160 MB and log file size 1 MB
[?]

In what way you’ve exported the data? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I am using Export from Enterprise Manager

You may have to generate dummy transactions to move the log onto the next virtual log to enable the file to be shrunk further. Check this thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6095 Tom Pullen
DBA, Oxfam GB

I follow the tread as thomas said create and run the proc sp_force_shrink_log made the transaction log about 1 MB only. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Thanks thomas

]]>