How to use parsename with SERVERPROPERTY('productversion')

Question: I'd like to use SERVERPROPERTY('productversion') for purposes of reporting on current sql server level. I'm using the SERVERPROPERTY('productversion') funcion , but am struggling with how to split the parts of the details , to make it easier for reporting. For example : 13.0.5081.1 , I'd like to split as Major = 13 Minor = 0 Build = 5081 Revision = 1 Answer: The PARSENAME function can be used to split the SERVERPROPERTY('productversion') and avoid using various string functions. PARSENAME is commonly used to split object names into there constituent parts. Here is an example: select parsename('master.dbo.sysdatabases',1) select parsename('master.dbo.sysdatabases',2) select... Read more →

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 →