12

There are really two questions in this question: Why is the query slow? Why are tempdb properties inaccessible? Let's start with #2. Why are tempdb properties inaccessible? You get this error: Cannot show requested dialog. (SqlMgmt) Property SpaceAvailable is not available for Database '[tempdb]'. This property may not exist for this object, or ...


9

is this a bug in SQL Server? Yes, certainly, the 1 that is returned in all rows in your final result only exists in the first row of the outer input so shouldn't even be in scope for the subsequent rows. It looks like the same basic issue as looked at in detail by Paul White here. I executed your final query in dbfiddle (SQL Server 2019) and pasted the ...


8

Just because you can does not mean you should. Please do not apply the changes below. This answer is only to show you why you should not change system tables or system procedures and what can go wrong. msdb.dbo.sp_add_operator When adding the operator with the ssms gui, msdb.dbo.sp_add_operator is called behind the scenes. The email address parameter used ...


5

Parameters are passed as an RPC (remote procedure call) to SQL Server over the TDS protocol. Consequently, a parameterized query will show as RPC starting/completed events in a trace (SQL Trace or Extended Events). Depending on the API and application methods used, parameterized queries might call API system procedures (e.g. sp_prepare) instead of ...


4

Perform the following commands in SQL Server Management Studio (SSMS) on the SQL Server instance you are trying to restore the database to. Verify BAK File RESTORE VERIFYONLY FROM DISK = 'T:\DIRECTORY\DATAWAREHOUSE_FULL_20200115_190000.bak' This should provide you with a result set that looks like this: The backup set on file 1 is valid. Possible Errors ...


4

I see your comment that Explicitly doing a "SET ROWCOUNT 0;" in the procedure resolved it You can achieve the same by configuring the menu options in SSMS: Tools > Options > Query Execution > SET ROWCOUNT: 0


4

To find lockers on a particular index you have to use seek on that index explicitly: select * from entities WITH (index(unique_entities)) where %%lockres%% like '%7e24236fccb8%'


3

You need to remove the log shipping metadata. The correct way to do this is via stored procedures, which also removes the jobs (I'm guessing you've deleted those manually). Execute sp_delete_log_shipping_secondary_primary. This removes the information about the old primaries on the secondary server (Server A). Also removes the jobs if they exist. Execute ...


3

The user_lookups column of sys.dm_db_index_usage_stats is a count of clustered index key lookups (a.k.a. bookmark lookups). This indicates a data row retrieved via a non-clustered index using the clustered index key row locator. The separate user_seeks and user_scans columns for a clustered index count the number of times the CI was used directly. These ...


3

Do you think this plan will work? Without a lot of headaches, no I don't. Most times I have witnessed an attempt to use replication for DR it has failed. Every time I've witnessed an attempt to 'fail-back' from a replication 'failover' it has failed miserably. As you've mentioned, replication is not a HA/DR technology and shouldn't be used as such. If you'...


3

All this information is the the catalog views. Here's a query to get you started: select schema_name(t.schema_id) schema_name, t.name table_name, c.name column_name, p.permission_name, p.state_desc from sys.tables t join sys.columns c on t.object_id = c.object_id join sys.database_permissions p on c.object_id = p....


3

The most likely explanations I can think of are: the database has been restored from a backup or snapshot in the case of a restore from backup, the log file would be reverted to whatever size it was when the backup was taken in the case of a restore from a snapshot, the log file is discarded and replaced with a tiny, 2-vlf log file the monitoring is ...


3

No. You can set Minimum & Maximum server memory to control the range of total memory utilization by the Instance, but there is not fine grained control for the constituent parts that make up "server memory"


2

The 8th row in your first screenshot shows what appears to be an active VLF in the file. If there is a single active log record in a VLF then the overall VLF will be treated as active log and that VLF cannot be truncated. If the server is SQL 2016 SP2 or later, you can use the below DMF to get information on the VLFs and check the active state and size. You ...


2

You can configure a timeout for a query to a remote server. The documentation is here ( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15 ) You can change this on a database with this script which sets a 100 second timeout USE AdventureWorks2012 ; ...


2

I have never tried to use SSMS to copy a live database as you have done. But I have copied many, many databases without an issue. Use the following approach in the future and you will not have the same issue again. Take a full backup (use COPY_ONLY) Then restore the backup with a new name. Your live database will never be detached, so you won't have ...


2

Q1 Does the backup process buffers data in cache ? If yes, could this cause a variation of PLE trend or memory pressure ? The backup process does not read data into the buffer pool, it uses separate threads that use buffers to read and write the data pages. More on that here. It does create a checkpoint prior to starting the backup. This checkpoint ...


2

Two big problems with using functions in a query are that cardinalities are not accurate and that the function code is called once per row. When setting a variable neither of these are a concern. So I would think, generally speaking, there would be no measurable performance impact.


2

The best thing I have found to help in big index creation is to (1) have enough RAM, (2) have a slow time before the index creation starts, and (3) perform a SELECT...INTO of the index fields into a temp table with an ORDER BY of the desired index order right before creating the index. This can speed the process by up to 75% in some cases (which I have not ...


2

The only idea I can think of is if I maintained a copy of the database on a separate server where I can make DDL changes, then re-point my applications to that server. Instead, consider just building a new table, and loading it incrementally from your existing table (things like Change Tracking or even Triggers can help here). Then during your short ...


2

This question is definitely a duplicate of SQL Server default collation vs database with different collation - potential problems? However, as that question was purely generic and this question specifies the two collations, I can at least clarify one of the issues raised in that other question: do you see any potential data loss? In this case there is ...


2

It would be at least a little faster, and also use less disk space, depending on how many rows there are with UniverseName = 'Unknown'. The original index would allow the query to seek to the correct movie title starting character, and apply a residual predicate to each row to make sure it's not from an 'Unknown' universe. The filtered index would still ...


2

For Jobs, you cannot use syslogins as the owner may be part of a group and not exist in logins. Use the below select msdb.[dbo].[SQLAGENT_SUSER_SNAME](owner_sid), * from msdb.dbo.sysjobs


1

I have a special exception that I feel should be mentioned. With SQL Server 2012 Enterprise and newer, adding a new NOT NULL column with a runtime constant is an online operation that is completed instantly and does not depend on the number of rows in the table. More information about this is found in MSDN I'll reproduce the important section Starting ...


1

Considering the presence of high HADR_DATABASE_FLOW_CONTROL waits on the primary, and the combination of PARALLEL_REDO_FLOW_CONTROL and PARALLEL_REDO_TRAN_TURN waits on the secondary, it sounds like parallel redo might not be a good fit for your workload. Can anyone think of any ddl,dml operations or other task that could cause this type of behavior? ...


1

This has been answered before. The problem is you're reading the top-level JSON only (Products). You need to use CROSS APPLY and read the nested JSON to get all the attributes. See this working example. DECLARE @J NVARCHAR(MAX) = N'{ "products": [ { "description": "Horse shoes", "productid": 1, "orders": [ { "customerid": 101 }, { "...


1

The process failed (unfortunately I missed the error message) and now the source database is missing as well. I found another post on the same topic, which suggests locating and atttaching the .mdf file. If the database is no longer listed on the instance, but you can still see the .mdf file, it somehow got detached during this first unsuccessful ...


1

I have run across this type of deadlock before. The issue is a foreign key validation in the entities table to the revisions table is being blocked by the update to the revisions table. This is caused by the revisions missing a key to make one of the processes more efficient - probably a key on the uuid field - and it scanning the PK while the reference is ...


1

You need to parse the string to separate the alpha + numeric part. Assuming that it is always alpha first follow by numeric and not the other way round the 2 cross apply split the alpha and numeric part from the string select * from yourtable t cross apply ( select alpha = LEFT(your_col, PATINDEX('%[^0-9]%', your_col)) ...


1

I would go with TINYINT for these reasons: You might come up with other modes - for example, "verifying" Bit can't be aggregated - that usually comes up with PIVOT function, where you have to aggregate - you first need to cast to int family type. Single bit column doesn't actually provide storage savings - it still takes a byte (the first bit in a byte) - ...


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