Natty Boh man on Thu, 28 Feb 2013 18:54:37
I have VERY little experience with SQL and I'm supporting an Event Log Management server which uses SQL 2008 as it's database.
We are required to keep all Event Logs and use an application to pull Event Logs from all workstations once a week into the database server. The server is Windows 2008 R2.
Right now the database storing the log files is 300GB with 125GB left on the partition. I'd like to figure out a way to free up some space.
Possible solutions may be to move the database somewhere else, shrink the database, create a new database to dump the event logs into and then move the old one... etc.
As I said, I don't have a lot of experience with this and the simplest way would be the best. I am ok for now, but can see a problem down the road if I don't free up some space.
Thanks, in advance, for your time.
Stan210 on Thu, 28 Feb 2013 20:37:43
IMHO, all the options are okay except shrinking. Rest of the options you have to evaluate with your business side as well.
Deleting the data is more on the business side than technical. so, You may want to check with your manager to see if it is possible and if so, how much data could be deleted.
Let's say: you can delete data older than 6 months. You can have purge(delete) transaction as a step before you get the New inserts on weekly basis.assuming the new data and deleted data is almost same size, your database ideally should not grow significantly.
Worst Case: If it comes down to the point where you need extra space, you can add a new file to database pointing to a different drive(assuming you have one available)
Move the database to a different drive :
You can move the database but how much is large enough for you database. you might still encounter this problem later.
Create a new database:
This is again a business decision but I do not see any reason you would want to create a similar new database you almost can achevie the same thing with adding new file to the current database.
my suggestion : Try to see if you can delete the rows older than certain date on a regular bases and see if that can give enough space and since you would be doing it on regular bases, you should be good. Else, you can create new file to the database on a different drive. Also, make sure if the new disk is atleast as good as the current one so that applications performance is not adversly effected.
Hope it Helps!!