7

It is running a stored procedure named proc_DefragmentIndices in many (possibly all) of these databases at a time. The stored procedure rebuilds every index in the database unconditionally. I assume you don't have any control of this, but I'd be remiss if I didn't suggest that you make this stop happening as soon as possible 😀 Use Ola Hallengren's script, ...


7

There are two type of timeouts. One is when the client application (API) aborts the batch after a certain amount of time. Called "attention" in a SQL server trace. Default in many APIs is 30 seconds. The other is when the client requests that SQL server abort the query with error 1222 after a certain time. SET LOCK_TIMEOUT. Default is indefinitely. ...


6

There's really no database involved. You can see it hits no tables. But yes we've run this "in" several databases and run CHECKDB against them just to be sure. The database involved is tempdb (database ID 2). That's where the value of LOB variables gets written. You can see that by looking in the sys.dm_db_task_space_usage DMV (if your query ...


6

This reproduces for me on Microsoft SQL Server 2008 R2 (SP2) build 10.50.4000. It seems to have been fixed some time after SP3. The issue was reported on Connect (and subsequently migrated to user voice, so that link works). You need more than 512KB of data in the LOB, and a variable assignment that reads from the same variable, without performing any ...


5

The difference is caused by noise word "W". By default SQL Server uses stoplist to exclude some short or frequently used words like "and", "does", "could". You can see the full list using such query: select * from sys.fulltext_system_stopwords where language_id = 1033 And you can disable the stoplist for your full ...


4

A clustered index is the table. Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be stored in only one order. Clustered and Nonclustered Indexes Described


4

There is no "middle of the table" - a table is an unordered set of rows. Think of it as a bag of marbles - if you empty it on the floor, can you predict which marble will stop moving first, last, etc.? If you don't use ORDER BY in your query, SQL Server is free to return the data in any order it chooses, and it will do based on what is most ...


3

How about: select LOGID, DESCRIPTION, STARTTIME Time from T union all select LOGID, DESCRIPTION, ENDTIME from T order by LOGID, Time If you want to order the event pairs by STARTTIME instead of LOGID, then with q as ( select LOGID, DESCRIPTION, STARTTIME, STARTTIME Time from T union all select LOGID, DESCRIPTION, STARTTIME, ENDTIME Time from T ...


3

There are two options: a) The service isn't stopped. b) You access some other SQL server instance from your 2019 machine. It is literally impossible to access a SQL server which isn't running, so we can rule that out. My guess is that you have a client alias on the 2019 machine with the name of the old 2008 machine (and possibly instance), which points back ...


3

OK, found how to do it in SQL Server 2008: EXEC master..sp_dropsrvrolemember @loginame = N'YourLogin', @rolename = N'dbcreator'; Thanks again for the suggestions: when this DB will be migrated now I know that probably I'll have to modify the script to use ALTER SERVER ROLE


3

You can only add logins to server roles, not database users. To remove a login, you use ALTER SERVER ROLE ... DROP MEMBER: ALTER SERVER ROLE dbcreator DROP MEMBER login_name; Please stop using the stored procedures; they're all deprecated. For example, from the sp_addsrvrolemember documentation: This feature is in maintenance mode and may be removed in a ...


3

The OLE DB provider is usually specified via a connection string for database applications. Connection strings are usually stored in an external application configuration file so that the driver and other attributes (server, database, etc.) can be changed without code changes. Below is are SQL Server 2008 and 2012 Native Client examples, with SQLNCLI11 being ...


3

You can close the existing log file by running sp_cycle_errorlog Which will create a new empty file After that just delete the old log files You should probably look what errors are being logged & try and fix those so the new error log doesn’t fill up so fast https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-cycle-...


3

I agree with everything @Stephen said, especially about checking what is filling up your error log. You can manage this in two different ways. By Size I am adding this code as an example ONLY. You need to adjust the numbers based on your need. I want to keep 20 files, each size 1 MB (1024 KB). USE [master] GO EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', ...


2

From comments: The answer "no" is not specific to SQL Server. Any relational database system that respects the rules of ACID is required to execute statements in one batch sequentially, or at least behave as if they do. In practice, it would take a very clever engine indeed to parallelize a batch (as opposed to an individual statement) without ...


2

Background: On a stand-alone Windows 2016 server, we had three SQL Server Express instances: 2019, 2016 and 2014. All were installed at about the same time (2019 first, then 2016 then 2014). After completing some data migration tasks, we no longer needed 2014, and uninstalled it. Problem: SQL 2016 would no longer start on Windows start-up, but started fine ...


2

32 bit OS and 64 bit Processor If the OS is 32-bit, then your applications would have to be 32-bit as well. 64 bit OS and 32 bit Processor You cannot install a 64-bit OS on a 32-bit processor, so this isn't relevant when to install SQL Server 64 bit package and when to install 32 bit package SQL Server 2008 R2 is no longer a supported platform, ...


2

Refresh Management Console The first step to perform, is to check that the SSMS (SQL Server Management Studio) is displaying the actual state of all the databases. To achieve this, you would right-click the databases branch in the Management Console and in the context menu select Refresh. Possible Outcomes Now depending on the actual state of the database ...


2

I believe the easiest way would be using LAG or LEAD functions. They allow you to accesses data from a previous or subsequent row in the same result set without the use of a self-join. But they're available starting with SQL Server 2012 (11.x). So your solution would require the a self-join like this: WITH CTE_MostRecentChange AS ( SELECT ...


2

Are you attempting this with SSMS? I followed this process using SSMS v18.4: Take DB offline Manually delete mdf and ldf files. Right click on database in SSMS and click 'Delete'. This worked just fine for me.


2

You can use the (documented) stored procedure sp_help_job to get the next execution time, however progratically capturing the results can be difficult due to the limitation around nested INSERT...EXEC calls. As an alternative, many folks use the (undocumented) stored procedure xp_sqlagent_enum_jobs which captures much of the same info. Like you, I prefer to ...


2

Any one has idea that SP update causing any issue? The security KB (KB4057113) is for the spectre / meltdown group of CPU security attacks. They have been known to cause some performance slowdowns, but nothing that drastic. Still, I thought it would be worth mentioning in an answer. You may want to install the latest operating system patches to make sure ...


2

Plase try to follow this steps: Launch the Microsoft Management Console. Select File > Add/Remove snap-in: Add a Certificates snap-in: Select “Computer account”: Use the Local Computer: With the snap-in now configured, right-click on the “Personal” certificate store folder, select All Tasks > Import: Use the certificate import wizard to select ...


2

The fx glyph indicates that an Expression exists on the item in reference. The first picture indicates there is a an expression on the Precedent Constraint between two items. Whether they're backed by Variables or hard coded won't be known until you explore. The blue color I think is that the preceding task must finish (Completion) - failure or success does ...


2

Is there a means to perform the restoration if I do not know the encryption key? No. Currently if you lose the old server you lose the database. So immediately decrypt the database and take a backup. The SQL Server service account has access to the Server Master Key, which grants access to the Database Master Key in the Master database, which in turn ...


2

You can keep the Agent enabled but disable the individual jobs on ServerB and create a dedicated job on ServerB that continuously runs (or schedule it to run every X amount of time) and checks if the current server is the Primary. If it is, then enable the other jobs with sp_update_job. You can also check which jobs are enabled with msdb.dbo.sysjobs so your ...


1

You could use CROSS APPLY to unpivot the time values into a column (along with a description of each in another column). That would give you the two new columns, EventTime and EventType, but also keep all the original columns accessible. As a result, you would be able to sort the resulting set first by StartTime, then by EventTime, while limiting the output ...


1

I think you may be neglecting to add a label to your new transformed dataset. You can ignore this if you are rigorously certain that all data in the base set conforms to your expectations, but my instinct would be to create a new dataset like so... create table events ( EventId int identity not null primary key, LogID int not ...


1

Because when your query using a non-clustered index (not covering every column), SQL Server finds your data with key lookup. This is more work than scan the entire clustered index. SQL Server has a cost-based optimization. So, it compares two execution plans and chooses the less coster. But sometimes (depends on your statistics), it does wrong chooses. Also, ...


1

Because of licensing concerns I cannot have SQL Server 2016 running but I still need to upgrade the SSRS to 2016. They require the same license. So if you can't run SQL Server 2016, you can't run SSRS 2016.


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