Forum Widgets
Latest Discussions
Query on sys.dm_db_index_usage_stats slow
Hi, we discovered that queries on sys.dm_db_index_usage_stats are getting very slowly when the sql server is running for a longer time without restart. The execution time is up to 30 seconds for the following query: SELECT object_name(object_id) objectName, last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id=db_id() We get the following query plan: The Actual Rows in LOGINDEXSTATS are about 2 million. We found 2 similiar cases by searching the internet: https://stackoverflow.com/questions/52165370/query-against-sys-tables-sys-dm-db-index-usage-stats-slow https://www.linkedin.com/pulse/sql-server-2014-object-dependencies-dmvdmf-slow-andrea-bruschetta We tested the workaround (UPDATE STATISTICS sys.*** WITH FULLSCAN;) without success. How can we increase performance without restarting the database? Regards Dominik
"ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON" does not finish for several hours
I have tried enabling snapshot isolation on several SQL Server instances (2017 and 2022 developer editions) in single user mode with the same huge DB (4 TB) but the command does not finish even after 6 hours... ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON Session is waiting on "ENABLE_VERSIONING" and I don't see any noticeable I/O activity on the Windows disk itself.
How Can a Company Receive Support from Microsoft for SQL Server Enterprise with Software Assurance?
Hello, I’m currently managing SQL Server under the following licensing agreement: SQL Server Enterprise Core Single Language License & Software Assurance Open Value | 2 Licenses | No Level | 1 Year | Acquired Year 1 | AP I’ve been informed that Software Assurance (SA) no longer includes technical support for SQL Server. Could you please confirm if this is correct? If our organization needs technical support from Microsoft for SQL Server, I would like to clarify the following: Is it mandatory to have a Unified Support contract or to purchase incidents via the Microsoft Services Hub in order to receive support? Regarding Services Hub, I’ve heard that support incidents must be purchased using a personal Microsoft account (MSA). If this is true, can this method be used to receive support for corporate environments? Thank you in advance.
Fail to OPENROWSET insert geometry data from parquet
Hi! I'm trying to insert geometry data (WKB) from a parquet file to a data base table. I manage to get it working for small geometries but it's failing for lager geometriers. Here is the code: INSERT db.s.t SELECT geometry::STGeomFromWKB(a.geom,1) FROM OPENROWSET ( BULK 'path_to.parquet', FORMAT = 'PARQUET', DATA_SOURCE = 's3_sorce' ) AS a The failure: String or binary data would be truncated while reading column of type 'VARBINARY(8000)'. Check ANSI_WARNINGS option.... Seems as if the geometry type is not recognized. Is there a way around this?SQL Server 2017 – CLR was loaded in an unsupported manner (All SSIS jobs failed)
Hi, We are facing a critical issue in our SQL Server 2017 instance. When trying to use a built-in CLR function or running SSIS-related jobs, we are getting the below error: The Common Language Runtime (CLR) was loaded in an unsupported manner. This can occur if an extended stored procedure or OLE Automation object running in SQL Server calls into managed code before the CLR integration runtime host loads the CLR. You need to restart SQL Server to use CLR integration features. Steps tried so far: Restarted SQL Server service Restarted the entire Windows Server Verified .NET Framework version (4.7.03062 installed) Confirmed CLR integration is enabled (sp_configure 'clr enabled', 1) All SSIS jobs are failing due to this issue. Any suggestions, please?Problem with differential backups, after a problem with a full backup
I have a database in Microsoft SQL server 2008 R2, in which I have configured the maintenance plan for backups (a full back on Tuesdays, Thursdays and Saturdays at 00, three transactional logs at 4, 5 and 6 am, and then at 7 am a differential, and so on, finishing with a differential backup at 11 PM). At the beginning of July, there was a problem with a full backup that was not done, because of lack of space (this problem was July 5th), then the differentials began to increase in size, up to 10 GB, when the full was done, the differentials decreased in size to 1 GB (before this, the differentials had a maximum size of 800 MB), the problem is that every day it increases in size, until today, when each differential weighs 6 GB, the full backup's size is about 96 GB because the database is too old. and I've seen some strange behavior, when the plan gets to the differentials, at 7:46 (for example), the file is finished creating, and it weighs 500mb, but then, 1 minute later, it's like the differential is overwritten, and there it increases in size up to 3GB. What could be causing the error? Maybe it's due to something with the TRUNCATE of the full backup? How can I solve this? I've already tried doing a new manual full backup (on a day that it's not done, at 00 am), and at the moment that it finished being done, a new manual differential, but that didn't solve it. The queries that I run: FULL BACKUP: BACKUP DATABASE [xxx] TO DISK = N'\xxx\SQLServerDatabases\Backups\full_reset.bak' WITH INIT, NAME = N'Full_Reset', SKIP, STATS = 10; DIFFERENTIAL BACKUP: BACKUP DATABASE [xxx] TO DISK = N'\xxx\SQLServerDatabases\Backups\diff_reset.dif' WITH DIFFERENTIAL, INIT, NAME = N'Diff_Reset', SKIP, STATS = 10;execution SP via linked server from SQL Job
Hello colleagues, we have next environment: 1. SQL Server standard edition 2022 16.0.4185.3. 2. SQL Server Job service running under domain managed service account. Category type is Data collector. 3. Job owned by [sa] 4. Job execute stored procedure using next command exec RemoteServer.[DB].[dbo].Load_RTL 0 5. Linked server RemoteServer is MS OLEDB Provider and configured to use (Be made using this security context). Login is a SQL Login with provided password. When job is started, it trying to execute remote SP using SQL Server Agent account, but not linked account which I indicated above in line №5. Why and how to change this so remote SP executed using linked server account? Thank you.link server error after switching from SQL authentication to Active directory service account
Hi All, I am trying to switch from using a SQL account to use active directory service acccount selecting impersonate option on a link server. When i try do this i get below errror. any ideas how to fix? ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ TITLE: Microsoft SQL Server Management Studio ------------------------------ The linked server has been updated but failed a connection test. Do you want to edit the linked server properties? ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416) For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-7416-database-engine-error ------------------------------ BUTTONS: &Yes &No ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
