ALTER Schema / Owner name

Query to change the schema / Owner of database objects like 

EXEC sys.sp_changeobjectowner <Object Name>, <New Owner Name>

The Object Name(@objname) parameter should in the format "[owner].[object]". The New Owner Name(@newowner) should be valid name from sysUsers object

SELECT * FROM SysUsers

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