I wrote two functions that do an OK job:
for the integer/decimal part:
ALTER function [dbo].[get_dec](@string varchar(max))
@i int = 1,
@end int = len(@string),
while @i <= @end
set @char = left(@string, @i)
We can use APPLY to calculate the position of the various things we need, we use each APPLY in series like variables in procedural code.
We assume that the number is the start of the string, followed by arbitrary letters, a slash, another number, then more letters.
Note: no tally function needed here:
Since the log file is only growing on the secondary replica, something is preventing transaction log truncation on that database (but not on the primary).
You'll need to check the value of log_reuse_wait_desc in sys.databases on the secondary replica to see why the transaction log isn't being cleared there.
Since log_reuse_wait_desc is currently showing &...
There's no change in OPENROWSET functionality between 2012 and 2016.
This seems like a settings issue, In 2012 you (or someone) has probably set the service broker to enabled, configured db_sendmail with recipients, added permissions for metadata discovery for your account, etc.
But in 2016 since it was a new install maybe you don't have one or all of those ...
If you intend to be able to update/insert through the view, it will likely not work. Please see my post here:
Rows cannot transition to/from the memory-optimzed side by changing the key/partitioning value. Depending on the version of SQL you are running, it will either ...
I try to answer to all your questions:
If I create indexes on the CentralDB's table, will the views use it?
Yes for sure, if the indexed columns are used in the views, like the Department Code in your example.
Are Columnstore Indexes better choice?
Yes and No, it depends on numbers of rows and the usage of the central table.
It's something you have to ...
Fisrt, you cannot create indexed views when the tables are spread over different databases.
Second, SQL Server have special DMVs with name beginning as dm_db_missing index_* that will be filled with demands of creating indexes needed for the executed queries. So let it works a lot (one month) and look at the content of theeses views to create the appropriate ...
How are you measuring the size of your tables and indexes?
The way SQL Server works is once the database is allocated disk space, it still retains that disk space and only marks it empty / for re-use internally when data is reduced / removed until a SHRINK or similar operation occurs that forces the release of that acquired space back to the disk.
I'm using SQL 2019 and notice these XTP_THREAD_POOL cmd threads show up when I use Studio Manager (SSMS) and use a user database.
select Hostname, program_name, db_name(dbid) AS 'Database', *
WHERE spid >50
Shows these new threads. I'm not using in-memory indexes or anything unless 2019 has this enabled.
SQL 2019 CU ...
the problem is the error severity of the backup exception.
I simulated your situation with this script:
BACKUP DATABASE [test] TO DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [test] TO DISK = N'C:\DB\SQL\2019\MSSQL15....
An alternative solution would be to stop worrying about index fragmentation. In my company, we were having similar issues with index rebuilds blocking and generally being resource-intensive on a large ERP database and another large data warehouse database in SQL Server 2016 Enterprise. We have since stopped index rebuilds and reorganizes completely and ...
There is something which is incredibly wrong here. Your tables are tiny!
Just for comparison, I did a reorg on a clustered table with 450,000 rows and 22,000 pages having 42% fragmentation level. It took 1 second. This was on my 3 years old laptop.
You need to do some troubleshooting here.
I would restore the database from a backup taken before such reorg to ...
The problem here is that your secondary nonclustered index is not partiotion aligned so when a new record is added it generates fragmentation because that index follow a different order.
New records don't generate fragmentation for the clustered index and this is very good.
You can't prevent fragmentation on indexes ordered by accountid like in the secondary....
After reading the post of Sean Gallardy I setup StorPort tracing using Perfmon.
How to configure the tracing you find here and used StorPort-Trace-Reader to analyze the results.
The results showed that there was indeed some latency.
Next I used Windows Performance Recorder to trace the "Minifilter I/O activity". Using the Windows Performance ...
I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation
You've got the partition key in the nonclustered index, so it's on the partition scheme (by default), and only the head partition is going to get inserts and additional fragmentation.
So you can rebuild the older partitions rarely and ...
try to give the VCO AD object full control on the listener AD object.
To do this, go to AD user and computer, locate the listener object, open properties, security and verify if the virtual cluster object is here and if it has full control on this object.
Then retry the failover.
If you intend to use the cloned database for production use, specify the VERIFY_CLONEDB option which also clears the statistics in the cloned database.
Verifies the consistency of the new database. This option is required
if the cloned database is intended for production use. Enabling
VERIFY_CLONEDB also disables statistics and query store ...
I got this error when updating a lot of tables in one transaction ( via an entity framework migration)
It seemed to turn out that I needed to update the tables in order of referential integrity, doing the child tables first.
I split the process into several migrations.
I believe this is by design because the DBCC CLONEDATABASE documentation specifies system statistics are copied. My interpretation is that includes not only stats blobs but row counts as well.
To rectify the row counts, execute DBCC UPDATEUSAGE with the COUNT_ROWS option:
DBCC UPDATEUSAGE('CloneDBIssue_Clone') WITH COUNT_ROWS;
To answer your question, I did the following (see the fiddle here):
Create and populate tables - you could do this yourself for future questions - it's really helpful, removes duplication of effort and also eliminates copy and paste errors.
I've slightly changed the table definitions more or less in line with the SQL Style Guide - this is my personal ...
There are more compact ways to write this, but this illustrates a way to do it.
First select the territories and periods that Anne was looking after that territory.
Then pull all the sales where the transaction date falls in one of the periods when Anne was covering the territory where the sale was made.
Then sum up the sales.
territory_periods AS (
The query in the SP which is deadlocking is running in parallel (multi-threaded), Eric Darling's suggestion of MAXDOP 1 is good in that it eliminates parallelism as the casue of the deadlock. It is clearly something else.
An exchange event happens in parallelism operators when rows are passed between threads.
In this case you have both a row lock (Key Lock) ...