June 29, 2007 at 4:52 pm
Hi,
I am stuck with shrinking the transaction log file. I am using SQL Server 2000.
I had backed-up the transaction log only into a single file. Later, I used the Enterprise Manager > select the database that I want to to shrink > Right-click on it > All Tasks > Shrink Database.
Then I clicked the Files button to shrink the transaction log only and chose "Shrink File to " and I chose the size that I wanted to be.
The Current Size showed 100 MB and Space used was 20 MB. I entered 20MB to be the final size.
I click OK and OK again ... BUT the size has not changed to 20MB. It still maintains to 100MB.
I am wondering what is wrong with this. I have patched the SQL server to the latest update.
In the server, there is a Cisco security software that can allow/block any process. Can Cisco security software affect the behavior of any sql transactions?
Any advice will be appreciated.
Thanks
Hendry
June 29, 2007 at 5:40 pm
On the first page of the dialog, there is a check-box "Move pages to the beginning of the file before shrinking" try checking that box; Then click Files to reach the next dialog, select your log file from the drop-down list and click OK.
Or how about running BACKUP LOG databasename WITH TRUNCATE_ONLY
June 29, 2007 at 8:41 pm
hi hendry,
refer http://support.microsoft.com/kb/272318 .....it shud be useful!!!!chek it..perform backup log with truncate_only option to truncate the uncommitted transactions from the log file and then perform shrinkfile or thru GUI...it shud be fine
[font="Verdana"]- Deepak[/font]
June 30, 2007 at 12:22 am
You can use DBCC SQLPERF(logspace) to check what is the size of the log file and how much is actually being used. So that you can plan and shrink the log file accordingly.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply