Database size issue...
-
Hi there.
In a nutshell the problem is:
We have a database on one server that claims it's size is 1,803MB.
Yet if we export that database to another server, its size decreases to only 875MB.
WHY?
I've carried out DBCC CHECKDB, and everything checks out ok. There's no actual data missing from the smaller database.
Carrying out DBCC SHRINKDATABASE yields nothing very exciting.
Have also done;
DBCC CHECKTABLE on all tables;
DBCC CHECKALLOC WITH ALL_ERRORMSGS
.. and not a thing.
Something that may be related;
Recently we need to change some column data types from text to ntext to accommodate some international customers. Without the ability to change these types easily, we:
- created a new temp column;
- copied all the data across;
- deleted all the data from the original column;
- changed the column data type to ntext;
- copied all the data from the temp col;
- dropped the temp col.
Could this be related in any way?
Any help and advise would be great.
Thanks in advance,
Ollie.
-
What is the size of the log files for the database in question on the two servers?
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley -
Hi,
The allocated space for the files are as follows;
On the oversized database;
Data file: 1801
Trans log: 3
On the small database;
Data file: 872
Trans log: 4
Thanks,
Ollie.
-
When you talk size you do not mean the datafile size?
And export/import could be a means of defragmentation/index rebuilds and that should account for a little decrease in the database size.
Mike
-
"When you talk size you do not mean the datafile size?"
- Yes, I do mean the data file size. The size that's allocated when (in Enterprise manager) you right click and view properties.
Like I say, we can't get it to allocate any less space.
"defragmentation/index rebuilds should account for a little decrease "
... but prehaps not a gigabyte on an 800Mb database?? What do you think?
Any advice would be greatly recieved.
Thank you.
Ollie.
-
Using EM Right Click on the original large database, Choose All Tasks, the chose Shrink Database. At the top of this window you should be able to see the Space Allocated and Space Free. What is that?
If you now click the files button at the bottom a new window will pop up. Near the bottom of this window it will say Shrink file to ... and just to the right the minimun size to which you can shrink the data file. Here can you shrink the file to about the 872 M size?
Francis
-
Have you tried running sp_spaceused on both databases? That stored procedure (refer to BOL for more information on it's usage) will show you how much space is being used and what is using it. It shows the total space used, how much for indexes, unallocated space, data, etc.
Run it on both servers and compare the two. The difference might be the unallocated space or it might show that something wasn't copied over.
-SQLBill
-
I had a similar situation we had a 10 Gig
database and it comes over as 1.XG.
The database was badly in need of a reorg though.
Among many things we deleted 90Million rows in a table that had 100M rows.
Yes you can loose more than a Gigabyte due to database reorganization.
And I will not worry to much about it if It could be verified that all the objects are present.
Mike
-
I had a problem yesterday with the transaction log fle being far too big. In the end we had to do run the command
"backup log DBase_Name with Truncate_Only"
from OSQL to force the truncation of the log (we had previously done a full back up of the database). The we ran
"DBCC Shrinkfile (DBase_Logfile_name)"
This shrunk the log file to just 1 MB. Now I realise that this isn't a transaction log but rather a database problem, but perhaps you could try
"DBCC Shrinkfile (Database_Filename)" ?
-
Hello Ollie!
I guess you should look into DBCC CLEANTABLE
BOL:
DBCC CLEANTABLE DBCC CLEANTABLE reclaims space after a variable length column or a text column is dropped using the ALTER TABLE DROP COLUMN statement. It does not reclaim space after a fixed length column is dropped.- Hope this helps!
- Regards, Hans!
-
hello,
i suggest you should correctly shrink your DB. That means, you alternate shrinking and Backups until the free space is 0.
Then have look at the real size
fred
-
My co-worker had the same issue.
When he was shrinking the database, he was shrinking the database without having the "Move pages to beginning of file" option checked. When the shrink ran, the size stayed about the same.
When he ran with the "Move pages to beginning of file" option checked, the database shrunk by 4 gig.
Are we having fun yet....
EP
-
-dbcc updateusage can help out. statistics get a bit messy when you use truncate table every now and then.
-then dbcc spaceused your db and look for fragmentation rates.
-make sure you have clusterd indexes on your objects. and rebuild them whenever you can.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply