August 22, 2017 at 10:28 am
Sue gets all the thanks--I barely know how to spell extended events, much less how to use them. 🙂
Yes, app vendors are the bane of DBAs' existence...because they do "stuff" like that...know there's a problem and wait for customers to bring it up before admitting they screwed up. Hope the hotfix clears things up for you.
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 22, 2017 at 10:39 am
Beatrix Kiddo - Tuesday, August 22, 2017 8:13 AMSue and Alan, I think we may be getting somewhere! I set up an Extended Events session and ran it for an hour. It captured a ton of primary key violation attempts happening every couple of seconds on a particular table, which the vendor has FINALLY admitted is a known issue caused by a configuration change last week, and that they have a hotfix for (honestly, app vendors are TERRIBLE). The timescale fits with when the problem started, so I need to wait for the hotfix to be applied and then see what's what. Thanks for forcing me to use XEvents 😀. I was convinced it was an application issue, but couldn't prove it for sure.
Wow....what an ugly ordeal. Glad it looks like things are narrowed down. And thanks for posting and keeping the thread apprised of everything - although I figured you would. Drives me nuts when people get replies and never post anything back.
It's kind of weird how nonchalant vendors are about hosing out peoples database systems. If we were to act the same, we'd be fired. Extended events aren't too bad and with having less overhead it's better and you can capture more than you can with traces. I am much more used to using server side traces though and tend to go to that first. I'm working on doing more with XEvents as well.
Sue
August 22, 2017 at 11:49 am
Not sure how many people have seen the tool, but Idera has an extended events tool that has a similar feel to Profiler but more modern. SQL XEvent Profiler is what it is called.
I don't work for them, but it is a neat tool for extended events. The built in templates are a bit limiting, but as the tool grows I am expecting more from it. But a nice tool for free and for quick looks at what is happening in SQL Server (if you like the built in templates).
Still, it is good to know how XE's work so you can customize them when the tool doesn't fit your needs. But for those who love profiler and want a tool to do profiler like traces but using XE's, it is nice.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 22, 2017 at 12:04 pm
bmg002 - Tuesday, August 22, 2017 11:49 AMNot sure how many people have seen the tool, but Idera has an extended events tool that has a similar feel to Profiler but more modern. SQL XEvent Profiler is what it is called.
I don't work for them, but it is a neat tool for extended events. The built in templates are a bit limiting, but as the tool grows I am expecting more from it. But a nice tool for free and for quick looks at what is happening in SQL Server (if you like the built in templates).
Still, it is good to know how XE's work so you can customize them when the tool doesn't fit your needs. But for those who love profiler and want a tool to do profiler like traces but using XE's, it is nice.
That's not a bad idea. I have built a few XEvents templates but that tool might be worth a try.
Everyone was pushing so much on using XEvents that I think I just got a bad taste for it early by trying to use them on SQL Server 2008. So I ignored them for quite awhile.
Sue
August 24, 2017 at 1:35 am
I did too, Sue. All the evangelists put me off them for quite a while. I have seen the light :D.
August 24, 2017 at 8:21 am
The tool is neat for a free tool, but lacks some features that I see as important. Like the ability to create new templates. You are stuck with the stock ones. For the majority of what I do, the stock ones work great. But you want to debug service broker (for example) and the tool isn't very helpful.
But it is a nifty tool to try to get people away from profiler (well, when they only use the default templates with profiler).
I didn't really touch XE's until I was using SQL 2012 and even then, I still had (and HAVE) a habit of going back to profiler. Never in heavy use production boxes, but sometimes I'll touch a low use production server with profiler to see why a thing is failing. But profiler is my "last resort" tool. XE's should be my "last resort" tool and I"m trying to push myself to use them instead of profiler but old habits are hard to break and Idera's tool is slowly pushing me away from profiler.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 25, 2017 at 5:12 am
Beatrix Kiddo - Friday, August 18, 2017 7:49 AMSue_H - Friday, August 18, 2017 7:44 AMMay want to check for any open transactions on the server (not just the one database) as well as dbcc loginfo to see where, what is going on with the active part of the log.
Sue
Thanks, Sue; DBCC OPENTRAN returned no open transactions on any of the databases. I'm not sure what I'm looking for with DBCC LOGINFO in this case but I will have a Google.
DBCC SQLPERF(LOGINFO) will show you (very roughly) how the transaction logs are being used. If there is a lot of white space in the file then I would guess everything is OK but if log space usage is around the 99% mark constantly then I would suggest that there is data not being cleaned out after a log backup.
Have you tried the obvious instance restart, empty the log and observe what happens afterwards?
August 25, 2017 at 5:14 am
SQL_Hacker - Monday, August 21, 2017 10:42 AMAgree with Sue as that's the best solution.
However, if you're not familiar with Extended Events, and since it is isolated to a 30 minute window of time, you could pull out the "old beast", Profiler, and capture the events there too. Definitely want to do this when users are not trying to connect and work though...I called it "old beast" for a reason...it sucks all the life out of SQL Server if left to its own devices. Also, if you do choose to use Profiler, make sure to NOT use the default settings...go in and only grab the items you absolutely need and turn off everything else.
Probably better to configure a server-side trace rather than a profiler trace. Not so much life is sucked out......
August 25, 2017 at 6:29 am
Have you read the whole thread...?
August 25, 2017 at 6:34 am
Beatrix Kiddo - Friday, August 25, 2017 6:29 AMHave you read the whole thread...?
Oooops! Sorry Sue.....my bad...I missed your comment. Server-side trace is your credit....
August 25, 2017 at 7:18 am
Nah, I just meant we've got to the bottom of it now. (By the way where do you work that they let you restart instances any time 🙂? The amount of hoops we have to jump through here...)
August 25, 2017 at 7:48 am
Beatrix Kiddo - Friday, August 25, 2017 7:18 AMNah, I just meant we've got to the bottom of it now. (By the way where do you work that they let you restart instances any time 🙂? The amount of hoops we have to jump through here...)
lol! We work only in Europe with companies with a standard 9-5 business day so each day we have a couple of hours to do what we want before the first DWH load starts.....it really is a luxury!
August 25, 2017 at 12:11 pm
kevaburg - Friday, August 25, 2017 6:34 AMOooops! Sorry Sue.....my bad...I missed your comment. Server-side trace is your credit....
See what you did?
And then you posted to use DBCC SQLPERF(LOGINFO). I believe you meant DBCC SQLPERF(LOGSPACE)
Just kidding, no worries. Well..not kidding on what you meant in the DBCC. 🙂
Sue
August 29, 2017 at 2:29 am
😀
And just for completeness, the hotfix over the weekend worked, and all is well again for this database. App 0 : SQL Server 1.
August 30, 2017 at 12:29 pm
Beatrix Kiddo - Tuesday, August 29, 2017 2:29 AM😀And just for completeness, the hotfix over the weekend worked, and all is well again for this database. App 0 : SQL Server 1.
Maybe it should be:
App 0, Beatrix 1
Glad to hear all is well. For now anyway....
Sue
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply