Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for August 20th, 2012

server information include port #

Posted by Simon Cho on 08/20/2012

http://smartypeeps.blogspot.com/2006/11/t-sql-script-to-find-nw-port-of-sql.html

declare @Server as varchar(128) 
declare @KeyToInterogate as varchar(200)
declare @Version as varchar (512) 
declare @PortNumber as varchar(8) 

set @Server = @@ServerName 
set @Version = left(@@Version, 38) 
set @KeyToInterogate = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'

if charindex('\',@@ServerName) > 0
begin
set @KeyToInterogate = 'SOFTWARE\Microsoft\Microsoft SQL Server\'
set @KeyToInterogate = @KeyToInterogate + substring(@@ServerName,charindex('\',@@ServerName) + 1,len(@@ServerName) - charindex('\',@@ServerName)) 
set @KeyToInterogate = @KeyToInterogate + '\MSSQLServer\SuperSocketNetLib\Tcp'
end

exec xp_regread 
@rootkey = 'HKEY_LOCAL_MACHINE', 
@key = @KeyToInterogate, 
@value_name = 'TcpPort', 
@value = @PortNumber output 

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'ProductName'

exec master..xp_regread
                     'HKEY_LOCAL_MACHINE'
                    , 'SOFTWARE\Microsoft\Windows NT\CurrentVersion'
                    , 'CSDVersion'
                    
SELECT @@servername AS hostname
, SERVERPROPERTY('Edition') AS Edition
, ISNULL(SERVERPROPERTY('InstanceName'),'') AS InstanceName
, SERVERPROPERTY('MachineName') AS MachineName
, SERVERPROPERTY('ProductVersion') AS ProductVersion
, SERVERPROPERTY('ProductLevel') as ProductLevel 
, @@VERSION
, CASE 
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '8.0%' THEN '2000'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '9.0%' THEN '2005'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '10.0%' THEN '2008'
WHEN CONVERT(VARCHAR(255),SERVERPROPERTY('ProductVersion')) LIKE '10.5%' THEN '2008 R2'
END
, cast(@PortNumber as varchar)
, (select top 1 local_net_address from sys.dm_exec_connections
where local_net_address is not null) as IP_address

SELECT
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('EngineEdition') AS EngineEdition;

Posted in Common | Tagged: , , | Leave a Comment »

Backup Size check including compression backup

Posted by Simon Cho on 08/20/2012

use msdb

--select top 10 * from backupfile
--select top 10 * from backupmediaset
--select top 10 * from backupmediafamily

declare @sql nvarchar(4000)
if exists(select OBJECT_NAME(id) from syscolumns where name='compressed_backup_size') begin
	set @sql = '
	select top 1 @@servername as servername, case when sum(compressed_backup_size)convert(varchar(8),getdate()-30,112)
	group by convert(varchar(8), backup_start_date, 112)
	order by sum(backup_size) desc
	'
end else begin
	set @sql = '
	select top 1 @@servername as servername, ''N'' compressed_backup
	, convert(numeric(10,2),sum(backup_size/1024/1024/1024.0)) as [backup_size(GB)] from msdb.dbo.backupset with(nolock)
	where backup_start_date>convert(varchar(8),getdate()-30,112)
	group by convert(varchar(8), backup_start_date, 112)
	order by sum(backup_size) desc
	'
end

exec(@sql)

Posted in Common | Tagged: , , | Leave a Comment »