Skip to main content

Posts

Showing posts from May, 2010

Time Off, Recharge, Rethink

Some of you may have noticed that I haven’t posted for a while. The 12th May was the date of my last post. Well, in the last few weeks I have been on holiday. Me and the family took a trip to the USA and the Sunshine state to celebrate my birthday, as I recently turned 30.We spent time in the theme parks in Orlando riding rollercoaster's and generally having fun, and we took a trip out to Tampa. The weather has been very hot and very sunny and we have all had a great time. We did intend to visit the beach but with the current oil disaster affecting the region we decided to give the beach a miss… which is a shame, I have been before and it is a beautiful place and I hope that the disaster does not have too much of an impact on the region.My holidays and time off never fail to recharge my batteries and I always return home with fresh ideas and renewed motivation to develop my business and career. I have few new business ideas that I will share with you shortly. On a career note I wi…

Are Transaction Rolled Back When CHECKDB Runs?

Ever come across a scary error in the SQL Server log when running CHECKDB against your databases. The error looks something like this:1 transactions rolled back in database 'CHECKDB' (32). This is an informational message only. No user action is required.

Are transactions rolled back when CHECKDB runs?

No.

The key to this error is the last two sentences, Crash recovery has not been run on your production database. Since SQL Server 2005 an internal snapshot of the database is used for running integrity checks, this snapshot provides CHECKDB with a transitionally consistent snapshot of your your database. This error relates to the snapshot, crash recovery is run against the database snapshot so it is in a transactional consistent state for CHECKDB to run. Your production database is not affected.

Don’t believe me, well I don’t blame you, so checkout Paul Randal(Blog|Twitter) post(s) on the very subject.

Related Posts

SQL Server Integrity Checks

SQL Server Integrity Checks – DBCC CHECKDB

I like to run integrity checks against my databases very regularly, sometimes daily if I can get away with it. For some of the larger databases that I manage I change how this is run but on my consolidated instances which can house several different smaller databases I run the following script in a job and schedule the job to run daily, out of hours, using the SQL Server agent. My script looks something like this:--Gethyn Ellis April 2009
--runs DBCC checkdb against ALL databases on a server
--Needs to be run against master database i.e. that iswhere sp_MSForEachdb isfound
--To exclude a databaseadd the database name to the NOTIN list in @cmd1
DECLARE @cmd1 VARCHAR(500)


SET @cmd1 = 'if ''?'' NOT IN (''tempdb'') DBCC CHECKDB([?]) WITH NO_INFOMSGS'
EXEC sp_MSforeachdb @command1 = @cmd1


SQL Server Replication - Replication Agents

My first post in my replication series gave a brief overview of replication, before I dive into setting up and configuring the different types of replication I wanted to look a little more closely at the respective agents and the role they play in the different types of replication.
Replication AgentsSnapshot Agent - usually resides on the distributor server, it is used in all types of replication. The Snapshot Agent makes a copy of the schema objects and the data in the tables to published and stores them in  snapshot files in the snapshot folder, information about the snapshot  is stored in the distribution database.
Log Reader Agent - This is used in transactional replication. The log reader agent monitors the transaction log of all databases involved in replication. it monitors changes in the data marked for replication in the publication database's  transaction log and sends them to the distributor server. The transactions are held here until they are are ready to be sent to t…