Reduce The Size Of SQL Server Transaction Log

Introduction
To stop the nightmare of low disk space which cased by huge transaction log file. There three things to do. First truncate the inactive items in the transaction log. However it does not reduce the size of the log file. Secondly shrink the transaction log file. Finally, If possible set a fix maximum size of the transaction log file.

Truncate the inactive items in the transaction log
There are two ways to truncate the transaction log. Doing backup transaction log in the enterprise manager and truncate the transaction log by using T-SQL command.

Using SQL server enterprise manager to backup the transaction log will automatically truncate the inactive transactions after the backup. By doing this you need:
* Open the enterprise manager from the desktop shortcut or start menu.
* In the enterprise manager expend following items in the Console Root tree one by one: Microsoft SQL Servers, SQL Server Group, The database server which need perform backup task, Databases.
* Right click the database name and point to All Tasks, and then select Backup Database.
*Fill in the task name and description
* Click the radio button in front of the Transaction Log which in the backup section.
*In the Overwrite section choose Append to media or Overwrite existing media.
* Go to the Option tab make sure Remove inactive entries from transaction log has been selected.

If there are no enough space on the backup device or the backup of transaction log is not necessary. Executing truncate command in SQL Query Analyzer could be more straightforward.
* Open SQL Query Analyzer from start menu or executing the isqlw utility
* Select database which need truncate transaction log.
* Type in following T-SQL command:
use database name
BACKUP LOG WITH TRUNCATE_ONLY
* Press the execute button.

Shrinking the transaction log

Execute following command in the SQL Query Analyzer.

USE DatabaseName
DBCC SHRINKFILE(transaction log file name,desired shrink size in MB)


Fix the maximum size of the transaction log so you do not need worry about it again.

Open the enterprise manager from the desktop shortcut or start menu.
* In the enterprise manager expend following items in the Console Root tree one by one: Microsoft SQL Servers, SQL Server Group, The database server which need perform backup task, Databases.
* Right click the database name and point to Properties.
* Go to Transaction Log tab in the Database Properties window.
* Select Restrict file growth(MB): and fill in the size.

hsqldb_stratup_usage

Usage: java org.hsqldb.Server [options]

OPTION	TYPE	DEFAULT	DESCRIPTION
-?	--	--	prints this message
-address	name|number	any	server inet address
-port	number	9001/544	port at which server listens
-database.i	[type]spec	0=test	name of database i
-dbname.i	alias	--	url alias for database i
-silent	true|false	true	false => display all queries
-trace	true|false	false	display JDBC trace messages
-tls	true|false	false	TLS/SSL (secure) sockets
-no_system_exit	true|false	false	do not issue System.exit()

The server looks for a 'server.properties' file in the current directory and
loads properties from it if it exists.

Here is an example 'server.properties' file:

server.port=1234
#mounts a file-based (persistent) database with alias 'filetest'
#database connection url would be 'jdbc:hsqldb:hsql://host:1234/filetest'
#but '/filetest' can be omitted because database.0 is the default
server.database.0=file:/mydatbasedir/test
server.dbname.0=filetest
#mounts a 'file_in_jar' database with alias 'restest'
#database connection url would be 'jdbc:hsqldb:hsql://host:1234/restest'
server.database.1=res:/mypackage/test
server.dbname.1=restest
#mounts a 100% in-memory (transient) database with alias 'memtest'
#database connection url would be 'jdbc:hsqldb:hsql://host:1234/memtest'
server.database.2=mem:test
server.dbname.2=memtest
...
server.database.n=...
server.dbname.n=...
server.silent=true

#end of 'server.properties' file

Command line options override those loaded from the 'server.properties' file.

The default port is 9001 if tls is false and 544 if it is true.
When omitted, the value of tls is true iff the system property:

javax.net.ssl.keyStore

is defined. An example command line invocation requesting TLS might look like:

java -Djavax.net.ssl.keyStore=x -Djavax.net.ssl.keyStorePassword=y org.hsqldb.Server [options]

HSQLDB server model startup example:

  java -cp ../lib/hsqldb.jar org.hsqldb.Server -database.0 mydb -dbname.0 xdb

HSQLDB manager starup example:

  java -cp ../lib/hsqldb.jar org.hsqldb.util.DatabaseManager