Recent 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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql server 2019 how to reverse engineer a View using VS or Visio
Hi, I am trying to trace back, and document the lineage of a series of Views that have been created in SQL Server 2019 over many years. Many of the views are quite complex and are Views built or several other Views, Tables and functions. I need to unpick all of the dependencies and logic that has been used in creating these views. I tried to use both Visio and Visual Studios 'Reverse Engineer' tools to do this, but this is not supported for SQL Server 2019 or later. When I connect my database to Visio and select the server and have the connection authenticated, the dialogue box greys out the Views checkbox. I have been told that Visio does not support reverse engineering for SQL Server 2019 or newer. The correct ODBC driver is installed, and I am working with a supported version of Visio (Visio Plan 2) and Visio 2505. Is anyone aware of a workaround to this, and how I might use either Visio and VS to reverse engineer my 50+ views and find all their dependencies and calculations, outputting these in a diagrams that I can give to the engineers to easily understand and unpick? Otherwise, this will take me weeks to do. My company is not keen on using any Third party tools that we would need to install on the server, as these could cause a security issue, but any suggestions of anything that would be light touch would be most welcome. Any help would be much appreciated. Thanks!
CSV file size from "Save Results As..." versus exported via SSIS Package / Export Wizard
When exporting query results from the grid using "Save Results As...", I'm consistently creating CSV files that are half the size of CSV files created by exporting via the Import/Export Wizard (and the resulting SSIS Package). Exact same query used for both, same results, same number of records etc. I believe this has to do with the file encoding used by each export process. I know this is not a precise science, but based on how Notepad interprets the resulting files, the encoding of the "Save Results As..." files is "UTF-8 with BOM", while the SSIS Package generates a "UTF-16 LE" file. I've tried a variety of approaches to try to get the Import / Export Wizard to mimic the "Save Results As" encoding without success. Changing the Code Page field on the "Choose a Destination" screen to "65001 (UTF-8)" results in the dreaded "DT_NTEXT not supported with ANSI files" error upon export due to nvarchar(max) data types in the source table. That can be resolved by checking the Unicode box on the "Choose a Destination" page, but checking Unicode also disables the Code Page dropdown. Even if you select "65001 (UTF-8)" in Code Page and then check Unicode, it still produces a UTF-16 encoded file. Two questions at this point: 1) How does the "Save Results As..." function in the query grid avoid the DT_NTEXT error and produce a UTF-8 encoded file? 2) If I tried to edit the package in SSIS Designer, would I have more control over the encoding and be able to mimic the "Save Results As..." file sizes? This would take a bit of effort (installing Visual Studio data tools, learning how to use it, etc) so if that's a dead end I'd rather not pursue it.SQL connector for Azure Key Vault on Linux
Hi everybody, after having successfully configured EKM (to access Azure Key Vault) and encrypted a database with a asymmetric key on a SQL running on a windows VM, i am trying to replicate the same configuration in SQL (2022, CU19) running in a linux container. I cannot find the CRYPTOGRAPHIC PROVIDER (from the documentation it should be built-in), nor create it from file (tried in many ways using the windows dll, i get an error 33027 without additional informations). The guide bellow (see last link) suggest to skip step 3 and 4, and proceed with step 5. But the next steps (create credential, create asymmetric key) requires to refer the cryptographic provider. Can someone supply more information for this step? Thks in adv. Stefano Links: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/setup-steps-for-extensible-key-management-using-the-azure-key-vault?view=sql-server-ver16&tabs=portal) https://techcommunity.microsoft.com/blog/sqlserver/enabling-azure-key-vault-for-sql-server-on-linux/4091248Sql Server complexity
For someone who has worked in PostgreSQL env for a very long time, learning the ropes of Sql server looks like some task. I practice with SSMS everyday, creating DB objects - schemas, tables, views, indexes, triggers, sequences, users, roles. And have noticed that Sql server code is more verbose, complex and not as straightforward as PostgreSQL. The use of 'stored procedures' for modifying column/table name and 'severity level' and 'state number' parameters for triggers was really topsy-turvy. I guess, it will take some time before I get used to the 'proprietary' way of doing things.Pet project on SQL Server 2022 platform
Hello, world! I would like to share my pet project on SQL Server 2022 platform. I have created a DWH solution that includes many MS's best practices and interesting features such us: ETL process with data cleansing and MDM that easy expand Documentation CI/CD Functional ETL test Ready analytical templates Time intelligence New & returning customers Cluster customers based on spending volume Product ABC classification Basket analysis Events in progress https://dev.azure.com/zinykov/NorthwindBI Unfortunately in SQL Server 2025 will be no DQS & MDS...
sp_lock memory leak
I spotted this in the release notes for CU17 in SQL Server 2022: KB3616559 - "Fixes a performance issue that you might encounter only when sp_lock is called frequently from multiple connections, which might cause a memory leak. The memory isn't cleaned up until you restart the SQL Server service. Note: You need to turn on trace flag 15915." We actually have been having an issue that feels like a memory issue since upgrading to SQL 2022, and have engaged MSFT and a few other "expert" SQL support engagements and nobody could figure it out. It's an instance that is acceptable to restart SQL on every weekend, so we've just been doing that, but I'm wondering if this is our issue. I can't find any information from MSFT giving any information on this memory leak they are fixing, and if there is any way to tell by comparing anything before/after that trace flag being enabled to see if behavior changes. Has anyone happened to had this issue, and resolved it with this trace flag by chance? The symptoms we see are essentially everything looking healthy, but every query (including a simple "select 1") just takes 5-10ms more than our normal baseline to execute across the whole instance. And a SQL service restart always fixes it. I think sp_lock is probably called by some of the DB monitoring tools we use, so not something we can easily just stop doing. Thanks in advance for any info anyone may be able to share.SSAS 2022 Connections fail following restart
I'm using an application which has SSAS 2022 OLAP cubes at the back end. We are having an issue that whenever we restart the server or the service, the connections to the SQL Server that is the data source break. I suspect this is a consequence of SSAS CU1 behaviour where the connection string is encrypted, but - because they get encrypted - there's no way to identify what the change is. SSAS is on the same instance as the SQL Server. Before a restart, i've tried adjusting a few connection properties, notably Impersonation set to Service Account Trust Server Certifcate to True Encryption for data to Optional The connection works fine with these settings. However, post reboot I get a connection error whenver I try toprocess any objects: Errors in the back-end database access module. No provider was specified for the data source. We are using MSOLEDB19 so should be fine, but it seems that post reboot the encrypted connection is somehow misconfiguring. Appreciate any guidance on what could be happening here? I can't avoid restarting the server as org policy demands servers are rebooted every fortnight.Getting SQL support from Microsoft
Any got a trick to open a ticket with MS for SQL support (Paid)? We have CSP licenses, but CSP will not take a SQL ticket. I tried online, but it makes you use a personal account, then errors out. When I phone it says you can only open SQL tickets with web. We just have a urgent problem, and I think it's simple for a SQL expert. Should not be this hard to get support. Used to be easy. (BTW I am the MSP - they cut off our old way of opening tickets)
Recent Blogs
- SQL Server Management Studio (SSMS) 22 GA is now available The SSMS team is thrilled to announce the general availability release of SQL Server Management Studio (SSMS) 22! This has been a busy yea...Nov 11, 2025
- SQL Server Management Studio (SSMS) 22 Preview 5 was released this week and includes updates to GitHub Copilot in SSMS.Nov 06, 2025
