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?

    Thanks.
  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

    Dave
    I had went through your ticket (#11292) and our administrator had mentioned this link not once but twice.
    http://jodohost.com/hsphere/mssql_support.html#quota

    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