New answers tagged

1

Sounds like DOMAIN\GroupName is a group and DOMAIN\user.name is a member of that group. This means that DOMAIN\user.name has access to SQL Server through that group. You have 2 options: Create another group, let's say DOMAIN\NewGroupName and grant that group INSERT and UPDATE, then make DOMAIN\user.name a member of that group. This is not the fastest ...


0

You almost certainly have a very large number of virtual log files (VLFs) in the problematic log file of the database in question. Shutting down and restarting SQL Server causes the recovery process to run on all databases on the instance. For databases that have a very large number of VLFs this process can be agonizingly slow. You can see the VLFs via ...


0

Yes you Can through Powershell using various Modules: 1- Using DBATools: Open Powershell <# Install-Module dbatools#> #only if not installed <# Load the module#> Import-module dbatools export-dbalogin -SqlInstance ServerName f:\floginsdba.txt 2- Using SQLSERVER Module: <# Install-Module sqlserver#> #only if not installed <# Load ...


0

SQL Server for now does not allow using Distinct with windowed functions. But once you remember how windowed functions work (that is: they're applied to result set of the query), you can work around that: select B, min(count(distinct A)) over (partition by B) / max(count(*)) over() as A_B from MyTable group by B


2

While it will work, it's not necessary to move tables to a new filegroup. If you simply add a file to the databases existing filegroup(s) SQL Server will start using the new file(s). SQL Server uses a "proportional fill algorithm" when a filegroup has multiple files. So if you add a new file (perhaps on a different volume) to a filegroup, SQL Server ...


1

Your idea to move tables to a new filegroup should work. It will take those tables offline for the duration of the move, but then they will be available for queries again. You can potentially limit the amount of downtime by doing them strategically, one at a time. Note if you were on Enterprise Edition, you might be able to utilize the ONLINE index ...


2

First of all, it would be really great if you could provide an actual execution plan for the query you're working on tuning. Otherwise, all of the answers you will get will be somewhat general, and may not perfectly apply to your situation. The question is why, in a production environment with heavy executions, would cardinality estimates affect CPU and ...


3

If you plan to remain on SP2 for now, then you can install any SP2 CUs that you determine are needed. To specifically answer why you should install an SP2 CU at this point, that is somewhat of a murky answer. You don't really need to install a CU unless it has a fix you need, so if you don't have any issues that the CUs fix, then you might consider just ...


0

A couple of suggestions: Don't ask us to write an entire system for you. It's probably ok if you have a particular problem with how some script operates, as long as it's DBMS related. Realize that all SQL Server Agent Jobs are stored in the msdb system database. In order to "save" jobs you could simply take a backup of the msdb database daily. You should ...


4

The team lead is correct in the statement about memory grants given the version of SQL Server you're using (SQL Server 2014, based on the tags here). They are part of the cached execution plan, and the values are re-used across executions as long as the cached plan is in place. Improvements have been made in this area on newer versions of SQL Server: SQL ...


4

This query will show you all T-SQL modules (i.e. stored procedures, functions, etc) on a SQL Server instance that have XACT_ABORT in their code: DECLARE @cmd nvarchar(max) ; SET @cmd = N''; SELECT @cmd = @cmd + CASE WHEN (@cmd = N'') THEN N'' ELSE N'UNION ALL ' END + N'SELECT ServerName = @@SERVERNAME COLLATE SQL_Latin1_General_CP1_CI_AS , db = ''' + d....


6

By default SQL will not encrypt backups. From what I see in your screenshot, the backup you're taking there, will not be encrypted either. (The option is just disabled) A simple way would be to just use T-SQL to create your backup. ( Note copy_only unless you want to be starting a new backup chain) (and replace the folder with your backup dir) BACKUP ...


4

The two query plans are identical, they are trivial plans and they just use the cluster index, but the total reads and duration are different. Following the two different BlitzCache results: Total reads in that result set are most likely totalled across multiple executions. See the total rows column is 76,218 but the min, max and avg are identical to ...


3

From comments: The built-in STRING_SPLIT() function was added in SQL Server 2016, not 2014. And for me, I go the SQLCLR route because I am not using Azure Single DB or AWS RDS and thus have no reason not to. I use the SQLCLR library that I created, SQL#, which contains String_Split and String_SplitInts (optimized for splitting a list of BIGINT / INT / ...


0

declare @bk nvarchar(1000) set @bk = 'SIS5.0~person_id~696969' ;with hizizzle as ( select left(@bk, charindex('~', @bk)-1) as flippity, right(@bk, len(@bk) - charindex('~', @bk)) as floppity) select flippity, left(floppity, charindex('~',floppity)-1) as floppity, right(floppity, len(floppity) - charindex('~', floppity)) as flooo from hizizzle It was ...


0

If you know the number of items and want to access one or more by its index, you can do something like the following: DECLARE @bk nvarchar(1000) SET @bk = 'SIS5.0~person_id~696969' SELECT CAST(N'<x>' + REPLACE(@bk, '~', N'</x><x>') + N'</x>' AS XML).value('/x[1]', 'nvarchar(max)') [string1], CAST(N'<x>' + REPLACE(@bk, '~', N'&...


0

I was watching this excellent talk on PASS - "Finding root cause for unexplained Availability Group failover", by Trayce Jordan. In that he mentions that the lease timeout mechanism is there for extra redundancy to extra make sure that we do not get into a split brain situation.


1

The reason the CNAME is not working is you are using a named instance. When you specify the name of the instance as in SERVER\INSTANCE the SQL Server client looks up the port number of the instance via the SQL Server Browser service. Because you are using a name that is different from the actual SQL Server instance name, that lookup fails. Instead of ...


2

And, there several different developers which developes that CRM web app. They know the user name and password from the web config file they develope. Some of them can connect to my database with that user and execute some DDL and DML commands. And i want to block them by their client hostname. If a developer connects to db from his/her machine ...


0

Once a user opens a connection, he has the rights that were configured as server roles plus inside of the databases. From a technical view point, this could be solved using a trigger but just because it could, doesn't mean it should. (it shouldn't!!) The way a trigger would work is to check before each DDL / DML statement, if the current statement is being ...


0

Question you need to ask yourself is: do I need to work on single rows? And it seems like you don't. And in that case, just put a Clustered Columnstore Index (your option 1). This will happen: Your table will be compressed Aggregation queries will be faster You don't need to think about which columns your users will use (unlike create a non-clustered index)....


1

As Vérace suggested, you can do both: create a surrogate (auto-increment) key to simplify joins and related table structures, and add a unique constraint on the two current PK columns to continue to enforce the proper data integrity. In SQL Server, multiple columns can participate in a unique index / constraint: ALTER TABLE dbo.Orders ADD CONSTRAINT ...


Top 50 recent answers are included