Bitten: Default Max Replication Size in SQL Server

Thinking of keeping some logs of things that have bitten me so I don't forget them (those who forget history are doomed to repeat their mistakes, right?)

Just got bit by this one when turning on replication for reporting for a server (production DBAs did the work).  We have a business requirement for customer driven Ad-Hoc reporting to every field in the application.  This data in the report must not be older than 24 hours.  I have communicated this requirement to the production support DBAs and informed them that we were ready to begin testing this environment.  They chose transactional replication as the implementation technology.

They configured replication between the transactional database server and another database server that would host the Ad-Hoc queries.  We funnel calls to reporting services through a service layer and this service layer keeps a copy of the rendered image in the database (in an image column).  We were trying to write a 1MB record to the database with default settings and it was erroring out.

This caused the application to fail to insert a record.  The error was not being trapped correctly, so there was no logging or any way to capture this.  Of course, we did this in a customer facing environment where the customer was performing active testing.  This in turn, brought down the system and it took us two days (2 days!!!) to find this problem.

Important note:  Any change can be significant.

One Technet article on setting server-wide sql server settings.  Another on the actual setting that would have made all the difference in the world.

Default New Query for SQL Server Management Studio

I found an article about how to change this on the web.  The file can be found at C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql for SQL 2008.  It can be found at C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SQLFile.sql for SQL 2005.  I thought I would give it a shot and see what became useful for me. This is now my default New Query document contents in both SSMS 2005 and SSMS 2008.

-- Set the transaction isolation level explictly
SET TRANSACTION ISOLATION LEVEL READ uncommitted

BEGIN TRAN

BEGIN TRY

-- Replace this with the actual work ;)

-- Here is an example breakpoint
RAISERROR (N'BREAKPOINT.',16,1);
END TRY

BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);

DECLARE @ErrorSeverity INT;

DECLARE @ErrorState INT;

SELECT Error_number() AS errornumber,
Error_severity() AS errorseverity,
Error_state() AS errorstate,
Error_procedure() AS errorprocedure,
Error_line() AS errorline,
Error_message() AS errormessage;

-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;

RESEED all tables with Identity Columns in a Database

I had a problem today where a comparison tool had inserted lots of data into a database and it was then propagated to other environments. We started seeing errors and needed to ensure that all tables were fixed. This was the hack I came up with. Enjoy

DECLARE @SQL NVARCHAR(MAX)

DECLARE reseed CURSOR FAST_FORWARD FOR
SELECT N'DBCC CHECKIDENT (' + Quotename(Object_name(object_id)) + N',RESEED)'
FROM sys.columns
WHERE is_identity = 1
AND Objectproperty(object_id,'IsTable') = 1

OPEN reseed

FETCH NEXT FROM reseed
INTO @SQL

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
EXEC dbo.Sp_executesql
@SQL
END

FETCH NEXT FROM reseed
INTO @SQL
END

CLOSE reseed
DEALLOCATE reseed

REPOST How to see when restores happend and what files were restored

How to identify when a database was restored, the source of the backup and the date of the backup

Repost: List of tables in dependency order

A great script article on getting a list of tables in foreign key dependency order.

http://sqlblog.com/blogs/jamie_thomson/archive/2009/09/08/deriving-a-list-of-tables-in-dependency-order.aspx

with fk_tables as (
select s1.name as
from_schema
, o1.Name as
from_table
, s2.name as
to_schema
, o2.Name as
to_table
from sys.foreign_keys fk
inner join
sys.objects
o1
on fk.parent_object_id = o1.object_id
inner join
sys.schemas
s1
on o1.schema_id
= s1.schema_id
inner join
sys.objects
o2
on fk.referenced_object_id = o2.object_id
inner join
sys.schemas
s2
on o2.schema_id
= s2.schema_id
/*For the purposes of finding dependency
hierarchy
we're not
worried about self-referencing tables*/
where not ( s1.name
= s2.name
and o1.name =
o2.name)
)
,ordered_tables AS
( SELECT s.name as
schemaName
, t.name as
tableName
, 0 AS Level
FROM ( select *
from sys.tables
where name <> 'sysdiagrams')
t
INNER JOIN sys.schemas
s
on t.schema_id
= s.schema_id
LEFT OUTER
JOIN fk_tables fk
ON s.name =
fk.from_schema
AND t.name =
fk.from_table
WHERE fk.from_schema IS NULL
UNION ALL
SELECT fk.from_schema
, fk.from_table
, ot.Level + 1
FROM fk_tables fk
INNER JOIN
ordered_tables ot
ON fk.to_schema =
ot.schemaName
AND fk.to_table =
ot.tableName
)select distinct ot.schemaName
, ot.tableName
, ot.Level
from ordered_tables ot
inner join (
select schemaName
, tableName
, MAX(Level)
maxLevel
from ordered_tables
group by
schemaName,tableName
) mx
on ot.schemaName =
mx.schemaName
and ot.tableName =
mx.tableName
and mx.maxLevel =
ot.Level;

Distributed Transaction Coordinator orphaned transactions in SQL 2005

Thes types of transactions can cause resource contention and show up in sp_who2 as being blocked by spid -2. You can kill them if you know the Unit of Work (a guid) that identifies them. You can find the guids using the following query in SQL 2005:

SQL 2005 using system table

SELECT DISTINCT(req_transactionUOW)
FROM sys.syslockinfo
WHERE req_spid = -2
AND req_transactionUOW <&gt; '00000000-0000-0000-0000-000000000000'

You would kill the UOW with the same Kill command from SQL as you would a spid.

KILL '00000000-0000-0000-0000-000000000000'


This should clear the transactions and remove any resource contention that they may be having.
Sources: SQL Authority


Enjoy!

SQL February 2008 CTP is out...

I am looking forward to exploring this within a virtual environment. I am currently in a contract where the time I have is not well suited for exploring new technologies, but I plan on spending some "quality" time with this version to get to understand it better. Along with the VS 2008 Trial, the SQL 2008 February CTP should provide some fantastic late night sessions.