April 18, 2022 at 3:10 pm
Before I ask the question, I know this can be done via *.bat files and task scheduler on the server or a forced reboot of the server and a few other methods, but unfortunately, due to security reasons at my company, we do not have the ability to do this outside of SQL Server itself, as we are full admins for SQL Server, but not the actual server itself.
I know I can use a T-SQL -kill command to -kill all database connections, but my understanding is that the -kill command does not kill connections that are in "Rollback" and unfortunately, we seem to have connections in ROLLBACK. But what we want to do is automate some process that at a given time, will sever / kill all database connections regardless of their state. I understand the risks with this but please know, we need to do this to prove / disprove a theory with an issue that has plagued us for months that unfortunately, has not been resolved and we are looking at this as a way to test a theory we are having.
A third party pushes us data. It seems to almost always complete Monday morning, but starts to fail Tuesday - Friday and our theory is that server resources / hung connections / etc. is the reason it is failing. We want to sever all connections first before they run their process and see if this matters. A second thought is to give this team a window for xyz hours to push the data, at which point, no one else can access the server, but is an extreme test and want to try the first one before going down this path.
April 18, 2022 at 3:42 pm
You can certainly write a T-SQL script and run it in a SQL job to kill connections. However, it's not going to kill a connection in a rollback state. You typically have to wait for that to complete.
I guess I'm wondering why using an extended events session to capture the activity on the server is not a first step. Doing that would allow you to trace all of the activity that has occurred, and then actually see the specifics of what may be occurring.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 18, 2022 at 4:38 pm
We have something like that running that captures all queries being executed on the server.
April 18, 2022 at 5:00 pm
We have something like that running that captures all queries being executed on the server.
What did that fail to show you? I'm making a large assumption, but if this is listing the SQL statements that are executing against the database, you should be able to find out if one if the imports is causing an issue.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 18, 2022 at 6:21 pm
Please understand, I am not trying to be rude here, but we have done all that analysis and presented the evidence to the 3rd party team as to the causes of this. Unfortunately, we have gotten no support and have been told "it isn't our issue, as it is 'big data servers which we do not control how they manage xyz'". Of course, I have no idea how it isn't their issue, since they are pushing us the data, but unfortunately my title isn't high enough to overcome the backroom politics. We have all the evidence we need pointing to the causes and presented this for months, but unfortunately it has done nothing to solve. We were told it is our resources on the server, so we maxed out RAM, Processors, added drives, etc. We were told it is due to not having an "isolation layer", so we added one. We were told it was because they were TRUNCATING the tables before starting and it isn't their job, so now we wake up early each morning to perform this task. Everything they claim is the cause, we have done and still the issue persists. All the evidence we present is met with skepticism, deflection and silence.
As such, these are things we want to try on our own and see the results, however we are unsure how to do this via SQL Server / T-SQL. The only thing we can see is that for some reason, Monday mornings we seem to get all our data in 3 - 4 hours, but the rest of the week it fails after 7 - 8 hours. We wanted to perform nightly reboots, but we are not server admins. We want to run scripts to start / stop SQL Server Agent, but we cannot automate these. The best we have is admin rights on the databases / SQL Server.
April 18, 2022 at 6:35 pm
Now, I am not trying to be a wise-guy, but with levels of "un-cooperation" that exist at your workplace, I would be leaving. Fast.
Is xp_cmdshell enabled? Re-start the SQL service using that.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 18, 2022 at 6:45 pm
Actually, overall, this is one of the best places I have worked. I have been remote long before COVID. I won't find anything close to the salary anywhere near where I live and the benefits and health insurance are amazing. There are also no "in-person" jobs within an hour one-way commute of my residence. The company as a whole, IMHO, really tries hard to give employees a work-life balance as well as tries to do what is best for their employees. Yes, they are a publicly traded company I understand the pull between stock holders and boosting stock and dividends but they are not solely about stock holders and a lot of that money makes it way back into the company and its employees. My actual team is amazing and my manager is one of the best I have ever had. However, my one complaint has always been cooperation between front-end teams and the data teams and it has always been a major issue like this.
As to xm_cmpdshell, we use this for other items, so I know it is enabled for us, so I will look into that as a possible solution. Appreciate the help 🙂
April 18, 2022 at 6:51 pm
but my understanding is that the -kill command does not kill connections that are in "Rollback" and unfortunately, we seem to have connections in ROLLBACK.
To the first point, there's a good reason you can't kill a ROLLBACK -- because that could corrupt your db.
To the second point, that should be a major issue for you to resolve ASAP. Rollbacks should be infrequent, not a very common thing. Either you're not doing enough pre-checking before starting a data modification or you have some serious issues in your table/index design, or both of those, of course.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 18, 2022 at 6:58 pm
To the first point, there's a good reason you can't kill a ROLLBACK -- because that could corrupt your db.
To the second point, that should be a major issue for you to resolve ASAP. Rollbacks should be infrequent, not a very common thing. Either you're not doing enough pre-checking before starting a data modification or you have some serious issues in your table/index design, or both of those, of course.
We are aware of this, but unfortunately the ROLLBACKS are not caused by our code or something that we can control. The ROLLBACKS in question are a result of the 3rd party's process failing and their connections going into a ROLLBACK.
We understand the reasons behind why you cannot kill a ROLLBACK, but unfortunately we are at a point that their process prevents us from doing our jobs when it fails and as of right now, our only recourse is a manual reboot which throws the database into "Recovery" mode.
We simply want to test a theory and see if we can get it automated so we can stop having to wake up three hours early to deal with this.
April 18, 2022 at 10:18 pm
Why do you have to wake up early to truncate some tables? Can't you schedule the truncates before they start their push? If you don't have permissions to create a job, can you run a script every afternoon with a WAITFOR TIME before running the truncates? Not ideal. but if you are doing your job with one hand tied behind your back then so be it.
The same script could then put the database in single user mode with rollback immediate (or something like that) to make sure there are no other connections, then put the database back into multi-user mode before the nightly push begins.
If the failed push results in a slow rollback, does that mean that the inserts are is a fully logged operations? Have you done everything you can to allow minimally logged bulk inserts? It requires the third party to use appropriate locking, which may not be possible. https://www.mssqltips.com/sqlservertip/1185/minimally-logging-bulk-load-inserts-into-sql-server/
Are there any indexes or constraints on the tables that slow down inserts?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply