January 20, 2011 at 8:39 am
I have a database that is at the 4096MB limit. I already backed up
the database, but just want to make room by deleting the older records.
Is there an easy way to do this? (I'm a newbie at SQL)
Thanks!
mwk
January 20, 2011 at 9:02 am
I have a database that is at the 4096MB limit.
Is this the limit that just shows in SSMS or is there a space restriction or limit you are about to reach on your server? Is this a user database or one of the system databases (master, msdb, tempdb)?
I already backed up the database
For this to make free space in your database it depends on what recovery model your database is. If you are in Full recovery model you need to perform log backups on a regular basis to keep your database size in check.
just want to make room by deleting the older records. Is there an easy way to do this? (I'm a newbie at SQL)
Not really. It would depend on the structure of your database. If you have foreign keys on tables then it can be a little more work to get the old records out.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 20, 2011 at 9:07 am
Are you on SQL server express?
If so you can always upgrade to 2008 R2 and enjoy a 10 gb limit instead assuming you can't afford a license at the moment.
January 20, 2011 at 9:22 am
This is the DB limit in the SQL Express 2005 version I'm using.
The event log errors confirm I am at the limit! 🙂
January 20, 2011 at 9:23 am
Yes, I may have to do the upgrade to 2008 express r2. I was
hoping I could avoid that though! Thanks! - mwk
January 20, 2011 at 9:24 am
So can you upgrade to express 2008 R2?
You'll have 10GB instead of 4.
January 20, 2011 at 9:27 am
mwk (1/20/2011)
Yes, I may have to do the upgrade to 2008 express r2. I washoping I could avoid that though! Thanks! - mwk
Well yes you can avoid it.
Buy a license... and upgrade to standard.
Delete old, useless data.
Archive data into 2nd db and change the application to read from both.
There's no easy button for this problem assuming you need to keep all your data.
January 20, 2011 at 9:55 am
mwk (1/20/2011)
I have a database that is at the 4096MB limit. I already backed upthe database, but just want to make room by deleting the older records.
Is there an easy way to do this? (I'm a newbie at SQL)
Thanks!
mwk
The order in which rows are returned in SQL is NOT guaranteed to be in any order, except when the SELECT statement contains an ORDER BY clause.
That said, in your tables do you have field(s) that identify "older"? For example if you have a table of say customer orders, and it contains a column let's call it "ShipDate" and it is a DATETIME data type with appropriate entries. You could then delete rows by using a WHERE clause like WHERE ShipDate < some date you select.
Another possibility do your tables have a column defined as an IDENTITY with the identity originally defined as starting with a positive value with an increment of +1. In this case the "older" entries could be those with an IDENTITY value of less than a value you select. However if columns in that row where updated, the IDENTITY value would NOT be altered, thus at best they could only give you a "guesstimate" of it containing older data.
To further complicate things are some of your tables, "linked" to others by foreign key constraints, which then would dictate the sequence in which "older" data would have to be deleted.
Now all this sounds complicated, and it is, to get further support post the structure of a few tables, some sample data following the format shown in the article in the first link of my signature block and I am sure someone will give you more specific tested code to assist you in solving your problem.
January 20, 2011 at 9:59 am
bitbucket-25253 (1/20/2011)
mwk (1/20/2011)
I have a database that is at the 4096MB limit. I already backed upthe database, but just want to make room by deleting the older records.
Is there an easy way to do this? (I'm a newbie at SQL)
Thanks!
mwk
The order in which rows are returned in SQL is NOT guaranteed to be in any order, except when the SELECT statement contains an ORDER BY clause.
That said, in your tables do you have field(s) that identify "older"? For example if you have a table of say customer orders, and it contains a column let's call it "ShipDate" and it is a DATETIME data type with appropriate entries. You could then delete rows by using a WHERE clause like WHERE ShipDate < some date you select.
Another possibility do your tables have a column defined as an IDENTITY with the identity originally defined as starting with a positive value with an increment of +1. In this case the "older" entries could be those with an IDENTITY value of less than a value you select. However if columns in that row where updated, the IDENTITY value would NOT be altered, thus at best they could only give you a "guesstimate" of it containing older data.
To further complicate things are some of your tables, "linked" to others by foreign key constraints, which then would dictate the sequence in which "older" data would have to be deleted.
Now all this sounds complicated, and it is, to get further support post the structure of a few tables, some sample data following the format shown in the article in the first link of my signature block and I am sure someone will give you more specific tested code to assist you in solving your problem.
All great information.
time to install and move to sql 2008 r2??? A couple hours.
Time to refactor the whole application?!?!?! Don't even go there unless you have 2-3 tables and less than 10 forms / reports.
Anything longer than that is worth investing in a sql standard license and avoid the hassle to do that purge ever again.
January 20, 2011 at 10:10 am
Thank you all!! I appreciate the replies. I most likely will do the 2008 Express upgrade at this point.
Thanks,
mwk
January 20, 2011 at 10:38 am
mwk (1/20/2011)
Thank you all!! I appreciate the replies. I most likely will do the 2008 Express upgrade at this point.Thanks,
mwk
This is a quick fix, however if 2008 Express could eventual reach its limit on size. If you believe that could be the case, it at the very least, gives you time to refractor your database so as it make it easier to "control" its size.
January 20, 2011 at 10:45 am
Agreed here. There's no way out of this. You either pay for a license or pay for time.
The only question here is how much is the license compared to man hours.
That's when you realize how cheap a license is :hehe:.
January 20, 2011 at 11:56 am
Ninja's_RGR'us (1/20/2011)
Agreed here. There's no way out of this. You either pay for a license or pay for time.The only question here is how much is the license compared to man hours.
That's when you realize how cheap a license is :hehe:.
This morphing into a completely different subject, but for what it is worth let me also point out:
1. Paying for time - lets change that slightly to paying for your training in SQL.
2. Subtract from the time cost by the intangible. That is what you learn will (could) make you an more productive employee, whose new knowledge will allow for the development of new databases in a shorter time period, and that are more effective in answering your companies needs.
Thus offsetting what is considered a cost to an investment and estimating the ROI (Return On Investment). Which might even become a positive value.
So like most things in SQL the phrase "It depends" applies to your situation.
January 24, 2011 at 12:12 pm
Maybe I missed something in the previous replies, but the real question might be "Why has my DB grown so large?" It would be good to know how it's designed, how much data is going into the big transactional tables and so on. Without casting aspertions on this DB, experience shows that there are an awful lot of databases out there using up way more space than they should, due to poor design and management.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply