Once my database starts to fill up with the tables being used, my stored procedures are taking longer to execute. If I truncate the data and execute again, execution time usually takes 3 minutes. Once the tables fill up with millions of records I'm seeing execution time increasing to 6 minutes. I...
@Marian This is a great solution, if order doesn't matter. For generating a list of include column combinations, no problems. A list of key columns? Different story, since A,B and B,A are not equivalent in that case.
I was trying to understand the effects of introducing variables in a predicate. I have this query
declare @CommentedOnly nvarchar(max)=null
select top 5 * from journalemail je
where @CommentedOnly IS NULL OR je.ID IN (SELECT EmailID FROM EmailComments)
And here is the actual plan
I was hop...
@Gonsalu That's like saying you don't mind spitting in the sausage when you're making it. So many people have to eat it (use the programs you create with Java)
@BlueBerry-vignesh4303 sounds like you created some tables in model
That's kind of how model works - so maybe you should delete the objects you created in model
@AaronBertrand I don't do desktop programming, but you could use the same argument with C#... it's a bit of a bummer when you have to install .net framework x.y.z because of some programs
@Gonsalu Installing .net framework doesn't hose my machine (and nag me for updates constantly)
@Gonsalu And let's be honest most people already have .net framework, the issues only come when you choose to deploy your app in latest and greatest, then people need to update (not install fresh)
@Gonsalu And again, I wasn't talking about just you in your isolated world. Plenty of people still use Java to write desktop apps, developer apps, etc.
@Gonsalu I never said the programming language itself was bad.
The output is a pain in the ass. Also dealing with interop with SQL Server, for example, is annoying. People need to use JDBC and other things, which are years behind in functionality (do you know of a Java client library for SQL Server that supports TVPs, introduced in 2008)?
"freely used as files" meaning users should be able to e-mail copies of .mdf files to each other, take "backups" of them, etc.? No, no, no. — Aaron Bertrand11 secs ago
Please pummel that answer into the ground. It is a bunch of baloney and the OP has disappeared - I don't want people skipping the comments and thinking anything he's saying is true.
The transaction log file size is nearing 100% on one database.
I run the below and both return 0 so no active transaction running.
The last query tells me that the database is in SIMPLE mode, but 'ACTIVE_TRANSACTION' is returned in log_reuse_wait_desc column.
How do I find this?
SELECT @@TRA...
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:
In SQL Server -
What are some reasons the transaction log grows so large?
Why is my log file so big?
What are some ways to prevent this prob...
The Rent Is Too Damn High Party is a political party, primarily active in the state of New York, that has nominated candidates for mayor of New York City in 2005 and 2009, and for governor and senator in 2010. Jimmy McMillan was the mayoral candidate both times as well as a candidate for governor. In 2005, he received more than 4,000 votes, and more than 40,000 in 2010. The party has three registered members in the state. McMillan himself is registered as a Republican (previously a Democrat) for the purposes of running in that party's primary elections.
In 2014, the party expanded beyond New York...
So to fix an issue that we ran into yesterday, it's been suggested that we assign user permissions directly to several stored procs, instead of using a role based permission - that doesn't seem right to me
@bluefeet nope, that doesn't seem right to me either, but it depends - when you start expecting roles and members to map inside SQL Server (and inside a database) exactly how they work in AD, and stay in sync... I'd probably just do it manually too.
Everything's going to be a trade-off. The nice thing about a specific user is that it's easy to turn that user off. When you use roles and you have no idea what 400 users might be in the role...
In a similar way, you need to decide if you want your pain at config time or troubleshooting time. How likely is it that you'll need to maintain and track permissions on specific objects? How likely is it that you'll need to take away those permissions later, but not just remove the user?
So maybe your waits are meaningless. Are you troubleshooting the highest wait, or are you troubleshooting an actual performance problem that you think you can attribute to this wait?
(Most people do the former - OMG these waits are high, put down your sandwich, we have a problem to solve!)
So track down a little closer exactly what those ASYNC_IO_COMPLETION waits are, but in the meantime, investigate some others. Don't assume the highest wait has anything to do with the problem.
@JNK yes, you can run it from within the UI. Just look at any plan for a Top SQL or plan you've captured, then use the command text to fire something else.
If you can't get that to work (I honestly always use the standalone), let me know.
But the standalone free will let you do all of that except for the wait stats.
And of course you can manually create your own xevents session to capture your own wait stats, if you want to see what this query contributes to your overall waits...
hello, I want to get the time in hh:mi in sql server but I cant find the format in this list msdn.microsoft.com/en-us/library/ms187928.aspx I'm using 108, but I want the time without the seconds, is there any in-built way to get the time in hh:mi?
This past weekend, I picked up additional hard drives for my my desktop and the HP z800 workstation. I will never buy/build a desktop again and I hate you people for never telling me what a dream it is to work with something like that
Sliced my hands up getting the drive in there and trying to get wires all lined up and such, fought far too much with the internal layout on the desktop. Open up the workstation and initially I was like, WTF, where are the cables
let me get some ideas, to see it's correct my way of doing? I want do the time without the seconds, because it's a SP that it can be used in many sides for different aplications, but the data in the database always has the second part 00
That works great, until one application needs to display the seconds, or wants military time, or wants AM/PM, etc. You write the code once, and if it's the same for all the applications, it's still an extra few characters around whatever code you're already writing to display the time. If it's not going to change, nothing lost. If you make the database do it, though, you lose a little CPU every time you do that, forever.
You've already spent more time debating whether you should put this maintenance in the procedure or the code, than it would have taken you to just put it in the code.
There's not even that. A lot of the work you would typically do in the database could be handling by a caching tier, for example, if you have that capacity.
And sometimes the consumer is the database, there is no client tier.
Unimog is a range of multi-purpose auto four-wheel drive medium trucks produced by Mercedes-Benz, a division of Daimler AG. In the United States and Canada, the Unimog was sold as the Freightliner Unimog. The name Unimog is pronounced [ˈuːnɪmɔk] in German and is an acronym for the German "UNIversal-MOtor-Gerät", Gerät being the German word for machine or device. Daimler Benz took over manufacture of the Unimog in 1951 and they are currently built in the Mercedes truck plant in Wörth am Rhein in Germany. Another Mercedes-Benz Türk A.Ş. plant assembles Unimogs in Aksaray, Turkey. Unimogs were also...
Q 1:
Whenever an Oracle SP wants to return error code/message, it fails returning below exeption:
OLE DB provider "OraOLEDB.Oracle" for linked server "ORASRV" returned message "ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "DB.GET_COST", line 244
ORA...
I thought I had posted a meta question or answer that suggested questions should be approved before they can be answered - basically the opposite of now, where they can be answered until 5 people decide to close them
Sadly they have the information they can derive from other sources (e.g. census data) and back when it was handwritten the reliability of the data is only as good as the transcribers. I'd suspect 40% of the data I've used directly from those transcriptions has had errors (that are clear when I zoom in on the originals).
It's not a magic click-click-click, you're done, though it can serve that purpose if you only want to go a couple of generations back. It's helpful if you have unique names in your lineage; lot of false positives otherwise. (Hey, this person was named Bertrand, they must be related to you!)
NPR had a thing about some website, not ancestry, that allows for linking all the family trees together. Some person was organizing a big family reunion in NYC for basically the whole world. Can't find the link
Mike is right that the error message you're receiving is from the Management Studio application itself, and not from SQL Server. It is the memory on your local workstation that has been exhausted, likely due to trying to pull 16 billion rows into the client application (rendering that much data i...
@Marian that's ok, definitely useful for the include side, just not for the key side. I published my post without that use case, will investigate further
Thanks. That is actually not even the part I am trying to demonstrate - more the group concat side where we take the set and build all the create index commands. But I need to generate the set first. :-)
@swasheck for quick one-off stuff it's not a big deal (nor are 99% of my bad habits rants). The problem is that habits tend to creep into production code, sometimes at an alarming rate.
It's much worse when people post answers for n00bs to consume on SO/dba, because they look at it and think "this is the best (or even only) way to do it!" Then that's all they ever do. People being lazy about DATEPART(M, etc. drives me ballistic.