The transaction log for database is full

Error message:

The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

Possible solutions and checks:

Make sure you have enough space free on the drive.
Shrink Database and/or files.
You can change the database RECOVERY model to SIMPLE (or BULK) if you want to keep log file minimal. (not recommended in production databases).

Go Further. Forward. Faster. with Microsoft SQL Server 2012

Go Further. Forward. Faster. with Microsoft SQL Server 2012:

http://www.sqlserverlaunch.com/ww/Home

Search part of words using CONTAINS command

In order to search part of words using CONTAINS command add * in the start and/or the end of the search Phrase:

select * from TABLE WHERE CONTAINS(Field4Search, N'"*copy pas*"')

If you have a parameter, you need to add those 2 *: 

(N'"*' + @TxtValue + N'*"')

Parent tag ID 1 is not among the open tags

Error message:

Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set

Cause and solution:

Wrong Parent Tag:

Check the parent field, make sure that the parent is not NULL (instead of the root), and that the Parent ID is already created and/or it's among the open tags.

Comment out the FOR XML EXPLICIT part of the query to get the results and understand the ordering.

Maybe you need to add/remove/change the order of the tag and/or the parent fields in the ORDER BY part of the query.

Recursive CTE

With T As

( Select    1 N -- first/base action

Union All

Select    N+1

From    T

Where    N < 10 -- number of times to repeat the action

)

Select    * From    T

Option (MaxRecursion 0);


An example: Get a list of all of the letters:
With T As
(
Select N'a' Letter, -- first letter
N'bcdefghijklmnopqrstuvwxyz' Letters
Union All
Select LEFT(Letters, 1) Letter, 
RIGHT(Letters, len(Letters)-1) Letters
From T
Where len(Letters)>0
)
Select Letter
From    T
Option (MaxRecursion 0);

Partition Scheme DMVs

-- Partition Functions:

SELECT PF.function_id, PF.name, PF.fanout AS NumPartitions, 

CASE WHEN PF.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'RIGHT' END AS RangeType, 

PP.parameter_id, 

CASE WHEN PP.system_type_id = PP.user_type_id THEN T1.name ELSE T2.name END AS ParameterDataType

FROM sys.partition_functions AS PF

JOIN sys.partition_parameters AS PP ON PF.function_id = PP.function_id

JOIN sys.types AS T1 ON T1.system_type_id = PP.system_type_id

JOIN sys.types AS T2 ON T2.user_type_id= PP.user_type_id

-- Index - Partitioned Table - Partition Scheme - Partition - Function

SELECT SI.name AS IndexName, OBJECT_NAME(SI.object_id) AS PartitionedTable, 

DS.name AS PartitionScheme, PF.name AS PartitionFunction

FROM sys.indexes AS SI

JOIN sys.data_spaces AS DS ON DS.data_space_id = SI.data_space_id

JOIN sys.partition_schemes AS PS ON PS.data_space_id = SI.data_space_id

JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id

WHERE DS.type = 'PS' AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' AND SI.index_id IN(0,1)

-- Index - Partitioned Table - Partition Scheme - Partition - Function - more detailed

SELECT SI.name AS IndexName,  OBJECT_NAME(SI.object_id) AS PartitionedTable, DS.name AS PartitionSchemeName, PF.name AS PartitionFunction, 

P.partition_number AS PartitionNumber, P.rows AS PartitionRows, FG.name AS FileGroupName

FROM sys.partitions AS P

JOIN sys.indexes AS SI ON P.object_id = SI.object_id AND P.index_id = SI.index_id

JOIN sys.data_spaces AS DS ON DS.data_space_id = SI.data_space_id

JOIN sys.partition_schemes AS PS ON PS.data_space_id = SI.data_space_id

JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id

JOIN sys.destination_data_spaces AS DDS ON DDS.partition_scheme_id = SI.data_space_id AND DDS.destination_id = P.partition_number

JOIN sys.filegroups AS FG ON DDS.data_space_id = FG.data_space_id

WHERE DS.type = 'PS' AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' AND SI.type IN(0,1)

Comparison to the previous cell in a SSRS table

IIF(Fields!Cell.Value <> Previous(Fields!Cell.Value),"Solid","None")