shrinkfile in sql 7

[JRtoad]

trying to decrease the size of the trans log for a sql7 db. In sql 2000 the dbcc shrinkfile seems to actually descrease the size of the file when run after using the truncate log, but in sql7 running that same command does not seem to descrease the physical size of the file on the hard drive.

Is there a way in sql7 to descrease the physical size of the log file(or data file for that matter)?

thanks,

Todd

 

[NoCoolHandle]

In sql 7 you really need to be aware of what is happening with your log files (I am assuming it is there that they aren't getting smaller)

Important concepts.

Logs grow in a single direction (towards the end of the file from the beginning..)

They don't go back to the beginning unless a checkpoint has marked all the early pages as checkpointed. This means that a backup log, needs to be followed with enough activity to fill the current log expent, then it can go back to the beginning again (but until the next checkpoint there is still a bit of log out there at the end of the log that you won't be able to shrink past..

HTH

Rob

 

[JRtoad]

Right, but I unable to decrease the physical size of the file. When the DB was created it was created without a limit on the size of the trans log, as a result it kept growing to a size of 17gb. Generally, there only needs to be about 1gb of log space required for this db. I have truncated the log and backed up the db. now the log size is only 300mb, but the physical size of the file remains at the largest size that it reached; 17gb. I want that physical space back so i can free it for other use.

Using this statement does not decrease the size of the log file's physical space on the hard drive. (on sql7) (it works on a sql2000 server)

DBCC SHRINKFILE(log_file, 1000)

 

[NoCoolHandle]

THe point I was trying to make is that although you only have 300 meg in your log, it is probably at the "Wrong" end of your log, and is therefore stopping you from reclaiming space....

I am also assuming you didn't explicitly make a 17g log and that it has only managed to get there due to infrequent log backups...

Again, checkpoint then do enough work (update/insert/delete) to fill the active extent and go back to the beginning,then another checkpoint followed by your backup.

2k is much easier and handles the move internally in 7 it wasn't smart enough, you need to work a bit more....

Rob

 

[JRtoad]

got ya...
ok i will give it a try. thanks