Updated for SQL Server 2025. SELECT ‘BuildClrVersion’ ,SERVERPROPERTY(N’BuildClrVersion’) UNION ALL SELECT ‘Collation’ ,SERVERPROPERTY(N’Collation’) UNION ALL SELECT ‘CollationID’ ,SERVERPROPERTY(N’CollationID’) UNION ALL SELECT ‘ComparisonStyle’ ,SERVERPROPERTY(N’ComparisonStyle’) UNION ALL SELECT ‘ComputerNamePhysicalNetBIOS’ ,SERVERPROPERTY(N’ComputerNamePhysicalNetBIOS’) UNION ALL SELECT ‘Edition’ ,SERVERPROPERTY(N’Edition’) UNION ALL SELECT ‘EditionID’ ,SERVERPROPERTY(N’EditionID’) UNION ALL SELECT ‘EngineEdition’ ,SERVERPROPERTY(N’EngineEdition’) UNION ALL SELECT ‘FilestreamConfiguredLevel’ ,SERVERPROPERTY(N’FilestreamConfiguredLevel’) UNION ALL…
Move TempDb Files to a New Location
23. 10. 2024
SELECT ‘ALTER DATABASE [tempdb] MODIFY FILE ‘ + CHAR(13) + ‘ (NAME = [‘ + [name] + ‘], FILENAME = ”D:\NewPath\’ + [name] + CASE [type] WHEN 1 THEN ‘.ldf’ ELSE ‘.mdf’ END + ”’);’ FROM [sys].[master_files] WHERE [database_id] = DB_ID(N’tempdb’)
Get disk free space on physical host
2. 4. 2021
Get a list of all volumes on the physical host running SQL Server instance, their allocated/free space in GB, and few more properties. This is an alternative to run EXEC xp_fixedDrives because that won’t work on a Linux-hosted instance. Note: This script can’t be executed on the Azure SQL database…
Get instance TCP port
9. 3. 2018
USE master GO xp_readerrorlog 0, 1, N’Server is listening on’, ‘any’, NULL, NULL, N’asc’ GO
Get Services and Service Accounts
9. 3. 2018
SELECT servicename, service_account FROM [sys].[dm_server_services] GO