Dan (question author):
It was fixed reinstalling SP1. We reproduced the issue by following these steps:
Install SQL 2016
Update it with SP1
Add R Services using the installation (no SP1)
Dave Mason: On SQL Server 2017, reinstalling the latest CU (after installing ML services) worked for me.
If Launchpad does not start up and the service account is saying locked out in Event viewers follow these steps.
Go to Edit Users and Groups
Click on Users
Go through each users MSSQLSERVER00 T0 MSSQLSERVER20 (one of these will be locked out). unlock it and try to launchpad Should work.
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
While MS releases a patch (not sure if will ever do), this is a quick fix:
Get and save the source code of this procedure
Edit the source and find the variable @message, change its length to 4000. Then find the column message in the temp table #JobHistory and do the same
Start SQL Server in Single mode ...
Echoing other comments from replies and answers I'd advise against opening up a SQL Server directly to the internet. Setting up a VPN on your network that the other person can connect to is the preferred solution.
The steps to prepare SQL Server for a remote connection via VPN or directly are mostly the same so here's how to get your server ready:
This a 2 step process.
1)Tools > Options > Query Execution > SQL Server > Advanced - Uncheck: Display Execution Time
2)Close the query window and open an new one. If you don't do this it will always show. This drove me nuts as I had a long proc and I could not get it to stop until I closed and re-opened.
Thanks to David Browne,
I used procmon and could find the settings are stored in the below regedit section,
Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\SQL Server Management Studio\18.0_IsoShell\General
Change these values to 1-24
Since you are logging to table (@LogToTable='Y') - check to see if perhaps usage patterns changed and maybe you are possibly rebuilding more?
select count([Command]) [count_]
, convert(date, [StartTime]) [day_]
where CommandType in ( 'UPDATE_STATISTICS', 'ALTER_INDEX' )
group by [CommandType]
A simpler method that worked for us was to:
Import the CSV using either the method you mentioned or through an SSMS Task, but to a new table.
Encrypt the column on the new table so the column data types match between the new and existing tables.
Perform an INSERT INTO ... SELECT FROM ... query to insert from the new table to the existing table.
Drop the new ...
You could try along:
Occupancy as (
case when r.RoomId < p.Guests - p.Rooms + 1 then 2 else 1 end as GuestsInRoom,
case when r.RoomId < p.Guests - p.Rooms + 1 then 2 when r.RoomId > p.Guests then 0 else 1 end as GuestsInRoomWithSparesAndMissing
from Package p
Not possible to do that, I'm afraid. There are only two special cases where you won't see all databases:
You have deny view any database, and now you will only see the databases that you own. As you have already noticed.
You login using contained authentication (directly against a user in the database instead of a login). This session is not sandboxed to ...
To add a little bit, that check will find any reason that a query is forced to run single threaded:
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
SET is_forced_serial = 1
FROM #query_plan qp
WHERE qp.SqlHandle = ##BlitzCacheProcs.SqlHandle
AND SPID = @@SPID
Yes, this would cause that problem.
if nothing stands against it (other services, vms etc) use as values the number of cpus and with hyperthreading the number of all virtual cpus.
For more information and recommendation you can see at
Prior to 2017 version of SQL Server, there was Service Packs (SP). And Microsoft released Security Updates for every SPs and RTMs.
For example, in this particular situation builds with numbers 13.0.5081.1, 13.0.5101.9 and 13.0.5102.14 are Security Updates for SQL Server 2016 SP2 GDR.
And if we'll look upper on the patch list, we'll see the same Security ...
This is the essence of what it means to be a system_versioning temporal table. You cannot control the row start and row end values. If you want the rows to be deleted after 5 days, you'll have to have a job to delete them.
Based on the execution plans you provided, it appears that your table doesn't have a clustered index of any sorts, as noticeable by the RID Lookup operation. As a result, for the SELECT query, my guess is the SQL Engine is thinking using any available index to scan in conjunction with a RID Lookup would be the most performant operation for locating the data.