July 21, 2014 at 1:48 pm
I have a stored procedure that creates backups of all the databases on my server. It is called from a VBScript routine called every 15 minutes. It has been working well for quite some time, with no apparent problems. Recently, I started using another computer on my desk to run Idera's SQL check monitoring program. I saw dozens of stalled processes, all caused by this script. I don't know if it started recently, or if has been happening for a long time, and the server manages to blow all these stalled processes away after some time, since I just started watching this monitoring program. Also, the server had a bad power supply for a few weeks, causing it to shut down for no apparent reason. It took me a while to figure out why it was happening, but a side effect was a periodic restart of the server, including, of course, the SQL Server engine. No idea how many processes may have built up and gotten lost when the machine reset.
I've littered the procedure with print commands, and they all print exactly what I expect the procedure to do at that point. When I run the procedure manually, it performs correctly, but it every time it's called automatically, a process pops up on the monitor and stays. I've written a small script that kills all these excess processes, and I run it periodically to clear the machine, but that's hardly a viable long-term solution.
I realize that this an extremely vague problem description. I can post the scripts, and the stored procedure, or provide any other information anyone may think is useful, but I'm momentarily at a complete loss as to what I should even look at. Can anyone think of what I might investigate?
July 21, 2014 at 2:00 pm
How long does your procedure take? I am wondering if your are running into concurrency issues because you have multiple backups being run at the same time. 15 minutes is not a very long span between backups. Why do you need them so frequent?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 21, 2014 at 2:10 pm
Sean Lange (7/21/2014)
How long does your procedure take? I am wondering if your are running into concurrency issues because you have multiple backups being run at the same time. 15 minutes is not a very long span between backups. Why do you need them so frequent?
The procedure takes only seconds, even for the largest database. The procedure performs different functions, depending on conditions. It does transaction log backups on 15, 30 and 45 minutes after the hour, differential backup on the hour, and a full backup at 11pm. There is an initial check for the date last changed. If there has been no change since the last backup, the procedure exits immediately. If there have been changes, the appropriate backup is performed.
15 minutes seemed like a good interval to minimize data loss, and it was an opportunity to experiment with backup procedures, which I had only done manually up until I created this.
Also, the VBScript that runs these routines is called with a WaitUntilComplete flag, so each database is backed up independently, with no concurrency. There are eight total, and the stored procedure seems to be getting stuck on only one of them.
July 23, 2014 at 12:58 pm
Found it - there are a series of chained VBScript routines that activate all this, and one of them was sending a bad parameter to the stored procedure. Simple mistake, the script calls my backup procedure, one at a time, with the name of each database that is to be backed up. I had deleted one of my development databases, but forgot to take its name out of the backup script. The script hung with an error message, AFTER opening a connection to SQL Server. My stored procedure, where I was concentrating all my trouble-shooting efforts, works fine, but the VBScript that called it wasn't handling errors properly. So, the script hung, with the connection open, so Idera's SQL check monitor showed a new task for every instance when this assemblage was activated - every 15 minutes. Needless to say, they mounted up quickly.
And to think, if I had answered that matchbook ad, I could be driving a truck for big wages...
July 23, 2014 at 1:12 pm
pdanes (7/23/2014)
Found it - there are a series of chained VBScript routines that activate all this, and one of them was sending a bad parameter to the stored procedure. Simple mistake, the script calls my backup procedure, one at a time, with the name of each database that is to be backed up. I had deleted one of my development databases, but forgot to take its name out of the backup script. The script hung with an error message, AFTER opening a connection to SQL Server. My stored procedure, where I was concentrating all my trouble-shooting efforts, works fine, but the VBScript that called it wasn't handling errors properly. So, the script hung, with the connection open, so Idera's SQL check monitor showed a new task for every instance when this assemblage was activated - every 15 minutes. Needless to say, they mounted up quickly.And to think, if I had answered that matchbook ad, I could be driving a truck for big wages...
Glad you found it. That had me rather stumped. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2014 at 1:14 pm
pdanes (7/23/2014)And to think, if I had answered that matchbook ad, I could be driving a truck for big wages...
And wishing you'd answered that matchbook ad for a technical school ...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply