May 10, 2002 at 3:36 am
I have a database which was created with unrestricted growth. The log file space is 209 mb. I would like to reduce this,as it really doesn't need to be this big. I have executed BACKUP LOG "" WITH TRUNCATE_ONLY, but the physical file remains the same. I tried to change the 'Space Allocated' in EM, but it wouldn't allow me to reduce it (even though the LOG file has over 201 MB free.
Is there any way I can achieve this?
Thanks
May 10, 2002 at 4:14 am
Hey Julliff,
When you backup the log file, the log is truncated to leave only the active portion of the log file. This will not effect the log file size as truncating refers to the data within the Log file itself.
Once you have backed up the log and it is truncated, to reduce the size you need to shrink the file. This is done by using
DBCC SHRINKFILE (MyDB_Log, 10)
Would shrink the MyDB_Log file to 10MB. See BOL for more info.
Clive Strong
May 10, 2002 at 4:58 am
Clive,
Thanks for your reply. I have tried it, and althought it executed without error, the file size remained the same. I tried it on another db, and it did shrink the file, so there must be something about the file in question.
May 10, 2002 at 5:06 am
Hey,
Sorry, forgot to mention, backup your TLog again after you shrink the file. You should notice that it will have shrunk!
Clive Strong
May 10, 2002 at 5:57 am
If you are running SQL 7 then the virtual logfiles inside the TL tend to not go away. Goto http://qa.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1 and you will find a script to help you get past this issue.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply