TRUNCATE Log in SQL Server 2008, 2012, 2014

You have probably seen message like
‘Transaction log of myDatabase is full’.

Prior to version 2008 of SQL Server we will execute statement like

BACKUP LOG MyDatabase WITH TRUNCATE_ONLY

but if you tried to execute this on SQLServer 2008, 2008R2, 2012 and 2014 you will get and error like

?Msg 155, Level 15, State 1, Line 1
‘TRUNCATE_ONLY’ is not a recognized BACKUP option.

Assuming that you know that TRUNCATE_ONLY is an dangerous option, not very usable in a live systems and that officialy and recomended way to do it is to change database recovery model from FULL to SIMPLE still you can save a lot of time, space and headaches on test systems, on SQL Server 2008 and later you need to execute

BACKUP LOG MyDatabase
TO DISK = 'nul:' WITH STATS = 10

and magic is done. Your database log is truncated.

.NET Parametrized queries and sp_executesql performance improvements

If you fetching performance problems by executing sp_executesql consider to use in your query hint RECOMPILE. Note, that sp_executesql is used all the times when you execute parametrized queries in .NET for example. So, the need for this hint is much bigger than you think.
Initally your query can be something like

select col1 from Test_table where col1 = @param1
order by col1

what is transfered to sql server like

exec sp_executesql N'select col1 from Test_table where col1 = @param1
order by col1', N'@param1 int'

So your query should looks like

select col1 from Test_table where col1 = @param1
order by col1 OPTION (RECOMPILE)

Notethat OPTION(RECOMPILE) has been added to the query. This option in some cases can boost your query performances.

Main problem here is well known in SQL Server and is called parameter sniffing, what means that SQL Server looks at the input parameters to build a query plan and if the parameters are fine for the first invocation of the query, than you are fine with the performances. But if parameters dramatically changes, than you might need a new execution plan which will not be any more index seek but index scan. With the option RECOMPILE you are requesting from SQL SErver ptimizer to generate a new plan for your query and to ignore the existing one. Thus, you ghet proper plan. Note, that using always option RECOMPILE will decrease query performances if the proper plan will be found in the cashed plans.

In my environement, this option increases some of the queries more than 100 times.

Add NOT FOR REPLICATION to the column

If you had a table defined something like

CREATE TABLE [MyTable](
	[IdCol] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY

)

and later you want to add this table in the replication but you want that replication agent treats it differently when performs an insert operation. With NOT FOR REPLICATION option you can generaly control if foreign keys, check constraints, triggers and identity colums are enforced on the subscriber or not.

Updating column from previos definition to not enforce IDENTITY constraint to subscriber can be done with

ALTER TABLE [MyTable] ALTER COLUMN [MyCol] ADD NOT FOR REPLICATION

After this update table definition will looks like

CREATE TABLE [MyTable](
	[IdCol] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL PRIMARY KEY

)

SQL Server: Get cached plans

If you ever wanted to see which SQL plans are cached and how many times called, you can use the following query:

SELECT [cp].[refcounts] ,
[cp].[usecounts] ,
[cp].[objtype] ,
[st].[dbid] ,
[st].[objectid] ,
[st]. ,
[qp].[query_plan], [cp].[plan_handle]
FROM sys.dm_exec_cached_plans cp
	CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
	CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
	INNER JOIN sys.[databases] d ON st.[dbid] = d.[database_id]
WHERE d.[name] LIKE 'YOUR_DATABASE_NAME'
ORDER BY [cp].[usecounts] DESC

Provided plan handle can be used in

DBCC FREEPROCCACHE

as parameter to remove just selected sql plan from the cache.

SQL Server: How to PRINT statement during loop execution

Have you tried to print some information message during some loop execution on SQL Server? So statements like

DECLARE @i INT
DECLARE @msg AS VARCHAR(20)
SET @i = 0
WHILE <condition>
BEGIN
SET @msg = 'Updating ' + CAST(@i AS VARCHAR(30))
PRINT @msg
UPDATE TOP(50) myTable SET col1 = 0 WHERE <update condition>
WAITFOR DELAY '000:00:10'
SET @i = @i + 1
END

print out your messages just when your loop completes (or print in a blocks of messages from once).

But this is not what you wanted. You do not see progress of UPDATE statements. The reason for this is because SQL Server does not allow any interactivity during batch execution. Therefore, PRINT statement buffers it’s output.

To get message whithin the loop execution you should use

REISEERROR(…) WITH NOWAIT 

statement with the severity 10.

So loop will look like:

DECLARE @i INT
DECLARE @msg AS VARCHAR(20)
SET @i = 0
WHILE <condition>
BEGIN
SET @msg = 'Updating ' + CAST(@i AS VARCHAR(30))
RAISERROR(@msg, 10, 0 ) WITH NOWAIT
UPDATE TOP(50) myTable SET col1 = 0 WHERE <update condition>
WAITFOR DELAY '000:00:10'
SET @i = @i + 1
END

On this way you will see messages “Updating 1”, “Updating 2” … during loop execution.

 

Error: Length of LOB data (66291) to be replicated exceeds configured maximum

Solution

SQL Server replication uses max text repl size option as maximum size in bytes of text, ntext, varchar(max), nvarchar(max), varbinary(max), xml, and image data types to be replicated. Default value is  65536 and value of -1 means no limit. To solve this problem execute the following statement, which will reconfigure SQL Server:


EXEC sp_configure ‘max text repl size’, 2147483647

RECONFIGURE

Row sizes in a SQL Server table

This script will return real row sizes in a specified table, order by row size descending:

DECLARE @table VARCHAR(30)
DECLARE @idcol VARCHAR(10)
DECLARE @sql VARCHAR(MAX)
DECLARE @pos INT

SET @table = '&lt;table_name&gt;'
SET @idcol = '&lt;id_column&gt;'
SET @sql = 'select ' + @idcol +', ('

SELECT @sql = @sql + ' isnull(datalength(' + [name] + '), 1) + '
FROM syscolumns WHERE [id] = object_id(@table)
SET @sql = @sql + ') as rowsize from ' + @table + ' ORDER BY rowsize DESC'

SET @pos = LEN(@sql) - (CHARINDEX('+', REVERSE(@sql))-1)

-- remove last + sign from the query
SET @sql = LEFT(@sql, @pos-1) + ' ' + RIGHT(@sql, LEN(@sql) - (@pos) )
--PRINT (LEFT(@sql, @pos-1) + ' ' + RIGHT(@sql, LEN(@sql) - (@pos) ))
EXEC (@sql)

Table Sizes in SQL Server

The following query gives a sizes of tables and number of records for the database:

select o.name
, reservedpages = sum(a.total_pages)
, usedpages = sum(a.used_pages)
, pages = sum(case when a.type &lt;&gt; 1 then a.used_pages
when p.index_id &lt; 2 then a.data_pages else 0 end)
, SUM(a.used_pages)*8096 AS 'Size(B)'
, rows = sum(case when (p.index_id &lt; 2) and (a.type = 1) then p.rows else 0 end)
from sys.objects o
join sys.partitions p on p.object_id = o.object_id
join sys.allocation_units a on p.partition_id = a.container_id
where o.type = 'U'
group by o.name
order by 3 desc

or similar:

SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID &gt; 255
GROUP BY
t.Name, p.Rows
ORDER BY
3 DESC

The row was not found at the Subscriber when applying the replicated command

Error:
The row was not found at the Subscriber when applying the replicated command
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x0013DD67000002EA007F00000000, Command ID: 1)

Solution:
The error is beacuse an update or delete statement cannot find rows in the subscriber database.

Execute the below command in the distribution database to find the row which its trying to access. Check if this row is existing in the subscriber database.

exec sp_browsereplcmds @xact_seqno_start = ‘0x0013DD67000002EA007F00000000’
,@xact_seqno_end = ‘0x0013DD67000002EA007F00000000’
,@publisher_database_id = xxx
,@article_id = xxx
,@command_id= ‘1’

You could also try the below query & check for command id 1

select * from dbo.MSarticles
where article_id IN (SELECT Article_id from MSrepl_commands
where xact_seqno = 0x0013DD67000002EA007F00000000)

It is probably because someone might have deleted that row from the subscriber.

Or

Enable -OutputVerbloseLevel to 2 in the distribution agent & get the exact statement.

Get SQL Server Database Files Sizes

If you want to check how big are your databases on some SQL Server you need to query  [sys].[master_files]. Better results you will get with the following query:

SELECT CASE GROUPING(d.[name])
WHEN 1 THEN 'All'
ELSE d.[name]
END AS [DatabaseName],
CASE GROUPING(f.[name])
WHEN 1 THEN 'Total'
else f.[name]
END AS [FileName],
ROUND(SUM(f.[size]) * 8 / 1024, 0) SizeInMBs
FROM [sys].[master_files] f
INNER JOIN [sys].[databases] d ON d.[database_id] = f.[database_id]
GROUP BY [d].[name], f.[name] WITH ROLLUP
ORDER BY d.[name]