New answers tagged

1

First and foremost: Why are you storing dates as text? Second, as I pointed out in comments, there is an accepted answer of Aaron Bertrand that states: 'Sorry, not possible', you cannot change the default conversion behaviour. And that is why I flagged this question as duplicated. And third, having a look at Microsoft SQL Server 2008 Internals, at ...


1

You probably have a full backup before the ones that fails, like a snapshot backup or a VDI backup). Check the backup history tables in msdb and the errorlog file for clues.


0

I had same issue once, You may need to recreate the linked server with this set to false (or just change it in the linked server properties->server options): EXEC master.dbo.sp_serveroption @server=N'servername', @optname=N'remote proc transaction promotion', @optvalue=N'false' Additionally, try using OPENQUERY to run this against the link syntax: ...


4

As a general rule, this migration should be simple. AGs use the database mirroring framework and endpoints for transmitting log blocks, so the transition is quite straightforward. One thing to note is you currently run your mirrors async but you've stated you want to run your AG sync to provide HA. This shouldn't be a problem, but you need to check your ...


0

That's all in msdb. Note that the new server must be at exactly the same version to restore a system database. System databases can be restored only from backups that are created on the version of SQL Server that the server instance is currently running. For example, to restore a system database on a server instance that is running on SQL Server ...


1

There are no pros as such that motivates you to use this mode of failover. its completely dependent on the requirement of the business and requirement of availability or activation of secondary server. so may be you want to initiate a failover yourself just to satisfy your believe that failover works best this way or you can make yourself confident that ...


2

Instance-level certificates are stored in the master database You will need to set your context to master database, and then check certificates: use [master] select * from sys.certificates Or simply use three-part name in a single command: select * from [master].sys.certificates


1

As mentioned by others use spwhoisactive or spblitzwho to find the behavior. However I am suspecting you do not have sufficient index on that table. A better index will just make the lead blockers query run fast and avoid blocking the query scanning same table. Also check for open transactions with sleeping status . Sometimes they create those blocking ...


0

You want to run something like 'sp_whoisactive', 'sp_blitzwho', etc while waiting for your select. With those store procedure, you will get the "blocking" session and you will be able to see what it is running.


1

That design is terrible. You should have separate tables and real foreign keys. For a quick fix, use the indexed views. If there are perf problems, fix the design.


0

As an alternative to @Akinas solution (which probably is better), we can use the fact that null is treated lower than any real value: SELECT ... FROM ( SELECT st.* , FIRST_VALUE(finish) OVER (PARTITION BY employee_no ORDER BY finish) as fst FROM t ) as u WHERE fst IS NOT NULL


0

Tools > Options > Query Execution > SQL Server > Advanced. Uncheck the Show Completion Time checkbox. Watch this video: How to disable “Completion time:…” in SSMS SQL Server Messages window


1

SELECT employee_no FROM source_table GROUP BY employee_no HAVING COUNT(*) = COUNT(finish) Is there a way to include the whole table in that sequence? – Chris select serverproperty('ProductVersion') as [version], serverproperty('Edition') as [edition] and got Version 13.0.5102.14 of edition Devleoper Edition (64-bit) – Chris This is (Microsoft) ...


2

If you have synchronous replicas you typically have one with automatic failover. The most common reason not to is when the primary is hosted on a Failover Cluster Instance, and it's a rule that only the FCI can failover automatically in that configuration. But you could also have a scenario where you would prefer an outage over a failover, for instance if ...


2

Don't backup to a compressed folder. There are supportability problems for SQL Server with compressed folders. See here. Take a compressed backup instead.


1

I can't get that to cost nearly that much. On my (very fast) desktop it's under 1ms of CPU time, and on Azure SQL Database it's only 4ms. Perhaps your SQL Server is on a VM and is not getting full access to the host's CPUs. EG --use tempdb go drop table if exists tblTask SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblTask]...


0

Without further information about the table, columns, or other actions already taken, its hard to know what exactly could be done. Including the execution plan is always really useful in these cases. I would start by making sure there is an index on DeskModelID in order for a seek to occur. CREATE INDEX IX_DeskModelID ON dbo.tblTask(DeskModelID) INCLUDE (...


0

You can also look into alternative like Minion Re-index at https://www.minionware.net/products-1/reindex/ where it has some different features like DB or Table ordering https://minionware.freshdesk.com/support/solutions/articles/43000040362-minion-reindex-1-2-how-to-reindex-tables-in-a-specific-order http://www.midnightsql.com/wp-content/uploads/2015/01/...


1

The easiest solution is to use a unique clustered index which does not have to be unique in the system (unlike a primary key constraint). Otherwise, they mostly function the same - a unique index is still a constraint that prevents duplicates and can also be used as the target of foreign keys. I forget if this specific inline index syntax was possible in ...


4

Is the above approach the best way to do this? Essentially this is the only way to approach this - indexes can only be created by one command. You could execute T-SQL for the CREATE INDEX manually, but the same command is going to be executed on the server regardless (barring any different options you choose, such as online). As with any production ...


7

Tony's answer was initially pretty near, but only worked for Native SQL Server Logins and not for Windows Authenticated SQL Server Logins. However, the general idea is the same. Create a Windows account on the local server: C:\> NET USER <account> /ADD Create a Windows Authenticated SQL Server Login: USE [master] GO CREATE LOGIN [<server&...


8

You can accomplish this in a roundabout way be creating a temporary login, remapping the user to the temporary login, and then dropping the temporary login. For a SQL login: USE [master] CREATE LOGIN [temp_user] WITH PASSWORD=N'asdf' MUST_CHANGE, DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON ALTER LOGIN [temp_user] DISABLE USE [...


0

To answer my own question for anyone in the same situation: Although the DBCC CHECKTABLE ... with ALLOW_DATA_LOSS option very nicely fixed the problem and removed the corrupt pages in a test-environment, it was decided to redo the migration. That was possible because the source-data turned out to be static: nothing had changed there...


1

Following is a partial solution that will return permissions assigned to objects in a given database to groups that a user is a member of either directly or indirectly. The script loops through the groups the user is a member of, and gets all of the groups those groups are members of recursively to build a list that is used for a WHERE IN predicate. The ...


3

What philcart did should help you 'start back up the next day where it previously left off': I run the IndexOptimise in two SQL Agent jobs. In the first job that runs weekly, I pull out all the indexes that need optimisation using the following, @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,...


0

The @Indexes parameter allows you to specify individual indexes or tables. We use it on some very large tables and then exclude that from the main index job for that database. Read more here: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html, particularly the Indexes section. You can also create a meta table to populate the list ...


9

Don't defrag your indexes every night. You don't need to anymore on modern hardware (especially with solid-state storage with zero seek times). For daily maintenance, you only need to update modified statistics, and even then you only need to do this when you notice performance degradation (i.e. check what your monitoring system tells you). Per Ola's site, ...


0

The trigger is failing because the query that is used to set @Logon is returning more than one row. Since @Logon is a varchar variable, an error occurs if anything more than one row is returned. To test if the login is in the table, eliminate the @Logon parameter and use IF EXISTS to test if LoginName and the other criteria match a row in the table: IF ...


1

Usually, if you want to use a node for "failover" (high availability), you want to have the AG set for synchronous replication (to enable the auto-failover). That being said, if you set it to "sync" and it's in another site, you may have performance issue caused by network latency. Also, if your "read only" node is not part of the AG, then you will have to ...


2

There is a stored procedure called SpaceUsedRating: https://github.com/aleksey-vitsko/Database-Administrator-Tools When you execute it exec SpaceUsedRating without any parameters, it will show space usage stats inside a given database This procedure can also be called with parameter @command = 'log' When called with @command = 'log' parameter, this SP ...


1

I know this is a bit of an old thread, but we actually have exactly the same requirements - Allow users to connect with read only intent and prevent them from falling back to the primary. The reason is simple - Our databases are complex with lots of little queries constantly running against them from our production systems and our users are typically ...


17

An exchange spill (ExchangeSpillDetails) occurs only in response to an intra-query parallelism deadlock. SQL Server resolves the deadlock by forcing one or more of the exchanges (parallelism operators) to write its buffers to tempdb. There is no concept of a spill "level" for an exchange spill (unlike sort or hash spills, which may require multiple passes ...


1

The issue seems to be that SQL is treating the pseudo field from the inner SELECT as text rather than JSON when it has been unioned. When the outer FOR JSON is applied to this pseudo field, it tries to escape the special characters in the text field. See this link for more info. You can negate this using the JSON_QUERY function, essentially forcing SQL ...


0

While there are several different types of restrictions in SQL Server Express, there are not any restrictions concerning the number of connections when compared to other SQL Server editions. Notably, SQL Server by default has: ...maximum (32,767) user connections... Any other limitation of connections will be Dictated by the application connecting ...


0

There is no audit action to capture this information, it is a limitation of SQL Server Auditing. Basically it captures the statements after the query optimiser has already parameterised the query. There are two open requests that I know of to alter this behaviour (here and here) that you could vote for, however, this limitation has been around for some time ...


1

The other half of the version requirements is that your distributor be the highest SQL Server version in your Replication topography. In your case, you are replicating from SQL Server 2012 to SQL Server 2016, which works only if your distributor is also SQL Server 2016. If you are running your distribution database on your 2012 publisher, configuration ...


0

I think you should run profiler in here to see if something is running manually your job with very specific filters like the job name.


Top 50 recent answers are included