| Author |
 Topic  |
|
RaiderUK
Average Member
United Kingdom
577 Posts |
 Posted - 20 October 2005 : 05:33:13
|
Hi,
I have a SQL server running at my ISP, up until 4 weeks ago everything was fine and i had to empty the Trasaction Log every 6 months. Now the Log needs clearing every day and grows 15mb every 5 hours. Can anyone please help?
Kind Regards,
Daniel |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 20 October 2005 : 05:52:57
|
ask your host to set the recovery model of your database to "simple" this should stop your transaction log from filling up.
It may also be worth your while looking for a host who knows something about SQL databases  |
 |
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 20 October 2005 : 17:19:21
|
Hi HuwR,
I have spoke to the ISP and they say the model is already set to simple. Is there anything else i can try? Why would it have been fine for the past year and now grow by 15mb per day?
Thanks for the help,
Daniel |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 21 October 2005 : 04:49:51
|
| This sounds very odd - there are some diagnostics you can do as SQL Admin that you probably can't as a regular user - try asking your host if he has any ideas, as it certainly sounds most odd - 15Mb/day is huge growth. |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 21 October 2005 : 05:25:00
|
How busy is your site ?
How many posts do you get in an average day ?
Are you doing lots of maintenance (archiving or deleting) ?
Does anything else use your db ?
How big is your db ?
How big is your log ? |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 21 October 2005 : 12:18:30
|
the database is only 5mb, the log is 15mb then it hits our limit which is 20mb.
The usage has not gone up recently and the last time the log got full was 6 months ago and i cleared it and took another 6 months to fill until now. |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 21 October 2005 : 12:40:40
|
I would really get your host to check things, it is pretty implossible to have a transaction log 3 times the size your db when using the simple model, It would also indicate that your host does not back up your database, something they really should be doing. The transaction log for Snitz is < 20Mb and our main DB is >350Mb, in fact the largest transaction log on my servers is only 25Mb.
As I said in my earlier post, I would seriously consider looking for another SQL host, a 20Mb db limit is a bit of a joke to be honest |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 24 October 2005 : 06:22:07
|
Thanks for the help everyone.
I am going to call the host today, but i first have to log a support call in text on their site and they might get back to me in the next 6 months, i really don't think they know what they are doing with SQL databses at all.
Kind Regards,
Daniel. |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 24 October 2005 : 07:58:58
|
Hi Me again.
I have spoke to the ISP and they say it will not be their fault and the interigate the transaction log to see what info is in it an what is creating it.
Can anyone tell me how i can do this?
Kind Regards,
Daniel. |
|
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 24 October 2005 : 12:06:30
|
this one is interesting
quote:
Client Applications Do Not Process All Results
If you issue a query to SQL Server and you do not handle the results immediately, you may be holding locks and reducing concurrency on your server.
For example, suppose you issue a query that requires rows from two pages to populate your result set. SQL Server parses, compiles, and runs the query. This means that shared locks are placed on the two pages that contain the rows that you must have to satisfy your query. Additionally, suppose that not all rows fit onto one SQL Server TDS packet (the method by which the server communicates with the client). TDS packets are filled and sent to the client. If all rows from the first page fit on the TDS packet, SQL Server releases the shared lock on that page but leaves a shared lock on the second page. SQL Server then waits for the client to request more data (you can do this by using DBNEXTROW/DBRESULTS, SQLNextRow/SQLResults, or FetchLast/FetchFirst for example).
This means that the shared lock is held until the client requests the rest of the data. Other processes that request data from the second page may be blocked.
Are you using pure Snitz, or do you have any mods? Maybe a mod is causing the above?
If your host is saying they won't analyse the logs, do they give you sufficient privs to do so yourself? |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 24 October 2005 : 12:35:27
|
| pdrg, what you quoted would really affect select query performance, however a select query will not affect the size of your transaction log in any way, the transaction log contains updates, inserts and deletes to your data not select queries |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 24 October 2005 : 12:36:36
|
quote:
Originally posted by RaiderUK
Hi Me again.
I have spoke to the ISP and they say it will not be their fault and the interigate the transaction log to see what info is in it an what is creating it.
Can anyone tell me how i can do this?
Kind Regards,
Daniel.
Does your host back up your DB ? if they do not, then it WILL affect the size of your log. |
|
|
|
RaiderUK
Average Member
United Kingdom
577 Posts |
Posted - 24 October 2005 : 13:33:18
|
| Yes they do but weekly i think, the lof file grows to 15mb in 5-7 hours. |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 25 October 2005 : 05:42:39
|
Hi Huw, I just spotted it on that KB about causes for Log Bloat, made me wonder if we were dealing with pure Snitz or Snitz+mods...
for instance, if some mod has left a transaction uncommitted, or a transaction cannot commit due to locking, or something.
quote:
Transaction not committed due to blocking: In a multi-user environment it is
possible for an open transaction to become blocked on locks held by another
process. In this case, the transaction will nevertheless remain open, preventing
log truncation.
I wonder what running
DBCC LOGINFO (databasename)
sp_helpdb databasename
sp_spaceused
dbcc sqlperf (logspace)
would throw up? |
|
|
|
SiSL
Average Member
Turkey
671 Posts |
|
|
Topic |
|
Topic Locked
