Changing SQL Server database size

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.
 
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 :).
 
Dave said:
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.
Click to expand...

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
 
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).
 
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.
 
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!
 
You must log in or register to reply here.
Back
Top