Sql-server – Efficient table structure for application logging

sql servertable

Am using SQL Server as my RDBMS and i want to maintain some transaction of an auto run tool which will log some info on a daily basis. I need some suggestions how an efficient structure to be.

I mean single table will be an efficient one or two or more tables based on the transaction type (say for example success,failure).

If possible, explain with a simple scenario

Best Answer

1) The first thing you would need to figure out, what recovery model you should go with , i.e. decide accordingly as per the need between , FULL , BULK and SIMPLE recovery model.

Read here for configuring the same: Choosing the Recovery Model for a Database

2) When you create a new database in SQL Server 2008 and SQL Server 2005, the data and log files have the following characteristics by default:

Data: initial size 2MB; Autogrowth: by 1MB, unrestricted growth

Log: initial size 1MB; Autogrowth: by 10 percent, unrestricted growth

Therefore the first thing you will need to look upon what is the default value you will like to give while setting up for first time.

Its better to test the activity first on test env, come up with a minimal amount of capacity planning to help define your initial size and a maximum size.

3) Once you have come up with a default values for log file, its recommended to monitor the usage of log file or track its growth:

There are various ways of doing that.

You can use below query and collect the results storing in a table over a period of time via SQL agent job:

SELECT name,
  size, -- in 8-KB pages
  max_size, -- in 8-KB pages
  growth,
  is_percent_growth
FROM sys.database_files
WHERE type_desc = 'LOG'

or using DBCC SQLPERF(LOGSPACE)

4) If a database is configured with the Full or Bulk Logged recovery model, you should back up the transaction log regularly so it can be truncated to free up inactive log space. The backup can also be used (along with the database backups) to restore the database in the event of failure.

Therefore its better to choose you're defaults correctly in order to avoid lot of event changes in log that may hamper the performance.

Related Question