Recent Discussions
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.SSMS "Intellisense" behaviour is driving me demented
This behaviour is doing my head in and I'm hoping that there is a simple way to change it?Just to give you an example, I am starting to write a little query to check the status of FullText Indexes on database objects. This is for illustration purposes only so don't tell me what "better" alternatives there are for doing this, I just want to illustrate the behaviour that's bugging me.So the query I would like to run is:SELECT [SO].[name], [FI].[is_enabled] FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idThe behaviour that annoys me can be illustrated as follows. In SSMS, type this:SELECT * FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idThen go back to the *, remove it and start typing [SO] in order to get intellisense to show you column names you can choose from. So you have typed [SO] and your cursor is right behind the closing bracket:SELECT [SO]<cursor here> FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_id Type the dot The statement changes to:SELECT [SOUNDEX]. FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idI didn't want "soundex". I wanted intellisense to show me a list of column names in sys.objects, aliased to "[SO]" by me. It does that once I hit Ctrl+Z which removes the auto-inserted [SOUNDEX] and then when I hit the dot again it shows me the list of columns. So I pick [name] and start adding the next column by typing , [FI]. And here it goes again:SELECT [SO].[name], [FILE_ID]. FROM sys.objects [SO] inner join sys.fulltext_indexes [FI] on [FI].object_id = [SO].object_idI didn't want [FILE_ID]. I wanted [FI]. and a popup showing the the column names in sys.fulltext_indexes I can choose from.Sure, this is one heck of a "first world problem" but as a touch typist this is driving me around the bend. If there's a way to change this behaviour (without losing Intellisense altogether), please tell me how.
Changing dates on a sql select query to previous year
I want to be able look at the current years sales by month as in the current sql script. Then I want to look at the same information for the previous year as well. SELECT CASE EXTRACT(MONTH FROM t.transdate) WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec' END AS "Month", SUM(sm.quantity * sm.exsell) AS "Monthly Sales", SUM(sm.quantity * sm.cost) AS "Sales Cost", SUM(sm.quantity * sm.exsell) - SUM(sm.quantity * sm.cost) AS "Gross Profit" FROM stockmovement sm JOIN transactions t ON t.transref = sm.transref JOIN item i ON i.itemref = sm.itemref JOIN maker m ON m.makerref = i.makerref WHERE t.transource = 'D' AND sm.itemref <> '100037' AND sm.itemref <> '176176' AND t.transdate < CAST(EXTRACT(YEAR FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO CURRENT_DATE)) || '-' || '01' AS DATE) AND t.transdate >= CAST(EXTRACT(YEAR FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || EXTRACT(MONTH FROM DATEADD(-1 YEAR TO CURRENT_DATE)) || '-' || '01' AS DATE) GROUP BY EXTRACT(MONTH FROM t.transdate) ORDER BY EXTRACT(MONTH FROM t.transdate)
Insert & Update both implemented wrong semantically
Insert means to introduce something, adding/entering something new Update means to change something, change and existing value. Semantically and logically speaking, Insert should only be used to enter data into an empty or partially empty record/table. Update should only be used to change existing values within a record/table. Using a 5 column table as an example where column 1 is an email address Example1 INSERT INTO table (Column1,Column2,Column3) VALUES (value1,value2,value3) Or Example2 INSERT INTO table VALUES (value1,value2,value3,value4,value5) With example 1, 2 columns are left empty Semantically, insert should be used to populate columns for the first time INSERT INTO table (column4, column5) VALUES (value4,value5) where column1 = "EmailAddress" Then for example, UPDATE table SET COLUMN2 = "newValue" WHERE COLUMN1 = "EmailAddress" Updating (setting) an empty column should cause an error, cannot update an empty column SQL, a great idea, pity its implementation is tainted by bad design. SQL works, but its design/implementation of Insert & Update is semantically wrong. IMO As the saying goes, if it isn't broken, don't fix it. Who cares about semantics or logic.
SERVERPROPERTY('IsClustered') does not return the correct value in a cluster configuration
Hi all! I'm testing some t-sql SQL properties for a data collection project and I got a strange case with the query below: SELECT SERVERPROPERTY(''MachineName') AS [MachineName], SERVERPROPERTY('ServerName') AS [ServerName], SERVERPROPERTY('InstanceName') AS [Instance], SERVERPROPERTY('IsClustered') AS [IsClustered], SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerNamePhysicalNetBIOS], SERVERPROPERTY('Edition') AS [Edition] I have two test instances of SQL Server 2019. They both belong to a cluster without shared storage, as the availability groups allow it: SQLAGCLU I don't understand why the query is returning "incorrect" properties. I expected them to have the value 1 for the "IsClustered" column. From the query that I use, I have no idea witch SQL instance are in a cluster... Some one can give a feedback about it? Thanks ALENHow to communicate with Linked Server encryption
I'm using SQL SERVER's Linked Server to link between databases. But there is a security problem with this method. So is there a way to encrypt the communication section when accessing other databases through Linked Server and importing data? Please let me know if you have any good method or experience applying it Thank you.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.Compatibility change from 110 to 160 doubles the CPU
We are running SQL Server 2022, when we change our database from compatibility level 110 to 160, the CPU utilization eventually doubles. After switching to compatibility 160 I do run update statistics FULL on the whole database but I am unable to figure out which queries are the ones that jump to using more CPUs than on compatibility level of 110. Our frequently used queries are dynamic SQL so I am having difficulty identifying their execution stats before and after. I need some help. Thank you.
sql-machine-learning-services setup: RegisterRext.exe aborts with “MPI installation was not found”
In a SQL Server 2022 environment I have a problem in setting up the sql-machine-learning-services. When Configure Python runtime with SQL Server, executing the RegisterRext.exe command results in an error message “MPI installation was not found” I have been following the steps at this site https://learn.microsoft.com/en-us/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022?view=sql-server-ver16 In The Python section, an error occures when executing .\RegisterRext.exe /configure /pythonhome:"C:\Program Files\Python312" /instance:"MSSQLSERVER" Error: …. Granting NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 ... Granted NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 Error: Exception occurred in Settings Install Failed to complete the operation successfully. Usage …. The logfile RegisterRExt.log shows the detailed error: Granted NT Service\MSSQLLaunchpad ReadAndExecute permission to C:\Program Files\Python312 -- DateTime=2024-01-07T08:41:21.2670000Z --Error: 0 : System.Exception: Exception occurred in Settings Install ---> System.IO.DirectoryNotFoundException: MPI installation was not found. -- bei Microsoft.SqlServer.Server.Settings.GetFirstValidPath(IEnumerable`1 paths, String exceptionString) -- bei Microsoft.SqlServer.Server.Settings..ctor(ParsedArguments parsedArgs, String instancePath, Boolean isXcopyInstall, Boolean isAppContainerEnabled, String sqlExtDataPath) -- bei Microsoft.SqlServer.Server.Settings.Install(ParsedArguments parsedArgs, String binnPath, Boolean isXcopyInstall, FileSecurity fileSecurity) -- --- Ende der internen Ausnahmestapelüberwachung --- -- bei Microsoft.SqlServer.Server.Settings.Install(ParsedArguments parsedArgs, String binnPath, Boolean isXcopyInstall, FileSecurity fileSecurity) -- bei Microsoft.SqlServer.Server.Program.Main(String[] args) -- DateTime=2024-01-07T08:41:21.2826203Z --Information: 0 : Failed to complete the operation successfully. Environment is Microsoft SQL Server Express (64-bit) ( SQL Server 2022 (RTM-GDR) (KB5032968) - 16.0.1110.1 (X64)) ,Windows 10 , Version 22H2 Python 3.12.1 After installing and setup MPI the error remained the same. I need help regarding this problem.
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)
Password Expiration Behavior
If I set check_expiration = on for a SQL authenticated login, will the user get prompted to change their password either: after it expires just before it expires not at all I can't force a login's password to EXPIRED or else I'd test it myself and I've not found any documentation on what happens once IsExpired flips to 1.
Cannot create an external table in SQL Server
Hi, I have an on-premise SQL Server 16.01.1135.2. I am trying to create an external file format: CREATE EXTERNAL FILE FORMAT skipHeader_CSV WITH (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2, USE_TYPE_DEFAULT = True) ); but it gives me the following error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'EXTERNAL'. Completion time: 2025-03-24T23:13:46.7487583+01:00 Table creation clause is copied from the documentation. What am I doing wrong?SQL Server does not reduce the size of MDF and LDF
I am a SQL Server 2017 user, I deleted a table that had too many records, but after the delete process, the size of the MDF and LDF files did not decrease, but increased. I have shrunk the file but the file size remains the same. What should I do to change the file size?How to create a view with unique record based on date
I have an EMPLOYEE table that looks like this: EmployeeID,Name,PositionID,HireDate,PositionStatus E001,Chelsey,P123,2013-11-23,Terminated E001,Chelsey,P234,2019-03-13,Active E002,Mike,P345,2014-03-23,Terminated E002,Mike,P345,2021-10-13,Terminated E003,James,P543,2015-01-12,Active E004,Samantha,P895,2018-12-13,Terminated E004,Samantha,P430,2020-05-21,Terminated E004,Samantha,P945,2022-06-16,Active E005,Kayla,P459,2023-03-13,Terminated As you can see, the EmployeeID can have duplicate. The reason for this is, there's a secondary ID called PositionID, which essentially describes the employee's role. For example, whenever an employee gets promoted, or moves to a different department, a new record will be created for them with the same EmployeeID but a different PositionID. When that happens, the Position Status of the older role gets set to Terminated, and the new one gets set to Active. The HireDate represents the date teh employee started working in that role. I would like to create a VIEW to filter this to have only the unique employees, based on the HireDate. If there are multiple records for the same employee, I want the view to show only the record with the most recent HireDate. The resulting table should look like this: What's the best way to go about this?Restricting Access to Underlying Tables in Different Databases While Allowing View Access
Hi Experts, I am looking to get assistance with the concept of ownership chaining in the SQL Server Database 2016. We have a data warehouse database that is maintained by an ETL Tool. We have segregated objects (e.g. Views, store procedure) from the data warehouse database to the another database on the same server. The non-data warehouse database which references to the Data warehouse database has very complex model of permissions and roles. I am unable to grant access to the objects mainly views in the non data warehouse database without granting select/View definition permissions on the data warehouse database. I'd like to know if it is possible to grant access to the views in the non-data warehouse database without granting direct access on the actual objects like Tables in the data warehouse database. Using windows Microsoft SQL Server 2019 (RTM-CU22-GDR)SQL suppres create info items in MS Event Viewer
Hello, in MS Event Viewer are stored thousand of information events. Stored are tents of events in every seconds (see example screenshot). Why is happened this so often, please? How to suppress (lot of information) such items in MS Event Viewer from SQL, please? Thank you in advance for answers.SQL Server 2022 SSIS package deployment issue. A required privilege is not held by the client
My case is I have Windows Server 2019 and SQL Server 2022 installed on it. SSIS was included during installation. I deploy packages in VS 2022 from different PC on Windows 10 as admin account. Error A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal": System.ComponentModel.Win32Exception: A required privilege is not held by the client System.ComponentModel.Win32Exception: at Microsoft.SqlServer.IntegrationServices.Server.ISServerProcess.StartProcess(Boolean bSuspendThread) at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectInternal(SqlInt64 deployId, SqlInt64 versionId, SqlInt64 projectId, SqlString projectName) . (Microsoft SQL Server, Error: 6522) Solution: Go to Server launch SQL Server Configurator Manager Find following SQL Server Services SQL Server Integration Services (16.0 in my case) SQL Server SQL Server Agent Go to properties of each Default log on accounts for these services have limited access to server system. We need to change Log on As from default account to built-in account Network Service Restart the services and you should be able now to deploy projects without any issues. Conclusion Common solution on the web is to add desired domain account to list of security policies. This solution didn’t work for me since security policy user assignments was inactive to add additional accounts and contained only 2 bulit-in accounts which in this case we use as log on account. I want to mention again that my SQL Server 2022 is installed on Windows Server 2019. Run-> Secpol.msc -> User Rights Assignment: Only way to get rid of this error for SSIS in my case was to use bulit-in accounts for SQL Services. Useful references that helped me to come up with this solution: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Learn
Recent Blogs
- This blog post dives deep into the deployment-related aspects, aiming to clarify these choices. To provide a clear visual guide based on my experience working with customers, I've put together a flow...Apr 25, 2025
- Are you still using System.Data.SqlClient in your .NET applications? If so, it’s time to plan your move to Microsoft.Data.SqlClient — the official, modern SQL Server driver designed for today’s platf...Apr 21, 2025
