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

Tables to clear when space is an issue

delete from dbo.LMK_POP10100_PURCHASEORDER_HDR where docdate < '2010-01-10'
delete from dbo.LMK_POP10110_PURCHASEORDER_DET where reqdate <= '2010-01-01'
delete from dbo.LMK_AR_Errors_Data_To_GP
delete from dbo.LMK_BSB10100_OUTBOUND_Errors
delete from dbo.LMK_Mat_Issues_Errors_Data_To_GP
delete from dbo.LMK_Payables_Credit_Errors_Data_To_GP
delete from dbo.LMK_Payables_Errors_Data_To_GP
delete from dbo.LMS_GP_MFG_CustomerMaster_error
delete from dbo.BV_AR_Errors_Data_To_GP
delete from dbo.BV_Payables_Credit_Errors_Data_To_GP
delete from dbo.BV_Payables_Errors_Data_To_GP

SQL 2005 PAGE FILE BIG

Today we had a page file size issue, due to this the memory utilization was 16 GB, this stop all the integrations and giving out time out errors, the following actions where taken to resolve this.

1. Restart the sql server.
2. Check the database size.
3. Shrink the database mdb and log file size using the following scripts.
4. Check the max table size.

use manufacturing
exec sp_helpfile
dbcc shrinkfile (Manufacturing_Data)

SQL Database on Suspect Mode

We had a scenario in our US office where the Sql server was abruptly stopped due to power outage.

This has caused the database to go on SUSPECT Mode.

To get the database back on line the following sql scripts can be used.

The below script is used to check the database.

DBCC CHECKDB ('ManufacturingTulsa') WITH NO_INFOMSGS,

ALL_ERRORMSGS

The below script is used bring the database on line.

EXEC sp_resetstatus 'ManufacturingTulsa';

ALTER DATABASE ManufacturingTulsa SET EMERGENCY

DBCC checkdb('ManufacturingTulsa')

ALTER DATABASE ManufacturingTulsa SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('ManufacturingTulsa', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE ManufacturingTulsa SET MULTI_USER

SQL Log Shipping

SQL Log Shipping is an automated process of backingup, copying and restoring the transactionlog from one database from a primary server to one or more secondary server.

Using Log shipping you can frequently synchronize the copy of the database with the original , so this can be used as warm standy by database for high availability.

I would see this as another alternative of database mirroring where asynchronous mirroring is only available in the sql enterprise edition.