February 6, 2014 at 1:02 am
Comments posted to this topic are about the item Server_Config_Script_2012
February 21, 2014 at 3:00 am
Hello Patrick!
Some DB statuses may be cause that script fails...
:smooooth:
for example, like in this case (DB has status "Restoring"):
Msg 927, Level 14, State 2, Line 126
Database 'NNN' cannot be opened. It is in the middle of a restore.
It is possible validate, before querying.
Good luck!
March 13, 2014 at 10:31 pm
Hi Newbie!
You can modify the query to only return results for databases that have a status of online. The ERP tells you the state of the database anyway.
State_desc is the column to focus on for the database status.
May 8, 2014 at 8:31 am
I always appreciate using SQL to get the important metadata. I have a few that I use as well (some I mention here are repeats of what you mentioned):
DECLARE @tcp_port nvarchar(5);
EXEC xp_regread -- prep for getting the port number
@rootkey = 'HKEY_LOCAL_MACHINE',
@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',
@value_name = 'TcpPort',
@value = @tcp_port OUTPUT;
SELECT 'ComputerNamePhysicalNetBIOS' AS 'SERVERPROPERTY'
, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS 'VALUE' -- These two are headings
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'FilestreamConfiguredLevel', SERVERPROPERTY('FilestreamConfiguredLevel')
UNION ALL
SELECT 'FilestreamEffectiveLevel', SERVERPROPERTY('FilestreamEffectiveLevel')
UNION ALL
SELECT 'FilestreamShareName', SERVERPROPERTY('FilestreamShareName')
UNION ALL
SELECT 'HadrManagerStatus', SERVERPROPERTY('HadrManagerStatus')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsHadrEnabled', SERVERPROPERTY('IsHadrEnabled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsLocalDB', SERVERPROPERTY('IsLocalDB')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'ServiceName', SERVERPROPERTY( 'ServiceName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')
UNION ALL
SELECT 'TCP Port', @tcp_port
If your shop permits xp_cmdshell to be run, you can get some good hardware information relating to the server SQL is running on using a WMIC call. This is what I run:
Use master;
GO
--get CPU info (if VM, then VM Mfr)
EXEC xp_cmdshell 'wmic cpu get Name, MaxClockSpeed, CurrentClockSpeed, L2CacheSize, L2CacheSpeed';
EXEC xp_cmdshell 'wmic cpu get Status'; -- What is the current status of the CPU? OK?
EXEC xp_cmdshell 'wmic cpu get Manufacturer'; -- Chip manufacturer; Genuine Intel?
EXEC xp_cmdshell 'wmic cpu get ProcessorID'; -- Chip, not HW processor, serial no.
EXEC xp_cmdshell 'wmic cpu get loadpercentage'; -- utilization rate on CPU
EXEC xp_cmdshell 'wmic cpu get SystemCreationClassName'; -- 32/64 bit OS
EXEC xp_cmdshell 'wmic cpu get SystemName'
EXEC xp_cmdshell 'wmic MemoryChip get capacity';
EXEC xp_cmdshell 'wmic MemoryChip get CreationClassName';
EXEC xp_cmdshell 'wmic MemoryChip get SerialNumber, SKU'; -- Won't show on VM
EXEC xp_cmdshell 'wmic MemoryChip get Speed, Status'; -- Won't show on VM
EXEC xp_cmdshell 'wmic OS get Caption, BuildNumber'
EXEC xp_cmdshell 'wmic OS get RegisteredUser, SerialNumber '
EXEC xp_cmdshell 'wmic OS get SystemDrive, WindowsDirectory, Version'
EXEC xp_cmdshell 'wmic OS get TotalVirtualMemorySize, TotalVisibleMemorySize'
These are a few I thought I would share.
May 14, 2014 at 10:55 am
Thanks, very useful script.
A few nagging problems:
1. Exclude OFF-LINE databases (good to exclude the READ-ONLY databases as well as u cannot alter them)
2. Expand the following fields as below:
[Shrink File (Truncate free space)] VARCHAR(2000) NULL,
[Shrink file To 20percent freespace] VARCHAR(2000) NULL,
[Increase File Size by 20percent] VARCHAR(2000) NULL
3. Exclude the FILESTREAM file types from the scope (as the script is hitting the division by zero)
by planting the the WHERE clause to a qouted dynamic statement as follows:
WHERE sys.database_files.type_desc not in (''FILESTREAM'')
See corrected script below (tested against 2005 -2008 R2)
SELECT SERVERPROPERTY('MachineName') AS [MACHINE NAME],
SERVERPROPERTY('InstanceName') AS [SQL SERVICE NAME],
SERVERPROPERTY('ServerName') AS [INSTANCE NAME],
SERVERPROPERTY('ProductVersion') AS [VersionBuild],
SERVERPROPERTY('ProductLevel') AS [Current SP],
SERVERPROPERTY ('Edition') AS [Edition 32 or 64 BIT],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly],
SERVERPROPERTY('IsClustered') AS [IsClustered],
(cpu_count/hyperthread_ratio) as [physical CPUs], [cpu_count] AS [CPU CORES], hyperthread_ratio,
--(([physical_memory_kb]/1024/1024) +1) AS [RAM (GB)], -- This column exists in sql server 2012 (Denali) and above only.
OSVersion =RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ', @@VERSION))
FROM [sys].[dm_os_sys_info] with (nolock)
--SECTION NEXT : Get all drive space info on server
-- Enable the OLE automation on the server
EXEC sp_configure 'allow updates', 0
--GO
RECONFIGURE WITH OVERRIDE;
--GO
EXEC sp_configure 'show advanced options', 1;
--GO
RECONFIGURE WITH OVERRIDE;
--GO
EXEC sp_configure 'Ole Automation Procedures', 1;
--GO
RECONFIGURE WITH OVERRIDE;
--GO
-- Run the below script to get db file space info
SET NOCOUNT ON
DECLARE @dbName sysname
DECLARE @sqlstmt nvarchar(max)
DECLARE @dbContext nvarchar(512)
DECLARE @rowcnt INT
DECLARE @iterator INT,
@sortby varchar(20) = 'drive'
DECLARE DBcursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE (status & 512) = 0 /*online dbs only*/
and (status & 1024) = 0 /*read-write dbs only*/
CREATE TABLE #db_file_info (
[Drive Letter] CHAR(01) NULL,
[Database Name] SYSNAME NULL,
[File Type] VARCHAR(10) NULL,
[Logical File Name] SYSNAME NULL,
[Physical Name] VARCHAR(2000) NULL,
[File Size (MB)] FLOAT NULL,
[File Size (GB)] FLOAT NULL,
[Space Used (MB)] INT NULL,
[Space Used (GB)] FLOAT NULL,
[Space Left (MB)] FLOAT NULL,
[Space Left (GB)] FLOAT NULL,
[Percent Free Space] FLOAT NULL,
[Max File Size (MB)] FLOAT NULL,
[Max File Size (GB)] FLOAT NULL,
[Autogrowth (MB)] FLOAT NULL,
[File ID] SMALLINT NULL,
[DB State] VARCHAR(10) NULL,
[Shrink File (Truncate free space)] VARCHAR(2000) NULL,
[Shrink file To 20percent freespace] VARCHAR(2000) NULL,
[Increase File Size by 20percent] VARCHAR(2000) NULL
)
OPEN DBcursor
FETCH DBcursor INTO @dbName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @dbContext = '[' + @dbName + ']' + '.dbo.' + 'sp_executesql'
SET @sqlstmt = ' Insert into #db_file_info
SELECT LEFT(physical_name,1) AS drive_letter
, db_name()
, CASE
WHEN RIGHT(physical_name,4) = ''.mdf'' THEN ''mdf''
WHEN RIGHT(physical_name,4) = ''.ndf'' THEN ''ndf''
WHEN RIGHT(physical_name,4) = ''.ldf'' THEN ''ldf''
ELSE type_desc
END as ''File Type''
, name
, physical_name
, ROUND( (((CAST( as FLOAT) * 8192) /1024) /1024), 2) as [File Size MB]
, ROUND( ((((CAST( as FLOAT) * 8192) /1024) /1024) /1024), 2) as [File Size GB]
, ROUND( (((CAST(fileproperty(name,''SpaceUsed'') as FLOAT) * 8192) /1024) /1024), 2) as [Space Used MB]
, ROUND( ((((CAST(fileproperty(name,''SpaceUsed'') as FLOAT) * 8192) /1024) /1024) /1024), 2) as [Space Used GB]
, ROUND( (((CAST((size-fileproperty(name,''SpaceUsed'')) as FLOAT) * 8192) /1024) /1024), 2) as [Space Left MB]
, ROUND( ((((CAST((size-fileproperty(name,''SpaceUsed'')) as FLOAT) * 8192) /1024) /1024) /1024), 2) as [Space Left GB]
, ROUND(
ROUND( (((CAST((size-fileproperty(name,''SpaceUsed'')) as FLOAT) * 8192) /1024) /1024), 2) /
ROUND( (((CAST( as FLOAT) * 8192) /1024) /1024), 2)
* 100
, 2) as [percent free space]
, CASE [max_size]
WHEN -1 THEN 0
ELSE ROUND( (((CAST([max_size] as FLOAT) * 8192) /1024) /1024), 2)
END as [Max File Size (MB)]
, CASE [max_size]
WHEN -1 THEN 0
ELSE ROUND( ((((CAST([max_size] as FLOAT) * 8192) /1024) /1024) /1024), 2)
END as [Max File Size (GB)]
, ROUND( (((CAST(growth as FLOAT) * 8192) /1024) /1024), 2) as [Autogrowth (MB)]
, file_id
, state_desc as [DB State]
, ''USE ['' + DB_NAME() + '']; DBCC SHRINKFILE (N'''''' + name + '''''' , 0, TRUNCATEONLY);'' as [Shrink_FileSize_command]
, ''USE ['' + DB_NAME() + '']; DBCC SHRINKFILE (N'''''' + name + '''''' , ''
+ CAST( ROUND ( 1.2*(ROUND( (((CAST(fileproperty(name,''SpaceUsed'') as FLOAT) * 8192) /1024) /1024), 2)),0) AS VARCHAR(20))
+ '');'' as [Shrink_FileSize_command]
, ''USE [MASTER]; ALTER DATABASE ['' + DB_NAME() + ''] MODIFY FILE (NAME = '' + name + '', SIZE = ''
+ CAST( ROUND( 1.2*(ROUND( (((CAST(fileproperty(name,''SpaceUsed'') as FLOAT) * 8192) /1024) /1024), 2)), 0) AS VARCHAR(20))
+ ''MB)'' as [Increase_FileSize_by20]
FROM sys.database_files
WHERE sys.database_files.type_desc not in (''FILESTREAM'')
'
--PRINT @sqlstmt
EXEC @dbContext @sqlstmt
FETCH DBcursor INTO @dbName
END
CLOSE DBcursor
DEALLOCATE DBcursor
SET @sqlstmt = 'SELECT * FROM #db_file_info '
IF @sortby = 'drive'
SET @sqlstmt = @sqlstmt + 'ORDER BY [Drive Letter], [Database Name]'
ELSE IF @sortby = 'db'
SET @sqlstmt = @sqlstmt + 'ORDER BY [Database Name], [Percent Free Space]'
ELSE IF @sortby = 'freespace'
SET @sqlstmt = @sqlstmt + 'ORDER BY [Percent Free Space], [Database Name]'
EXEC (@sqlstmt)
DROP TABLE #db_file_info
-- Run the below script to get TOTAL space, free space, used space in MBs
SET NOCOUNT ON
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')
DROP TABLE ##_DriveSpace
IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')
DROP TABLE ##_DriveInfo
DECLARE @Result INT
, @objFSO INT
, @Drv INT
, @cDrive VARCHAR(13)
, @Size VARCHAR(50)
, @Free VARCHAR(50)
, @Volume_Label varchar(50)
CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null
)
CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Volume_Label varchar(50)
)
INSERT INTO ##_DriveSpace
EXEC master.dbo.xp_fixeddrives
-- Iterate through drive letters.
DECLARE curDriveLetters CURSOR
FOR SELECT driveletter FROM ##_DriveSpace
DECLARE @DriveLetter char(1)
OPEN curDriveLetters
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @cDrive = 'GetDrive("' + @DriveLetter + '")'
EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT
IF @Result = 0
EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT
IF @Result = 0
EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Volume_Label OUTPUT
IF @Result <> 0
EXEC sp_OADestroy @Drv
EXEC sp_OADestroy @objFSO
SET @Size = (CONVERT(BIGINT,@Size) / 1048576 )
SET @Free = (CONVERT(BIGINT,@Free) / 1048576 )
INSERT INTO ##_DriveInfo
VALUES (@DriveLetter, @Size, @Free, @Volume_Label)
END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END
CLOSE curDriveLetters
DEALLOCATE curDriveLetters
PRINT 'Drive information for server ' + @@SERVERNAME + '.'
PRINT ''
-- Produce report.
SELECT DriveLetter
, Volume_Label
, CAST(FreeSpace AS varchar(15)) as [FreeSpace MB]
, CAST( ROUND( cast(FreeSpace as FLOAT) / cast(1024 as FLOAT), 2) AS VARCHAR(15)) AS [FreeSpace GB]
, CAST((TotalSpace - FreeSpace) AS varchar(15)) AS [UsedSpace MB]
, CAST( ROUND( cast((TotalSpace - FreeSpace) as FLOAT) / cast(1024 as FLOAT), 2) AS VARCHAR(15)) AS [UsedSpace GB]
, CAST(TotalSpace AS varchar(15)) AS [TotalSpace MB]
, CAST( ROUND( cast(TotalSpace as FLOAT) / cast(1024 as FLOAT), 2) AS VARCHAR(15)) AS [TotalSpace GB]
, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]
INTO ##_DriveInfo2
FROM ##_DriveInfo
--WHERE ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) >=20
ORDER BY [DriveLetter] ASC
SELECT *
FROM ##_DriveInfo2
ORDER BY [DriveLetter] ASC
DROP TABLE ##_DriveInfo2
Thanks again.
Alex Donskoy
October 15, 2015 at 1:23 am
Thanks for the script, it's very handy. It runs on 2008 as well. I use something similar to script out various settings.
October 21, 2015 at 7:22 am
I'll save it for when we move from 2008.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply