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


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

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.