New answers tagged

11

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. It is also an interval with two ends rather than one. Writing BETWEEN is the same as writing two separate predicates with >= and <=. Interval with ...


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 ...


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

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 ...


1

According to these comments Can we somehow force SQL Server to use a cached plan for executing a SP or sql query? I am unable to get the same plan while executing the SP or the internal query manually so cannot really do a like for like comparison of any performance improvement that I make to the query / SP. It looks like you only want to run ...


0

To capture client API transactions, add the TM: Begin Tran completed, TM: Commit Tran completed, and TM: Rollback Tran completed events in the trace along with the TransactionID column. Queries with the same TransactionID are part of the same transaction. Note that Profiler is deprecated so I suggest you move to Extended Event tracing. The events are ...


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

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 ...


2

I started by extracting the SELECT query into a separate SSMS window. I replaced double-ticks (quotes) with single-ticks and added into dbo.TableForEmailingLogBackups that would allow me to create a real table to hold all of the results. Note: I had to change a part of your main query from WHERE sysdb.name = DB_NAME() to AND sysdb.name = DB_NAME() to ...


1

Consider moving clustered indexes into different file-group means the table itself moved new file-group. I believe, your scope is to move NON Clustered indexes into new file-group to improve the performance in SQL Server. In that case you can use following query, which moves the existing index into new file-group. For more details.. CREATE NONCLUSTERED ...


2

The answer is: you cannot run heavy scripts and not slow down the DB server. I quess all you have to do is to pick kind of light-workload-hours and REBUILD indexes one by one with your own hands monitoring blocking and overall server load. General advice would be to use ALTER INDEX <IndexName> REBUILD; instead of recreating them. Also consider ...


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 ...


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 ...


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 = '...


2

Trace flag 1117 deals with uniform auto-growth across multiple data files(https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/), while 1118 deals with mixed versus dedicated extents. These trace flags are not limited to use on tempdb, so it will affect your entire instance. I'd look at this article as well. As far as ...


2

Uncommitted Transactions As Dan Guzman mentioned, check for uncommitted transactions (e.g. using DBCC OPENTRAN) and look at the locks held by the blocking session. It may be a query timed out, wasn't rolled back properly, and the pooled connection with the open transaction reused. You need to remember locks are held by the session of that 'unrelated' query ...


0

You need to pass the powershell file in as an argument powershell.exe -file C:\Users\domainAccount\Desktop\deleteold.ps1 -FolderPath \\192.168.x.x\Backups -FileAge 2 -ListOnly -LogFile \\192.168.x.x\Backups\log.log Or if the step type doesn’t have to be CmdExe you can change the type to PowerShell


4

This will do it select UserName, EventDate, row_number over (partition by UserName order by EventDate asc) as EventNumber from AllEvents


0

Old thread I know, but in case this helps anyone... I definitely typed my path out by hand because I just have a dedicated drive "f:\" in my case. When I read this and looked at my job, it was empty as well. This time I selected it via the ... button and it fixed the problem.


1

Deleting files on NAS should not be done by sql server. You should rather use powershell to do it. You can use my function Clean-NASOldFiles which uses robocopy to scan files extremely fast and will then delete based on your input.


1

You can use a SEQUENCE, but remember that sequences don't allow transactions, hence you cannot rollback it. If you always need 2 ID's you can set INCREMENT BY 2 CREATE SEQUENCE [dbo].[MySeq] AS integer START WITH 1 INCREMENT BY 2; GO And then in you code: BEGIN DECLARE @id bigint, @id2 bigint; SELECT @id = NEXT VALUE FOR [dbo]....


0

Not a solution but for the sake of completeness: I had a similar problem on SQL 2016 while creating a filtered index on a computed column in a temporary table (must not be in a procedure, "works" stand alone too). CREATE TABLE #temp_table_name_goes_here ( [the_first_col] BIGINT NULL ,[the_second_col] INT NULL ,[another_col] VARCHAR(20) NULL ...


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 ...


1

SQL Server documentation should provide the information you need to determine login permissions. https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-2017


0

Modern versions of SQL server have a really small chance of hanging up altogether. SQL Server loads .NET Framework into its address space and uses it under normal operation. If physical memory and page file both run out, Windows will try to grow the page file; however even if it can grow the page file, this is not an instantaneous operation, and memory ...


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 ...


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 ...


0

Personally, I'd generate a unique certificate for each database encrypted with TDE. Having a single certificate for all your TDE-encrypted databases means any leak of the certificate would allow an attacker to easily decrypt every database they can locate. If you have one cert-per-database, any leaked certificate can only be used to decrypt that individual ...


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. ...


1

I am not 100% sure this is the cause of your issue, but this is too long for a comment. This can occur if SQL Server components are installed similar to the following: Install SQL Server Database Engine Install a service pack Install Reporting Services At this point, the installer will refuse to install a post-SP CU due to the RTM version of Reporting ...


0

Why is the LOG file growing so much? The answer is within the script (mentioned in question) that been executed: Online = On As it already confirmed in the comments, while you rebuilding index online there would be additional space (probably double of index size) required in LOG File as every index's (clustered or non-clustered) online operation generates ...


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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


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 ...


0

Page split is normal behavior of SQL Server. When we insert new records to a table, if current page doesn't have enough space to write the record/row, new page will be allocated to the table. This processes is also comes under page split. This type of page split is not bad. It becomes bad when it caused by UPDATE and new records doesn't fit within the page....


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 ...


0

Log backups back up everything in the log (active and inactive portions) and then mark the inactive portions of the log as reusable. Inactive meaning there's no uncommitted transactions, all the changed data pages have been hardened into the data file, the log records are not needed for replication, CDC or similar. Uncommitted literally just means open ...


0

It shouldn't be. read this. It should temporarily reduce the number of page split occurrence due to the allowance set in the page, in your case, 15%. But if data movement/transaction is quite fast, it may become full fast and just perform page splits again.


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 ...


0

Normally with a non named instance, using that cluster IP, it points to the primary SLQ instance on that cluster Usually, this managed by listener not with Cluster IP. As i understand (from title), both instances (SQLPRIMARY and SQLBACKUP) are replicas of an Availability Group. Normally, this group resources would be available in WSFC to have auto-fail-...


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) ...


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 ...


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 ...


1

There are two HA/DR technologies that you can use. Official names are: Always On Availability Groups (AG) Always On Failover Cluster Instances (FCI) They are quite different but both rely on WSFC (except for Basic Availability Groups). For FCI you need a shared storage which is also a single point of failure. You only have one copy of data and if it fails ...


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 ...


Top 50 recent answers are included