Skip to main content

Posts

Showing posts from 2010

Happy New Year

Well it’s Friday again and another big day. Hopefully you all had a great Christmas and as we move to a New Year, the end of 2010 and start 2011 I’d like to wish everyone Happy and prosperous New Year.U2N9AD334FTK

SELECT * FROM SQLBLOGGERS WHERE LOCATION = ‘UK’

I published a post yesterday where I listed my top 5 bloggers from the worldwide SQL community for 2010. The long and short of it, these were the people whose blog’s I had read the most throughout the year. There are many other great blogs and bloggers out there that I read regularly. When that post published and fed out to twitter it prompted a few people, whose blogs I do follow and value, to give me a bit of stick . Pretending to be  all hurt and upset. I won’t name any names (John Sansom Blog|@johnsansom) but it did get me thinking.There are many great bloggers based here in the UK several of which were recently nominated in the sqlserverpedia.com annual blog awards for 2010 (a couple of us won in our categories too).  I realised that although I read many of these UK based blogs and several were considered for the top 5 bloggers 2010 post, none of them made my list! So having thought about it I decided to make my own UK specific list. I was going to go for the top 5 approach, but …

SELECT TOP 5 * FROM BLOGGERS

Hopefully my title gives away what this post is about. This my list of the top five people or more precisely their blogs that have made most use out in the year gone, I am not going to put them in any specific order they all add to my knowledge in different ways:Brent Ozar(Blog|@BrentO) Paul Randal (Blog|@PaulRandal) Steve Jones (Blog|@Way0utwest) Grant Fritchey (Blog|@gfritchey) Andy Warren (Blog|@SQLAndy)Also it is worth noting that besides these guys there are many other great blogs and bloggers out there that I read and fully appreciate, I needed a cut-off that wasn’t that deep but if you do blog and write keep going, keep writing and keep blogging.Who would be in your top five, would it differ from mine? let me know in the comments below.

Merry Christmas

This is just a short post for a Friday and not just any old Friday either, Santa is on his way :-) I would just like to wish everybody a very Merry Christmas.

SQL Bits 8

It seems the dates for the next SQLBITS have been released will take between April the 7th and April 9th 2011.Location: The Grand Hotel, Brighton

Turn Off AutoShrink!

This is kind of a follow up to a post I published last week regarding autogrowth. A forum poster said they had turned on AutoGrow and they were using autogrow as way of managing the growth of their databases – No! This post titled “Should I have AutoGrowth enabled on my SQL Server Database?” addresses why you should have autogrow enabled and why you should only use it as a fail back. Instead monitor and grow your databases manually.The same forum poster also said “that just in case the database grew too much and needed space released back to Operating System, he had enabled AutoShrink.” No! Why?This is the short answer: Shrinking databases is generally a bad thing to do it can will cause fragmentation in your indexes, which in turn can be bad for performance. Even though you may *occasionally* need to shrink your database, Shrinking a database should not be  part of your  regular  database maintenance routines. If shrink is used at all it should be used very very infrequently. Letting…

Let it Snow, Let it Snow, Let it Snow!

I don’t usually post on my blog on the weekend, but this weekend, the weekend before a Christmas is a little different. Where I live in South Wales in the UK we don’t usually get a lot of snow, but since Thursday this week we have had the best part of a foot of snow fall and where my parents live, which is only about fifteen miles away, they have had more like a foot and a half of the white stuff. That is a lot of snow for us. Yesterday, Friday, ground pretty much to a halt. I heard off several friends who had to cancel their office Christmas parties as a result of the weather.But it  does make you feel like it’s Christmas and I like Christmas.My two golden retrievers, are particularly fond of the snow, I guess their double fur coats come in pretty useful this time of year. Anyway I just thought I’d post a couple of pictures:

INSERT INTO Reading2011 VALUES…

Last Friday I posted a short note on what books I have read this year past, so this Friday keeping up with the same theme and the fact we are quickly approaching the end of the year I thought I would write a post  on what books I want to read next year:SQL Server 2008 Query Performance Tuning Distilled (Expert's Voice in SQL Server) by Grant Fritchey  After recommendation by Joseph Sack (Blog|@josephsack) last week SQL Antipatterns: Avoiding the Pitfalls of Database Programming (Pragmatic Programmers)by Bill Karwin I don’t have a third choice, please can you help me by leaving a recommendation in the comments? That would be fabulous.

Should I have AutoGrowth enabled on my SQL Server Database

I was reading a few forum posts yesterday where a few people were complaining that the transaction log had grown during index maintenance and they were asking:
If they should have autogrowth enabled?
If they should restrict the size of the file?I think autogrowth should be enabled on all databases but it should only be used as a fall back, or fail safe. The DBA should be sizing the database appropriately right up front and then monitoring the database size and space used to decide if and when to grow it again manually, and always increase size for reasonable medium term use.  I think autogrowth is fine as a fail safe in case you miss something but if you rely on it to size your database you will find it kicks in at the most inappropriate times, which you have no control over, which can affect performance. When setting up autogrowth I like to set the files to grow by fixed amounts in megabytes, and not let the files grow by a percentage of their current size. This is especially importan…

T-SQL Tuesday #13 - What the Business Says Is Not What the Business Wants

So this month’s blog party is being held by non-other than Steve Jones (blog|@Way0utwest) of SQL Server Central fame. This month’s topic is all about the business, your customers, clients etc asking for something but not really knowing what they are asking for. Or  in my interpretation the business, client, customer, telling you the technology or methodology or tool to use instead of what really they should be telling  you…what the business wants. Steve’s example of in his post sums in it up perfectly. I have many examples to draw on here, I guess the one that springs to mind the most was a client that told me they wanted to ‘Shrink the Database’ as part of their maintenance routines. When I asked why? that’s self defeating. I was told that the databases were growing uncontrollably and this needed to be included in order to keep the systems up and running and to avoid eating up all available disk space. Smelling a rat I decided to do a little investigation of my own and I found that t…

SELECT TOP 3 SQLBOOKS FROM 2010

This is just a short post for a Friday. This is my list of the Top 3 SQL Server books from the year[ish]:SQL Server MVP Deep DivesMicrosoft SQL Server 2008 Internals (Pro - Developer)Professional SQL Server 2008 Internals and TroubleshootingGranted some of these book were published earlier than 2010 but not by much and these have been what I have been reading this year. If you have a list of good books let us all know about them in the comments

British Heart Foundation – Christmas Event

CHRISTMAS AT THE MUSICALS Originally posted on Senghenydd Cricket Club website.Join The Romilly Singers as they entertain you through a selection of musicals through the ages on Sunday December 12th at St Peter's Hall, Roath, 6.30pm. Tickets are £20 and include a two course dinner plus entertainment. To purchase tickets contact St Peter's Hall on 029 20494425 or Sharon Owen 029 20382408 or by email owensh@bhf.org.uk.

Blog – Domain Changes Update

As I mentioned in an earlier post I have recently moved my blog to a new domain name. This is something I have been thinking about for while but in truth and with hindsight I did not manage the transition all that well. I let the old domain and hosting run down to expiration before getting my new one up and running which meant that I lost control of the old domain and I wasn’t able to use some of Google’s ‘Change of Address’ features to help the transition of the old domain to the new name. BUT we live and learn and not all is lost. My new domain has been picked up in the Google crawl already and although it is very likely to be six months to recover in terms of Search Engine rankings and SEO traffic I feel the benefit of the change will benefit my brand in the long run. So although is not quite like starting again I do feel like it has been a step backwards but sometimes a step backwards can help you go forwards. This is an opportunity for me to learn a few things and take my blog an…

My Weekend

We are now well into December and Christmas is fast approaching, I have most, but not all of my shopping done, so a few more things to get before the Party season starts in earnest. Which for me is this Friday :-)  It was a busy week for me last week and I only managed to publish one post. Last week and this weekend gone has been very busy for me, I was on driving duty Friday night taking Mrs Ellis to and from her Christmas Party, Saturday was the day to decorate the house ready for Christmas, this is one of Mrs Ellis’ favourite days of the year. The dogs needed lots of long W.A.L.K.I..E.S….They can tell when I type it these days, never mind say it so shhh! don’t say it again. This is Seth posing for a Picture:Sunday was a little more restful and in between all of that I tried to take in as much of the 2nd #ashes test match as possible, as well as reading my new copy of ProBlogger: Secrets for Blogging Your Way to a Six-Figure Income. I will post more on this tomorrow but for now that…

Digg

I’m currently doing some experiments with my blog and trying out digg.com. In order to do that I need to paste this key <!--261640fe8cc94a1194161809571dac4c—> into a post to verify that it is, in fact, my blog.

MCM – Useful Resources

This is just a short post for a Friday. There has been a lot written over the past few weeks since the PASS summit about changes to the Microsoft Certified Master (MCM) programme. As part of that announcement several very useful resources were made available, an series of MCM readiness video list, put together by the good people over at sqlskills.com which can be found here:http://technet.microsoft.com/en-us/sqlserver/ff977043.aspxThere is also a very good reading list which can be found here:http://www.microsoft.com/learning/en/us/certification/master-sql-path.aspx#mcm-microsoft-sql-server-2008-readinessThis is a post my own future reference, I would recommend that if you are seriously considering taking the re-vamped MCM, or even if you are not but want a more detailed knowledge of SQL Server, then check out these resources.

New Domain - My blog has a new domain name gethynellis.com

About a year ago I started to write a blog, previously to that, for about three years I had been writing a SQL Server themed tips and articles on my website. When I started writing the blog I decided to build it into my existing domain name, that I think was a little mistake on my part. It’s that time of year again when it was time to renew my domain name, I hadn’t written on the actual site for well over a year, people reading the site itself had fallen through the floor as I was no longer paying it any attention,  the hosting costs were creeping up with that particular provider and I found that as I’ve developed and increased my contribution  to the SQL Server community my name has become more and more important. The existing domain name, I felt did not really represent me, it was long, possibly too long and difficult to remember and if you came across it on a Search Engine search you wouldn’t necessarily know that it was my site without clicking the link and visiting the site. So a…

SQLServerPedia – Awards 2010

The results of the SQLServerPedia Awards 2010 are out....I was nominated in two categories and I won the "New to SQLServerPedia" award. Being nominated was a nice unexpected surprise and winning is unbelievable, it actually ties in nicely with my blog's one Hundredth post and a nice way to finish my first year writing on the blog. The "New to SQLServerPedia" category had some some greater bloggers nominated and two of those nominated in the "New to SQLServerPedia” category won awards in some of the other categories,  Jason Brimhall (Blog|Twitter) won an award for the "Book Review" category, you can view that post here and Bob Pusateri (Blog|Twitter) won an award in the "Internals" category, you can read Bob’s post here. So there was lots excellent of competition and I can’t believe I actually won. Congratulations to Bob and Jason and all the contestants on on their success . I'm just sorry I couldn't make the trip to Seattle to …

Post 100 – A Year Gone (not quite)!

I wasn’t going to make a special effort for the one hundredth post on my blog, I was intending to let it slip by without much of a mention…But I couldn’t help myself. I have maintained a SQL themed website since 2007 without much luck in term of hits and visitors. When I started out in 2007 I bought some hosting that included a content management system, but that was clunky and at best difficult to use. In three years of it running and writing for that site I probably churned about 50 articles/posts…tops. Late last year (2009) after following along and reading some of the well known bloggers out there like Steve Jones (Blog|Twitter) and Brent Ozar (Blog|Twitter) and just before attending the SQLBITS conference at the Celtic Manor (home of the 2010 Ryder Cup) I decided to start blogging. My reason being, I was writing for my website and now on my blog mainly for my own benefit. I find writing helps me learn and remember technology much better than reading alone, it forces you to think …

SQL ServerPedia – Blog Awards

I have been catching up on some blog posts and I read a few posts by well known bloggers like Grant Fritchely and Mike Walsh about voting for their posts in the SQLServerPedia blog awards. So I visited the links they provided to have look at what was what and who was nominated and then maybe vote. I was stunned to learn that I have been nominated in two categories!
I have an entry in the management and automation category for my Policy Based Management post. I’m in there with along with Grant and a few others.  I have also been nominated in the New To SQLServerPedia category. WOW! I still pretty stunned to be honest, I’m in there with some great writers.
So if you would like to vote then a list of all the categories can be found here. And If you did happen to vote for me, then that would be fabulous.

What’s in a Full Backup?

I got asked a question today that I though was quite interesting. If my backup starts at 6pm and it runs for say 30 minutes what happens to the transactions that occur during the 30 minutes that the backup is running?The short answer:All committed transactions that run during the duration of the backup will be included in the full backup, open transactions will not be included.The proof:Please note, that your database will get around 784MB in size and the backup created will be roughly the same size, so before running these scripts, make sure you have enough space on the box you testing it on.I will create a dummy database called buptest for the purpose of this testUSE master
go
CREATEDATABASE buptest
GO
ALTERDATABASE buptest SET RECOVERY SIMPLE




I have put the database into simple recovery mode just to keep the log manageable, I am not worried about point in time recovery for this demo.
Next we will create two tables to hold some test dataUSE buptest
go

CREATETABLE t1 (id INT, val CHAR(8000))

TSQL2SDAY #12 - Why are DBA Skills Necessary?

WOW this is the 12th TSQL2Sday albeit this month’s is week early because of the PASS summit next week, so these little blog parties have been running for a year now. This month is being hosted by none other than Paul Randal (Blog|Twitter) and we are going to be discussing Why are DBA Skills necessary?
There is a wide range of topics that I could write about here and Paul has listed several topics areas on his blog, I am going to be discussing how business continuity could be affected by a lack of DBA skills.
Paul in his Master class, in London in the summer just gone (review here) gave a great example from his time with Microsoft when a bank got their disaster recovery plan wrong and it ended up costing them dearly, I’m won’t go into much more than that here but it was a great story demonstrating firstly how important data can be to company or firm and also how important it is protect and look after that data – ultimately in lots of cases (backing up the database) = (backing up the bu…

Movember – Team IPMO

Some friends of mine  decided to support the Movember charity this year. I would try and explain what Movember is but the charity website  does a much better job than anything I could put together:“Movember challenges men to change their appearance and the face of men’s health by growing a moustache. The rules are simple, start Movember 1st  clean shaven and then grow a moustache for the entire month.  The moustache becomes the ribbon for men’s health, the means by which awareness and funds are raised for cancers that affect men.  Much like the commitment to run or walk for charity, the men of Movember commit to growing a moustache for 30 days.”The team was put together by lloyd Bayliss and as such he is the team captain, the rest of the team are made up of (in no particular order):me
Chris Unsted
Michael J Elvy
Ian Treasure
Mike Larner – always has The Mo but has kindly shaved it off and is growing it again for this good cause.
Mark Edwards
Ian Smith
Graham Atwick��…

Bulk Insert Permissions

In order to be able to execute bulk operations you need to certain level of privilege both on the database (table) you are performing the operation against and the instance you are performing the operation on.
If you don't have the necessary permissions you will get an error similar to the following:
Msg 4834, Level 16, State 4, Line 5
You do not have permission to use the bulk load statement.
This is what one of my users got earlier today when trying to perform a BULK INSERT. To be able to do this successfully they will need the ability (permission) to INSERT records into the table in question.
In our case the following script will do the trick:
USE orderdemo goGRANT INSERT ON dbo.orders_BULK TO bulktest go

They will also need Administer Bulk Operations permission, you can give that permission by executing the following, this is a server level permission:


USE master goGRANT ADMINISTER BULK OPERATIONS TO bulktest


The user will need ALTER table permissions in certain circumstances.


What I Did This Weekend

About two weeks ago my good friend and former colleague Justin Hostettler-Davie -JHD (Blog | Twitter) asked me via my wife on  Facebook if I fancied climbing Scafell Pike, the highest peak in England, with him on Saturday the 16th October and for some reason I said OK why not. Justin is training to climb Kilimanjaro in the new year (January 2011) to raise money for Velindre Cancer Centre. You can read more about Justin’s challenge and make a donation if you want to here. He has climbed several mountains in preparation for his challenge including Cadair Idris, Pen Y Fan, and Snowden and this Saturday we attempted Scafell Pike he has been accompanied by several friends over the course of his training, but Lance Thomas (No Blog | No Twitter) has been on most of his training climbs with him.We set off from South Wales Friday lunch time, slightly late because JHD missed my junction on the motorway, again! He never remembers where I live, so instead of leaving at 1230 we left at 1315. JHD, …

What I Did This Weekend

About two weeks ago my good friend and former colleague Justin Hostettler-Davie -JHD (Blog | Twitter) asked me via my wife on  Facebook if I fancied climbing Scafell Pike, the highest peak in England, with him on Saturday the 16th October and for some reason I said OK why not. Justin is training to climb Kilimanjaro in the new year (January 2011) to raise money for Velindre Cancer Centre. You can read more about Justin’s challenge and make a donation if you want to here. He has climbed several mountains in preparation for his challenge including Cadair Idris, Pen Y Fan, and Snowden and this Saturday we attempted Scafell Pike he has been accompanied by several friends over the course of his training, but Lance Thomas (No Blog | No Twitter) has been on most of his training climbs with him.We set off from South Wales Friday lunch time, slightly late because JHD missed my junction on the motorway, again! He never remembers where I live, so instead of leaving at 1230 we left at 1315. JHD, …

TSQL Tuesday Misconceptions in SQL Server

I haven’t written a post for a TSQL Tuesday for a little while and I know that I’m late getting this one out too. This month’s party is being hosted by Sankar Reddy This topic  I think, maybe expect, really I know, that this topic has been covered already, I know that the Steve Jones has posted on this misconception already today and Paul Randal (Blog | Twitter) in his misconceptions series back in April covered this too much better than I have here so go and check out Paul’s post . The misconception I will look at here is - SQL Server will truncate the the transaction log after a full backup when the database is in full recovery mode. After my blog post around recovery models earlier today I think this maybe quite apt for me.

The misconception I am going to talk about here is SQL Server truncates the log of a database in full recovery mode when a full backup is taken. It doesn’t.
This one of the biggest problems I come across, a database in full recovery mode and regular full backup…

SQL Server Recovery Models

A SQL Server database can be in one of three recovery models. FULL, BULK_LOGGED and SIMPLE. The recovery model you choose for your databases will  affect your restore plans in the event of failure or need to restore, the recovery model you pick will also influence how you mange the transaction log of your database. So you need to choose carefully. FullThe full recovery model offers your database the most protection. Full recovery mode means all database operations are fully logged. It allows point in time and point of failure restores. It does come with some overhead though, you need to implement both Full and Log backups as part of your backup plan. You need the transaction log backups for two reasons. 1. Transaction log backups allow you to perform point of failure or point in time restores. 2. If you don’t take transaction log backups of your database when its in Full recovery mode then your transaction log will grow uncontrollably and will eventually use up all available drive spa…