Shrink the Unshrinkable SQL Transaction Log

There are many reason for not shrinking of Transaction log. Our production database backup was failing for last several weeks, so the backup process could not clear out the transaction log. Our approx 1 GB database grow up to 37.5 GB.

The SQL Server GUI for shrinking the database rendered no effect, and even using the DBCC SHRINKFILE command was not working.

The key, as explained by Pinal Dave, is to run the SHRINKFILE command twice,with an explicit backup log truncation in between both runs. This code here will get you up and running:

DBCC SHRINKFILE("DemoData_Log", 1)

BACKUP LOG DemoData WITH TRUNCATE_ONLY

DBCC SHRINKFILE("DemoData_Log", 1)

Query to get SQL Server name, Version, Edition, Authentication Mode, No of CPU and RAM

Below query returns the SQL Server name, Version, Edition, Authentication Mode, No of CPU and RAM available in system

SELECT SERVERPROPERTY('ServerName') AS [SQLServer],
SERVERPROPERTY('ProductVersion') AS [VersionBuild], 
SERVERPROPERTY('ProductLevel') AS [Product],
SERVERPROPERTY ('Edition') AS [Edition],
SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly], 
SERVERPROPERTY('IsClustered') AS [IsClustered], 
[cpu_count] AS [CPUs], 
round(cast([physical_memory_in_bytes]/1048576 as real)/1024,2) AS [RAM (GB)]FROM [sys].[dm_os_sys_info]

Backup types in sql server

In SQL Server 2008 or later following are the backup types:


Full backup
Differential backup
Partial backup
Differential partial backup
File backup
Differential file backups
Transaction Log Backups
Copy-Only Backups

Find out which host / IP address and Program deleted the records from SQL Server

You will not be able to know from which system records are deleted directly. But you can get this information if you add a trigger and a logging table. Use below query to implement a logging table and a trigger:

CREATE TABLE dbo.DeleteLogTable(   PK_From_Table INT,  Host NVARCHAR(128),  IP VARCHAR(48),  Program NVARCHAR(128),  Deleted_When DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP);GO

CREATE TRIGGER dbo.LogDelete_TableON dbo.TableNameFOR DELETEASBEGIN  SET NOCOUNT ON;  DECLARE @p NVARCHAR(128), @h NVARCHAR(128), @i VARCHAR(48);  SELECT @p = s.host_name, @h = host_name, @i = c.client_net_address    FROM sys.dm_exec_sessions AS s    INNER JOIN sys.dm_exec_connections AS c    ON s.session_id = c.session_id    WHERE s.session_id = @@SPID;  INSERT dbo.DeleteLogTable(PK_From_Table, Program, Host, IP)    SELECT PK_Column, @p, @h, @i    FROM deleted;END
GO

Script to list dependencies on database objects

Use below query to get a List of Database Dependencies Object: 

SELECT OBJECT_NAME(FKC.PARENT_OBJECT_ID) Foreign_Key_Table,
C2.NAME Foreign_Key_Field,
OBJECT_NAME(FKC.REFERENCED_OBJECT_ID) Primary_Key_Table,
C3.NAME Primary_Key_Field,
OBJECT_NAME(FKC.CONSTRAINT_OBJECT_ID) Relation_Name
FROM  SYS.FOREIGN_KEY_COLUMNS FKC
JOIN  SYS.COLUMNS C2 ON FKC.PARENT_OBJECT_ID = C2.OBJECT_ID
            AND FKC.PARENT_COLUMN_ID = C2.COLUMN_ID
JOIN  SYS.COLUMNS C3 ON FKC.REFERENCED_OBJECT_ID = C3.OBJECT_ID
            AND FKC.REFERENCED_COLUMN_ID = C3.COLUMN_ID
ORDER BY Foreign_Key_Table
GO

Msg 306, Level 16, State 2, Line 23

You will receive below error when you try to ORDER BY or GROUP BY a column with data type Image, Text, Ntext.

Msg 306, Level 16, State 2, Line 23
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Image – Data type is used for storing variable length binary data.
Ntext – Data type is used for storing variable length of Unicode data.
Text – Data types is used for storing variable length non-Unicode data.

The image, text and ntext, data type columns cannot be compared or sorted, except when using IS NULL or LIKE operator.

The solution for this type of error is to convert Image column to VarBinary, Text column to VarChar and Ntext column to nVarChar when you use these columns in ORDER BY or GROUP BY clause. 

Example for GROUP BY clause:
SELECT * FROM dbo.CustomerMaster GROUP BY CustomerType;

Modify the query as 
SELECT * FROM dbo.CustomerMaster  GROUP BY CAST (CustomerType as nvarchar)

Example for ORDER BY clause:
SELECT * FROM dbo.CustomerMaster ORDER BY CustomerName;

Modify the query as 
SELECT * FROM dbo.CustomerMaster ORDER BY CAST(CustomerName as nVarChar)