| Author |
Topic |
|
Mkono
Starting Member
5 Posts |
 Posted - 2006-10-04 : 20:28:22
|
| Ok, here is the problem.Client created a database with Allocated Space of 5GB. THEY WILL ONLY EVER HOLD A MAXIMUM OF 1GB IN DATA!!! Don't ask.In any case, they currently have a database filled with 600MB of Data and 4.4GB of White Space. From what I can see, the database is backing up that entire database nightly, based on 5GB of allocated space. It doesnt care that only 600MB is being used, it only knows that someone put aside 5GB and its gonna darn well back it up.I need to get rid of that space. Their Backups are taking hours on end and Batch Files are timing out. Quite honestly, they dont need to back up 4.5GB of white space.QUESTION:How can I eliminate that white space? I have tried to run several SQL scripts to Truncate the DB and eliminate unused space, unfortunately it is not working.Any help would be GREATLY appreciated.Matt. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-04 : 20:49:58
|
quote:
In any case, they currently have a database filled with 600MB of Data and 4.4GB of White Space.
What do you mean by "white space"? SQL Server stores data, indexes, and internal structures using pages of 8K apiece, grouped into 8 extents of 64K. Pages are either allocated or unallocated, "white space" is not a correct term to describe them.quote:
From what I can see, the database is backing up that entire database nightly, based on 5GB of allocated space. It doesnt care that only 600MB is being used, it only knows that someone put aside 5GB and its gonna darn well back it up.
This is not true. A backup will contain information on the size of the database, but it only backs up allocated pages and the active portion of the transaction log. If you only have 600MB of data, it's likely your transaction log is contributing the rest of the space in the backup file (and the total database size). The log is filled and needs to be backed up or truncated to clear it.quote:
I have tried to run several SQL scripts to Truncate the DB and eliminate unused space, unfortunately it is not working.
Please post this code you are using so we have some idea what you've tried so far. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-05 : 01:24:37
|
| Check the following please using Enterprise Manager:Right click the Database : Properties : [General]What are the "Size" and "Space available" ?What, if any, is the "Maintenance plan"In the [Data Files] and [Transaction Log] tabs what are the:Space allocated (3rd column in the grid)And what is checked in the bottom pane, and what figures, if any, are set?and on the [Options] tab:What is "Recovery : Model" ?Kristen |
|
|
|
Mkono
Starting Member
5 Posts |
Posted - 2006-10-10 : 14:57:54
|
| Kristen,Thanks for the reply.1)Allocated Space is 5512.44MB2)Remaining Space - 0.00MB3)None4)6058MB5)6MB6)Automatically Grow file, By 10%, unrestricted growth, simple.Hope this helps. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-11 : 00:57:52
|
| Well it looks to me as if there is 5512.44 MB of data in that database.You didn't tell me (at [4]) the figures separately for DATA and LOG - but as you are using SIMPLE recover model the situation which I was considering that you did not have a TLog backup isn't relevant.Where do you get your figure that the database is storing 600MB of data?Kristen |
|
|
|
Mkono
Starting Member
5 Posts |
Posted - 2006-10-11 : 08:45:28
|
| I did a data comparrison of the tables within the database and the figures reflected 600MB of data. The log file is 1200KB... Normal size.It looks like Im going to have to create a new database with only 2GB of allocated space and run a DTS package from the Production DB to the new DB... Should work.Is it possible for files within SQL to be fragmented? or does it write from begining of disk to end of disk. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-11 : 14:03:43
|
| Why do you feel the need to do this? SQL will organise its disk space in an efficient manner. Yes you can defragment the indexes, but I doubt it is going to make a huge difference, the database is reporting no free space."I did a data comparrison of the tables within the database and the figures reflected 600MB of data"It would help if you could explain how you arrived at this figure in case I'm overlooking something.This script will give you some reliable space-used figures:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Script+to+analyze+table+space+usageKristen |
|
|
|
Mkono
Starting Member
5 Posts |
Posted - 2006-10-11 : 14:24:27
|
| I have to do this because they are backing up 5GB everynight...That takes 8 hours and is not necessary if there is only 600MB of data and the rest is White Space. I came to those numbers by running that script you provided last week. Bottom line is a database was accidently changed by a stupid administrator to allocate 5GB of space to it. Now they are running backups that time out and take hours to complete, if at all.Why carry around a shopping bag when what you want to put in it can fit in your pocket? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-11 : 14:54:11
|
| "database was accidently changed by a stupid administrator to allocate 5GB of space to it."I'm struggling with this. Yes you can make a database 1,000GB big and put a couple of MB of data in it - but the backups will be 2MB, not 1,000 GB. That's the way it works.Your database was showing as "Remaining Space - 0.00MB" - its full! and no slack space in it that I can see.Would be helpful if you posted the output from the script so we can advise on the actual usage, and advise how much "overhead" is used by indexes etc."backing up 5GB everynight...That takes 8 hours"That bothers me too! 5GB database backups here take about 5 minutes.Kristen |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2006-10-11 : 18:37:54
|
| It's likely that the data is fragmented, 600 MB of data on sparesely populated pages, if it's showing 0% available then all the existing pages are allocated. DBCC DBREINDEX should clean this up, and maybe DBCC SHRINKFILE...NOTRUNCATE afterwards. I would recommend against actually shrinking the file size until you establish a data growth pattern. If it somehow expanded to fill 5 GB through normal use it will do it again. (unless you can pin the growth down to a specific action that won't be repeated). Again, the backup file will only contain allocated pages and the active portion of the log, once everything is rebuilt and compacted you'll get 600 MB backup files. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-10-11 : 19:24:11
|
| why is a backup of only 5GB of data taking hours and hours? you have another issue here.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2006-10-11 : 19:27:03
|
quote:
Originally posted by KristenYour database was showing as "Remaining Space - 0.00MB" - its full! and no slack space in it that I can see.
you just need to run DBCC UPDATEUSAGE to correct this. This is actually very typical to see 0MB available, especially if you don't run some kind of regular maintenance on the database (like an optimize job). This may also explain why Matt only sees 600MB in use.-ec |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-13 : 04:03:17
|
| "DBCC UPDATEUSAGE"Good point, should have thought of that, thanks EC.I would still like to see some raw size figures just to check Matt's "600MB of data" calculation. its not that I doubt it, but if it IS wrong then Matt needs to make a different plan!Kristen |
|
|
|
Mkono
Starting Member
5 Posts |
Posted - 2006-10-16 : 15:25:44
|
| Thank you ALL very much for your suggestions and insight on this. It really is appreciated and valued.The numbers are still the same. The Data on this DB is 600MB, the allocated space is 5GB. I wish there was more I could tell you that might help us all figure out what the situation is.I am confident that the numbers provided are acurate.Their network isnt exactly Robust. I have done a few test DTS packages and it works great, I setup a new DB to grow by 10% with no pre determined allocated space. I then run a DTS package fromn the Production DB to the new DB and it only tranfers the Files.....and guess what? At the end of this DTS package, all that remains is a Database that is 602 MB in size... The extra 2MB is log file....My new situation is I want to be 100% sure that nothing has been lost and all Tables are in check. I already ran the "Table Size" script, now I want to run a script that will compare two databases and give me results in the same grid.Anyone have any ideas on what that script would look like?I would greatly appreciate any help on this script.Once again, Many Many Thanks for everyone taking the time to look at my post and lend a helping hand.-Matt |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-17 : 13:28:41
|
| "I am confident that the numbers provided are acurate"I would still like to see the raw data.Kristen |
|
|
|
|
SQL White Space! HELP!