There’s a well known problem that every time a backup operation succeeds a message is written into the error log and Windows event log. If you’re taking very frequent log backups (say every 5 minutes) of multiple databases, that’s a significant amount of clutter in the logs. Well – now there’s a fix! Kevin Farlee, [...]
Paul S. Randal
In Recovery...
Fed up with BACKUP success messages bloating your error logs?
Posted on: October 30, 2007 7:26 pm
Debunking a couple of myths around full database backups
Posted on: October 25, 2007 9:42 pm
I’ve been involved in a few conversations today that have highlighted some big misconceptions about how backups work. I’d like to use this blog post to debunk them. I checked everything I say here with my friend Steve Schmidt, the developer on the Storage Engine team responsible for BACKUP/RESTORE for the last ten years. Myth 1: [...]
Just how long should you make character fields? What’s the longest word?
Posted on: October 25, 2007 7:38 pm
Ok – this post is a little strange and fun. I was thinking about word length and how it relates to designing software/schemas to support multiple-languages. How far do you have to go in your research to figure out the maximum string length to support? So I started digging about and found some interesting things [...]
Search Engine Q&A #8: How can defragging an index break synchronous mirroring? And what happens?
Posted on: October 25, 2007 5:04 pm
(Been a few days since I posted – had some real work to do :-) Today I'll post a few things from the queue that's been building up) This is part Q&A and part follow-on from my last post about running index maintenance when a database is mirrored. A customer has a maintenance plan that involves [...]
Conferences: updated events page listing ALL the team’s sessions for November
Posted on: October 21, 2007 9:35 pm
Two weeks to go until the conferences start… I've revamped the main Upcoming Events page on SQLskills.com to list all the DBA/IT-Pro, Developer, and BI sessions we're collectively doing at SQL Connections Fall, Microsoft TechEd Developers, Microsoft TechEd IT Forum, and a Microsoft TechNet Deep-Dive in November. (Now that I've finished my Lego model, what [...]
Tool for estimating the size of a database
Posted on: October 19, 2007 7:26 pm
During SQL Server 2005 development I did a comprehensive rewrite of the Books Online entries concerned with estimating index, table, and database size (see http://msdn2.microsoft.com/en-us/library/ms187445.aspx). I was recently having a discussion with Øystein Sundsbø about a bug in one of the formulas I wrote and it turns out he’s written a neat tool that codifies all the [...]
SQL Down Under podcast interview – CHECKDB, 2008, and what does Paul get up to when Kimberly’s away?
Posted on: October 19, 2007 2:17 am
Today I finally managed to hook-up with SQL Server MVP and Regional Director Greg Low so he could interview me for his podcast show – we’d spent the last few months juggling schedules and time-zone differences (he’s 17 hours ahead of Redmond) but today the stars aligned and we made it. You can download the interview at [...]
More on Database Mirroring performance and index maintenance
Posted on: October 19, 2007 12:37 am
There’s been some discussion over on the SQL Server Central forums about database mirroring performance, and one question in particular on how to do index rebuilds on mirrored VLDBs. Remember that in database mirroring, the database has to be in the full recovery mode, so all index rebuilds are fully logged. It all comes down [...]
Admin: Comments are fixed now
Posted on: October 18, 2007 12:52 am
Thanks for your patience and to all those who emailed to let me know. All the SQLskills.com blogs have been updated to the latest dasBlog version and everything’s working again. I’d appreciate you taking the time to go back and re-enter any comments you tried to over the last few days. Thanks!
BACKUP WITH COPY_ONLY – how to avoid breaking the backup chain
Posted on: October 15, 2007 9:37 pm
After posting last week about a BACKUP feature that I don’t like (WITH NO_LOG – see here), I thought I’d do a quick post this week about a new backup feature that was introduced in SS2005 that I DO like – the COPY_ONLY option to BACKUP DATABASE and BACKUP LOG. Here’s a situation I’ve seen [...]
SQL Server 2008: What’s been removed in this version? BACKUP LOG WITH NO_LOG for one!
Posted on: October 12, 2007 7:26 pm
All the razzamatazz about new releases go on about what's new but hardly ever is there discussion of what's been removed. So that's the topic of this short post. In the Books Online that comes with the July CTP of SS2008 (here's a link to the download page), the topic Discontinued Database Engine Functionality in [...]
BACKUP LOG WITH NO_LOG – use, abuse, and undocumented trace flags to stop it
Posted on: October 12, 2007 5:09 pm
This has come up several times over the last few days, and is something that Kimberly and I discuss (and deplore) when we lecture – the use of BACKUP LOG WITH NO_LOG (or TRUNCATE_ONLY – they’re synonymous) to allow log truncation. How is it used? The common use is when the transaction log grows to [...]
New additions to the SQLskills team
Posted on: October 11, 2007 9:09 pm
It gives me great pleasure to announce two new additions to the SQLskills team – Stacia Misner and Simon Sabin. Stacia’s a BI expert who will be working alongside Liz Vitt, and Simon’s a developer expert who will be working alongside Bob Beauchemin. Bringing Stacia and Simon on-board really strengthens the capabilities of the SQLskills [...]
SQL Server 2008: Performance boost for Database Mirroring
Posted on: October 11, 2007 12:06 am
As I mentioned in a previous post, one of the new features for database mirroring in SQL Server 2008 is log stream compression. There’s a good explanation of what this is (along with some example workloads and performance graphs) on the SQL Customer Advisory Team blog so I’m not going to duplicate all that here. Basically, the [...]
SQL injection attacks can happen anywhere…
Posted on: October 10, 2007 5:59 pm
It’s not like me to post on security – that’s one of Kimberly’s areas - but this is an interesting example of a real-life injection attack :-)
Search Engine Q&A #7: Restoring from a backup file containing multiple database backups
Posted on: October 10, 2007 1:35 am
This is another question that came up on the Disaster Recovery forum on MSDN. Paraphrasing – ‘ I have a backup file containing full backups for 45 databases. How can I restore them all using a script?’ The answer is pretty straightforward. Let’s create the situation described, using 3 databases for clarity rather than 45: [...]
New aggregate feeds for SQLskills blogs
Posted on: October 9, 2007 1:35 am
Lots of people have been asking for us to create some aggregate feeds of the various blogs on the SQLskills site – well, now I’ve done it. The three new feeds are: SQLskills BI Team Blog http://pipes.yahoo.com/pipes/pipe.run?_id=xMvfPeB13BGn_EL9iHrL0A&_render=rss or http://feeds.feedburner.com/SQLskillsBITeamBlog SQLskills SQL Server 2008 Category Aggregate Feed http://pipes.yahoo.com/pipes/pipe.run?_id=mhCQs_B13BGv3jGgjtzu1g&_render=rss or http://feeds.feedburner.com/SQLskillsSQLServer2008TeamBlog SQLskills All Blogs Aggregate Feed http://pipes.yahoo.com/pipes/pipe.run?_id=vv9PBOp13BGAc67kLO2fWQ&_render=rss. There’s no [...]
Inside the Storage Engine: Ghost cleanup in depth
Posted on: October 8, 2007 4:24 pm
Over the years I was in the Storage Engine team I saw a lot of concern on the various forums about the ghost cleanup task. There have been a few bugs with it in previous versions (see these KB articles – 932115 and 815594) and there's very little info available on it. For some reason [...]
How does daylight savings time affect disaster recovery?
Posted on: October 6, 2007 5:49 pm
It’s common knowledge that SQL Server copes with daylight savings time (DST) correctly so why should you care? Well, it’s not so common knowledge that at the end of DST when the clocks go back an hour (always at 02:00 in the U.S.), SQL Agent essentially pauses for an hour (in at least SS2000 onwards). This [...]
Search Engine Q&A #6: Using fn_dblog to tell if a transaction is contained in a backup
Posted on: October 6, 2007 1:00 am
Here’s a really interesting question that was in my search engine logs yesterday - if I have a transaction that runs and completes while a backup is running, will the complete transaction be in the backup? The answer is…. it depends! In terms of what gets backed up, the way a full backup works is: Note [...]
Search Engine Q&A #5: Do multi-core CPUs perform better than single-core CPUs?
Posted on: October 5, 2007 7:43 pm
Here’s an interesting question I was sent by my friend Steve Jones over at SQL Server Central – will a single CPU with dual-cores perform better than two single-core CPUs? Both have two processing cores but the hardware architecture is different – which one will make SQL Server perform better? Well, there’s no hard and [...]
Indexes From Every Angle: How can you tell if an index is being used?
Posted on: October 5, 2007 1:39 am
Whenever I’m discussing index maintenance, and specifically fragmentation, I always make a point of saying ‘Make sure the index is being used before doing anything about fragmentation’. If an index isn’t being used very much, but has very low page density (lots of free space in the index pages), then it will be occupying a [...]
SQL Server Magazine Connections Spring 2008 Call For Presentations
Posted on: October 5, 2007 1:27 am
With the November 5-9th SQL Connections conference in Las Vegas around the corner, Shirley Brothers, the Connections Conference Manager, would like to start planning the Spring show. From the Spring show onwards, Kimberly and I will be the co-chairs of the SQL Connections conference. We would like to invite you to submit abstracts for the [...]
Indexes From Every Angle: What happens to non-clustered indexes when the table structure is changed?
Posted on: October 5, 2007 12:52 am
I’d like to kick off the Indexes From Every Angle series this evening by re-posting some articles from my old blog. Both of these topics continue to crop up on newsgroups and forums. What happens to non-clustered indexes when changes are made to the underlying table? Are they always rebuilt or not? Well – it [...]
Coming to Connections in November? Send us your questions and win a prize!
Posted on: October 4, 2007 9:19 pm
Are you coming to SQL Connections in November? If you are, and you’re coming to either of our interactive sessions, we invite you to send us questions in advance that we will pick from and answer during the sessions. The two sessions are: SDB351: Follow the Rabbit – Interactive Q&A on Availability In this session, Kimberly Tripp [...]
Inside the Storage Engine: Proof that records are not always physically stored in index key order
Posted on: October 4, 2007 3:29 pm
I mentioned this in my Anatomy of a page post – its a common misconception that records in an index are ALWAYS stored in the same physical order as the logical order defined by the index key. Here's proof for you that this is incorrect (as well as introducing you to the other dump styles [...]
Inside the Storage Engine: IAM pages, IAM chains, and allocation units
Posted on: October 4, 2007 12:42 am
This is a combo from some previously posted material, with some more DBCC PAGE output thrown in. IAM pages An IAM (Index Allocation Map) page tracks approximately 4GB worth of space in a single file, aligned on a 4GB boundary. These 4GB chunks are called 'GAM intervals'. An IAM page tracks which extents within that [...]
SQL Server 2008: Parallelism improvements for partitioning
Posted on: October 3, 2007 10:40 pm
In SQL Server 2005, queries over partitioned tables use a single-thread per partition. This can cause performance problems under certain circumstances: On systems with many CPUs, if the table schema has less partitions than there are CPUs, then not all the CPUs will be used to process the query. Some examples: On a 32-way box, [...]
Inside the Storage Engine: Anatomy of an extent
Posted on: October 3, 2007 10:13 pm
This one's a quickie. In the previous post I explained about database pages – their structure and some page types. Now I'd like to explain how pages are grouped into units called extents. An extent is a group of eight physically consecutive pages in a data file. Extents are always aligned on 64KB boundaries (i.e. [...]
Inside the Storage Engine: Anatomy of a page
Posted on: October 3, 2007 6:49 pm
Next up in the Inside the Storage Engine series is a discussion of page structure. Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Here's a picture of the [...]
Coming up soon: TechEd IT Forum 2007
Posted on: October 3, 2007 3:58 pm
November’s going to be a busy month for Kimberly and I! Hot on the heels of SQL Connections in Las Vegas is the yearly pilgrimage to Barcelona for TechEd IT Forum and this year we’re packing in a ton of sessions in between us. Here’s what we’re doing: Sessions (DAT205) The [...]
EMERGENCY-mode repair: the very, very last resort
Posted on: October 2, 2007 11:26 pm
(Be sure to join our community to get our bi-weekly newsletter with exclusive content, demo videos, and other SQL Server goodies! Also check out our online training courses.) This is a follow-on from two posts: Corruption: Last resorts that people try first… where I discussed the two worst things you can do (in my opinion) [...]
SQL Server 2008: Lock escalation changes
Posted on: October 2, 2007 10:06 pm
SQL Server supports lock escalation – when the server decides to move from a large number of row or page locks on an object to a table-level lock. Sunil Agarwal posted a great description of lock escalation in SQL Server 2005 on the Storage Engine blog so I won’t repeat it all here. The problem with [...]
Search Engine Q&A #4: Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database
Posted on: October 2, 2007 5:20 pm
By far the most common search engine query leading to the blog is about fixing a suspect or unrecovered database. The very best way to do this is to use your backups, and to have a backup strategy that allows you to recover in the smallest amount of time and with no data loss. But [...]
Search Engine Q&A #3: Database mirroring failover types and partner timeouts
Posted on: October 1, 2007 11:31 pm
Ok – I’m on a roll today so to finish off I’d like to repost some info about database mirroring failover types (including how you may not actually get a failover when you expect it) and a tip for how to avoid unwanted failovers when combining clustering and mirroring. Mirroring failovers have cropped up several [...]
SQL Server 2008: New Performance Counters for Database Mirroring
Posted on: October 1, 2007 8:31 pm
Part of the improvements to database mirroring in SQL Server 2008 are the addition of 10 new performance counters under the Database Mirroring Performance Object. These add some cool troubleshooting capabilities. SQL Server 2005 provided the following 11 counters (from Books Online): Name Description Bytes Received/Sec Number of bytes received per second. Bytes Sent/sec Number [...]
Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back
Posted on: October 1, 2007 1:53 am
Time for the first post in the Inside the Storage Engine series. I’m going to focus on SQL Server 2005 in this series and I’ll point out major differences between 2005 and previous versions. Please drop me a line if there’s something you’d like to see explained and demo’d. Before jumping into how things work, [...]
