6

In general, yes, you can move the backup files. Backup files are not in use by SQL Server once the associated backup is complete. .BAK files are typically full or differential backups, and .TRN are typically transaction log backups, and they can all be moved. It is certainly possible to use different file extensions for the backups, but it's not common. ...


5

Below ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 150 | 140 | 130 | 120 | 110 | 100 | 90 } is not propagated to secondaries in DB mirroring or AlwaysON since they ship log blocks vs transactions - (logshipping you take log backups and ship it to secondary server and restore them). There is a reason why they should not change the DB ...


2

The optimizer doen't need any stats to optimize those queries, so none are created. When the automatic create statistics option, AUTO_CREATE_STATISTICS is ON, the Query Optimizer creates statistics on individual columns in the query predicate, as necessary, to improve cardinality estimates for the query plan. AUTO_CREATE_STATISTICS Option Try ...


2

As I understand it, a plan guide is not compiled like an execution plan. Therefore, it cannot be recompiled. That being said, a plan guide does result in an execution plan, either through its hints or by specifying a specific plan to use. The resulting or specified execution plan could be affected by auto update statistics. If auto update statistics ...


2

Yes. The Plan Cache and Page Cache share the available server memory. The Plan Cache is capped, as growing a large plan cache rarely provides much marginal benefit. AFAIK, this old doc page is still accurate: Memory Allocated To Plan Caching Most memory used by SQL Server is allocated to the Buffer Pool, which is used to store data pages. SQL ...


2

Using OFFSET...FETCH likely introduces a row goal (in the same way that TOP does so - see Setting and Identifying Row Goals in Execution Plans by Paul White for more information on that subject). This is why you see such a difference without that part of the query - the lack of a row goal changes the plan entirely. The row goal leads to SQL Server choosing ...


2

As long as the backups are not actively being written to, you can delete, copy, or move them without impacting SQL. You can do this to .bak or .trn or .dif Beware that during the backup process you can use any extension you want like .WhatEver It doesn't really mater what extension they have, same rules apply. If the files have extensions .mdf or .ndf ...


1

If you're changing the SQL Server Agent Service, make sure you observe this orientation about SQL Server Role Membership from the doc: The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles: The account must be a member of the sysadmin fixed server role. To use multiserver job processing, the ...


1

Like Dan Guzman said, you should add an index on fsams.CTB_SUBSIDY_CARD_HEADER(LotNumber). The clustered index scan of this table represents 59% of the cost of the query. It seems also that sh.LogNumber and h.PaymentLotNo are not the same type. It gives you an implicit conversion warning in your plan on sh.LotNumber, you should try to fix that. And you ...


1

If there are no transformations involved, you could write this process fairly simply in SSIS with a for loop in the control flow that iterates over a file directory, then passes each file name as a variable to a data flow. The filename for the csv would be bound to that variable, and your destination could similarly be bound to the file name. Just use bulk ...


1

Avoid Row_Number because first it process whole table then again you select row where rn=1 for each group DECLARE @Customer_Channel_Branch TABLE ( Customer_Num VARCHAR(10) ,Channel_Num INT ,Branch_Code INT ,Issue_date INT ) INSERT INTO @Customer_Channel_Branch VALUES ( 'x' ,1 ,1000 ,1990 ) ,( 'x' ,2 ,...


1

very Easy for you: enter in powershell mode and write this code: ./setup /UIMODE=EnableUIOnServerCore /ACTION=INSTALL good luck.


1

First, you state that: Yes, this is part of statement in stored procedure. Is this the only statement in the stored procedure? While not impossible if testing, it would otherwise seem odd to have a stored procedure that sets a variable and then never uses it or does anything else. So, what then of the other statements in the stored procedure? Have all of ...


1

Generally speaking rpc_completed includes network time, while sp_statement_completed does not. This is very likely the case for your query, since it only returns one row. So one explanation is the extra time is spent sending data over the network to the client. You could try measuring wait statistics for this query to confirm there are 24 seconds of ...


1

You're looking for the PIVOT clause. You can see a working example in this db<>fiddle. The query is: SELECT customer_num ,p.I AS I_Branch_Code ,p.Y AS Y_Branch_Code ,p.L AS L_Branch_Code FROM ( SELECT customer_num ,Channel_Id ,Branch_code FROM [table] )s PIVOT ( MAX(Branch_Code) FOR Channel_Id IN ([I], [Y], [L]) ) p


1

Adding to the SQL Mail Fail to Send quoted in the comments by LowlyDBA From this question: Comprehensive troubleshooting of DatabaseMail I would first of all see what is the outcome of the following query (as you already got dbmail up and running): --============================================================== -- I’m doing a TOP 100 on these next ...


1

The correct format of the parameter -S for the SQLCMD prompt is: Server\Instance. You seem to have tried localhost\instance\database which doesn't work. SQLCMD -S .\BACKUP-MGMT-01\CONSULT ^^ ^ ^ ^ || | | +--- Database || | +------- Separator || +------------- Instance ...


Only top voted, non community-wiki answers of a minimum length are eligible