Tuesday, 17 December 2013

Colour Code your Email

So you have set up a whole load of warnings via email,  However they are starting to look messy now.  Its hard to make out what they are saying.

How about checking them into a nice color coded HTML table.  And make it look all lovely and pretty.

Check this code out:-

DECLARE @EmailAddress varchar(1000)

select @EmailAddress = 'Mail@mail.com'

where [Feild1] <> '') > 0

DECLARE @Mailsubject nvarchar(255)

SET @Mailsubject = 'DBA - Warning its all dead Dave'

SET @tableHTML =
  N'<html><head><style type="text/css">'+
  N'font: 12px arial'+
  N'<H3>Dave Its Dead, Its Dead Dave</H3>' +
  N'<table border="1">' +
       N'<th>Feild1</th>' +
  N'<th>Feild2</th>' +
  N'<th>Feild3</th>' +
  N'</tr>' +


when [Feild1] like '%somthing%'
   when [feild1] like '%somthingelse%'

   when [feild1] like '%Anotherthing%'

  else '#FFFFFF'
  end AS [@bgcolor],

  td = feild3, ''
, td = feild2, ''
, td = [feild1]
from Table
where [feild1] <> ''
  ) AS NVARCHAR(MAX) ),'') +
  N'</table></body></html>' ;

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Email_Profile',

@recipients= @EmailAddress,
  @subject = @Mailsubject,
  @body = @tableHTML,
  @body_format = 'HTML' ;


Monday, 16 December 2013

To Shrink, Or not to Shrink, that is the question

Whether 'tis nobler in the log to suffer
The growth and space of outrageous fortune - bla bla

You see many a post debating this issue.  On the one hand you have the newer DBA's that will shrink the data file at every opportunity to try to regain space on the disk.  Only for it to grow again the next day.

Or others that will shrink the data file and then do a reindex, and make it grow again.

Its my opinion that for every day usage, the data and log file should be left alone.  To ensure that databases can be restored in a disaster, you should be taking regular backups, this would included fulls, diffs, and transnational.

 These transnational backups should keep the database log from growing too high, by allowing the space to be reused.

However ..... There are times when the data file does need shrinking.  When?

Lets say you have a very large database that get unmanageable, you might want to split that up into smaller yearly (for example) databases.  You would then need top reclaim that space at the end.

So how should this be done.  Below is a helpful script that will shrink the file stage by stage, to avoid putting a disk performance issue on the database.

USE <DB Name>

DECLARE  @vLogicalFilename VARCHAR(250) 
DECLARE  @vTargetSizeinMB INT 
DECLARE  @vCurrentSizeinMB INT 
DECLARE  @vDecrementinMB INT 

SET @vLogicalFileName = 'DataFileName' 

/* Please make sure that the Target size is achievable  
   otherwise the script will go in an infinite loop    */ 
SET @vTargetSizeinMB = 21000 

SET @vDecrementinMB = 200 /* Don't set this value to more than 2000 */ 

           FROM   sysfiles 
           WHERE  name = @vLogicalFileName) 
    SELECT @vCurrentSizeinMB = size / 128 
    FROM   sysfiles 
    WHERE  name = @vLogicalFileName 
    WHILE (@vTargetSizeinMB <= @vCurrentSizeinMB - @vDecrementinMB) 
        SELECT @vSQL = 'DBCC SHRINKFILE(' + @vLogicalFileName + ',' + Cast((@vCurrentSizeinMB - @vDecrementinMB) AS VARCHAR(10)) + ')' 
        SELECT @vSQL 
        EXEC( @vSQL) 
        SELECT @vCurrentSizeinMB = size / 128 
        FROM   sysfiles 
        WHERE  name = @vLogicalFileName 

Tuesday, 10 December 2013

Bit of PowerShell

PowerShell is becoming more and more useful in our world.

In later weeks, I will go into how I have used powershell to enhance my SQL performance knowledge.

For now a quick script to that will give you information on the processors in your SQL server.  Always handy to know the server guys have given you the box you where promised.

$property = "systemname","maxclockspeed","addressWidth",

            "numberOfCores", "NumberOfLogicalProcessors"

Get-WmiObject -class win32_processor -Property  $property |

Select-Object -Property $property 

Failing over all database mirrors to Secondy server

You box is dying fast!  You need to get your databases to DR.  No one wants to sit and right click each.  So here is the quick way:-

DECLARE @DBName sysname
Declare @SQL nvarchar(1000)
Set NoCount On;
/*Get first database where it is online and part of a mirror as principle role and is synced*/
SET @DBName = (select min (name)
     from sys.databases
     where [state] = 0
     and database_id in (select distinct(m.database_id)
          from sys.database_mirroring m
          where m.mirroring_role = 1
           And mirroring_state = 4)
while @DBName is not null
 Print @DBName

  /*Alter database to fail over to partner*/
   Set @SQL = 'Alter Database ' + quotename(@DBName) + ' Set Partner Failover;'
  Print @SQL
   Exec sp_executesql @SQL;
   /*Get next database that is online and part of a mirror as partner role that is synced*/
   set @DBName = (select min( name)
     from sys.databases
     where [state] = 0
     and database_id in (select distinct(m.database_id)
          from sys.database_mirroring m
          where m.mirroring_role = 1
           And mirroring_state = 4)
     and name > @DBName

Thursday, 5 December 2013

Index Space Used

Every need to find out how much disk space an index takes up?:-

SELECT SUM(used_page_count) * 8 AS [Size in kb]

FROM sys.indexes i

JOIN sys.dm_db_partition_stats p ON p.object_id = i.object_id AND i.index_id = p.index_id

WHERE i.object_id=OBJECT_ID('dbo.TableName') AND i.name='Indexname'