In a previous post (see here) I debunked a myth about how much transaction log a full backup would include. I had a question in the blog post comments that asked (paraphrasing): The full backup has to include all the transaction log from the begin LSN of the oldest active transaction at the time the [...]
Paul S. Randal
In Recovery...
More on how much transaction log a full backup includes
Posted on: January 31, 2008 8:01 am
Tales from the women’s bathroom in the Microsoft office in Beijing
Posted on: January 30, 2008 5:17 am
Well, I bet that title grabbed a bunch of people’s attention :-) In the Beijing Advanced Research Centre offices of Microsoft where we’re teaching this week, the bathroom stalls have print-outs of jokes on them. The ones in the women’s bathroom are pretty good – here are a couple of them that made us laugh [...]
Database snapshots – when things go wrong
Posted on: January 30, 2008 4:26 am
This is a post I’ve been meaning to do for a while – detailing some of the problems you can run into when using database snapshots. Reverting to a snapshot Many people use snapshots as a way of protecting against mistakes during a complicated set of changes to a database – you can just revert [...]
Search Engine Q&A #13: Difference between database version and database compatibility level
Posted on: January 29, 2008 11:51 pm
This has been causing some problems on the various groups and forums over the last few days so I thought I’d repost this from my old Storage Engine blog. The questions have been around attaching 2005 databases to 2000 servers – even databases that are in 80 compat mode – and it doesn’t work. Why? [...]
The frustration of sweeping generalizations – follow on from Search Engine Q&A #12
Posted on: January 29, 2008 5:35 am
A bit more traffic on the thread (see previous post here) prompted me to give my thoughts on the many sweeping generalizations that plague the computer industry and make it difficult sometimes to give advice in forums and blogs. I’d like to repost here (with a few tweaks for clarity). Some examples of questions that [...]
Search Engine Q&A #12: Should you create multiple files for a user DB on a multi-core box?
Posted on: January 29, 2008 2:10 am
There’s been a very interesting discussion going on over at SQLServerCentral.com about whether to create multiple files for a user database because the server has multiple CPUs – see the thread here. I wrote a couple of long responses during the thread that I wanted to duplicate here as I think it’s of broad interest. My [...]
Search Engine Q&A #11: How to design a global-ready database?
Posted on: January 28, 2008 8:33 am
This kind of follows on from my previous post about making sure you have character column lengths that can handle data from different countries (e.g. city names that may be longer in one country than another). A question on the forums today asked what info there is available to help in designing a global-ready database. It [...]
SQL Server 2008: RTM slips to Q3
Posted on: January 28, 2008 1:59 am
Microsoft has announced that the RTM date of SQL Server 2008 has slipped out to Q3 of this year. See this official blog post here. The official launch will still go ahead as planned on February 27th. While some people may see this as disappointing, I don’t see many large customers going straight into SS2008 [...]
Personal: And on to Beijing and the Great Wall…
Posted on: January 27, 2008 9:36 am
Start of the second week in China. We flew up from Shanghai to Beijing yesterday, and just in time it seems. As we flew out of Shanghai it was being enveloped in a snowstorm. Beijing’s much colder than Shanghai though – being further north and inland quite a way compared to Shanghai. As I said [...]
Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics
Posted on: January 27, 2008 12:01 am
It seems like all I’ve been talking about on the forums the last couple of days is the correct order of operations in a maintenance plan. The biggest confusion seems to be about the effect of rebuilding an index on statistics, with some incorrect advice being given out on what to do. Rebuilding an index [...]
SQL Server 2008: FILESTREAM
Posted on: January 25, 2008 9:51 am
Well it’s the end of our first week in China – tomorrow we fly to Beijing for another week of teaching. Hopefully we’ll get to do some sightseeing on Sunday – the last time I was in Beijing (for TechEd ’06) the conference hotel was about 25 miles outside the city centre – so I [...]
Personal: First trip of the year – China!
Posted on: January 21, 2008 9:23 am
Nothing technical in this blog post for a change, just some info about the first trip of the year for us. We’re in China for two weeks, teaching classes at the Microsoft campuses in Shanghai and Beijing. We flew in Sunday afternoon on a direct flight from Vancouver, Canada which was just wonderful. When I [...]
SQL Server 2008: Spatial indexes
Posted on: January 17, 2008 7:59 pm
One of the cool features of SQL Server 2008 for developers is spatial data support. There have been some great posts recently about using spatial (see Simon Sabin’s blog here), which is all developer stuff – but what I’m interested in are the implications of spatial support for DBAs, and they are focused on spatial [...]
See? We *can* all get along…
Posted on: January 17, 2008 5:48 am
Despite the fact that I was in the Storage Engine, and there’s always been humorous rivalry between the Storage Engine team and the Relational Engine (a.k.a. the Query Processor) team, I did actually get along with some of the QP guys :-) One of my good friends, Conor Cunningham, has been wanting to get back [...]
SQL Server 2008: Partition-level lock escalation details and examples
Posted on: January 14, 2008 10:10 pm
Back in October 2007 I blogged about partition-level lock escalation in SQL Server 2008 (see here) and I promised to do a follow-up once CTP-5 came out with syntax etc. So here it is. A brief recap – lock escalation in SQL Server 2005 and before only allowed table-level lock escalation. If you have a [...]
More new whitepapers: Database Mirroring and Log Shipping
Posted on: January 11, 2008 2:02 am
Just got an email notification of a new whitepaper from the SQL Customer Advisory Team on Database Mirroring and Log Shipping Working Together. It covers: Converting a log shipping setup to a database mirroring partnership Setting up log shipping to a 3rd destination (i.e. warm standby to go with the mirroring hot standby) Swapping the [...]
Whitepaper on Predeployment I/O Best Practices, plus a few urban legends around SQL Server IO
Posted on: January 10, 2008 10:38 pm
There’s a new whitepaper on TechNet that I’ve just come across (even though it was published 6 months ago!) called Predeployment I/O Best Practices. It’s really good – discussing the following: Guidelines for determining I/O capacity Disk configuration best practices and common pitfalls Using SQLIO to determine capacity and interpreting its results Using System Monitor [...]
SQL Server 2008: Hot-Add CPU (and affinity masks)
Posted on: January 10, 2008 12:11 am
Quickie today as I’m preparing to speak at the Pacific Northwest SQL Server User Group meeting tonight on the MS Campus here in Redmond (my trademark Detection and Recovery from Database Corruptions talk). SQL Server 2005 introduced the concept of hot-add memory, to allow for dynamic workload handling. SQL Server 2008 increases these capabilities by [...]
SQL Server 2008: Backup Compression CPU Cost
Posted on: January 9, 2008 10:26 am
Back in September last year I blogged about the native Backup Compression that is in SQL Server 2008 (see here) and promised to blog more when CTP-5 came out and I ran some tests. Well, it’s here and I have so here’s some data for you. I expanded the AdventureWorks database to be 322Mb (random [...]
Enabling VS2005 support for SQL Server 2008 CTP-5
Posted on: January 4, 2008 6:05 pm
Hit another issue last night while playing with the latest CTP – VS2005 can’t cope with some of the changes in SS2008. The problem arose while defining an ASP.NET connection string that was pointing to a 2008 instance (while playing with query notifications and cache invalidation, but that’s unrelated) – it failed with a slightly [...]
Katmai bug: SSMS doesn’t drop database locks when leaving the context of a database
Posted on: January 4, 2008 3:07 am
I came across an interesting bug in Management Studio in the latest Katmai CTP today – when a connection disconnects from a database, SSMS doesn’t release the shared database lock that it holds. This prevents any operations that need exclusive database access (like a RESTORE) and can be somewhat disconcerting if you don’t realize what’s going [...]
Coming up soon: SQL Server Magazine Spring 2008 Conference
Posted on: January 3, 2008 10:58 pm
No sooner has Fall conference season finished then it’s New Year and we start everything all over again! SQLskills (Me, Kimberly, Bob, and Stacia) is doing a *ton* of stuff at SQL Connections this Spring. The conference is in Orlando as usual and runs from April 20th to 23rd, with pre-con workshops on the 20th. [...]
