SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


invoke-sqlcmd and stolen single user session


invoke-sqlcmd and stolen single user session

Author
Message
veepee78
veepee78
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 28
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
SQLPirate
SQLPirate
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 945
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
Steve Jones
SSC Guru
SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)

Group: Administrators
Points: 351140 Visits: 20201
Connection pooling?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
veepee78
veepee78
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 28
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
veepee78
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 28
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
Steve Jones
SSC Guru
SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)

Group: Administrators
Points: 351140 Visits: 20201
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
veepee78
veepee78
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 28
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
Steve Jones
SSC Guru
SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)SSC Guru (351K reputation)

Group: Administrators
Points: 351140 Visits: 20201
OK, so you lose the connection where? You're leaving something out.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Lowell
Lowell
SSC Guru
SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)SSC Guru (196K reputation)

Group: General Forum Members
Points: 196571 Visits: 41573
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
veepee78
SSC Rookie
SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)SSC Rookie (48 reputation)

Group: General Forum Members
Points: 48 Visits: 28
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..
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search