|
|
|
A log file has been growing very fast from 2 gig up to 19 gig in a matter of about 4 hours.
The recovery model is set as full.
Even with backing up the transaction log file, every 30 minutes, I have found that the log file is not getting truncated.
I did notice that the file growth stopped as soon as the workday came to a close.
On running DBCC SQLPERF(LOGSPACE) I noticed that the log space was being eaten up fairly quickly. Also on running DBCC OPENTRAN I did not see any really old transactions that had not been commited
In my investigations someone mentioned that if a person was selecting a large volume of data this could make the log file grow. It is quite possible to select in the region of 9million plus rows in a select query of this database.
I was under the impression that the transaction log file only contained updates, deletes and inserts in order to be able to rebuild the database at any point in time. Having googled this I am not any the wiser.
I will run some more diagnostic tests on Monday to see if I can discover any more information if I see the log file growing rapidly again.
So my question is – would a large number of individual select commands, or a high volume of data being selected get logged to the transaction log file?
_____________________________________________
Solved - read below
_____________________________________________
It turns out that a user was running a large report which kept falling over.
They kept restarting the report.
The software is a 3rd party piece of software that appears to write results to some sort of temporary table.
I asked the user to run the report first thing this morning, when the office was very quiet.
Using DBCC SQLPERF(LOGSPACE) I then noticed the log starting to be eaten up again and when the report completed the log stopped growing.
So I think I am going to set up some sort of warning system that monitors log growth over time and alerts me when it starts ballooning as it did on Friday.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 6-Oct-14 8:04am.
|
|
|
|
|
Have a look at the log_reuse_wait_desc column in sys.databases - that should give you some idea of what's preventing the log from being truncated.
SELECT
[name],
recovery_model_desc,
log_reuse_wait_desc
FROM
sys.databases
;
Technet has a description of the values:
http://technet.microsoft.com/en-us/library/ms345414%28v=sql.105%29.aspx[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thanks - yes I was looking at this on Friday.
I discovered it was a user who was running a report that kept falling over and they kept restarting it.
The software is a 3rd party piece of software and it looks like when users select data the results are written to some sort of temporary table within the database.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
hi... is there a max limit to throughput of Microsoft SQL Server cluster in terms of how many request (or... *simple* query) the cluster can take per minute?
What about a single SQL server instance, assuming you can scale up throwing in $$$ put more RAM/CPU/faster disks...etc
Thanks
dev
|
|
|
|
|
|
Yes you can get better server performance o a single server by money at it, not your best question.
There is an entire industry based around server performance, not going to be answered by a forum post.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No, there's no hard limit defined. Yes, there will be a practical limit.
devvvy wrote: What about a single SQL server instance, assuming you can scale up throwing in
If you can throw in money, hire a dba.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
devvvy wrote: hi... is there a max limit to throughput of Microsoft SQL Server cluster in terms of how many request (or... *simple* query) the cluster can take per minute?
Everything in computing has limits but I doubt that has anything to do with your question.
Presumably you or someone else you know thinks that X is 'better' than SQL server without even doing any real analysis.
Here is an example of someone using SQL Server
http://highscalability.com/blog/2014/7/21/stackoverflow-update-560m-pageviews-a-month-25-servers-and-i.html[^]
Myself I was tasked with doing performance tests using the application that I was working on, and at the time the application server could handle 100+ TPS a second sustained where I estimated that the entire US market for that business domain was only 2000 TPS.
And during that test I couldn't get SQL Server to even provide any real CPU/Memory load on some pretty crappy equipment. Consequently my conclusion was that for any conceivable reality a single SQL server instance would serve the needs of the company.
Now it is certainly possible that you have a business domain that has some very specific data needs based on the business needs. Not hypothetical tech arguments. If so then you need to do the following
- Collect the actual business requirements that might lead to performance problems.
- Collect the actual market potential of business. One might reasonably claim as a top level that owning the entire market in the world is the goal but claiming astronomical numbers without any real world basis is foolhardy. So is making up numbers without looking at markets at all.
- Do an analysis of likely transaction flows based on the first item.
- Identify possible bottle necks.
- AFTER doing the above then look for solutions that will solve any bottle necks at the data persistence layer.
Finally expect that if you have bottle necks at the data persistence layer then you MUST expect that you are going to need to have other architectural changes necessary to deal with that. A specific type of data persistence server will NOT solve problems of this sort.
|
|
|
|
|
Make that the visible universe.
|
|
|
|
|
thanks I just saw a tweet yesterday claiming MongoDB can take few million hits over few minutes duration. I just want to know if, for example, on an average machine or even workstation, what kind of hits Microsoft SQL server can process?
dev
|
|
|
|
|
There's such a thing called Transaction Processing Performance Council[^], they have many different lists of test all adjusted to get a different vendor on the top, but they will still give you an idea of what to expect.
|
|
|
|
|
devvvy wrote: I just saw a tweet yesterday claiming MongoDB can take few million hits over few minutes duration
And I believe I saw an article where someone was getting something like 1 million TPS on something.
But still pointless unless there is a real potential for that sort of traffic.
|
|
|
|
|
yes many false claims, #bigclaims everywhere
dev
|
|
|
|
|
devvvy wrote: yes many false claims, #bigclaims everywhere
Given the site I suspect it is rather likely that the throughput was valid.
http://highscalability.com/[^]
|
|
|
|
|
Hello
In the query below, since I am using a LEFT JOIN I would expect all rows from podetm to be returned. This is true as long as the line that is commented out is not included. As soon as I include a predicate from the joined table, I seem to get only rows that exist in stockm in the result set. Why?
select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
|
|
|
|
|
Yes, you're effectively turning it into a normal join in this case.
Think about it, when you add the line and s.analysis_a not in ( 'LNG', 'INFANTRUST') you're telling it to give you all records where s.analysis_a does not contain 'LNG' or 'INFANTRUST'.
You didn't tell it to give you records where s.analysis_a is null.
So try this:
select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
and (s.analysis_a not in ( 'LNG', 'INFANTRUST') OR s.analysis_a IS NULL)
|
|
|
|
|
|
|
 I was looking at yours. 
|
|
|
|
|
Jorgen, as usual... Thanks for the clear explanation 
Getting all the required rows after following your suggestion.
|
|
|
|
|
If you want to include that condition then include that in the Join like
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product and s.analysis_a not in ( 'LNG', 'INFANTRUST')
Else you will face that issue
|
|
|
|
|
try it:
select * from scheme.podetm d
left join scheme.stockm s on s.warehouse+s.product = d.warehouse+d.product
where d.qty_received >0 and d.inv_value_posted =0
and (s.analysis_a not in ('LNG','INFANREUST')
or s.analysis_a is null)
|
|
|
|
|
I'm going to be developing a WPF/C# app that will run on single PC's/Laptops.
I have extensive experience with SQL Server, but now I need a replacement DB that I can install and use on the target environment.
Again, something similar to SQL would be nice so I keep the learning curve to a minimum.
Thanks
If it's not broken, fix it until it is
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
