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

High Availability

Failover clustering and database mirroring both provide the following:
Automatic detection and failover
Manual failover
Transparent client redirect
Failover clustering has the following constraints:
Operates at the server instance scope
Requires signed hardware
Has no reporting on standby
Utilizes a single copy of the database
Does not protect against disk failure

Database mirroring offers the following benefits:
Uses a single, duplicate copy of the database
Note: If you require additional copies, you can use log shipping on the database in addition to database mirroring.
Uses standard servers
Provides limited reporting on the mirror server by using database snapshots.
When it operates synchronously, provides for zero work loss through delayed commit on the principal database.

Database mirroring offers a substantive increase in availability over the level previously possible with SQL Server and offers an easy-to-manage alternative to failover clustering.
Asynchronous database mirroring is Not supported on standard edition. Asynchronous is only supported in Enterprise Version.

Log shipping

Log shipping can be a supplement or an alternative to database mirroring. Although similar in concept, asynchronous database mirroring and log shipping have key differences. Log shipping offers the following distinct capabilities:

Supports multiple secondary databases on multiple server instances for a single primary database.

Allows a user-specified delay between when the primary server backs up the log of the primary database and when the secondary servers must restore the log backup. A longer delay can be useful, for example, if data is accidentally changed on the primary database. If the accidental change is noticed quickly, a delay can let you retrieve still unchanged data from a secondary database before the change is reflected there.
Asynchronous database mirroring has the potential advantage over log shipping of a shorter time between when a given change is made in the primary database and when that change is reflected to the mirror database.
An advantage of database mirroring over log shipping is that high-safety mode is a no data loss configuration that is supported as a simple failover strategy.

Note:
For information about how to use log shipping with database mirroring, see Database Mirroring and Log Shipping.



Replication - Replication offers the following benefits:

Allows filtering in the database to provide a subset of data at the secondary databases because it operates at the database scope

Allows more than one redundant copy of the database

Allows real-time availability and scalability across multiple databases, supporting partitioned updates

Allows complete availability of the secondary databases for reporting or other functions, without query recovery.

Transaction Log Size

The transaction log size gets increased to the size of the disk, if the log file is not shrunk. For example if you see a transaction log file of size 274 GB, which is in my case as consumed all the disk space in the server.

The following need to be checked.

The recovery model option right click database properties - options - is this set to FULL or Simple.

If this set to FULL, then the log
In the full and bulk-logged recovery models, a sequence of transaction log backups is being maintained. The part of the logical log before the MinLSN (Log sequnce number) cannot be truncated until those log records have been copied to a log backup.

This means the following script need to be run

--back up the transaction log file
use Manufacturingarchive
BACKUP LOG [Manufacturingarchive] TO DISK = N'E:\Manufacturing.trn' WITH NOFORMAT, NOINIT,
NAME = N'ManufacturingArchive-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

--shrink the log file
go
use Manufacturingarchive
DBCC SHRINKFILE (N'MANUFACTURINGARCHIVE_Log' , 7)

--shrink the data file
use Manufacturingarchive
DBCC SHRINKFILE (N'MANUFACTURINGArchive_Data' , 786)

exec sp_helpfile

Now how do I reclaim the space in the server is the question?

The above query reclaims the logical space, but to relaim the physical space on the change the recovery model option --- right click database --- properties --- options --- to Simple then run
use manufacturingarchive
exec sp_helpfile

dbcc shrinkfile (MANUFACTURINGARCHIVE_Log,100,TRUNCATEONLY)

The above recove the space on the server.

SQL Server space issue

I had situation here with one of our affiliate sql server crashed in USA, we had integrations running between our local (Ireland) and USA server, since the usa server crashed the Ireland server started to slow down with no reason.

Having looked at the log evertime the integrations failed we had log files building up. In the following folders the log files can be deleted.

i. C:\Documents and Settings --- this had log files and pc health cab files
ii. C:\WINDOWS\Temp
iii. C:\WINDOWS\system32\LogFiles
iv. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
v. c:\program Files\Microsoft SQL Server\MSSQL.2\OLAP\Log
vi. C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles