July 19, 2017 at 7:35 am
For some reason i can't make topics on my PC anymore, nor can i put the below in the actual topic's post. So the above is just a holding psot, so that i can reply, and then actually post my question. Odd...
One of the team has recently finished working on a new SSIS project, which he tried to deploy to the Live Server today, however, when trying we receive the following error:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. (Microsoft SQL Server, Error: 3930)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.6248&EvtSrc=MSSQLServer&EvtID=3930&LinkId=20476
This doesn't really give much help, and neither does the link. (as it takes you to the Microsoft home page!).
I therefore opened a copy of the project up in VS 2015 (upgraded it), and tried deploying it to our test 2016 server. Deployed without a problem. Switched back the the lvie server changed the TargetServerVersion back to 2012, same error. Has anyone had this problem before during deployment? If so, how did you get around it? I've also attached a copy of the deployment summary xml (editted for anonymity), saved as text as xml files aren't allowed on SSC(?) .
In case you wondered, I tried redeploying one of our other existing projects, and this also deployed fine. It seems that it is just this one project that won't deploy to the live server.
Thanks guys!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2017 at 7:49 am
I assume you've checked disk space on the live server, plus any limits on log file size?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 19, 2017 at 7:55 am
Does the target server version of the SSIS project match the target server?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 19, 2017 at 7:56 am
Phil Parkin - Wednesday, July 19, 2017 7:49 AMI assume you've checked disk space on the live server, plus any limits on log file size?
Plenty of disk space left (over 500GB). There is a limit on SSISDB's logs which looks to be the default (2097152MB). It's currently sitting at around 5MB.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2017 at 7:59 am
Phil Parkin - Wednesday, July 19, 2017 7:55 AMDoes the target server version of the SSIS project match the target server?
Yes. The initial deployment attempt was done on VS 2010 with SSDT 2012. I then tried using VS 2015 with TargetServerVersion set to 2012. Same result to the live box. But it deployed fine to the 2016 box.
Unfortunately, no dev/test 2012 environments I can test on (however, as I've been complaining for the last 2 years almost daily we're FINALLY getting some in September).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2017 at 8:52 am
Tough one. That error message could be more helpful.
Are any of the packages in the project using post-2012 SSIS features.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 19, 2017 at 9:00 am
Phil Parkin - Wednesday, July 19, 2017 8:52 AMTough one. That error message could be more helpful.Are any of the packages in the project using post-2012 SSIS features.
I agree, the error message really doesn't give much help. I imagine that something is falling over somewhere, which invalidates the transaction. Unfortunately, instead of being told what invalidated it, you just get told the transaction couldn't be committed.
I would suggest that there aren't any 2014+ features, as the project itself was built in SSDT 2012 on VS 2010. Although I haven't personally gone through it myself (on my to do list for tomorrow), I would be surprised if they were able to put a 2014/2016 feature in using an older version of VS/SSDT.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2017 at 9:13 am
Thom A - Wednesday, July 19, 2017 9:00 AMI agree, the error message really doesn't give much help. I imagine that something is falling over somewhere, which invalidates the transaction. Unfortunately, instead of being told what invalidated it, you just get told the transaction couldn't be committed.I would suggest that there aren't any 2014+ features, as the project itself was built in SSDT 2012 on VS 2010. Although I haven't personally gone through it myself (on my to do list for tomorrow), I would be surprised if they were able to put a 2014/2016 feature in using an older version of VS/SSDT.
Fair point. I'm getting low on ideas. I remember that deploying packages edited (and thus 'upgraded') in VS2013 to SQL Server 2012 always failed, but I thought that this was resolved in VS2015. Not that I have had reason to try it ... luckily we're fully on 2016 here.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
July 19, 2017 at 12:10 pm
Phil Parkin - Wednesday, July 19, 2017 9:13 AMThom A - Wednesday, July 19, 2017 9:00 AMI agree, the error message really doesn't give much help. I imagine that something is falling over somewhere, which invalidates the transaction. Unfortunately, instead of being told what invalidated it, you just get told the transaction couldn't be committed.I would suggest that there aren't any 2014+ features, as the project itself was built in SSDT 2012 on VS 2010. Although I haven't personally gone through it myself (on my to do list for tomorrow), I would be surprised if they were able to put a 2014/2016 feature in using an older version of VS/SSDT.
Fair point. I'm getting low on ideas. I remember that deploying packages edited (and thus 'upgraded') in VS2013 to SQL Server 2012 always failed, but I thought that this was resolved in VS2015. Not that I have had reason to try it ... luckily we're fully on 2016 here.
Thanks anyway Phil. I'm going to have a stab at (effectively) rebuilding the entire project tomorrow, adding one package at a time, and seeing how it does. Might discover it's a specific one.
I'll keep you posted with any progress, or informative error messages. ^_^
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 27, 2017 at 4:21 am
Forgot to mention, resolved this in the end. The problem was due to a Server level trigger (one that a another DBA built and has been a bane in by existence ever since). Disabling the trigger meant the project could deploy without a problem.
It also means that the trigger is now switched off, permanently, as our Senior finally gave way that it's causing more problems than it solved, and it's up to me to write a replacement (which is fine, as I'll make sure it works!).
Thanks for your help, as always, Phil!
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 27, 2017 at 6:43 am
Thom A - Thursday, July 27, 2017 4:21 AMForgot to mention, resolved this in the end. The problem was due to a Server level trigger (one that a another DBA built and has been a bane in by existence ever since). Disabling the trigger meant the project could deploy without a problem.It also means that the trigger is now switched off, permanently, as our Senior finally gave way that it's causing more problems than it solved, and it's up to me to write a replacement (which is fine, as I'll make sure it works!).
Thanks for your help, as always, Phil!
No problem. There's no hint whatsoever in that error message that a trigger was the culprit, making this a rather nasty tool to have in your box of hacking tricks!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply