Thursday, December 18

Backups

There's quite a jump between being proficient in T-SQL/SSMS and understanding the guts of SQL Server. For example, being able to backup and restore databases is a handy skill, but it's only the tip of the iceberg. Under the water is the differences between the recovery models, the different types of backups, and the effects that doing backups have on recoverability and growth/maintenance of logs.

As mentioned in my post on "ongoing education", it's helpful to have reference resources which talk to you like a human, and pictures are pretty helpful too. So I'd like to pass on this amazing site: http://www.sqlbackuprestore.com/

It'll get you up to speed on the nitty-gritty of backups and restores a lot faster than any book or course I've attempted slogging through.

Just check it out if you're needing help, especially if you use Red Gate's backup gear, as he knows the innards of that lot intimately and provides a great lot of code samples which have saved me quite a few hours over the last few days.

Monday, December 15

Modifying your TempDB setup

Today I was moving the tempDB on a new server from the drive where every other DB data file sits to its own dedicated drive, with the intention of preventing drive contention.

To cut a long story short (and anyway I'm not even sure how I got myself into such a tangle) here's a couple of annoyances and their fixes:

Annoyance #1 - Reducing the Initial File Size of TempDB: If you right click a database, go into Properties, and Files, you can see the Initial file size of the database. Let's say that someone else set this DB up, and you'd like the initial size to be smaller. This is only really a problem you're going to get with TempDB, which re-creates each time you restart the SQL Service btw. So you type in a new value, and hit OK. Well, not OK, because if you re-open that same GUI, you'll see that your changes have not been kept! ARGH!

Solution #1 - Issue a DBCC SHRINKFILE (N'file' , size in MB) where the size in MB is an amount LESS than the initial size you wish to set. Then, ALTER DATABASE tempdb MODIFY FILE (NAME = 'file', SIZE = new initial size)

Annoyance #2 - Reducing the Number of Database Files: So you've got TempDB split up into too many files. So you go into the Properties, Files etc and remove the file(s), hit OK and they file's disappeared from the hard drive. But upon restarting the SQL Service for whatever reason, the files get recreated on the hard drive!

Solution #2 - They've got to be explicity removed from the system catalogue it seems. ALTER DATABASE tempdb
REMOVE FILE (NAME = tempdev2, FILENAME = 'c:\tempdev2.ndf')

Little secrets.

Friday, December 5

Ongoing Education for the Terminally Lazy/Busy

Reading msdn articles and whitepapers and textbooks blows goats as far as I'm concerned. I'm interested for about 5 minutes, max.

However I’ve been learning a lot about SQL (as well as plenty of other things) since I got myself set up in the RSS world, subscribing to blog and site feeds in Google Reader.

Maybe I’m preaching to the choir on this blog entry about RSS, but amongst the DB people I know very few seem that savvy about efficient net usage.

These sites below publish regularly, and when you subscribe to them in Google Reader, you can get a heap of relevant information into your unread “inbox” instead of regularly checking sites to see if anything’s updated.

If you’ve got a gmail account, you’re crazy not to start using Reader, and it works great on mobile phones cutting down bandwidth hungry sites into nice little readable chunks.

So, below, are my favourite sites/blogs which update regularly with useful SQL goodies. At least check them out, and if you’re not already in the RSS feed world, get yourself subscribed to these guys in Google Reader and you’ll be picking up more info than ever in a digestible fashion.

Blogs:
http://www.brentozar.com/
http://statisticsio.com/
http://itknowledgeexchange.techtarget.com/sql-server/
http://blog.sqlauthority.com/

For sites, check out:
http://sqlserverpedia.com/wiki and http://www.sqlservercentral.com/