27
when there is no physical memory left for data, then SQL Server moves the already existing data into TEMPDB
The article you linked to is misleading at best, and incorrect in some places. I think the author was attempting to over-simplify some complicated things, and in doing so went a little too far.
SQL Server doesn't move data from memory (the buffer ...
15
There are ways to tune backups by messing with different knobs like MAXTRANSFERSIZE or BUFFERCOUNT, or striping the file (which you've noted you're already doing).
The problem is that touching those knobs may still result in hitting the limits of your network and/or storage, and them not having any real impact on backup time.
Your first job should be to ...
11
SUM(CASE
For a limited number of Names you can use a SUM(CASE solution in this way:
SELECT
'Database status' as [DB Status],
SUM(CASE WHEN Name LIKE 'Test%' THEN 1 ELSE 0 END) As Test,
SUM(CASE WHEN Name LIKE 'Prod%' THEN 1 ELSE 0 END) AS Prod,
SUM(CASE WHEN Name = 'Migrated' THEN 1 ELSE 0 END) AS Migrated,
SUM(CASE WHEN Name = '...
10
The alternative you mentioned seems to be the best choice.
What you can do is a 2 step process :
Take native sql server backups with compression using Ola's backup solution locally.
Use Robocopy to do the transfers to a network share. This is decoupled and can run as a Windows scheduled task.
This way, your backups are local and they will be fast. You ...
9
A couple of potential solutions:
Going from full-only to a weekly full backup and nightly differential can be an easy solution.
There are a number of performance-related parameters that you can tweak in Ola's scripts, you might be able to tweak these to get the performance that you want:
BlockSize
Specify the physical blocksize in bytes.
The BlockSize ...
8
SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step boundary.
I do not have the same histograms as you. On my fresh copy of AdventureWorks2014 on SQL Server 2014 SP3 CU4 with no modifications to the table since the ...
7
Are service packs for SQL 2014 different than the service packs for
SQL 2014 Express?
There is no difference in service packs or cumulative updates between SQL Server Express edition and other editions such as developer, standard or enterprise.
When looking at the latest build of sql server 2014, SP3 CU4 is the last one with build number: 12.0.6329.1 (...
7
If you want to just use stand alone instances - which is the most common method of implementing availability groups [stand alone to stand alone] - then you only need to do a stand alone install. So, yes, you can do the stand alone if that's what you're looking for.
It is possible to use a SQL Server failover cluster instance install to also work with ...
7
When you have lots of page splits caused by random middle-of-BTree inserts, your pages will naturally average around 65%* full. Split pages start at 50% full and any that fill up past 100% get split back down to 50%.
So rebuilding your index with a fill factor of 85% might temporarily increase page splits, just not as much as rebuilding with a fill ...
7
I think it's important to strictly separate the two tasks you're trying to perform in one step here.
Classification
Transformation
For classifying the data, my instinct here is to recommend a lookup table to rigorously map records to a parent class. e.g.
CREATE TABLE StatusType (
ID INT IDENTITY PRIMARY KEY,
[Name] VARCHAR(10) NOT NULL ...
6
Well, after continue searching I found this article
Unlike a normal query, it won't pick up a new index due to statistics being updated, a new index being created, or even a server being rebooted. The only way I'm aware of to have a FK bind to a different index is to drop and recreate the FK, letting it automatically select the index with no options to ...
5
There are many possible options, but as databases get larger and full backups take longer, you will likely have to incorporate differential backups, if you haven't already:
Creating a differential backups can be very fast compared to creating a full backup. A differential backup records only the data that has changed since the full backup upon the ...
5
The link by @Ross will definitely help you determine syntax and what all of your options are, but for a little more guidance:
There are two database roles that will probably be useful to you, they are db_datawriter and db_datareader. The two of them will probably handle the majority of your CRUD (Create, Read, Update, Delete) operations.
ALTER ROLE ...
4
This will do it
select UserName,
EventDate,
row_number over (partition by UserName order by EventDate asc) as EventNumber
from AllEvents
4
Can this be solved by creating another table with a unique primary key plus the identifying columns from ACADEMIC?
Yes. Minimally you need a Foreign Key and an AFTER INSERT trigger.
eg
use tempdb
go
--create schema custom
go
--drop table if exists custom.AcademicKey
--drop table if exists dbo.Academic
go
create table dbo.Academic
(
PEOPLE_CODE_ID ...
4
My question is, Can I normally install "stand-alone installarion"
Yes absolutely, the Availability Groups meant for HA/DR solution with stand-alone instances (Replicas) with directly connected storage unlike FCI where it required special installation procedure and shared storage etc..
Although, the answer is already clear, following are the summarized ...
4
A possibility would be to check for DB_Name = 'Tempdb', and if it's tempdb just end the script.
This is a quick test I did, and it seems to works :
EXEC sp_msforeachdb 'USE [?] IF DB_Name() = ''Tempdb''BEGIN RETURN END SELECT db_Name()'
That will run the code on each db, but if it's tempdb it wont execute a thing, cause it'll return.
It's also known that ...
3
The issue here is cross-database ownership chaining, basically once you leave the context of the AdventureWorks database, you don't have the elevated rights that dbo/sa implies and cannot actually execute the procedure in msdb.
You should recreate the stored procedure in msdb instead and grant your unprivileged user CONNECT permission to msdb and EXECUTE ...
3
It's really not possible that changing the fill factor would cause more page splits, assuming that you are measuring the page split rate immediately after a 100% fill factor rebuild/reorg vs. an 85% fill factor rebuild/reorg. It will be difficult, if not impossible, to find an authoritative reference that states this explicitly, but any accurate discussion ...
3
It's possible to change the data type without data loss or breaking FK dependencies. It depends on whether you have some downtime where you can execute the required change though.
Changing from datetime to datetime2(7) will require every row in the table / indexes to be updated, since these two types are stored differently on disk. This can take a little ...
3
From your example, you are trying to insert the results of your query into a table variable and then trying to use that table variable in the sp_send_mail procedure call. That won't work. sp_send_mail needs to reference a real table and not a temp table either.
3
With Hyperthreading enabled, you currently have 4 hard NUMA nodes with 10 physical cores and 20 logical cores each. On SQL Server 2012 and 2014 you will have 4 matching soft NUMA nodes with 20 logical cores each.
Upon upgrading to SQL Server 2016 or higher, auto soft NUMA will automatically kick in. You'll now have 2 soft NUMA nodes per hard NUMA node for a ...
3
What other types of activity could use so much version store so fast?
If the instance was acting as a readable secondary replica for an availability group, then snapshot will be used for read queries against that database - regardless of the settings or specific isolation levels requested. If there are a lot of data changes on the primary for that AG ...
3
The database is created when you use the GUI to add databases to the secondary servers, and select the option Full database and log backup, you may safely delete it.
In the Specify a shared network location accessible by all replicas: field, specify a backup share to which all of the server instance that host replicas have read-write access. For more ...
2
I've only ever seen that code get scripted in two situations:
When the "Always re-create database" advanced publish option is checked.
That option can be set in multiple ways depending on how your deployment process works.
Here it is in the user interface, when you do a one-off publish or save a re-usable ".publish.xml" file:
Without using the UI, you ...
2
According to Tail-Log Backups (SQL Server) - (highlighting mine)
If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To
avoid an error for an online database, you must use the ... WITH
NORECOVERY option of the BACKUP Transact-SQL statement.
If a database is offline and ...
2
After reading MS DOCS here.
To modify a foreign key
To modify a FOREIGN KEY constraint by using Transact-SQL, you must
first delete the existing FOREIGN KEY constraint and then re-create it
with the new definition. For more information, see Delete Foreign Key
Relationships and Create Foreign Key Relationships.
In you case I believe add a new ...
2
SQL Server uses write-ahead logging so that changes (committed or not) are always written to the log file prior to being written to any data file.
Records written to the transaction log do not necessarily reflect the entire page being modified. Log records typically consist of compensation data that allow transactions to be rolled forward and back. ...
2
Sounds like you may have deleted the certificate from the certificate store prior to unbinding it from SQL Server. I haven't tested it yet, but I'm guessing that if you did that, no certificate would be shown in the SSCM dialog because it would be trying to find the certificate that matches the thumbprint stored in the registry, but wouldn't be able to find ...
2
I'm trying to connect via SSMS to the X.X.X.38 That is the CLUSTERs Ip but It shows the error that the instance cannot be found.
Named instances, by default, don't listen on port 1433, which is required to connect without a specific port or instance name.
However Failover Cluster Instances always listen on dedicated IPs, so you can (and should) ...
Only top voted, non community-wiki answers of a minimum length are eligible
