SQL Large log file size

There are instance where the log files have grown up too large for example 200 gb of file size, in that case, here are the following steps to be done to reduce the log file.
1.Check the database options if it is simple change it to Full.
2. Backup the database using the below script.
--****************************************************
--Backup database script
--****************************************************
BACKUP DATABASE [lmkdw] TO
DISK = N'd:\lmkdw_backup.bak' WITH NOFORMAT,
INIT, NAME = N'lmkdw_backup', SKIP, REWIND, NOUNLOAD,
STATS = 10
3. Then backup the log file using the below script
--****************************************************
--Backup database script
--****************************************************
BACKUP LOG [apacdw] TO DISK = N'd:\lmkdw.trn'
WITH NOFORMAT, INIT, NAME = N'lmkdw-Transaction Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
go
4. Now shrink the log file using the below script
DBCC SHRINKFILE (N'lmkdw_Log' , 786)
5. Finall turn the database options back to simple or based on your needs.

Robocopy Tool

Robocopy is a awesome tool to copy backup files. Here is the syntax to copy files.
robocopy "Z:\DATABASE\FULLBACKUP" "\\xxx.xxx.x.xxx\backups\FullBackup" /zb /copyall /purge /MIR

robocopy "source" "destination"

Copy options :
/S :: copy Subdirectories, but not empty ones./E :: copy subdirectories, including Empty ones./LEV:n :: only copy the top n LEVels of the source directory tree.
/Z :: copy files in restartable mode./B :: copy files in Backup mode./ZB :: use restartable mode; if access denied use Backup mode./EFSRAW :: copy all encrypted files in EFS RAW mode.
/COPY:copyflag[s] :: what to COPY for files (default is /COPY:DAT).(copyflags : D=Data, A=Attributes, T=Timestamps).(S=Security=NTFS ACLs, O=Owner info, U=aUditing info).
/DCOPY:T :: COPY Directory Timestamps.
/SEC :: copy files with SECurity (equivalent to /COPY:DATS)./COPYALL :: COPY ALL file info (equivalent to /COPY:DATSOU)./NOCOPY :: COPY NO file info (useful with /PURGE).
/SECFIX :: FIX file SECurity on all files, even skipped files./TIMFIX :: FIX file TIMes on all files, even skipped files.
/PURGE :: delete dest files/dirs that no longer exist in source./MIR :: MIRror a directory tree (equivalent to /E plus /PURGE).
/MOV :: MOVe files (delete from source after copying)./MOVE :: MOVE files AND dirs (delete from source after copying).

Copying folders, subfolders and files

Copying folders, subfolders and files from one location to another and over writing it.Very useful for backing up of files.

Const OverWriteFiles = TrueSet objFSO = CreateObject("Scripting.FileSystemObject")objFSO.CopyFolder "c:\vijay\t1" , "c:\vijay\t2" , OverWriteFiles
set objfso = nothing

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