| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-06-08 : 08:28:34
|
| I was pinged this AM with a note that informed me I should reduce the size of our 1.4Gb database because free space was getting scarce.I've taken a look at it and I suppose I could archive part of the historic data elsewhere.On the other hand, I could clean up some other unused DBs or buy a larger hard drive.Is there a tool that provides visibility into table size(s) at a glance?Sam |
|
|
acollins74
Yak Posting Veteran
82 Posts |
Posted - 2004-06-08 : 08:54:00
|
| Try sp_spaceused [[@objname =] 'objname'] Though 1.4 gigs is not large at all. Do you have any maintenance set up. Try runnind dbcc sqlperf(logspace) and see what size your log is.If you decide to archive some data, don't forget you will still have to shrink your db. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-06-08 : 09:00:07
|
[Edit] [edit]I'd buy some more HDD space. It's cheap, and you will run into this problem again down the road, depending on how much stuff you can clean up. A 1.4GB Database is on the tiny size (at least compared to what I generally work on. I don't think your database is "getting out of hand" as far as space is concerned. When you get up to about 1.4TB, then I'd say things might have gotten out of hand :)To check the table size, I think you could quickly put together something with the Information Schema and sp_spaceused.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-06-08 : 09:06:54
|
| MOO: Shrinking the database (log excluded) is a pointless exercise most of the time, since the amount of space gained is usually small and sooner or later you are going to need that space back. Also, queries will have to wait while the file is being increased in size by SQL Server. You may have a lot to gain by shrinking the transaction log if you haven't already done that. Also, regular backups the log prevent it from growing too much.OS |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-06-08 : 09:51:38
|
| Ok, Ok. It was log files.Should I buy another disk drive for log files? The DB itself is on RAID 1.Sam |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-08 : 10:01:48
|
| 1.4GB?That's not very big...What else is on the box?How big is the drive?Isn't an 80GB drive standard?That and memory are cheap...Brett8-) |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-08 : 10:04:02
|
quote:
Originally posted by SamC
Ok, Ok. It was log files.Should I buy another disk drive for log files? The DB itself is on RAID 1.Sam
Yes, it would be better to put the log files on a seperate partition/HDD... |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-06-08 : 10:06:38
|
| This is like a sweater unraveling. After cleaning up the log files, my hosting service just now tells me they won't apply log files - just last night's backup. This after they turned on transaction logging almost 4 months ago. Houston, we have a problem. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-06-08 : 10:47:47
|
| kick them in the nuts |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-08 : 11:00:03
|
quote:
Originally posted by crazyjoe
kick them in the nuts
I saw that in an IBM Manual once....dude...you don't manage the db?If you can write a sproc you can do whatever you want...Why do they need to manage it?All they have to do is be able to bounce the box....Brett8-) |
|
|
|
|
When is a DB too large (and other thoughts)