invoke-sqlcmd and stolen single user session

Question

invoke-sqlcmd and stolen single user session

veepee78

SSC Veteran

Points: 276
More actions
October 12, 2017 at 1:36 am

#333256

Hi
I run following sql script (part of script file) file with invoke-sqlcmd:
...
if db_id('xxx') is not NULL BEGIN if (select state from sys.databases where name='xxx')=0 BEGIN BEGIN PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF...' ALTER DATABASE [xxx] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT; END BEGIN PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET DISABLE_BROKER...' ALTER DATABASE [xxx] SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE; PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET SINGLE_USER...' ALTER DATABASE [xxx] SET SINGLE_USER WITH ROLLBACK IMMEDIATE END BEGIN PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' Rename DB as OLD...' ALTER DATABASE [xxx] MODIFY NAME = [xxx_OLD] END BEGIN PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' Rename DB filename info as OLD...' ALTER DATABASE [xxx_OLD] MODIFY FILE (Name='xxx', FILENAME='D:\MSSQL\DATA\xxx_OLD.mdf') ALTER DATABASE [xxx_OLD] MODIFY FILE (Name='xxx_log', FILENAME='D:\MSSQL\LOG\xxx_OLD.LDF') ALTER DATABASE [xxx_OLD] MODIFY FILE (Name=xxx_InMemory', FILENAME='D:\MSSQL\data\xxx_Inmemory_OLD.ndf') END BEGIN PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET OFFLINE...' ALTER DATABASE [xxx_OLD] SET OFFLINE END END END
...
Begin .. end mess is there to run the scripts in their own batches. without them, script would fail everytime.
Problem is, quite often I will loose the single user session created in the script to some other user. So between single user and rename db, someone (usually some ssms session) has highjacked the session. How is that even possible? This obviously does not happen with every run, but still way too often..
log:
2017-10-11T16:37:22.5807622Z VERBOSE: 2017-10-11 19:37:16.8179102 SET AUTO_UPDATE_STATISTICS_ASYNC OFF...
2017-10-11T16:37:22.5807622Z VERBOSE: 2017-10-11 19:37:17.0835837 SET DISABLE_BROKER...
2017-10-11T16:37:22.5807622Z VERBOSE: Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
2017-10-11T16:37:22.5807622Z VERBOSE: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
2017-10-11T16:37:22.5807622Z VERBOSE: 2017-10-11 19:37:20.1067547 SET SINGLE_USER...
2017-10-11T16:37:22.5807622Z VERBOSE: 2017-10-11 19:37:20.4348837 Rename DB...
2017-10-11T16:37:22.6744999Z Invoke-Sqlcmd : Database 'MWA_GO_Devel' is already open and can only have one user at a time.
and invoke command:
Invoke-Sqlcmd -ServerInstance $(DBServer) -Database 'master' -Username $(DBUid) -Password $(DBPwd) -inputfile $(Build.SourcesDirectory)\installation\devel\offline.sql -QueryTimeout 600 -Verbose -ErrorAction Stop

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

SQLPirate SSCrazy Points: 2880 More actions · Answer 1

Have you tried putting your connection in the database first? Either with a USE statement or changing your Invoke-Sqlcmd -Database parameter to specify the appropriate database instead of 'master'.

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736280

October 12, 2017 at 9:12 am

#1963398

Connection pooling?

veepee78 SSC Veteran Points: 276 More actions · Answer 3

SQLPirate - Thursday, October 12, 2017 8:24 AM
Have you tried putting your connection in the database first? Either with a USE statement or changing your Invoke-Sqlcmd -Database parameter to specify the appropriate database instead of 'master'.

I did test this with SSMS, I didn't find that I need to be using the single_user database to reserve it for the session. Even if I was using master, single user db was reserved for that session, couldn't connect from another session.

veepee78 SSC Veteran Points: 276 More actions · Answer 4

Steve Jones - SSC Editor - Thursday, October 12, 2017 9:12 AM
Connection pooling?

I'm not expert of this area, is it a case with sqlcmd also? I thought pooling is related to programming. What do you suggest to fix that?

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 5

Not sure, but when you say it appears some other operations gets your connection, that's what I think about. I've seen issues w/ connections not being closed (http://sqlblog.com/blogs/allen_white/archive/2009/08/14/a-couple-of-invoke-sqlcmd-issues.aspx), but that's not what's happening here.

Can you post the PoSh code you use? It seems strange what you're doing here to lose the single connection. I'm guessing that somewhere you're closing connections in the way the code is structured.

veepee78 SSC Veteran Points: 276 More actions · Answer 6

sure,
I run this as vsts build task.

Invoke-Sqlcmd -ServerInstance $(DBServer) -Database 'master' -Username $(DBUid) -Password $(DBPwd) -inputfile $(Build.SourcesDirectory)\installation\devel\Drop_devel_OLD.sql -QueryTimeout 600 -Verbose -ErrorAction Stop
Invoke-Sqlcmd -ServerInstance $(DBServer) -Database 'master' -Username $(DBUid) -Password $(DBPwd) -inputfile $(Build.SourcesDirectory)\installation\devel\aon11DropAndRestore.sql -QueryTimeout 600 -Verbose -ErrorAction Stop
Invoke-Sqlcmd -ServerInstance $(DBServer2) -Database 'master' -Username $(DBUid) -Password $(DBPwd) -inputfile $(Build.SourcesDirectory)\installation\devel\aon21Drop.sql -QueryTimeout 600 -Verbose -ErrorAction Stop
Invoke-Sqlcmd -ServerInstance $(DBServer) -Database 'master' -Username $(DBUid) -Password $(DBPwd) -inputfile $(Build.SourcesDirectory)\installation\devel\Rename_Devel_pre.sql -QueryTimeout 10000 -Verbose -ErrorAction Stop
$(Build.SourcesDirectory)\installation\devel\Rename_Devel_pre.ps1 -uid $(uid) -pwd $(pwd)
Invoke-Sqlcmd -ServerInstance $(DBServer) -Database 'master' -Username $(DBUid) -Password $(DBPwd) -inputfile $(Build.SourcesDirectory)\installation\devel\Devel_online.sql -QueryTimeout 10000 -Verbose -ErrorAction Stop

drop_devel_old.sql:

if db_id('MWA_GO_Devel_OLD') is not NULL 
BEGIN
    BEGIN
        PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF...'
        ALTER DATABASE [MWA_GO_Devel_OLD] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT;
    END
    BEGIN
        PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET SINGLE_USER...'
        ALTER DATABASE [MWA_GO_Devel_OLD] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    END
    BEGIN
        print CONVERT(varchar, SYSDATETIME(), 121) + ' drop Devel OLD...'
        DROP DATABASE [MWA_GO_Devel_OLD]
    END
END

aon11DropAndRestore.sql:

---Drop AON
print 'AON 11'
USE [master]
GO
IF EXISTS (
 SELECT 1
FROM master.sys.availability_groups AS AG
 LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
 ON AG.group_id = agstates.group_id
 INNER JOIN master.sys.availability_replicas AS AR
 ON AG.group_id = AR.group_id
 INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
 ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
)
BEGIN
 PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' DROP AVAILABILITY GROUP...'
 DROP AVAILABILITY GROUP [Devel];
END
GO
IF EXISTS (
 SELECT 1
FROM sys.endpoints
WHERE [name] = 'Hadr_endpoint'
)
BEGIN
 PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' DROP ENDPOINT...'
 DROP ENDPOINT [Hadr_endpoint]
END
GO
IF EXISTS (
 SELECT 1
FROM sys.syslogins
WHERE [name] = 'NT AUTHORITY\NETWORK SERVICE'
)
BEGIN
 PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' DROP LOGIN...'
 DROP LOGIN [NT AUTHORITY\NETWORK SERVICE]
END
GO
IF EXISTS (
 SELECT 1
FROM sys.syslogins
WHERE [name] = 'MAESTRO\MDEVSQL-AON2$'
)
BEGIN
 PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' DROP LOGIN...'
 DROP LOGIN [MAESTRO\MDEVSQL-AON2$]
END
GO

if db_id('MWA_GO_Devel') is not NULL
BEGIN
  if (select state from sys.databases where name='mwa_go_Devel')=0 --kannan tila, 0 = online
  BEGIN
    BEGIN
      PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET AUTO_UPDATE_STATISTICS_ASYNC OFF...'
      ALTER DATABASE [MWA_GO_Devel] SET AUTO_UPDATE_STATISTICS_ASYNC OFF WITH NO_WAIT;
    END
    BEGIN
      PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET DISABLE_BROKER...'
      ALTER DATABASE [MWA_GO_Devel] SET DISABLE_BROKER WITH ROLLBACK IMMEDIATE;
      PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET SINGLE_USER...'
      ALTER DATABASE [MWA_GO_Devel] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    END
    BEGIN
      PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' Rename DB as OLD...'
      ALTER DATABASE [MWA_GO_Devel] MODIFY NAME = [MWA_GO_Devel_OLD]
    END
    BEGIN
      PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' Rename DB filename info as OLD...'
      ALTER DATABASE [MWA_GO_Devel_OLD] MODIFY FILE (Name='MWA_GO', FILENAME='D:\MSSQL\DATA\MWA_GO_Devel_OLD.mdf')
      ALTER DATABASE [MWA_GO_Devel_OLD] MODIFY FILE (Name='MWA_GO_log', FILENAME='D:\MSSQL\LOG\MWA_GO_Devel_OLD.LDF')
      ALTER DATABASE [MWA_GO_Devel_OLD] MODIFY FILE (Name='MWA_GO_InMemory', FILENAME='D:\MSSQL\data\MWA_GO_Inmemory_Devel_OLD.ndf')
    END
    BEGIN
      PRINT CONVERT(varchar, SYSDATETIME(), 121) + ' SET OFFLINE...'
      ALTER DATABASE [MWA_GO_Devel_OLD] SET OFFLINE
    END
  END
END

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 7

OK, so you lose the connection where? You're leaving something out.

Lowell SSC Guru Points: 323518 More actions · Answer 8

i am under the impression that each Invoke-sql command opens a connection, does the work, and closes the connection.
so you need all the commands in a single script, and not individual, multiple invoke-sql statments.

can you change it so that it is a single script instead?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

veepee78 SSC Veteran Points: 276 More actions · Answer 9

Steve Jones - SSC Editor - Friday, October 13, 2017 4:34 PM
OK, so you lose the connection where? You're leaving something out.

I'm quite sure I told that in opening post, though I had changed some names in that:
2017-10-11T16:37:22.5807622Z VERBOSE: 2017-10-11 19:37:20.1067547 SET SINGLE_USER...
2017-10-11T16:37:22.5807622Z VERBOSE: 2017-10-11 19:37:20.4348837 Rename DB...
2017-10-11T16:37:22.6744999Z Invoke-Sqlcmd : Database 'MWA_GO_Devel' is already open and can only have one user at a time.

I loose connection in aon11DropAndRestore.sql, after set single user. Rename db will not go through. This happens about 50% of the time.

Lowell: As you can see, this happens inside one script..

SQLPirate SSCrazy Points: 2880 More actions · Answer 10

veepee78 - Friday, October 13, 2017 5:35 AM
I did test this with SSMS, I didn't find that I need to be using the single_user database to reserve it for the session. Even if I was using master, single user db was reserved for that session, couldn't connect from another session.

Setting single user mode doesn't reserve the session unless you're running the alter statement in a session already connected to the database being altered. Setting single user mode from Master creates a potential race condition where whichever session makes the connection first gets to be that single user connection. This is most likely why it doesn't happen to you every time - sometimes you're the faster session to make the connection, sometimes you're not.

Steve Jones - SSC Editor SSC Guru Points: 736280 More actions · Answer 11

I think that's correct. You're in master at times, and in the db at times, which opens the db to be used by others. Are there admin people that access the db? Can you use restricted_user for this?

veepee78 SSC Veteran Points: 276 More actions · Answer 12

Hi guys and thanks for replyes.

Not trying tostart any argument here, but I just cant find that advise anywhere else. I did try to use the "use mwa_go_devel" command before other commands and all I got was dead lock notification?
In MS's sample here https://docs.microsoft.com/en-us/sql/relational-databases/databases/set-a-database-to-single-user-mode, db is not being used either. I'd like to believe that my sql looks exactly like the sample there. Are you saying MS has false sample there?

result with the use:

2017-10-17T16:38:38.6553979Z VERBOSE: Changed database context to 'MWA_GO_Devel'.
2017-10-17T16:38:38.6553979Z VERBOSE: 2017-10-17 19:38:33.6171251 SET AUTO_UPDATE_STATISTICS_ASYNC OFF...
2017-10-17T16:38:38.6553979Z VERBOSE: 2017-10-17 19:38:33.8202620 SET DISABLE_BROKER...
2017-10-17T16:38:38.6553979Z VERBOSE: Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
2017-10-17T16:38:38.6553979Z VERBOSE: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
2017-10-17T16:38:38.6553979Z VERBOSE: 2017-10-17 19:38:36.8477381 SET SINGLE_USER...
2017-10-17T16:38:38.6553979Z VERBOSE: 2017-10-17 19:38:36.9611969 Rename DB as OLD...
2017-10-17T16:38:38.7335337Z Invoke-Sqlcmd : Transaction (Process ID 92) was deadlocked on lock resources with another process and has been chosen a
2017-10-17T16:38:38.7335337Z s the deadlock victim. Rerun the transaction.

SQLPirate SSCrazy Points: 2880 More actions · Answer 13

veepee78 - Tuesday, October 17, 2017 11:27 PM
Hi guys and thanks for replyes.
Not trying tostart any argument here, but I just cant find that advise anywhere else. I did try to use the "use mwa_go_devel" command before other commands and all I got was dead lock notification?

Except, according to your script, you deadlocked on a different ALTER statement after SINGLE_USER was successfully applied - which makes this a new issue. And that's progress! Also, it's a deadlock, so at least you can go back into extended events and start isolating where the conflict is. Or you can try skipping investigation altogether and upping your DEADLOCK PRIORITY instead.

In MS's sample here https://docs.microsoft.com/en-us/sql/relational-databases/databases/set-a-database-to-single-user-mode, db is not being used either. I'd like to believe that my sql looks exactly like the sample there. Are you saying MS has false sample there?

Not at all. Microsoft has provided a sample with exactly the commands needed to set SINGLE_USER, READ_ONLY, and then MULTI_USER. However, even in your script you're doing more than Microsoft shows in the sample: disabling AUTO_UPDATE_STATISTICS_ASYNC and service broker, both of which are frequent session stealers after SINGLE_USER has been set. Also a quick google search will bring up other examples of people having issues as we discussed earlier with the single user session being taken by other processes. So, while Microsoft provides a solid working sample, what I'm saying is that the sample is not 100% guaranteed that it can be cut-and-pasted and run flawlessly on every system. You have to take your own unique environment into consideration.

Lowell SSC Guru Points: 323518 More actions · Answer 14

veepee78 - Tuesday, October 17, 2017 12:04 AM
Steve Jones - SSC Editor - Friday, October 13, 2017 4:34 PM
OK, so you lose the connection where? You're leaving something out.
I'm quite sure I told that in opening post, though I had changed some names in that:
2017-10-11T16:37:22.5807622Z VERBOSE: 2017-10-11 19:37:20.1067547 SET SINGLE_USER...
2017-10-11T16:37:22.5807622Z VERBOSE: 2017-10-11 19:37:20.4348837 Rename DB...
2017-10-11T16:37:22.6744999Z Invoke-Sqlcmd : Database 'MWA_GO_Devel' is already open and can only have one user at a time.
I loose connection in aon11DropAndRestore.sql, after set single user. Rename db will not go through. This happens about 50% of the time.
Lowell: As you can see, this happens inside one script..

I mean all the code must be contained in a single invoke-sql, not a stack of multiple invoke-sql commands, as each invoke is a new connection

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!