Archive

Archive for December, 2012

Transaction Logging Mode in Oracle and Sql Server

December 20, 2012 Leave a comment

Oracle has two options for logging: ARCHIVELOG mode and NOARCHIVELOG mode.

Transactions are still logged to the online redo logs, but the difference here is whether or not the redo logs are archived out to disk when full.

SQL Server has three different logging modes: Full, Bulk Logged and Simple. Full and Simple respectively correspond to the two Oracle methods.

Categories: Uncategorized

Enable Archivelog in Oracle

December 19, 2012 Leave a comment

(1)

sqlplus / as sysdba

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE

Categories: Uncategorized

SQL Server configure recovery inteval

December 7, 2012 Leave a comment

EXEC sp_configure ‘recovery interval’,’300′

RECONFIGURE WITH OVERRIDE

sp_configure

‘show advanced options’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure

Categories: Uncategorized

SQL Server simple recovery model and Transaction log

December 7, 2012 Leave a comment

Checkpoint is the word:

Even in Simple recovery mode SQL still needs to write all logged operations to the SQL transaction log as SQL is build using write ahead logging.

Backing up the database does nothing for the transaction log in any of the recovery modes.

The actual process that allows log file truncation and wrap around to complete in simple mode is a checkpoint.

Categories: Uncategorized

SQL 2008: Shrink a log file

December 7, 2012 Leave a comment

Update on test database filled up Transaction logs on a SQL Server. They are now big more than 50 GB.

Solution:

(1) Shrink the transaction log

USE TEST

GO

DBCC SHRINKFILE(TEST_LOG1, 1)

BACKUP LOG TEST TO DISK = ‘nul:’ WITH STATS = 10

DBCC SHRINKFILE(TEST_LOG1, 1)

GO

USE TEST

GO

DBCC SHRINKFILE(TEST_LOG2, 1)

BACKUP LOG TEST TO DISK = ‘nul:’ WITH STATS = 10

DBCC SHRINKFILE(TEST_LOG2, 1)

GO

(2) set recovery to simple as it is a test database to avoid this in the future.

ALTER DATABASE TEST SET recovery simple

Categories: Uncategorized

List sql server configuration parameters

December 6, 2012 Leave a comment

– Connect to the sql server instance as superuser

– Open a new query window

– Run following commands

sp_configure

‘show advanced options’, 1

GO

reconfigure

GO

sp_configure

Categories: Uncategorized