Tag: 2019

Can’t remove “synchronizing/suspect” database after removed from availability group Sql Server 2019 Enterprise

I’ve removed a db from a synchronous availability group.The db shows "synchronizing/suspect" as it should. I’m trying to remove this database cannot. I have tried:

DROP DATABASE [db1];

but get "Cannot drop database "db1" because it is currently in use.". even though their are no sessions using this database.

ALTER DATABASE [db1] SET OFFLINE;

but get "ALTER DATABASE failed because a lock could not be placed on database ‘db1’. Try again later."

ALTER DATABASE [db1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

but get "ALTER DATABASE failed because a lock could not be placed on database ‘db1’. Try again later."

ALTER DATABASE [db1] SET EMERGENCY;

ALTER DATABASE failed because a lock could not be placed on database ‘db1’. Try again later.

This is a 24/7 production db and restarting is not a good option.

Any advice?

SQL Server Transactional Replication between SQL server 2019 Standard and SQL server 2016 Web Edition

We have SQL Server 2016 Web Edition on production currently. Since this edition supports replication as Subscriber only, I set up a new server with SQL Server 2019 Standard edition. I want to configure SQL Server 2019 as Publisher and SQL Server 2016 as subscriber. To initialize data for Publisher database on SQL server 2019, I created a backup on SQL Server 2016 and restored it on SQL Server 2019. Since our database is very large, I tried to initialize replication from a backup. So I did reverse backup-restore again by creating a backup of Publisher on SQL Server 2019 and restoring it on Subscriber on SQL Server 2016. But this did not work because [SQL Server 2019 backups cannot be restored by any earlier version of SQL Server] (https://docs.microsoft.com/en-us/sql/relational-databases/databases/copy-databases-with-backup-and-restore?view=sql-server-ver15). Could you please tell me what is the best method for initializing the replication in this case? Thank you very much for reading my question!

CmdExec on MS-SQL 2019 Linux

I’m trying to add a CmdExec as task in a job, on MS-SQL 2019 on Centos8. However, the task always fails with "running the requested program is not allowed". I have tried specifying both /bin/true and true as the program, but these also fail.

Is there a list of authorised programs for CmdExec, and is this is editable? I found that MS-SQL Linux 2017 doesn’t support CmdExec, but I think this has been fixed in 2019

Difference in light between Unity 5.5.2f1 and Unity 2019

The lighting is different between Unity 5.5.2f1 and Unity 2019. Please see the attached image.enter image description here

I dislike the way the lighting is done in the 2019 version. Both verions are installed out of the box, without any additions.

What do I need to change / set up inside Unity 2019, in order to have the exact lighting as in 5.5.2f1 version?

Debugging T-Sql code using Visual Studio 2019. Error message: frame not in module

DBAs,
Has anyone had success using Visual Studio 2019 for debugging T-SQL code, the way SSMS allows Step-into & Step-over?

When I hit debug (Alt-F5), gives me this error, after making some progress. Looks like once it hits the section of the code that calls another procedure, it gives this error.

Frame not in module.   The current stack frame was not found in a loaded module. Source cannot be shown for this location.

Example:

1  ..    2  ..   3  SELECT 1 FROM sys.objects   4  SELECT dbo.SomeFunction(1)   5  UPDATE TABLE SET X = 1 ....   6

It runs well, until line 3, once it hits line 4, where it is calling a different function, it throws the aboe error. In SSMS, that line just opens a new window with code for the function and show debugging progress for each F11 step-into — but not in Visual Studio 2019 (Version 16.7.1)

Thanks

SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server’s databases. I have a script to do this, and it has been working quite well when the local server is 2008 R2 or 2017. But I just installed 2019 and the same script puts the same value in every row of the table, rather than a different value for each row. It is as though the function is executed only once, then that value is used in the UPDATE statement. Perhaps it is some kind of optimization 2019 is doing? Here is a snippet of the script.

UPDATE Guest SET GuestFirstName=Utility.dbo.RANDWORD() WHERE GuestFirstName IS NOT NULL;

RANDWORD grabs a random word from a table containing approximately 100,000 English words.