1 - can't say for sure, I'd have to go find a server to dig into myself.
2 - yes, I see this periodically in my environment though we're not on sql 2012 yet on the systems we see this from. You may also want to check this post though State 46 seems to be related to having a specific Database=xxx in the connection string, does that db still exist?
The way my network is set up I suspect it's the network's automatic closing of tcp sessions after being 5 minutes idle that's the issue - neither the db nor the client is closing the session so the connection pool still thinks the connection is open and tries to use it only to find it's not really open anymore. You don't mention how the network between your web servers and db is configured, maybe your case is similar.
Another possibility may be the (old, not sure if ever really resolved, see http://support.microsoft.com/kb/942861) issue about TCP Chimney Offload settings.
3 - My understanding is pooling requires exact string matches, so whitespace and different order of parameters would cause different pools. (If I'm wrong on that, please let me know.)
Problem has been fixed. So I'll try to decribe solution to this problem and maybe for someone it'll be useful and save time. In the beginning we have an error message from wizard:
"Failed to deploy the project. Try again later. (Microsoft SQL Server,
Error: 27118)"
Let's see detailed error, to do this just need to click on "Show technical details button". In the section "Additional data" we can find object wich is responsible for this error. In my case it was procedure prepare_deploy
. This is an internal store procedure of database SSISDB. To catch an error I used statement RAISERROR in deferent places, because this error appears in different places. Thus my problem was there:
IF EXISTS (SELECT [project_id] FROM [internal].[projects]
WHERE [folder_id] = @folder_id AND [name] = @project_name)
BEGIN
RAISERROR(27118, 16, 1) WITH NOWAIT
RETURN 1
END
But to see this error you should have true in the following condition: IF(@project_id IS NULL)
See whole statement which appears earlier in the store procedure:
SET @project_id = (SELECT [project_id] FROM [catalog].[projects]
WHERE [folder_id] = @folder_id AND [name] = @project_name)
IF(@project_id IS NULL)
BEGIN
...
So we have almost identical SELECT statements except schema, but results are different: first statement gives us nothing although second gives some result.
The schema [catalog]
says that we have a deal with view. Take a look on the view:
CREATE VIEW [catalog].[projects]
AS
SELECT ...
FROM [internal].[object_versions] ver INNER JOIN
[internal].[projects] proj ON (ver.[object_id] = proj.[project_id]
AND ver.[object_version_lsn] = proj.[object_version_lsn]) INNER JOIN
[internal].[folders] ON proj.[folder_id] = [internal].[folders].[folder_id]
WHERE (ver.[object_status] = 'C')
AND (ver.[object_type]= 20)
AND (
proj.[project_id] IN (SELECT [id] FROM [internal].[current_user_readable_projects])
OR (IS_MEMBER('ssis_admin') = 1)
OR (IS_SRVROLEMEMBER('sysadmin') = 1)
)
Obviously, to see some result from this view user should have the permission either ssis_admin or sysadmin
. Let's see who executes procedure internal.prepare_deploy
ALTER PROCEDURE [internal].[prepare_deploy]
...
WITH EXECUTE AS 'AllSchemaOwner'
AS
Finally, checking permission for user AllSchemaOwner
in database SSISDB:
Bingo, we don't have appropriate permissions to see data from view [catalog].[projects]
. Just adding user AllSchemaOwner
to role ssis_admin
and problem is being gone.
Best Answer
You could try shutting down all instances of SSMS and deleting the file
SqlStudio.bin
, which works for clearing out things like the MRU list for server connections. Just note that this will also wipe out other preferences, so you may want to export certain settings (like if you've customized fonts and colors, startup options, etc).Also, why is SSMS RTM? You should at the very least apply Service Pack 2, if not use a newer version altogether.