How to convert sys.master_files size column into GB

Question: I need to convert the sys.master_files size column into GB. Currently when I run select name,size from sys.master_files I get back the file size as KB. I've tried doing something - size/1024/1024 - but I'm getting incorrect results. How do I get the current sys.master_files size unit into the GB format? Answer: The size column in the sys.master_files is an int data type and is represented as 8-KB pages. In the example you've given above - you're not taking into account the 8-KB pages. You need to multiply the size value by 8. i.e size * 8 Find an... Read more →

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 →