July 15, 2009 at 8:21 am
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 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 source of the recurring error?
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?
thank you for any help 🙂
Dan
July 15, 2009 at 8:52 am
Hi Dan,
the information logged in the AuditTrail table will be being logged by your web application, it really depends how many places in the code base that this is being called on how easy a task it would be to reduce the level of logging.
However, it doesn't sound as if this would be a great use of your time — locating all the places that this code is called from may be quite a time consuming process — as it has taken a number of months for the DB to fill up.
I would recommend you either truncate or selectively delete records (perhaps delete anything older than a given time, or delete all occurances of the error message you refer to older than a certain time) from AuditTrail which will give you time to focus on resolving the error.
Going forward it may be worthwhile at considering putting some housekeeping procedures in place which would periodically delete old records from AuditTrail — you could achieve this by implementing something in the web app, or perhaps using triggers or scheduled jobs with SQL Server Agent.
Hope this helps.
Allister
July 15, 2009 at 9:05 am
Hi Allister
many thanks for the quick response. Deleting all records in this table prior to a given date seems like the simplest way to approach this in the short term.
It may seem a rather elementary question but is there a command I could use in QA to do this, for example to delete all rows in this table prior to 23rd March 09 ?
Also once this is done, would I then need to Shrink the database to reclaim the empty space from this table?
Thanks
July 15, 2009 at 9:09 am
Hey Dan,
I had made the assumption that AuditTrail would have some timestamp column?
If this is the case then it'll be pretty easy DELETE FROM AuditTrail WHERE TimeStamp <= @YourChosenDate
If no timestamp column then ummmm can I see table structure and some sample data?
No need to shrink.
July 15, 2009 at 9:13 am
Thanks buddy. Yes it has timestamp so it's easy for me to pick a cut-off date.
Fingers crossed that comnand should do the trick then 🙂
July 15, 2009 at 9:20 am
Before doing a delete I often find it useful to do a select with the same where clause first so SELECT TOP 100 * FROM AuditTrail WHERE TimeStamp <= '2009/3/23' and/or SELECT COUNT(*) FROM AuditTrail WHERE TimeStamp <= '2009/3/23'
July 15, 2009 at 9:42 am
Hi Allister
I'm right-clicking the table > Edit , and pasted in this command:
DELETE FROM AuditTrail WHERE TimeStamp <= '2009/04/04'
It parses successfully but when I execute it, it gives these errors:
Msg 2714, Level 16, State 6, Line 1
There is already an object named 'AuditTrail' in the database.
Msg 207, Level 16, State 1, Line 3
Invalid column name 'TimeStamp'.
Just realised the column with the date and time in is called EntryDate, so I guess I need to use that instead of TimeStamp?
cheers
July 15, 2009 at 9:48 am
Hi Dan,
It could be, without more details I can't be sure.
Is there anyone else involved with this project, or are you on your own on this?
Also are you sure the backup completed successfully?
Allister
July 15, 2009 at 9:59 am
It's just me unfortunately.
I'm pretty certain the backup was successful as MSEE reported it as completing successfully, and the .bak file is just over 4Gb in size.
Would a screen shot of MSEE help maybe?
thanks
EDIT: although it would take a bit of time, could I also just open the table, select a large block of rows by holding down shift, and right-click > delete ?
July 15, 2009 at 10:28 am
If no one else is relying on data in AuditTrail, and you have it backed up, you could consider a truncate on the table (http://msdn.microsoft.com/en-us/library/ms177570.aspx).
However, it concerns me that you are on your own on this and so unsure of what to do. E.g. you are getting an 2714 error (There is already an object named 'AuditTrail' in the database.) Why is this? Are you calling create table above the delete command?
Are you working on the live database/webserver? If so I think you should set a test environment as a matter of urgency (once you have the space issue resolved).
Good luck.
July 16, 2009 at 2:45 am
morning Allister
I don't blame you for being concerned - my general Windows server knowledge is good but SQL is by far my weakest area (which I'm sure is obvious by now!). Unfortunately it's just me looking after the website/hosting server despite it not being my main area of expertise - we are a pretty small company.
In the end I took another backup last night, and decided to bite the bullet and run the truncate command, as that table was full of nothing but endless repetitions of the error message I mentioned.
Anyway it all seems ok, and thankfully it fixed the problems we were having with the website.
Thanks for all your help and patience, I really appreciate it.
July 16, 2009 at 2:53 am
No problem Dan, glad I could help.
Allister
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply