New answers tagged

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


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


0

You need to write a loop which polls serverB and checks whether the job has finished and what the outcome was. On each iteration of the loop make the job sleep using waitfor delay '00:02:00' (a 2 minute delay). To check if the status on a job see the link below https://sqljunkieshare.com/2014/11/21/get-sql-agent-job-status/


0

Parameter values containing double-quotes are not affected by the QUOTED_IDENTIFIER setting within the proc unless you use dynamic SQL. The setting affects how the T-SQL statements are parsed. As long as the T-SQL proc code encloses string literals using ISO SQL standard single quotes like your example, you can safely recreate the proc from a session with ...


0

The few times I thought column level locking was needed, I came to the realization later on that it was a bad schema design. The columns needing to be locked should have been placed in a separate table.


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


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


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


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


0

These need an index beginning with Batch. In the first case for the ORDER BY, in the second case for the WHERE: select * from IDSelection where IDType <> '' and Batch is not NULL ORDER by Batch desc -- cluster index scan select * from IDSelection where Batch = 9977 -- 8 minutes run for 19 records This needs INDEX(ParentID, Batch) in that order: ...


0

The DROP TABLE might be in a transaction trancount="2" so it might own X locks from previous statements, which makes for more deadlock risk. The other session is running sp_columns_100 which is a metadata discovery procedure that returns column metadata potentially for many tables. Also the other session was the victim, and was also connected from DEV01 ...


1

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


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


0

I wrote a replacement that has several improvements over the original sp_foreachdb, including the ability to nest. The newer, preferred procedure is called sp_ineachdb (part 1 / part 2), and is also available as part of Brent Ozar's First Responder Kit. Some background here: Bad Habits to Kick : Relying on undocumented behavior


0

Too much code--just create a temp table with the entire result-set. SELECT * INTO #tmpTbl FROM MyDatabase.dbo.tbl ALTER TABLE REMOVE columname SELECT * FROM #tmpTbl DROP TABLE #tmpTbl


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


0

first of all in your query where 1=1 is nor clear . Apart from all suggesation there is still one major scope of improving the given query. Apply Paging in only main query. Main query which affect the number of rows in Paging. Sorry if I am not clear about Main query, let me explain with example. ;With CTE as ( select h.ActionDate as Date, h....


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


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


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


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


0

I just came across this for the first time in my own code. My work around was to use a temp table to load the cursor. DROP TABLE IF EXISTS #mp SELECT DISTINCT(PeriodDateKey) INTO #mp FROM fact.SomeFact ORDER BY PeriodDateKey ASC DECLARE @loopPeriodDateKey INT DECLARE periodDateKeys CURSOR FAST_FORWARD FOR SELECT PeriodDateKey FROM #mp OPEN ...


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


Top 50 recent answers are included