New answers tagged

1

You need to define the criteria for what constitutes a "possible primary key". When you have done that, you/we can search for (for instance sp_special_columns) or produce a script that uses that criteria. Such criteria could be: A unique index on the column(s) (possibly created using a unique constraint). The table has a column having the IDENTITY ...


1

Most objects in SSMS's Object Explorer are drag and dropable directly into the Query Editor tab you're currently in. For example, if you hold left-click, and drag the name of the table you want, you can drop it on any line of code in your active Query Editor tab. Furthermore, I use this as a quick way to list out all of the columns of an entity in my query ...


0

Yes, doing that method you describe (I have done it myself) will leave your source environment fully operational and without issue. Any subscriptions you have in the source environment won't fully copy over unless you also copy the Agent Jobs from source to target though. .... although that may be what you want. And if you are going to a different ...


3

Rather than the detach/attach, take the database offline, change the location, move the file, and set back online. Example DDL: --take database offline ALTER DATABASE test SET OFFLINE; --modify the file location ALTER DATABASE test MODIFY FILE ( NAME = 'log', FILENAME = 'd:\test2.ldf' ); --manually move the file to the new location --finally, set database ...


2

Exactly what you asked does not seem to be fully achievable Alternatively, here is what you can do: Check virtual file IO stats (reads, writes, GB read, written, average reads, writes, etc.) for each database and log files on a server - since server restart https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Storage%20-%...


1

I thought that 8 Virtual Procesors is the maximum no? The document you link to specifically gives an example of more than 8 cores, on page 5, so no 8 is not the limit. But it may depend on how you are currently licensed which you do not state. Note though that licensing questions are generally considered off-topic here for a couple of reasons, and you ...


0

First steps you can take to troubleshoot your error: Try to increase Connection Timeout property in the connection string from default 15 (or 30) to 60 or more seconds Try to increase CommandTimeout property of the SqlCommand object (Hannah's answer has great detail on how to do it). Up until very recently, the .NET System.Data.SqlClient driver allowed ...


3

By default, the .Net client has a 30-second client-side command timeout. The error you are seeing is saying that the response from the server took more than 30 seconds. Here's some example C# code showing the problem by executing a WAITFOR DELAY statement that waits 31 seconds: using System; using System.Data.SqlClient; namespace ConnectionTimeoutTest { ...


3

I'd recommend using sys.dm_exec_text_query_plan instead of sys.dm_exec_query_plan. Using sys.dm_exec_query_plan you will lose stored procedures with deferred compilation, long-running stored procedures (in case the SP has not finished but its statement which is using the given index has already done), and multi-statement UDF. Additionally, you should add ...


2

As well as a simple text search, you can also use an XQuery filter, which may be faster or slower. DECLARE @idx sysname = N'[IX_YourIndexName]'; WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT qp.query_plan, qs.execution_count, st.text FROM sys.dm_exec_cached_plans cp CROSS APPLY sys....


2

try this: SELECT dm_exec_query_plan.query_plan, usecounts AS execution_count, dm_exec_sql_text.text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) INNER JOIN sys.dm_exec_query_stats ON dm_exec_query_stats.plan_handle = dm_exec_cached_plans.plan_handle CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle) ...


1

I got this error after several install/uninstall cycles of Microsoft SQL Server. The solution was to delete the dead profiles in directories: c:\Windows\ServiceProfiles\MSSQL$SQLEXPRESS\ SQLTELEMETRY$SQLEXPRESS and re-install Microsoft SQL Server.


1

how would I know which data file would be best to move to the G: drive? You could decide based on: The latency for each database in order to balance the read/write so that none of the two disks would be overloaded. See Monitoring Read/Write Latency How critical is the database to choose the ones that would allow the longer time offline for the maintenance ...


2

This is also based on sys.identity_columns: SELECT Q1.*, inserts_remaining = CONVERT(decimal(38), (V.max_value - V.last_value) / V.increment_value), percentage_used = CONVERT(decimal(5, 2), V.last_value * 100 / V.max_value) FROM ( SELECT [object_name] = OBJECT_SCHEMA_NAME(IC.[...


1

I wrote a script (based on sys.identity_columns) a long time ago for this purpose. I made an attempt at including an estimate for the date you will run out of identity values, assuming a default insert rate (you can configure this option).


7

There is a column on sys.columns that identifies identity columns (is_identity). Additionally, there is a IDENT_CURRENT() function that you can use to identify the most recently generated identity value for a table to determine if you're approaching the int max. (docs) You can simply query that to identify every table with an identity column, (and what the ...


2

After the secondary replica comes back online, it will not immediately be available for fail over. You will need to wait for the 5GB of logs to send to secondary and be redone. During which time Primary will have generated more logs that will need to sync and be redone. Once all logs are synched, you'll be able to fail over to secondary without incurring ...


0

Turns out that the problem was backing up to a container in a v2 Storage Account. SQL Server 2014 obviously doesnt like that. I got a v1 Storage Account provisioned and backups are now working. Thanks again to anyone who contributed.


Top 50 recent answers are included