set date field null

to set the date field null
update table name set date = null

VB Script file to delete backup files

Create vbs file Vbscript file with the below code to delete backup files which are older than 3 days


Dim FsoDim Directory
Dim Modified
Dim Files Set Fso = CreateObject("Scripting.FileSystemObject")
Set Directory = Fso.GetFolder("E:\transfer\TulsaBackup\goes")
Set Files = Directory.Files
For Each Modified in FilesIf DateDiff("D", Modified.DateLastModified, Now) > 3 Then Modified.Delete
Next

VBScript to copy files across

Script to copy the backup files form one location to another. This is very useful to copy the back and diff back up files across the network as well.This script checks if the file not exists and then copies it.

The below script can run on a sql job, as activex script ( vbscript)

Dim sOriginFolder, sDestinationFolder, sFile,oFSO
Set oFSO = CreateObject("Scripting.FileSystemObject")
sOriginFolder = "G:\GOES DB Backup\Diff\ManufacturingTulsa"sDestinationFolder = "\\192.xxx.x.xxx\transfer\tulsabackup\ManufacturingTulsa\Diff"
For Each sFile In oFSO.GetFolder(sOriginFolder).Files If Not oFSO.FileExists(sDestinationFolder & "\" & oFSO.GetFileName(sFile)) Then
oFSO.GetFile(sFile).Copy sDestinationFolder & "\" & oFSO.GetFileName(sFile),True
End If Next
set oFSO = Nothing

Cannot delete the Distributor Database

I was working on the Replication on one of my production database, to have high availability in our europe locations, as the production database was in the US. The replication I tried out was Transaction replication. Before the replication I tried out the log shipping, and had the recovery model on Full, due to this the log file had grown to 456 GB.

Leaving the recovery mode on Full and working on the transaction replication is not advisable. So I had to delete the replication, I was able to delete the subscription, publication but not the distributor.

The distributor database was huge in size i.e., 456 GB. I could not delete this, the message was it is used by replication.

So to delete the distributor database --- to to management --- activity monitor --- view processes and kill any process running.

Then using sp_dropdistributor you can drop the database or click on replication on the database engine and click on disable publishing and distribution

SQL DTC error

error "OLE DB provider SQLOLEDB was unable to begin a distributed transaction"

This error may occur when use begin and commit transction when you are using linked server. In the case go to control panel --- administrator tools --- components --- click on Local DTC ---
properties and check Allow remote clients

check allow remote administration
check allow inbound
check allow outbound

Further to this you need to have a windows account which is authenticated on both the servers.

SQL Log space utilization

--Gives the log space utilization of every database

DBCC SQLPERF(LOGSPACE);
GO

Max size of Table, Rows and byte

This query is very useful to find out the tables which has max row count, column count and byte utilized


USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp