How to find computed columns on a SQL Server table

Question: I'm having a problem with an index rebuild job , which is failing on tables with computed columns. I've read ALTER INDEX failed due to QUOTED IDENTIFIER set to OFF which details a solution for situations of computed columns, xml data type and LOB data types. How can I find the computed columns on sql tables? Answer: These are two ways in which you can identify the computed columns. One method is through the sys.columns and the other through sys.computed_columns . sys.computed_columns has the benefit of not requiring you to use the "is_computed = 1" predicate. Method 1 -... Read more →

Alter database compatibility on all databases in a SQL Server Instance

Question: I have a SQL Server Instance with 102 databases at a database compatibility level of SQL Server 2012 (110). I've recently migrated all the databases onto a SQL Server 2016 instance - and the database compatibility level is still at 110. Is there a scripted way I can change the database compatibility level of all the user databases to SQL Server 2016 (130), rather than having to hand crank for every database? Answer: Hi - this is a common problem particularly during SQL Server migration work , which you could solve by generating a script with all the ALTER... Read more →

Top 10 most popular SQL Server articles for 2017

2017 was a great year for sqlserver-dba.com , thanks for all the feedback and the great discussions I've had through the sql server community. These are the 10 most read articles on sqlserver-dba.com for 2017 SQL Server – DBA Scripts Powershell Script Library for DBA (SQL Server DBA) Calculate sql row size (SQL Server DBA) How to fix System.OutOfMemoryException when using SSMS (SQL ... SQL Server – Pageiolatch_sh and how to reduce it (SQL Server DBA) SQL Server RUNNABLE – SUSPENDED – RUNNING status model ... Top 4 Best SQL Editors (SQL Server DBA) SQL Server – Find queries causing... Read more →

How to change SQL Server 2016 Database default locations for data and log files

The best way to set the database default locations for data and logs is when you first install the SQL Server Instance. They can be set through the configuration.ini file. ; Default directory for the Database Engine user databases. SQLUSERDBDIR="E:\MSSQLSERVER\MSSQL$MYINST\Data" ; Default directory for the Database Engine user database logs. SQLUSERDBLOGDIR="F:\MSSQLSERVER\MSSQL$MYINST\Data" But there are occasions where there’s a requirement to adjust the default locations after the SQL Server Instance is built. You can either write out the code , which requires references to xp_instance_regwrite or you can use the GUI to generate the code for you . This will save... Read more →

How to find SQL Server Instance Default Path and Instance Default Log Path

Finding the SQL Server Instance Default Path and the Instance Default Log Path in the old days (pre SQL Server 2012) was generally limited to either checking SSMS for the Instance Default Path or Log Path , and you could also use xp_regread to extract the information from the Registry. To read more about these options read SQL Server - Find Default Data location and Default Log Location .... For the last few versions of SQL Server similar information can be extracted using the SERVERPROPERTY functionality. This has introduced the benefit of being able to programmatically extract the details and... Read more →