Huge "dbo.AuditTrail" table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Huge "dbo.AuditTrail" table

Author

Topic

dannyboy75
Starting Member

2 Posts

Posted - 2009-07-15 : 10:48:59

Hi all

Our website is hosted on a dedicated server by our hosting company. We just use SQL Express 2005 for the database part, as 4Gb should be plenty for our needs.

However, I noticed that in the space of ~ 4 months the database has hit its 4Gb limit. On further investigation, 99% of this data is in the dbo.AuditTrail table, which has 3.4m rows, nearly all of which are for the same recurring error message. I will look into this in my own time, as I have some idea what is causing it, and is kinda specific to how our website is configured. However I have 2 questions I am hoping you guys can advise on:

1) Is there a quick way to reduce the logging level / amount of information recorded in the AuditTrail, until I pinpoint the exact cause of the recurring error message?

2) I have backed up the database. Is there a way to clear down this AuditTrail table now, so that I can reduce the overall database size back to well under 4Gb?

thank you for any help

Dan

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-07-15 : 19:02:44

(1) depends on the program that is loggint the audits..
(2) sure..could'nt you truncate the table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dannyboy75
Starting Member

2 Posts

Posted - 2009-07-16 : 04:22:18

Hi Dinakar

thanks for your reply. Yes in the end I ran the truncate command, after backing up the database. (I should have made clear that my SQL knowledge is pretty basic, so I wasn't aware of this command until I found it on MSDN). It ran fine though and all is ok now.

ScottWhigham
Starting Member

49 Posts

Posted - 2009-09-03 : 06:42:10

Just so you know, there is no "dbo.AuditTrail" table by default; that was added somehow by you or your developers.

1) "Is there a quick way to reduce the logging level" - we have no clue since we don't know what is doing the auditing. Check with your developers.

2) "I have backed up the database. Is there a way to clear down this AuditTrail table now, so that I can reduce the overall database size back to well under 4Gb?" If you still want statistics of errors for reporting, what you could do is to create a summary_of_errors type of table that coalesces all errors for a given day (or week/month - whatever) into a single row.

Example:

CREATE TABLE dbo.AuditHistory (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY, TheDate DATETIME, NumberOfErrors INT)
GO
INSERT dbo.AuditHistory
SELECT CAST(CAST(@Date AS CHAR(11)) AS DATETIME) AS StartOfDay, COUNT(*)
FROM dbo.AuditTrail
GROUP BY CAST(CAST(@Date AS CHAR(11)) AS DATETIME)
GO

You could then have some sort of routine that clears out dbo.AuditTrail after "archiving" that data.

I've kept the example simple but you should be able to use this as a basis for some type of archival procedure/plan.

========================================================

I have about 1,000 video tutorials on SQL Server 2008, 2005, and 2000 over at http://www.learnitfirst.com/Database-Professionals.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources