Changing SQL Server database size

Discussion in 'Database Support' started by Dave, Oct 23, 2003.

  1. Dave

    Dave Perch

    I put in a ticket about the transaction log (3 times actually). Each time I specifically asked "how do I change the database size?" I kept getting answers about the transaction log quota, but in none of the 3 replies I got did anyone bother to respond to this question.

    In the 3rd response, I was told my log was truncated to 2 MB, and to make the database 20 MB and make the log file ratio 10%. I could not change the ratio because I always got an error saying the ratio was less than the 5 MB currently used (had a 50% ratio). So I took a chance at changing the value of the field called "Total quota size (in MB)" from 10 to 20 (which doesn't sound right but maybe it is). Then I tried changing the quota to 10%. I STILL get " Error: The database quota value you are trying to set (2 MB) is smaller than the disk space currently occupied (5 MB). "

    Now I have a Used transaction log file 1 MB out of 10 MB.

    How do I fix this?

  2. StPatrick

    StPatrick Perch

    No, set it to 90%. It actually means how much percent the DATABASE will take, so the rest is transaction log. So if you want a transaction log 10%, then set to value 90%, if you want 30% of log, then set it to 70% and so on :).
  3. Yash

    Yash Bass

    I had went through your ticket (#11292) and our administrator had mentioned this link not once but twice.

    It contained instructions how to change quota.
    I'm sorry about the unclarity regarding the log file ratio setting
  4. Dave

    Dave Perch

    My apologies for not seeing the link. I think I was mislead by the quota part at the end and thought it was a reference to the transacation log quota. It was late at night here when I was looking at the responses.

    I'm just familiarizing myself with SQL Server since I'm used to using only Access. The terminology it uses is quite different, so it'll take me a while to get a handle on it. I find it somewhat confusing at the moment.

    Thanks for your help (Yash, whoever answered my e-mails, and St Patrick).
  5. StPatrick

    StPatrick Perch

    Just a bit more info on this topic.

    After I've got a db_owner permissions set by support (for my database), I went to database properties and checked "Auto shrink". A few hours later, the transaction log was reduced 10 times - it was 11Mb, now it is only 1Mb. So I was able to change the percentage, etc.
  6. Dave

    Dave Perch

    Thanks for the tip, StPatrick. I didn't even know all those other options were available for the database until I went looking for the auto-shrink option. My database isn't too big yet, but I'll see what kind of impact it has. Reading the help file, it seems the auto-shrink would be roughly the equivalent to compressing an Access database. I didn't try changing any other options, but I'll do some reading to familiarize myself with them.

    I'm glad you mentioned it!

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services