Log table with millions of rows. Now, what?

I have a Log table with millions of rows. I’m thinking about separating the data into multiple tables (i.e. LoginHistory, ExceptionHistroy, PaymentProcessingHistory, etc.) What is the term used when taking a large table with many rows (not Columns) and creating multiple tables?

My current Log table schema resembles: LogID, LogMessage, LogReason, LoggedBy, LoggedOn, etc.

  • SQL Server Trigger Error-Subquery returned more than 1 value
  • How can I write an Excel plugin that loads a SQL Server data table?
  • Database Tuning Advisor recommends to create an existing index
  • conversion of a varchar data type to a datetime data type resulted in an out-of-range value
  • UPDATE Stored Procedure not Updating
  • Detect last login date/time in SQL Server 2000
  • The problem is I’m putting too many things in one table, I think? Perhaps the table is too generic?

    Thanks

  • PHP Insert Table Data Code not Inserting Data into MSSQL Table
  • SQL Server : splitting the results of GROUP BY into a separate columns
  • Why do I receive a Mutator error when modifying an Xml value in Sql
  • pyodbc on SQL Server - How can I do an insert and get the row ID back?
  • How To Create Generic SQL Server Stored Procedure To Perform Inserts Into Audit Table Based on Inserted and Deleted In Trigger
  • Insert Picture into SQL Server 2005 Image Field using only SQL
  • 3 Solutions collect form web for “Log table with millions of rows. Now, what?”

    It is called data partitioning.

    Sharding is the term de jour. From the link:

    Horizontal partitioning is a database design principle whereby rows of a database table are held separately, rather than splitting by columns (as for normalization). Each partition forms part of a shard, which may in turn be located on a separate database server or physical location.

    I think you should be looking at horizontal partitioning. Horizontal partitioning is more or less a subset of sharding.

    For more details on horizontal partitioning, here’s the wikipedia link:
    http://en.wikipedia.org/wiki/Partition_%28database%29

    You didn’t mention which DB technology you use, but here are some technical links that can get you going with partitioning your data:

    IBM DB2 partitioning
    MySQL partitioning
    Oracle partitioning
    SQL Server partitions
    PostgreSQL partitioning
    Sybase ASE 15.0 partitioning

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.