2016-10

SSDT And Pre-Model Scripts Part 2: Execute Pre-Model Scripts Using Octopus Deploy

Hello! Part one of this post concerned the notion of SSDT and Pre-Model Scripts. This post relates to an actual implementation of executing a SQL script using Octopus Deploy. My pull request on Github was approved, so my step template can be exported from the Octopus Library. The only part that I feel requires some explanation is the SQL Scripts and the option to select a previous step. If you’ve read part one of this post you’ll know that you keep the pre-model scripts in a folder in the database solution.

2016-10-25

The Alternative to Pre-Model Scripts

Hello!

Two of my most recent posts have concerned pre-model scripts; those scripts that need to be run before the dacpac is compared against the production database. These are sometimes necessary. And this necessity is usually because SSDT may not produce a script that is optimal. One such example is an index creation: no index is created “ONLINE”, and this can be a problem if the database being deployed is online during this operation. And can be even worse if the table is particularly large.

In respect to the SSDT team, I can see why this is the case: some versions of SQL have the online index feature, some don’t. So one solution may be to write a pre-model script that will create an index with the online operation included. And while there’s nothing wrong with this, there is an alternative: deployment contributors.

2016-10-23

2016-02

SQL Server Database File Size and Free Space Revisited

I recently had a comment posted to one of my first articles I wrote way back in 2012 concerning adding a column that detailed the used space as a %age. So without any further ado, here is an updated version of a script originally blogged about here. select sf.FILEID AS [File ID], [File Size in MB] = convert(decimal(12,2),round(sf.size/128.000,2)), [Max Size in MB] = convert(decimal(12,2),round(sf.maxsize/128.000,2)), [Space Used in MB] = convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)), [Free Space in MB] = convert(decimal(12,2),round((sf.

2016-02-06

2014-09

When NOLOCK Locks

A real quickie this evening, and it’s something I discovered yesterday the hard way: using NOLOCK table hints can cause blocking. Yep, you read that right. Given the fact that the table hint is called NOLOCK it’s counter intuitive to what you’d instinctively think, but it’s fact and by design and also, there’s nothing wrong with it.

2014-09-08

2013-09

Extract DacPac Using Command Line

When extracting Dacpacs it’s quite straightforward to go through the UI and extract, however what if you want to do this for more than one database, or automate the process for a build? The sqlpackage.exe enables us to extract a dacpac in silent mode. Below is the command, and I will go through each parameter: "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /action:Extract /OverwriteFiles:False /tf:"C:\Users\richard.lee\Documents\dacpacs\dbname_Baseline.dacpac" /SourceConnectionString:"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks;Data Source=SQLA" Some you need to be wary of:

2013-09-19

2013-05

View Partitions in Database Partition Schema

I feel like I haven’t posted in a while despite posting only yesterday. This is because I wrote all the WiX posts in a batch and have them on auto release (definitely a good idea to write all the posts for s a series and have them done before anything else.)

2013-05-16

2013-01

SQL Server 2012 Unknown Unknowns

Last year saw the release of both SQL Server 2012 and Windows Server 2012. Both bought significant updates, including changes to High Availability and Disaster Recovery for SQL Server 2012, and a focus on PowerShell and Server Core features for Windows Server 2012, not to mention improvements to Hyper V, which could lead my company to virtualize the only boxes in our company that are still physical, namely the database and SSAS boxes. So before we make this leap it’s important to hear from those who have experience and can pass it on. Apart from reading MSDN and trying out a few labs, there are a couple of user groups that I will be attending:

2013-01-31

SQL Bits XI Registration is Now Open

Hi all, registration to attend SQL Bits is now open. http://www.regonline.com/Register/Checkin.aspx?EventID=1163518 There’s a 600 capacity limit and they anticipate it to fill rather quickly. More details can be found here: http://sqlbits.com/ Hope to see you there!

2013-01-11

2012-12

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

2012-12-31

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, 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.

2012-12-24

bzzzt!

What's Going On?