SQLSaturday #405 - Buenos Aires 2015 - May 22

Finally Argentina will host the first local #SQLSaturday event so we are really excited. Here is the registration page for the ones who wants to attend 

https://www.sqlsaturday.com/405/eventhome.aspx

It will be on May 22 at Microsoft Argentina (Bouchard 710 4° Piso, Ciudad Autónoma de Buenos Aires, Buenos Aires, 1053, Argentina)


These are the tweeter info you need to know

#sqlsatBuenosAires


SAVE THE DATE !!



See you there

Monitoring Disk space using T-SQL and Powershell

UPDATE: New version with bug fixes. Now works on named instances !! (2014-10-14)

Hello everybody,

Here is a handy script that allows you to get disks information using T-SQL and Powershell. It is useful to monitor the free space on each disk so we can create a sql job to run it periodically and send out a notification when space is getting low

Here is the script

USE master

GO

SET NOCOUNT ON

declare @svrName varchar(255)

declare @sql varchar(400)

--by default it will take the current server name, we can the set the server name as well

set @svrName = cast(SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') as varchar(255))

set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace,Label | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''&''+$_.label+''*''}"'

--creating a temporary table

DECLARE @output TABLE

(line varchar(255))

--inserting disk name, total space and free space value in to temporary table

insert @output

EXEC xp_cmdshell @sql

DECLARE @DISKS TABLE(

id int identity

,[DiskName] varchar(10)

,[Capacity(MB)] bigint

,[FreeSpace(MB)] bigint

,[Label] varchar(200)

)

INSERT INTO @DISKS

select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename

      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'

      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,      (CHARINDEX('&',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'

      ,rtrim(ltrim(SUBSTRING(line,CHARINDEX('&',line)+1,      (CHARINDEX('*',line) -1)-CHARINDEX('&',line)) )) as 'Label'

from @output

where line like '[A-Z][:]%'

order by drivename

select *

,[Capacity(MB)]/1024 as [Capacity(GB)],[FreeSpace(MB)]/1024 as [FreeSpace(GB)]

,round( [FreeSpace(MB)]*100/[Capacity(MB)],2) as [% Free]

A workaround for the sp_helptext bug in SSMS 2012 RTM

Hello everyone,

If you use SQL Server Management Studio 2012 for sure you notice the behavior of sp_helptext when you get the results in grid mode and you paste it into a text editor or a new SSMS session. Basically the results have an extra line (carriage returns) between each line of code which is very annoying 

Please see the bug reported under the Microsoft Connect site. Please vote so we can have a permanent fix in the next Cumulative Update or Service Pack

http://connect.microsoft.com/SQLServer/feedback/details/668022/ssms-puts-extra-lines-in-the-result-set-in-grid-view-for-sp-helptext

Here is a quick workaround for this bug which I called sp_helptext2.

use master

go

IF EXISTS (select * from sys.procedures where name = 'sp_helptext2')

      DROP PROCEDURE dbo.sp_helptext2

GO

CREATE PROCEDURE dbo.sp_helptext2 (@ProcName NVARCHAR(256))

AS

BEGIN

      DECLARE @PROC_TABLE TABLE (X1  NVARCHAR(MAX))

      DECLARE @Proc NVARCHAR(MAX)

      DECLARE @Procedure NVARCHAR(MAX)

      DECLARE @ProcLines TABLE (PLID INT IDENTITY(1,1), Line NVARCHAR(MAX))

      SELECT @Procedure = 'SELECT DEFINITION FROM '+db_name()+'.SYS.SQL_MODULES WHERE OBJECT_ID = OBJECT_ID('''+@ProcName+''')'

     

      insert into @PROC_TABLE (X1)

            exec  (@Procedure)

     

      SELECT @Proc=X1 from @PROC_TABLE

      WHILE CHARINDEX(CHAR(13)+CHAR(10),@Proc) > 0

      BEGIN

            INSERT @ProcLines

            SELECT LEFT(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)-1)

            SELECT @Proc = SUBSTRING(@Proc,CHARINDEX(CHAR(13)+CHAR(10),@Proc)+2,LEN(@Proc))

      END

      SELECT Line FROM @ProcLines ORDER BY PLID

END

GO

Server Information - Updated version

 Hi Folks,

This is an updated version of the script used to get the server information. This one gets additional columns

Server Name

IP Address

SQL Server Build / Version and Edition (*)

Time Zone

Max Degree of Parallelism and Hyperthread Ratio

"Backup Compression" and "Optimized for AdHoc workloads" settings.

SQL Server Start Time

Number of CPUs and amount of Memory

Cluster

Database Mirroring
Server Collation

References

Backup Compression

http://technet.microsoft.com/en-us/library/bb964719.aspx

Optimized for AdHoc workloads

http://msdn.microsoft.com/en-us/library/cc645587.aspx

(*) when new service packs or cumulative updates are available, you have to update the section "SQL Version", use http://sqlserverbuilds.blogspot.com.ar/ to the the build/version data

/*

            -- !! Make sure you can see advanced options in sp_configure

            sp_configure 'show advanced options',1

            GO

            reconfigure

            GO

     

*/

-- MAIN SCRIPT

USE master

GO

SET NOCOUNT ON

DECLARE

@date datetime,

@start int,

@ver varchar(13),

@MDPconfig_value varchar(20),

@MDPrun_value varchar(20),

@Backup_Comp varchar(20),

@Adhoc varchar(20),

@sqlstart datetime,

@Mem int,

@optimal_maxdop int,

@cpu_count int,

@hyperthread_ratio int,

@IPAdd varchar(30),

@MirrorServer varchar(50),

@VersionNr varchar (200),

@SVCsecs INT,

@SVCTimeValue varchar(50)

DECLARE @CM Table

(

      [Index] int,

      Name nvarchar(1000) NOT NULL,

      Internal_Value int,

      Character_Value nvarchar(1000)

)

DECLARE @SP_CONFIGURE Table

(

      name nvarchar(1000),

      minimun int NOT NULL,

      maximun int NOT NULL,

      config_value int NOT NULL,

      run_value int NOT NULL

)

SELECT @date = getdate()

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2005',@@version)

if @start = 1

      SELECT @ver = rtrim(substring(@@version,29,13))

if @start = 0

      SELECT @ver = rtrim(substring(@@version,30,9))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008',@@version)

if @start = 1

      SELECT @ver = rtrim(substring(@@version,35,12))

SELECT @start = CHARINDEX ( 'Microsoft SQL Server 2008 R2',@@version)

if @start = 1

      SELECT @ver = rtrim(substring(@@version,38,12))

IF @@version like 'Microsoft SQL Server "Denali"%'

BEGIN

      SELECT @ver = rtrim(substring(@@version,40,11))

      SELECT rtrim(substring(@@version,40,11)),@@version

END

IF @@version like 'Microsoft SQL Server 2012%'

      SELECT @ver = rtrim(substring(@@version,charindex(' - ',@@version)+3,12))

INSERT INTO @SP_CONFIGURE exec sp_configure

-- MAX DEGREE OF PARALLELISM

SELECT @MDPconfig_value=rtrim(convert(varchar(8),config_value)) ,@MDPrun_value=rtrim(convert(varchar(8),run_value))

FROM @SP_CONFIGURE

WHERE name ='max degree of parallelism'

--BACKUP COMPRESSION

SELECT @Backup_Comp=rtrim(convert(varchar(8),run_value))

FROM @SP_CONFIGURE

WHERE name ='backup compression default'

--OPTIMIZE FOR AD HOC Workload

SELECT @Adhoc =rtrim(convert(varchar(8),run_value))

FROM @SP_CONFIGURE

WHERE name ='optimize for ad hoc workloads'

--SQL Server Service Start time

SELECT @sqlstart = create_date from sys.databases where name = 'Tempdb'

SET @SVCsecs = DATEDIFF(SECOND,@sqlstart,GETDATE())

select @SVCTimeValue =

convert(varchar(10), (@SVCsecs/86400)) + ':' +

convert(varchar(10), ((@SVCsecs%86400)/3600)) + ':'+

convert(varchar(10), (((@SVCsecs%86400)%3600)/60)) + ':'+

convert(varchar(10), (((@SVCsecs%86400)%3600)%60))

-- CPU and Memory

Insert into @CM exec xp_msver

--//REPORT

select @Mem = Internal_Value from @CM Where Name = 'PhysicalMemory'

select

@cpu_count=cpu_count,

@hyperthread_ratio=hyperthread_ratio,@optimal_maxdop=case

when cpu_count / hyperthread_ratio > 8 then 4

else CEILING((cpu_count / hyperthread_ratio)*.5)

end

from sys.dm_os_sys_info;

-- IP Address

SELECT TOP 1 @IPAdd = dec.local_net_address

FROM sys.dm_exec_connections AS dec

WHERE dec.session_id = @@SPID

--Database Mirroring

SELECT TOP 1 @MirrorServer=  mirroring_partner_instance from msdb.sys.database_mirroring (nolock)

where mirroring_partner_instance is not null

-- SQL Server Patches

/*

      You can use below site to update below list

      http://sqlserverbuilds.blogspot.com.ar/

*/

DECLARE @Builds TABLE ([Version] varchar(200),[Build] varchar(50))

INSERT INTO @Builds ([Version] ,[Build] )

VALUES

('SQL 2012 + Cumulative Update 1','11.0.2316.0'),

('SQL 2012 RTM','11.0.2100.60'),

('SQL 2008 R2 SP1 + Cumulative Update 5','10.50.2806.0'),

('SQL 2012 RC0','11.0.1750.32'),

('SQL 2012 CTP3 + QFE','11.0.1706.0'),

('SQL 2012 CTP3','11.0.1440.0'),

('SQL 2012 CTP1','11.0.1103.0'),

('SQL 2008 R2 SP1 + Cumulative Update 4','10.50.2796.0'),

('SQL 2008 R2 SP1 + Cumulative Update 3','10.50.2789.0'),

('SQL 2008 R2 SP1 + Cumulative Update 2','10.50.2772.0'),

('SQL 2008 R2 SP1 + Cumulative Update 1','10.50.2769.0'),

('SQL 2008 R2 + SP1','10.50.2500.0'),

('SQL 2008 R2 + SP1 CTP','10.50.2418.0'),

('SQL 2008 R2 + Cumulative Update 9','10.50.1804.0'),

('SQL 2008 R2 + Cumulative Update 8','10.50.1797.0'),

('SQL 2008 R2 + Q2494086','10.50.1790.0'),

('SQL 2008 R2 + Cumulative Update 7','10.50.1777.0'),

('SQL 2008 R2 + Cumulative Update 6','10.50.1765.0'),

('SQL 2008 R2 + Cumulative Update 5','10.50.1753.0'),

('SQL 2008 R2 + Cumulative Update 4','10.50.1746.0'),

('SQL 2008 R2 + Cumulative Update 3','10.50.1734.0'),

('SQL 2008 R2 + Cumulative Update 2','10.50.1720.0'),

('SQL 2008 R2 + Cumulative Update 1','10.50.1702.0'),

('SQL 2008 RTM','10.50.1600.1'),

('SQL 2008 RC 0','10.50.1450.3'),

('SQL 2008 + Service Pack 3','10.0.5500.0'),

('SQL 2008 SP2 + Cumulative Update 6','10.0.4321.0'),

('SQL 2008 SP2 + Cumulative Update 5','10.0.4316.0'),

('SQL 2008 SP2 + Q2494094','10.0.4311.0'),

('SQL 2008 + Cumulative Update 4 for SP2','10.0.4285.0'),

('SQL 2008 + Cumulative Update 3 for SP2','10.0.4279.0'),

('SQL 2008 + Cumulative Update 2 for SP2','10.0.4272.0'),

('SQL 2008 + Cumulative Update 1 for SP2','10.0.4266.0'),

('SQL 2008 SP2 + Q2494089','10.0.4064.0'),

('SQL 2008 + Service Pack 2','10.0.4000.0'),

('SQL 2008 + Cumulative Update 16 for SP1','10.0.2850.0'),

('SQL 2008 + Cumulative Update 15 for SP1','10.0.2847.0'),

('SQL 2008 SP1 + Q2494100','10.0.2841.0'),

('SQL 2008 + Cumulative Update 14 for SP1','10.0.2821.0'),

('SQL 2008 + Cumulative Update 13 for SP1','10.0.2816.0'),

('SQL 2008 + Cumulative Update 12 for SP1','10.0.2808.0'),

('SQL 2008 + Q2413738','10.0.2804.0'),

('SQL 2008 + Cumulative Update 10 for SP1','10.0.2799.0'),

('SQL 2008 + Cumulative Update 9 for SP1','10.0.2789.0'),

('SQL 2008 + Cumulative Update 8 for SP1','10.0.2775.0'),

('SQL 2008 + Cumulative Update 7 for SP1','10.0.2766.0'),

('SQL 2008 + Q978839','10.0.2760.0'),

('SQL 2008 SP1 + Q978791','10.0.2758.0'),

('SQL 2008 + Cumulative Update 6 for SP1','10.0.2757.0'),

('SQL 2008 + Cumulative Update 5 for SP1','10.0.2746.0'),

('SQL 2008 + Q976761','10.0.2740.0'),

('SQL 2008 + Cumulative Update 4 for SP1','10.0.2734.0'),

('SQL 2008 + Cumulative Update 3 for SP1','10.0.2723.0'),

('SQL 2008 + Cumulative Update 2 for SP1','10.0.2714.0'),

('SQL 2008 + Q970507','10.0.2712.0'),

('SQL 2008 + Cumulative Update 1 for SP1','10.0.2710.0'),

('SQL 2008 + Service Pack 1 GDR June 14, 2011','10.0.2573.0'),

('SQL 2008 + Service Pack 1','10.0.2531.0'),

('SQL 2008 + Cumulative Update 10','10.0.1835.0'),

('SQL 2008 + Cumulative Update 9','10.0.1828.0'),

('SQL 2008 + Cumulative Update 8','10.0.1823.0'),

('SQL 2008 + Q973601','10.0.1818.0'),

('SQL 2008 + Cumulative Update 6','10.0.1812.0'),

('SQL 2008 + Cumulative Update 5','10.0.1806.0'),

('SQL 2008 + Cumulative Update 4','10.0.1798.0'),

('SQL 2008 + Cumulative Update 3','10.0.1787.0'),

('SQL 2008 + Q958186 (Cumulative HF2, available byrequest.)','10.0.1779.0'),

('SQL 2008 + Q958611','10.0.1771.0'),

('SQL 2008 + Q956717','10.0.1763.0'),

('SQL 2008 + Q957387','10.0.1755.0'),

('SQL 2008 + Q956718','10.0.1750.0'),

('SQL 2008 RTM','10.0.1600.22.0'),

('SQL 2008 February CTP','10.0.1300.13'),

('SQL 2008 July CTP (requires Virtual Server 2005 R2),','10.0.1049.14'),

('SQL 2008 June CTP','10.0.1019.17')

SELECT @VersionNr = [Version] From @Builds where [Build] = @ver

---------------------------------------------------------------------------------------

--//REPORT

---------------------------------------------------------------------------------------

SELECT

@@servername as Servername

,@IPAdd as [IP Address]

,@ver as [SQL Build]

,@VersionNr as [SQL Version]

,getdate() as [Current Date/Time]

,DATEDIFF(HH,GETUTCDATE(),GETDATE()) as [Time Zone]

,cast(@MDPconfig_value as varchar(1))+' / '+ cast(@MDPrun_value as varchar(1)) +' / '+ cast(@optimal_maxdop as varchar(2))as [MDP Cfg/Run/Optimal]

,@hyperthread_ratio as [Hyperthread Ratio]

,@backup_comp as [Backup Compression]

,@Adhoc as [Opt for Ad hoc workload]

,convert(varchar(30),@sqlstart,120)+ ' / '+@SVCTimeValue as [SQL Svc Start / DD:HH:MM:SS]
,SERVERPROPERTY('collation') AS SQLServerCollation

,serverproperty('Edition') as [SQL Edition]

,case serverproperty('IsClustered') when 0 THEN 'NO' when 1 THEN 'YES' end as IsCluster

,@cpu_count as [CPU Count]

,@Mem as [Memory (MB)]

,@MirrorServer as [DBMirror Server]