SQLBits XI Session Voting

SQLBits XI is now open for voting for which sessions you would like to see. There are an awful lot to trawl through, and you can only vote for 10. Quite frankly I could’ve voted for 20, the range is that diverse and the sessions that good. You have to register to vote, but as the page says it’s not registering for attending, it is just registering on the site.

http://www.sqlbits.com/information/PublicSessions.aspx

If you want to register for updates the link is here:

http://sqlbits.us4.list-manage.com/subscribe?u=3e64686fd47743f72cf7ae22a&id=d86599f428

I’ll certainly be at SQL Bits in May. If you’re undecided if it is for you then I urge you to look through that list of potential sessions and you will see it is not just about T-SQL, or managing SQL Server 2012. There’s a whole choice of sessions to attend, and remember, it’s free to go!

Reports on Disk Space and Space in Files

Previously I have blogged about how to check for free space in a database at a file level; a file for a database can be very large, but the amount of data stored within the file can be very little. When initializing a file for SQL, it is generally best practice to create it to the size you think it is going to be so that auto-grow is not initialized as this can cause a loss in performance. Invariably however, databases do grow, and there is a finite amount of space to grow into, which is why in another blog I wrote bout the “xp_fixeddrives” stored proc which returns the space remaining on the disks which are attached to that instance of SQL.

So instead of checking these at random times, or finding out that we are running out of space when I am told we have run out of space, I have written a script that checks for the size of the remaining space on the disks on our SQL Servers, and if those disks hit a certain threshold then I send an email alert to the DBA Team that the space is running low. This also triggers another email that sends a file space report for each of the databases that are hosted on the disk that is reporting as running low on disk space. Some of the drives don’t host databases, but I still like to send an email as it hints that something is wrong with the server.

The job runs via SQL Agent every 15 minutes. The good thing about these reports is that, if at 2am I see that we are running low on disk space, then I just check the next email that shows how much free space is left, so then I can determine the urgency of the warning. This relies on me to know the workload of each of our databases, but at least I have an opportunity to do something about it before SQL reports errors.

I’m not going to go into how to setup database mail here, because there are plenty of good articles on how to do that on the web. If you don’t have it set up then I recommend it, as you’ll find that it is an effective way of reporting warnings to a team who need to know and can rectify and potential issues before  they actually occur.

You’ll need to make some changes to get it working (you’ll have to change the drive letters, or maybe you don’t have that many drives etc, and where it says ‘dbname’, you have to enter the name of your database) but all these are fairly straightforward.

If anyone has any suggestions to improve then let me know and I’ll update the script.

declare @N_MB_Free int
declare @O_MB_Free int
declare @M_MB_Free int
declare @L_MB_Free int
declare @B_MB_Free int
declare @C_MB_Free int
declare @E_MB_Free int
declare @FileSpaceReport bit = 0

Declare @FreeSpace table
( [Drive] char(1),
[MB_Free] int )

insert into @FreeSpace exec xp_fixeddrives

declare @reportCompletion Nvarchar(100)
SELECT @reportCompletion = GETDATE()

DECLARE
@BodyTxt03 VarChar(150),
@tableHTML NVARCHAR(MAX) ;

DECLARE

@BodyBackground NVARCHAR(50) ,
@BodyFont NVARCHAR(50),
@BodyFontColour NVARCHAR(50),
@BodyFontSize TINYINT,
@BodyText NVARCHAR(MAX),
@TableBorderWidth INT,
@TableBorderStyle NVARCHAR(20),
@TableBorderColour NVARCHAR(50),
@TablePadding INT,
@TableHeaderBackground NVARCHAR(50),
@TableHeaderFont NVARCHAR(50),
@TableHeaderFontColour NVARCHAR(50),
@TableHeaderFontSize TINYINT,
@TableDataBackground NVARCHAR(50),
@TableDataFont NVARCHAR(50),
@TableDataFontColour NVARCHAR(50) ,
@TableDataFontSize TINYINT,
@Subjecttext NVARCHAR(200)

--create table to send email

set @BodyBackground = 'light blue'
set @BodyFont = 'arial'
set @BodyFontColour = 'black'
set @BodyFontSize = 12
set @BodyText = NULL
set @TableBorderWidth= 1
set @TableBorderStyle = 'hidden'
set @TableBorderColour = 'black'
set @TablePadding = 1
set @TableHeaderBackground = 'gray'
set @TableHeaderFont = 'arial'
set @TableHeaderFontColour = 'Black'
set @TableHeaderFontSize = 12
set @TableDataBackground = 'light blue'
set @TableDataFont = 'arial'
set @TableDataFontColour = 'black'
set @TableDataFontSize = 12

SET @BodyTxt03 = 'The Report completed at '+ @reportCompletion +'. The following stored proc was executed to discover disk space: exec xp_fixeddrives'

SET @tableHTML =

N'<HEAD>'+
N'<STYLE type="text/css">'+
N'BODY { background-color:' + @BodyBackground + '; font-family:' + @BodyFont + '; font-size:' + CAST(@BodyFontSize AS NVARCHAR) + '; color:' + @BodyFontColour + ' }'+
N'TABLE { border-collapse:collapse }'+
N'TH { background-color:' + @TableHeaderBackground + '; font-size:' + CAST(@TableHeaderFontSize AS NVARCHAR) + '; color:' + @TableHeaderFontColour + ' }'+
N'TD { background-color:' + @TableDataBackground + '; font-size:' + CAST(@TableDataFontSize AS NVARCHAR) + '; color:' + @TableDataFontColour + ' }'+
N'TH, TD { border-width:' + CAST(@TableBorderWidth AS NVARCHAR) + '; border-style:' + @TableBorderStyle + '; border-color:' + @TableBorderColour + '; padding:' + CAST(@TablePadding AS NVARCHAR) + ' }'+
N'HTML { overflow: scroll }'+
N'</STYLE>'+
N'</HEAD>'+
N'<H2>Disk Size Database Report </H2>' +

N'<H4>'+ @BodyTxt03 +'</H4>' +

+ N'<table border="1" align = "Left">' +
N'<tr><th align = "left">Drive</th><th align = "left">MB Free</th>' +
CAST ( (select

td =[Drive],'',
td = [MB_Free],''

from @FreeSpace

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
+N'<table>'
+N'</tr>'
+N'</table>'
;

--check for each of the drives if the threshold has been hit. if so esend an email.
--If drive hosts adatabase we want to monitor then set the bit to 1, which triggers the 2nd part of this script
select @N_MB_Free = MB_Free from @FreeSpace where Drive = 'N'
if @N_MB_Free < 102400
BEGIN
set @Subjecttext = 'N Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @O_MB_Free = MB_Free from @FreeSpace where Drive = 'O'
if @O_MB_Free < 102400
BEGIN
set @Subjecttext = 'O Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @M_MB_Free = MB_Free from @FreeSpace where Drive = 'M'
if @M_MB_Free < 102400
BEGIN
set @Subjecttext = 'M Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @L_MB_Free = MB_Free from @FreeSpace where Drive = 'L'
if @L_MB_Free < 200000
BEGIN
set @Subjecttext = 'L Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @B_MB_Free = MB_Free from @FreeSpace where Drive = 'B'
if @B_MB_Free < 20480
BEGIN
set @Subjecttext = 'B Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @C_MB_Free = MB_Free from @FreeSpace where Drive = 'C'
if @C_MB_Free < 10240
BEGIN
set @Subjecttext = 'C Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @E_MB_Free = MB_Free from @FreeSpace where Drive = 'E'
if @E_MB_Free < 20480
BEGIN
set @Subjecttext = 'E Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

if @FileSpaceReport = 1
BEGIN
--We create table and send our dbname filegroup space report
--sending another email to show how much space we have left in the files
declare
@FileID int, @FileGroupName varchar (50), @FileSizeMB decimal(12,2), @MaxSizeMB decimal (12,2),
@SpaceUsedMB decimal(12,2), @FreeSpaceMB decimal(12,2), @FreeSpacePercentage decimal (12,2), @FileLocation nvarchar (80)

Declare @dbTableVar table(
[File ID] int
,[File Name] varchar(50)
,[File Size in MB] decimal(12,2)
,[Max Size in MB] decimal(12,2)
,[Space Used in MB] decimal(12,2)
,[Free Space in MB] decimal(12,2)
,[Free Space in %] decimal(12,2)
,[File Location] varchar (80)
)

DECLARE DB_cursor CURSOR
FOR SELECT fileID from dbname.dbo.sysfiles
OPEN DB_cursor
FETCH NEXT FROM DB_cursor into @fileID

WHILE @@FETCH_STATUS = 0
BEGIN
select @FileID = MAX (sf.FILEID)
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileGroupName = MAX(left(sf.NAME,50))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileSizeMB = MAX (convert(decimal(12,2),round(sf.size/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @MaxSizeMB = MAX (convert(decimal(12,2),round(sf.maxsize/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @SpaceUsedMB = MAX (convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FreeSpaceMB = MAX (convert(decimal(12,2),round((sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FreeSpacePercentage = MAX (convert(decimal(12,2),round(100*(sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)/(sf.size/128.000)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileLocation = MAX(left(sf.NAME,50))
from dbname.dbo.sysfiles sf
where fileid = @FileID

insert into @dbTableVar values (@FileID, @FileGroupName, @FileSizeMB, @MaxSizeMB, @SpaceUsedMB, @FreeSpaceMB, @FreeSpacePercentage, @FileLocation)

FETCH NEXT FROM db_cursor
INTO @fileID
END
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT @reportCompletion = GETDATE()
DECLARE
@BodyTxt10 VarChar(150)

-- Send Email message stating processing status
--create table to send email

set @Subjecttext = 'Filegroup Report for dbname'

SET @BodyTxt10 = 'The Report completed at '+ @reportCompletion

SET @tableHTML =

N'<HEAD>'+
N'<STYLE type="text/css">'+
N'BODY { background-color:' + @BodyBackground + '; font-family:' + @BodyFont + '; font-size:' + CAST(@BodyFontSize AS NVARCHAR) + '; color:' + @BodyFontColour + ' }'+
N'TABLE { border-collapse:collapse }'+
N'TH { background-color:' + @TableHeaderBackground + '; font-size:' + CAST(@TableHeaderFontSize AS NVARCHAR) + '; color:' + @TableHeaderFontColour + ' }'+
N'TD { background-color:' + @TableDataBackground + '; font-size:' + CAST(@TableDataFontSize AS NVARCHAR) + '; color:' + @TableDataFontColour + ' }'+
N'TH, TD { border-width:' + CAST(@TableBorderWidth AS NVARCHAR) + '; border-style:' + @TableBorderStyle + '; border-color:' + @TableBorderColour + '; padding:' + CAST(@TablePadding AS NVARCHAR) + ' }'+
N'HTML { overflow: scroll }'+
N'</STYLE>'+
N'</HEAD>'+
N'<H2>File Size Database Report </H2>' +

N'<H4>'+ @BodyTxt10 +'</H4>' +

+N'<table border="1" align = "Left">' +
N'<tr>
<th align = "left">File ID</th>
<th align = "left">File Name </th>
<th align = "left">Total File Size (MB)</th>
<th align = "left">Max File Size (MB)</th>
<th align = "left">Space Used (MB)</th>
<th align = "left">Free Space (MB)</th>
<th align = "left">Free Space (%age)</th>
<th align = "left">File Location</th>' +
CAST ( (select
td = [File ID],'',
td = [File Name],'',
td = [File Size in MB],'',
td = [Max Size in MB],'',
td = [Space Used in MB],'',
td = [Free Space in MB],'',
td = [Free Space in %],'',
td = [File Location],''

from @dbTableVar

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
+N'<table>'
+N'</tr>'
+N'</table>'
;
if @tableHTML <> ''
begin
set @tableHTML = @tableHTML
end
else
begin
set @tableHTML = 'No batches processed'
end

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';

END

List of Disks and Free Space

Recently I have been supporting our move from TFS from 2010 to TFS 2012. The database instances is stored on a server that I do not have log on rights to. During the upgrade, it became clear that one of the tables was growing at a rapid rate. This is because rows were being moved from one table to another, and there were a lot of rows to move: I came back from my Christmas break on the 31st and apparently it had been running from the 28th!

So I needed to know how much space the disk had left and whether we needed to increase it. But as I did not have access to the server I needed to check how much space we had before requesting a disk increase. By running the stored proc below I was able to check how much free space we had left on the disks.

EXEC master xp_fixeddrives

I was able to check which disk it was by running the query in my previous post

https://phoenixultd.wordpress.com/2012/12/24/sql-server-database-file-size-and-free-space/

SQLBits XI Dates Announced

SQLBits XI has been announced for the 2-4th of May 2013. Having taken place in London this year, it is moving back North to the Midlands at the East Midlands Conference Centre. Apparently there are great transport links, and being smack in the centre of England I guess it makes it convenient for everyone to get to.

If you work with SQL Server in any way, and you’ve never been to SQLBits I’d highly recommend it, because

  • it takes place over a weekend, so only requires one day away from the office
  • it’s free! So aside from room and traveling expenses, it’s a very low cost conference.
  • the quality of past speakers has been very high. SQL Experts such as Brent Ozar, Buck Woody, Ola Hallengren, Kevin Kline… the list goes on. I came away with a lot of ideas to try out at work, and certainly learnt of a few valuable resources out on the web.

Try and convince your boss that it is worth your while. Research the sessions that are taking place and outline the ones that are pertinent to your professional development; does attending help you achieve any of your objectives covered in your appraisal? If there are other people in your team who might also benefit from the knowledge learned, try to have a plan for how you intend to communicate what you have learnt to the team. At the moment they are not open for registration, they are just taking session submission, but keep checking back as registration fills up quick!

http://sqlbits.com/

 

Introduction to Hekaton Part One: MVCC

Hello folks, welcome to a post focusing on one of the future features of SQL Server. SQL Server 2012 is barely a year old, yet there is already talk on a feature that is to ship in the next major release of SQL Server. The current name of this feature is Hekaton, which is Greek for 100. I believe the reason for this name is because of the performance boost that one online gaming company experienced when working with Microsoft in implementing the technology preview of Hekaton.
Continue reading “Introduction to Hekaton Part One: MVCC”

Disabling Flight Recorder Logging in SSAS

Hello folks, I hope you all had a relaxing holiday break.

Welcome to a quick blog on SSAS optimisation. There’s a few quick wins that you can do w/r/t SSAS that can optimise the instance. One of these has to do with a feature called the FlightRecorder log. This log is by default set to ON, but really, unless you are trying to diagnose an issue, it doesn’t really provide any benefit to just having it switched on writing to the log location, particularly if the log location is on the same drive as the data location. To optimize your server, it’s safe to turn the FlightRecorder off and it is simple enough to turn back on and start collecting data again.

Continue reading “Disabling Flight Recorder Logging in SSAS”

SQL Server Database File Size and Free Space

(There is a more up to date version of this script here)
Hey folks, and welcome to my first proper blog post. One of the things that I like to monitor through my daily checks in the file size and the free space of each file within the databases. During a busy loading period, some databases can grow massively, so it’s important to keep an eye on the growth. Through the UI, SSMS only gives us the size of the files and what the max size is that they can grow to, which is clearly not very useful. Fortunately,  a quick query on the dbo.sysfiles of each databases that we want to monitor gives us some info:

utility_sys_files

but this isn’t entirely too useful. For starters the size is in KB. This makes sense as databases store data in 8kb sized files. Whilst it may be OK for a small database like this one, our data warehouses are far to big for us to find sizes in KB useful. Also, we can infer the remaining space, but again it’s not too helpful by having to figure this out for ourselves.

Continue reading “SQL Server Database File Size and Free Space”

Hello world.

Despite possibly the lamest title ever, this blog is about my experiences as an IT Professional working for a Microsoft workshop in London. Longer terms there is going to be a series on managing Analysis Services through PowerShell, but as my skills range from SSMS/SSIS/SSAS/MSBuild/WiX…. the list goes on, I plan to post whatever obscure things I come across that may help others who turn to Google for the answers.

My boss tells me that one of the skills he looks for in a developer is the ability to search the Internet for the answers, and when you look at Books Online and all the blogs, articles etc provided by Microsoft, he could well be on to something there. I frequently turn to the internet to find out how other people have solved a problem, and though it may not always be sensible to C&P off the internet, if it can lead you to solve the problem then it can only be a good thing. My hopes for this humble little blog is maybe to provide a solution to a problem you are having.