| Author |
 Topic  |
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 06 August 2002 : 17:06:01
|
Before I start, I used the search engine here and couldn't find the answer I was hoping to find.
I am on a new host and they cap the size of the Transaction Log. I was trying to make some changes through Enterprise Manager and received this message:
'FORUM_TOPICS (burningsoulsforum)' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Tmp_FORUM_TOPICS' in database 'burningsoulsforum' because the 'PRIMARY' filegroup is full.
Online support told me that the log was being filled up due to the coding I am using and that they have plenty of customers that never reach 25 Megs let alone the sizes I am reaching. Normally I would detach the database, rename the log file and then reattach the database which would create a new log file, but now I do not have the ability to do that.
Is there a way to control the size of the Transaction Logs. I don't want to limit it because I would more than likely start ending up with errors.
Is there a reason my transaction logs appear to be growing so quickly?
I really would appreciate explicit help on this because this is an area that I am not accustomed to, especially since I am not running the SQL server locally.
Thanks.
http://www.burningsoulsforum.com · Snitz Specs · Snitz Mods · Do's and Dont's |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 06 August 2002 : 17:13:05
|
did you use the 'new search' ?
did you search the archives ?
the SQL you need is definately here, just search for Transaction Log in the archives
|
 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 06 August 2002 : 17:15:37
|
As a breif explanation, if your log fles are filling up rapidly, then there are things your host can do to set up the database better, ask them if they back up the databases, if so how often. If they don't, then there is really absoluetely no point in keeping the transaction log at all since it is of no use, so they can set certain options on the server so that it doesn't keep growing like that.
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 06 August 2002 : 19:05:49
|
SQL2000 make sure the logging options are set to "SIMPLE" under SQL7 theres a similar option just can't recall what it's called off hand.
Normally the log fills up very quickly if your doing things like mass inserts / deletes / updates etc I was having similar problems for a while until I change the logging options too.
As Huw said its extremely unlikely in a "forum" environment you'd ever want to do a restore from the transaction log, full or incremental data backups are usually reliable enough to cover this.
www.daoc-halo.com |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 06 August 2002 : 20:44:45
|
If you only have 25 mb that seems pretty skimpy to me.
======
Doug G
====== |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 06 August 2002 : 20:51:14
|
quote:
If you only have 25 mb that seems pretty skimpy to me.
======
Doug G
======
I would agree. I went ahead and converted to Simple mode and I will watch it closely.
HuwR, I finally did find something in the New Search. I don't know why I didn't earlier unless I mistyped a word.
http://www.burningsoulsforum.com · Snitz Specs · Snitz Mods · Do's and Dont's |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 06 August 2002 : 21:34:14
|
Sorry to drag a little off topic, but I've found my biggest error (user error that is too) is that I forget to change the serach criteria from subject only to all etc which normally results in my first search finding nothing.
www.daoc-halo.com |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 August 2002 : 03:34:36
|
quote:
Sorry to drag a little off topic, but I've found my biggest error (user error that is too) is that I forget to change the serach criteria from subject only to all etc which normally results in my first search finding nothing.
www.daoc-halo.com
I make that mistake a lot too 
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 August 2002 : 03:36:52
|
quote:
If you only have 25 mb that seems pretty skimpy to me.
======
Doug G
======
If the server is set up correctly, it should be adequate, although this forum has a 100Mb transaction log file, it doesn't use more than 25Mb unless we have a very busy day, it is usually between 20 and 23Mb
the data is currently using 185Mb
|
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 07 August 2002 : 04:07:45
|
quote:
quote:
If you only have 25 mb that seems pretty skimpy to me.
======
Doug G
======
If the server is set up correctly, it should be adequate, although this forum has a 100Mb transaction log file, it doesn't use more than 25Mb unless we have a very busy day, it is usually between 20 and 23Mb
the data is currently using 185Mb
I didn't read dayve's original post correctly, I thought the entire db space was 25mb :)
======
Doug G
====== |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 August 2002 : 04:17:31
|
quote:
dayve's original post correctly, I thought the entire db space was 25mb :)
Dayve,
as you can see, althought your host is throwing the onus on you to resolve this, it is infact because they do not know how to set up a SQL server correctly, the reason the other sites don't use that much is because they are probably no where near as busy as yours
|
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 07 August 2002 : 13:42:19
|
quote:
thanks HuwR, btw, I just realized I did the same exact thing with the New Search. I used the default of Subject Only the first time.
Since I have changed the options to Simple it really did make a difference.
http://www.burningsoulsforum.com · Snitz Specs · Snitz Mods · Do's and Dont's
You are fortunate that you are able to do that, some SQL users do not have that kind of access to their database.
Your hosts should have this set as the default option when creating a db and then there wouldn't have been a problem. My own SQL servers have their log files on a completely different drive and are not growth restricted like on most SQL hosting plans, but because the db's are backed up daily, the log files never grow very large anyway, but it is better for performance to allocate the 100Mb of space evn though they only use 25% of it.
|
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 07 August 2002 : 17:29:07
|
they were kind enough to bump up my transaction log from 50 to 80 and gave me another 50 megs on the database as well. in the end, everything seems to be going well, but I will say you are right, the support was confused by some of my comments and at times I had to walk them through some things which they stated was "normally out of the range of their duties" which I understood from a liability point of view.
things seem pretty fast now as well, better than when I was hosting myself and the price was just right.
http://www.burningsoulsforum.com · Snitz Specs · Snitz Mods · Do's and Dont's |
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 07 August 2002 : 22:41:31
|
Well, even though I'm too late to actually help solve a problem I'm not too late to pimp my site :) And I never miss a chance for that!
We've got a group of articles discussing the transaction log and how to manage it's size (http://www.sqlteam.com/FilterTopics.asp?TopicID=116).
SQLTeam.com - For SQL Server Developers and Administrators
Snitz Info - SQL Server info on Snitz Forums |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 07 August 2002 : 23:30:55
|
quote:
Well, even though I'm too late to actually help solve a problem I'm not too late to pimp my site :) And I never miss a chance for that!
We've got a group of articles discussing the transaction log and how to manage it's size (http://www.sqlteam.com/FilterTopics.asp?TopicID=116).
SQLTeam.com - For SQL Server Developers and Administrators
Snitz Info - SQL Server info on Snitz Forums
grazman, the method described in the link you provided was something I was doing when I hosted my own SQL Server, but now when I detach the database I cannot delete the log file because I don't have access to it on my new host.
http://www.burningsoulsforum.com · Snitz Specs · Snitz Mods · Do's and Dont's
Edited by - dayve on 07 August 2002 23:31:20 |
|
|
|
Topic |
|
Topic Locked
