New answers tagged

11

No, this is not possible.SQL Server 2017 backups cannot be restored by any earlier version of SQL Server ref Also, regarding detatching and reattaching per the docs: After being attached to SQL Server 2017, the database is available immediately and is automatically upgraded. This prevents the database from being used with an older version of the ...


0

You can always check is there a need for the identity insert: SELECT CONCAT(N'SET IDENTITY_INSERT ', QUOTENAME(OBJECT_SCHEMA_NAME([object_id])), N'.', QUOTENAME( OBJECT_NAME([object_id])), N' ON;') FROM sys.identity_columns WHERE [object_id] = OBJECT_ID(table_of_your_interest); If the above returns a record, just paste it before your insert statement.


0

Just to provide a follow-up to this in case anyone is looking to do the same thing. We've been running SSRS 2014 with the database residing on a separate SQL 2017 instance for over three weeks now without any adverse effects. Do bear in mind that as HandyD has stated above, this is not explicitly supported and we're running it in a non-production ...


0

As with all design, there are multiple ways to get what you want; it will just depend on personal preference and your level of comfort with how complex/simple the final configuration should be. If you want to keep SSIS running on the same machine as your database server, you can configure SSIS as a clustered service such that it travels with your AG, but ...


1

I have been in a similar situation many times. Steps : Add a new col of desired width Use a cursor, with a few thousand iterations (perhaps ten or twenty thousand) per commit to copy data from old column to new column Drop old column Rename new column to name of old column Tada!


11

I don't know of a way to directly accomplish what you're looking for here. Note that the query optimizer isn't smart enough at this time to factor in constraints for memory grant calculations, so the constraint wouldn't have helped anyway. A few methods that avoid rewriting the table's data: CAST the column as NVARCHAR(260) in all codes that uses it. The ...


3

As mentioned in a comment by Kin, if you want to check on whether the "Enable Always On" checkbox is checked in SQL Server Configuration Manager, you should be using this property instead: SELECT SERVERPROPERTY('IsHadrEnabled'); From the docs on IsHadrEnabled: Always On availability groups is enabled on this server instance. Regarding the other setting ...


1

Based on what you want I would do the following: Create two files - List.txt (with the list of DBs you want to restore) and DBRestore.bat with sqlcmd command to run a stored procedure 'DBRestore' inside SQL Server Create a stored procedure 'DBRestore' which loads into SQL Server temp table the list from the LIST.txt and processes it - reads DB name one by ...


1

Talking with Mateus in the discussion he mentioned that he wants to avoid an insert onto the Animal table in this kind of instance if at all possible. As he and I discussed it is difficult since all conditions do not live in the same place. Using a Trigger you should be able to remove the Animal table record for an instance where an Insert into Fish fails ...


4

You can enforce this with an Indexed View (or a trigger, of course). EG create table Animal(Id int primary key, Alive bit) create table Fish(Id int primary key references Animal, FishEspecieId int) go create or alter view vFish_Alive with schemabinding as select f.Id, f.FishEspecieId, a.Alive from dbo.Fish f join dbo.Animal a on f.Id = a.Id where a....


0

With Scott Hodgin's help (see comment thread above), I was able to figure out the issue: First issue: Since I certainly was using an OLE DB destination, I needed to add FIRE_TRIGGERS in the Advanced Editor (Microsoft, make this easier to find) Second issue: once the INSERT trigger was firing, it was updating every record in the table, not just the ones ...


5

John's answer is the correct solution, this is just an addition as to what parts of the execution plan changed and en example on how to easily spot the differences with Sentry One Plan explorer An update statement that took 100ms before IndexOptimize took 78.000ms afterwards (using an identical plan) When looking at all the query plans when your ...


11

I suspect you've got a different sample rate defined between your two maintenance approaches. I believe Ola's scripts use default sampling unless you specify the @StatisticsSample parameter, which it doesn't look like you're currently doing. At this point, this is speculation, but you can check to see what sampling rate is currently being used on your ...


11

For large deletes in batches, consider specifying a clustered index key range instead of using TOP so that a clustered index seek can be used in the plan. Below is an example. DECLARE @BATCHSIZE INT = 4000 , @ITERATION INT = 0 , @TOTALROWS INT = 0 , @MSG VARCHAR(500) , @STARTTIME DATETIME , @ENDTIME DATETIME , @StartValue int = ...


1

Without more information we can only take lightly informed stabs in the dark, so you should edit the question to provide a little more. For instance the query plans for that update statement that you have given the timings for, both before and after the index maintenance operations as the plans may differ due to the index stats having been updated (https://...


0

Disable TLS1.0 and TLS1.1 and check It will work.


2

I keep finding more tables that leak information about other databases, hence I decided to post this collection of things that I block to the public role. They do not seem to affect any db functionality, though I take no responsibility on that as I'm only using a small subset of what SQL Server actually offers. It could very well be that this breaks ...


8

Tracing the queries When tracing the queries executed, below query is found that lists the folders on the drives one by one. declare @Path nvarchar(255) declare @Name nvarchar(255) select @Path = N'D:\' select @Name = null; create table #filetmpfin (Name nvarchar(255) NOT NULL, IsFile bit NULL, FullName nvarchar(300) not NULL) declare @...


2

yes, that is possible - you create 3 different publications - and in each publication you add only the respective tables you want for each server. for example - only 2 tables for server1 The other tables will be in other publications, not this one. Alternatively, within the merge replication you can set up filters and each subscriber would see only a ...


2

Without seeing the execution plan, it's impossible to say exactly what the problem was. However, the root cause seemed to be with the way the optimizer was handling joining the queries within your CTEs (or something along those lines). In SQL Server CTEs aren't currently materialized or fenced off (unless you use TOP inside one). In many cases, breaking up ...


13

How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records? There is one LOB physical read and 1,877,324 read-ahead reads. Read-ahead is still a physical read, just performed in advance (prefetching). The quote from Redgate is incorrect. How it is possible the total number of lob pages decreased from ...


2

"How it is possible to have only 1 lob physical read with cold cache if the table has about 470 million records?" Because the other physical reads were served by read-ahead (the LOB type). Perhaps that RedGate article confused you, but if you serve an extent (say) as a read-ahead (RA), then you won't see any physical reads. The thread that collects the ...


1

Next time SQL Server ignores the index try to update statistics with FULL SCAN on that index or on the whole table. If that works then fragmentation is not a problem. Also SQL Server doesn't care about fragmentation when choosing a plan, but it may ignore the index if it becomes too big because of the "internal fragmentaton". Please check the empty space ...


1

Finally found that there's a bug with SSMS 18.2 that creates this issue. Install another version of SSMS or connect from another server / computer it will show the correct settings.


3

You could write the value to a table. Something like create LogTable(Counter int, When datetime); You can query this table when you need to see the progress. If you don't want to pollute a production database with this it could be created in TempDB. Note I do not mean create a #temp table. That would be visible to the backup job only. I mean a "real" ...


8

There are two extensions of RAISERROR that may help: To write it to the error log: DECLARE @msg NVARCHAR(1000) = N'' RAISERROR(@msg, 16, 1) WITH LOG; To display it in the messages tab in "real time": DECLARE @msg NVARCHAR(1000) = N'' RAISERROR(@msg, 16, 1) WITH NOWAIT;


1

I just installed the ODBC client for SQL 2017 for a python process yesterday. It seems to be working just fine. MS has stated the ODBC driver would be deprecated for at least 7 years now. I'm not convinced it's ever going away, but who knows. edit: Just tested connecting to SQL Server 2017 using the driver... seems to work just fine. – Jacob H


3

Microsoft has deprecated the SQL Server Native Client (NCLI) driver, however SQL Server itself uses a wire-protocol, TDS that is independent of the driver used by the client. NCLI will continue to work for the forseable future, however no new work will be conducted by Microsoft on the NCLI driver. Pedro Lopez talks about the announcement, and subtle un-...


5

You should probably play with scripting options in Tools-Options-SQL Server Object Explorer-Scripting in SSMS menu. There you can define what you would like to script and what not. Compression is there. As for the other properties - have a look at this StackOverflow question - it will probably shed some light: SORT_IN_TEMPDB is not a property of an index, ...


14

Index Hint You can use an index hint to do that: SELECT * FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index) WHERE u.Reputation = 2; The downsides are: Potentially changing a lot of code If you rename an index, this breaks If you change an index definition, it might not be the best index to use anymore Plan Guides You can also use a Plan Guide, ...


2

Just for completeness, you can get dynamic partition elimination, but only if the join type is nested loops with correlated parameters. For example, using the provided partitioning function and scheme: CREATE PARTITION FUNCTION MonthlyPartition ([date]) AS RANGE RIGHT FOR VALUES ( '20190201', '20190301', '20190401', '20190501', '20190601', '...


9

Most data warehouses are in Simple recovery model, and most true HA solutions require Full recovery (AGs, Mirroring). The minimum for Log Shipping is Bulk Logged, but that's not really true HA since there's no automatic failover. If that's the case for yours (because really, a data warehouse in Full recovery is banana-town-crazy), your best bet would be a ...


0

I can see your have SQL Server 2017 STandard Edition, so you can use the Basic Always On availability groups for a single database. Please check the details here : Click here


0

I had exactly the same issue and found a workaround by specifying a combination of Windows/Linux path for the full text files in the restore database files as options. E.g. instead of the configured default "/SQLServerData/catalogname", I changed it to "C:\SQLServerData/catalogname" and then it was happy.


2

The issue was not in SQL or Windows Authentication methods, but in default_database setting Found that for my Windows Authentication Login, default_database = [MyDatabase] (which participates in availability group), while my SQL Authentication Login has default_database = [master] Because of above setting, while connecting in SSMS using Windows ...


2

I can't reproduce your problem, which means there's likely something missing in your description (or I've missed something in your description!). If you want more detailed help with this issue, please suggest changes to this repro code that allow it to more closely match your situation. Here's an attempt and recreating the tables and data based on your ...


5

I didn't really mean round trip all the way to the application. I consider a stored procedure one form of "application" in this context. DECLARE @c int; SELECT @c = COUNT(*) FROM dbo.table WHERE ...; IF @c < 200 BEGIN SELECT cols FROM dbo.table WHERE ...; END If the application always expects either an empty or non-empty resultset, you can do this:...


4

Here's a timeline of the events you described, as well as my commentary on what was the likely cause of your bad plan. You're alerted of a slow query that was previously fast Bad times, I empathize! You go crazy and run "the forbidden dbcc freeproccache" and it doesn't help Usually, clearing the cache fixes problems like this if the problem is parameter ...


Top 50 recent answers are included