| Author |
 Topic  |
|
|
Curt
Moderator
USA
6648 Posts
Status: offline |
 Posted - 07/11/2012 : 4:40:26 PM
|
Often organizations and their IT staff are so busy managing data in their databases that they do not look at it. Often, like a warehouse full of "Product" they don't look at it's relavance, value , or expiration date.
I also see folks using the SQL Management studio GUI to create Database Maintence plans to squelch down their .MDF files every night to "Save Disk space".
Granted, we are in the days of " Hardware as a Service" where we pay a good deal of coin for hosted disk space.
I came across a undocumented tool in SQL Server to enumerate the tables and data regarding those tables in the database.
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
I ran this in SQL 2008 query against the adventure works database.
The first table's data appears below.
name rows reserved data index_size unused
ProductCategory 41 48 KB 8 KB 40 KB 0 KB
On a production system, I used this to find a very large table in a Great Plains Database. A simple select statment showed me that much of the data was old and over 900,000 records were added in a week.
This alarmed the IT manager. He made some inquiries and found that much of the data could be archived. He removed a great deal of data.
This morning I was able to reorg the indexes and recover two GB of disk space while still leaving a good overhead of space in the database. Trying to shrink a database down everynight does not seem to be a great Idea.
Perhaps more of our SQL friends can chime in on this.
|
Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro
He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20
|
|
|
NMDANGE
Honorable But Hopeless Addict
USA
2054 Posts
Status: offline |
Posted - 07/11/2012 : 7:09:26 PM
|
You should never, ever, ever need to do a file shrink on a properly maintained SQL server database. The only time it makes sense is as a one-time operation when you delete a lot of data that you don't expect to come back. File shrinks, and file expansion are both very resource intensive and definitely have a negative impact on SQL performance while they are happening.
It's easier with your own application, but even with 3rd party apps, there should be some understanding of what's in the database, whether there are any log-type tables or similar tables that need to be cleaned up on a regular basis. If so, write a script and schedule it! The only thing you should use DB maintenance plans for is Index re-org/rebuild, if your application needs it, or DB backups if you don't have a 3rd party backup tool. In fact I wish Microsoft did not include the ability to do a file shrink in the maintenance plan wizard! |
Michael D'Angelo
(former)MVP-MIIS, Pace University Senior Systems Administrator (Windows)
(MS)NMDANGE
PhoeniX WorX Systems Administrator. If you play Total Annihilation, please join us. http://www.phoenixworx.org |
 |
|
|
Curt
Moderator
USA
6648 Posts
Status: offline |
Posted - 07/11/2012 : 10:42:57 PM
|
It seems that lots of Great Plains folks setup maintenance plans and they don't understand databases because they are accountants.
Thus, every option that is available in a maintenance plan is put in.
I traced the explosive growth of a transaction log to one such Maintenance plan created in 2009 by the VAR that deployed the upgrade of Great Plains.
The drive space provided is now maxing out. So in this case to get the server backup and running I had to shrink the log as an emergency measure. With proper backups it should be fine now. Still, fragmentation is a beast that is hard to deal with on a busy production server.
|
Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro
He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20
|
|
|
|
NMDANGE
Honorable But Hopeless Addict
USA
2054 Posts
Status: offline |
Posted - 07/12/2012 : 09:29:01 AM
|
| Oh yeah don't get me started on databases with FULL recovery mode and no transaction log backups! |
Michael D'Angelo
(former)MVP-MIIS, Pace University Senior Systems Administrator (Windows)
(MS)NMDANGE
PhoeniX WorX Systems Administrator. If you play Total Annihilation, please join us. http://www.phoenixworx.org |
|
|
|
netmarcos
Honorable But Hopeless Addict
USA
2219 Posts
Status: offline |
Posted - 07/12/2012 : 10:21:19 AM
|
quote:
Originally posted by NMDANGE
Oh yeah don't get me started on databases with FULL recovery mode and no transaction log backups!
Can we add FULL recovery mode on systems where all of the data and log files are on the same physical spindle to the pet peeve list? |
Mark M. Webster
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
 |
|
|
|
Curt
Moderator
USA
6648 Posts
Status: offline |
Posted - 07/12/2012 : 10:39:39 AM
|
Oh yes.
And all on the system partition.
Now.............. I don't like to be a SQL snob about things.
Like a house built with problems, SQL servers can be deployed with bad planning and now it's there and they are living with it.
It's difficult to move now.
Expensive and disruptive. SO.
Often the only thing to do is make improvements slowly.
For instance, it's a bit of a job to migrate Great Plains to to a new SQL server. You don't just move the databases to a new server.
Some folks have the view that a SQL server is like a file server.
That's a pet peeve of mine.
We should start a list of pet peeves about SQL.
|
Curt Spanburgh
Microsoft Certified Business Solution Specialist.
Dynamics CRM MVP
Contributing Editor, Windows IT Pro
He that is walking with wise persons will become wise, but he that is having dealings with the stupid ones will fare badly.
Proverbs 13:20
|
|
|
|
netmarcos
Honorable But Hopeless Addict
USA
2219 Posts
Status: offline |
Posted - 07/12/2012 : 4:14:21 PM
|
quote:
Originally posted by Curt
We should start a list of pet peeves about SQL.
We're gonna need more disk space...
|
Mark M. Webster
Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard
|
Edited by - netmarcos on 07/12/2012 4:14:54 PM |
|
|
| |
Topic |
|
Table sizes
