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'


IF (SELECT COUNT(*) FROM <Table>
where [Feild1] <> '') > 0
BEGIN

DECLARE @tableHTML  NVARCHAR(MAX) ;
DECLARE @Mailsubject nvarchar(255)

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

SET @tableHTML =
  N'<html><head><style type="text/css">'+
  N'body'+
  N'{'+
  N'font: 12px arial'+
  N'}'+
  N'</style></head>'+
  N'<body>'+
  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>' +
  ISNULL(CAST ( (



  select

    case
when [Feild1] like '%somthing%'
then
  '#F78181'
   when [feild1] like '%somthingelse%'
then
  '#FF0000'

   when [feild1] like '%Anotherthing%'
then
  '#00FFFF'



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

  td = feild3, ''
, td = feild2, ''
, td = [feild1]
from Table
where [feild1] <> ''
FOR XML PATH('tr'), TYPE
  ) 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' ;

END



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>
GO

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

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 */ 

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



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
BEGIN
 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
     )
END

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'

Wednesday 20 November 2013

Removing Logshipping infomation from Primary and secondary servers

Many times, there is a need to log ship a database to a second server for upgrading or testing.

Once the logshipping is stopped and the database brought online, there can often be settings left behind.  This will cause reports, or checks to show errors.  (You all have checks in place to look for logshipped databases that are out of sync, right?)

So how can it be removed?

Here goes:-

On the primary (must be run in master)

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
@primary_database = N'AdventureWorks'
,@secondary_server = N'PRIMARY\STANDBY'
,@secondary_database = N'AdventureWorks'

On the secondary (also in master)



sp_delete_log_shipping_secondary_databaseN'AdventureWorks'


THIS WILL NOT DELETE THE DATABASE, its just bad wording on Microsoft's part.

Then if needed delete the secondary database.

Tuesday 5 November 2013

When you absolutely positively got to kill every mother ****er in the room ACCEPT NO SUBSTITUTES!!!!

Ever been in the situation where you need to restore a database, delete, take off line or detach, but "exclusive access cannot be obtained as the database is in use"?

Then here is the script for you.  Use with caution, it will end any open transactions, and is not recommended for any thing but a database that's now junk, ie to be deleted or restored over.


declare @SQLText varchar(8000) ,@spid int
select @spid = min(spid) from master..sysprocesses where dbid = db_id('<Databasename>')
while @spid is not null
begin
 select @SQLText = 'kill '+convert(varchar(5),@spid)
 select @SQLText
 execute (@SQLText)
 -- inc counter
 select @spid = min(spid) from master..sysprocesses where dbid = db_id('<databasename>') and spid > @spid
end


Remember when restoring a database over the top, its always best to not drop said database first. As this will cause the restore to have to recreate file's ect. And will make it take longer.


http://www.youtube.com/watch?v=yxQNYPYFq1c