Hot answers tagged

11

I believe you have just discovered why the recommended upgrade process is to to upgrade your database, enable the Query Store, and test before increasing the database compatibility level. Change the Database Compatibility Level and use the Query Store If you have a lot of plan regressions you can keep using the older cardinality estimator at the higher ...


4

I was able to successfully start the Integration Services 12.0 service after running a Repair using the SQL Server 2014 Installation Media. I then installed 2014 SP3 and 2014 SP3 CU4 to get it back to where it was before the upgrade, and all is well now.


4

This looks to be a problem with UDF inlining. As @ScottHodgin suggested, the first step is to install the latest CU to see if the problem persists. If that doesn't resolve the problem, your options are: Turn off TSQL_SCALAR_UDF_INLINING for the database: USE YourDatabase; ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF; Add INLINE ...


4

We had a similar issue upgrading from Sql Server 2012. Our issues were due to Cardinality Estimator changes introduced in SQL Server 2014 Try changing the Legacy Cardinality setting to ON in a test environment and compare the performance of workloads https://blog.sqlauthority.com/2019/02/09/sql-server-enabling-older-legacy-cardinality-estimation/


3

This will be somewhat an opinion answer, so likely to be downvoted. First up, in-place upgrades are fraught with issues. So many things can go wrong along the way and you have no fallback position except a restore. This means you must first ensure that at every step you have a restore option, for the databases and the OS. However, I recognize that not ...


3

We had a similar issue upgrading from SQL Server 2008 R2 to SQL Server 2019(compatibility level 150). Some of our nightly update jobs suddenly took 6-7 times as long to run(from 4 min to 39 min, and from 1 hour to 6 hours). Setting Legacy Cardinality setting to ON brought us back to our usual update speed. This is what we did(source: https://blog....


2

I cannot find anything specific to SQL Server 2019, but I was able to find unofficial references about previous versions of SQL Server not supporting CTP to RTM upgrades. Additionally, the reference Bahtiyar posted in the comments also shows that upgrades from previous versions of 2019 are not included. Once the RTM is available, the documentation for the ...


2

As pointed out in the answer to my other, more specific question, the reason is Just-in-time compilation. SET jit = false; solves all performance problems in my case.


2

The real error is: ORA-12801: error signaled in parallel query server P000 ORA-01115: IO error reading block from file (block # ) ORA-01115: IO error reading block from file 2 (block # 215616) ORA-27072: File I/O error Additional information: 4 Additional information: 215616 Additional information: 212992 The "solutions" you tried make no sense when you ...


2

From the spfile create a pfile, which can be edited directly: sqlplus / as sysdba create pfile='/tmp/pfile.ora' from spfile; exit Edit /tmp/pfile.ora, remove _first_spare_parameter. Then create the spfile again: sqlplus / as sysdba create spfile from pfile='/tmp/pfile.ora'; Now you can try starting your instance. Above should work in a standard single ...


2

SQL Server 2016 CU14 and later will upgrade R Services (in database) to 3.5.2 or later. You can download CU14 here: catalog.update.microsoft.com/.... You will then need to Change the default R language runtime version. This can be done using the C:\Program Files\Microsoft SQL Server\MSSQL13.xx\R_SERVICES.x.x\library\RevoScaleR\rxLibs\x64\RegisterRext.exe ...


1

Because of licensing concerns I cannot have SQL Server 2016 running but I still need to upgrade the SSRS to 2016. They require the same license. So if you can't run SQL Server 2016, you can't run SSRS 2016.


1

You need to find the columns with that type outside of information_schema. Information_schema is not user tables, they are system tables. SELECT * FROM pg_attribute join pg_class on attrelid=oid join pg_namespace on relnamespace=pg_namespace.oid WHERE atttypid::regtype::text like '%sql_identifier' and nspname!='information_schema'


1

I followed the comment from @Colin 't Hart and this solved the problem. With pg_upgradecluster -v 12 11 main /srv/postgres/data/12 the upgrade was done. I changed the port to 5432 and restarted it. Now my applications are using the version 12 database.


1

Which index to use? While your WHERE conditions are not very selective, the current query plan makes a lot of sense with ORDER BY activity_count DESC LIMIT 101. See: How to search a table with 80 million records faster? However, your predicates strike me as pretty selective: WHERE (search_index) @@ (to_tsquery('pg_catalog.english', '''1234567890'':*') AND ...


1

Query 1: You have to look this up in the release notes. Normally you can do that, but you always need to check the release notes regardless. There is a possibility that it is not possible. Please refer to this link, which will guide you to all the SPs and latestes CUs available, including the release notes. https://sqlserverbuilds.blogspot.com Query 2: ...


1

ibdata1 never shrinks. .ibd files never shrink. When a table is in ibdata1 and is ALTERed, space in that file is temporarily consumed, thereby possible stretching the file. With .ibd, an ALTER will probably create a new copy, then toss the old one, thereby temporarily using diskspace, then releasing it. There are too many variables to make any predictions. ...


1

AFAIK, the several ways to backup my cluster is : pg_dumpall, pg_basebackupp or tar. Also, filesystem instantaneous snapshots if your filesystem/OS has this feature. For small/medium databases it's probably OK to use any of these methods. Choose the method which you feel most comfortable with. With tar/cp backups remember to shutdown cluster before doing ...


1

If the oids of the tables are referenced in the code, you cannot simply get rid of them. Probably the old code relies on oid as an auto-generated identifier. Are there any primary keys or other indexes on these columns? A way to preserve the oids over an upgrade would be: Before upgrade, on the old database: ALTER TABLE has_oids ADD newoid bigint NOT NULL;...


1

Here is a query what will generate all the necessary update statements. It temporarily disables foreign key checks so that the update statements succeed. It updates the default character set for each database that needs it. It updates the default character set of each table: If it doesn't have the right default character set. If it contains text columns ...


1

What you're reading is referring to changing the failover mode of an availability group replica: To change the failover mode of an availability replica In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree. Expand the Always On High Availability node and the Availability Groups node. ...


1

There are 2 types of database directories in Db2 for LUW: system and local. When you create a database on some path /some_path, the following directory structure is created for this local database directory (if it doesn't exist): /some_path/${DB2INSTANCE}/NODE0000/sqldbdir and the database is cataloged (or registered) in the system database directory ...


1

Going from 2008 to 2016 is jumping too far. I suggest you to go move to 2012 first and then to 2016. First of all download Microsoft Migration Assistant and try to spot incompatibilities. Then download Microsoft Assessment and Planning Toolkit and scan for all sort of incompatibilities following a guide. Spot the incompatible queries using the Query Tuning ...


Only top voted, non community-wiki answers of a minimum length are eligible