338

A Shorter Answer: You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough). If it is a recovery model issue, the simple answer could be ...


327

I experienced this problem as well with SQL Server 2017 Developer and it appears to be just bad planning on the part of the SQL Server installation package people. The problem is that Visual Studio 2017 installs the Microsoft Visual C++ 2017 Redistributable (x86) and (x64) and the SQL Server installation tries to install the Microsoft Visual C++ 2015 ...


216

I once had a table and it was shiny and beautiful. It held all the financial transactions for an organization. And then we started loading data into it. In the current month, they can state and restate values as often as they want. In the final 10 days of a month, they'd restate numbers -> run ETL processing -> review reports several times a day. Once the ...


174

You don't have to stop the SQL Server service to move database files, but you do have to take the specific database offline. This is because you can't move files while they're being accessed and taking the database offline stops the files from being used by the SQL Server application. The process to move them is fairly simple. Detach/Attach was already ...


152

During parsing, SQL Server calls sqllang!DecodeCompOp to determine the type of comparison operator present: This occurs well before anything in the optimizer gets involved. From Comparison Operators (Transact-SQL) Tracing the code using a debugger and public symbols*, sqllang!DecodeCompOp returns a value in register eax** as follows: ╔════╦══════╗...


140

This is likely caused by the restore script adding the WITH NORECOVERY parameter, to make the database ready for a transaction log apply after the restore. The database is now waiting for the latest transaction log file. You can either: Apply the latest transaction log, using RESTORE LOG database_name FROM backup_device WITH RECOVERY; ... or Restore the ...


134

Brent here (the guy you're referring to in the question). The reason I tell you not to add tbl to the front of your table names is the same reason I'd say not to add child to the front of your child's name. You don't call them childJohn and childJane. Not only does it not add any value, they may not be a child later in life - and your objects may later ...


130

This will list all "most recent" restores for each database on your server: WITH LastRestores AS ( SELECT DatabaseName = [d].[name] , [d].[create_date] , [d].[compatibility_level] , [d].[collation_name] , r.*, RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC) FROM master.sys.databases d LEFT OUTER JOIN ...


128

I just overlaid the Microsoft.VisualStudio.Shell.Interop.8.0.dll in C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\PublicAssemblies with the copy from ..\PrivateAssemblies\Interop and the IDE opens. (Thanks to Mitch for discovering the offending DLL name.) This looks like a popular Q & A today, so I'm glad to know this ...


124

There is another way, which I now use in preference to the runas /netonly method. You can add the credentials to your profile in Windows using the Credential Manager found in the Windows control panel. Open Credential Manager Click "Add A Windows Credential" Populate the "internet or network address" field with the name and port number ...


121

That is expected and documented behavior: Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a ...


118

This is a very subjective argument, but here is my take: the tbl prefix is useless. How many scenarios are you looking at code and you can't tell if something's a table or something else? What value does tbl add except that when you look at a list of tables in Object Explorer, you have to do more work to find the one(s) you're looking for? Some people ...


118

Since I'm not really satisfied with any of the answers over on Stack Overflow, including the most heavily up-voted suggestion, and because there are a few things I'd like to address that Mike's answer does not, I thought I would provide my input here too. I placed a copy of this answer there as well. Making a log file smaller should really be reserved for ...


114

Is a bad practice to create a transaction always? It depends on what context you are talking here. If it is an update, then I would highly recommend using TRANSACTIONS explicitly. If it is a SELECT then NO (explicitly). But wait there is more to understand first : Everything in sql server is contained in a transaction. When the session option ...


113

In addition to the points in other answers, here are some key differences between the two. Note: The error messages are from SQL Server 2012. Errors Violation of a unique constraint returns error 2627. Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'P1U_pk'. Cannot insert duplicate key in object 'dbo.P1U'. The duplicate key value ...


108

The MERGE statement has a complex syntax and an even more complex implementation, but essentially the idea is to join two tables, filter down to rows that need to be changed (inserted, updated, or deleted), and then to perform the requested changes. Given the following sample data: DECLARE @CategoryItem AS TABLE ( CategoryId integer NOT NULL, ...


96

Your SQL Server is installed as named instance, so first of all try connecting to your server using the following server name: IP Address\SQLEXPRESS. When you install SQL Server as named instance it uses dynamic TCP/IP ports by default, so it is not possible to connect to it whitout specifying instance name (just IP address). If you need to connect to your ...


93

You need CROSS APPLY not join. The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table. select f.ID, f.Desc, u.Field1, u.Field2 from Foo f Cross apply ut_FooFunc(f.ID, 1) u where f.SomeCriterion = ...


93

Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012. A Longer Answer on SQL Server Upgrades/Migrations So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said ...


90

Dear [your name here]! Oh no, I'm sorry to hear that! Let's start with some basics to get you fixed up in a jiffy. The thing you're running into is called Parameter Sniffing It's a way out wiggy weird problem. The name rolls right off the tongue. Like the German word for squirrel. And it's usually your friend. When a query hits your server, a plan has ...


86

Yes. Failing to specify WITH SCHEMABINDING means SQL Server skips the detailed checks it normally makes on the function body. It simply marks the function as accessing data (as mentioned in the link given in the question). This is a performance optimization. If it did not make this assumption, SQL Server would have to perform the detailed checks on every ...


83

You are attempting to pass Windows credentials in plain text from the connection string of an application. This simply isn't how Windows authentication works, and largely defeats the purpose. You also can't just create the same username with the same password in your own domain, and expect that to magically work. Domain name is still part of the validation -...


80

I've worked on SQL Servers with 8 to 10 thousand databases on a single instance. It's not pretty. Restarting the server can take as long as an hour or more. Think about the recovery process for 10,000 databases. You cannot use SQL Server Management Studio to reliably locate a database in the Object Explorer. Backups are a nightmare, since for ...


77

Short version: seek is much better Less short version: seek is generally much better, but a great many seeks (caused by bad query design with nasty correlated sub-queries for instance, or because you are making many queries in a cursor operation or other loop) can be worse than a scan, especially if your query may end up returning data from most of the rows ...


76

This is a decision of the cost based optimiser. The estimated costs used in this choice are incorrect as it assumes statistical independence between values in different columns. It is similar to the issue described in Row Goals Gone Rogue where the even and odd numbers are negatively correlated. It is easy to reproduce. CREATE TABLE dbo.animal( id ...


75

I think the following query will at least get you quite close. It makes use of a DMV that was introduced in SQL Server 2014: sys.dm_exec_query_profiles (and thanks to Martin Smith for introducing it to me via this related DBA.StackExchange Answer: Progress of SELECT INTO statement :-). Please note: !! You will need to add SET STATISTICS PROFILE ON; or SET ...


75

If you want the shiny new 2016 monthly releases of Management Studio, which include a handy check for updates mechanism, negates the need for a full setup program and download (since it only retrieves the components you need), as well as a release cycle completely independent from SQL Server, go here (and you can read about it here and here). If you use ...


73

Let's start with the basic scenario. If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP. First, let's consider the whole set from Production.TransactionHistory for a particular ProductID: SELECT h.TransactionID, h.ProductID, h.TransactionDate FROM Production.TransactionHistory h WHERE h.ProductID = 800; ...


71

To quote Joe Celko (not only can you find this reference all over the web and in his Wikipedia entry, but you will even see it on T-shirts at some conferences): Rows are not records. A lot of people point him out as a pedantic jerk who just likes to humble and verbally abuse newbies, and I will admit that is how he comes across. But I have also met him ...


69

Historically, it has been recommended not to use the default ports for connections to SQL Server, as part of security best practice. Which was asinine then and still asinine now. Security through arguably obscurity isn't security at all. Is this advice still relevant IMHO it was never relevant. It was required for some compliance purposes because the ...


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