October 27, 2003 at 4:05 am
Hello,
I have one database which is around 500Mb after I delete trans. log.
Before few days I imported that db in new db and size of new db is around 180Mb.
After import I got report that all tables were sucessfully imported and when I check data it looks like everything is there but what happened with 320Mb of data ???
To be honest I am confused.
Any experience with similar problems ?
Thanks,
Oliver
October 27, 2003 at 4:20 am
What are the result of exec sp_spaceused on both db's?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 27, 2003 at 4:24 am
I will try and post it here. Now, I do not know.
October 27, 2003 at 7:01 am
Did you have indexes on the database? They may not have copied over. Also, they may have been reindexed as part of the copy. Did all of your jobs, scripts, etc copy over?
-SQLBill
October 27, 2003 at 7:10 am
Maybe this is stupid question but if I generate SQL script and check also to include indexes in it, if I run this script will all indexes be really created or not ?
I will check tommorow database because now server is under pressure and I can not connect to it.
In a way, it looks like to me that indexes were not created but I will check later.
October 27, 2003 at 7:15 am
Just to add that I found in script that I used that some indexes have following lines in front of them :
/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
and some do not have those lines. Why I have this difference between indexes ? I can not remember that I ever created statistics or anything similar.
October 27, 2003 at 7:32 am
I would say that you've included indexes with your import.
There is a different between 'regular' indexes and 'statistics'.
For further explanation on indexes and statistics I suggest reading BOL. Should explain in detail.
You can't connect to the server to check???
What I was after with sp_spaceused was your db size (= space reserved) contains space allocated for data and indexes *and* unused size.
Looks to me like your vanished 320MB of data were previously unused space.
You can also check by running
DBCC SHOWFILESTATS (look there for UsedExtents)
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 27, 2003 at 3:50 pm
If the original database has been around a while, and has had lots of inserts/deletes/updates, and you've never de-fragmented it, it could have been highly fragmented, which would cause it to take up a lot of extra space. When you imported it into your new database, it would have written it in an orderly fashion without fragmentation. Also, check fill factor. If you have a fill factor of 50% on your original, and a fill factor of 95% on your new one, that would cause a large difference as well. By the way, a fill factor of 0 (the default) is actually somewhere around 95%.
Steve Phelps
SQL Server DBA
American Fidelity Group
October 28, 2003 at 12:44 am
Here are data from both databases.
Old DB
DBSize : 456,06 Mb
Unallocated space : -31,68 Mb
Reserved space : 507640 Kb
Data : 305200 Kb
Index size : 141912 Kb
Unused : 60528 Kb
Used Extends : 7420
Total Extends : 7425
New DB
DBSize : 259,75 Mb
Unallocated space : 2,18 Mb
Reserved space : 251400 Kb
Data : 148512 Kb
Index size : 100160 Kb
Unused : 2728 Kb
Used Extends : 3963
Total Extends : 3931 (probably I wrote wrong this one).
DB was never fragmented before and there were a lot of insert/updates in it.
How I can do it ?
Thanks,
Oliver
October 28, 2003 at 1:18 am
If you use Import/Export Wizard ... the tables go with its data to the destination ... but with NO Primarykey or relationship ...
am I right? please check this again ..
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
October 28, 2003 at 4:44 am
Using Enterprise manager view the database (in 'task pad' view for SQL2000) and select 'Table Info' - Compare the two databases and I suspect you will see one or more tables that are much bigger in the original database. If you then add a field to the table (say 'X' Char(10)) and save it, then delete the field and save it the whole table is re-written and in my experience can reduce space to 10% of the orginal.
I have to say I've never understood the size business of SQL. I've studied BOL - truncate transaction log. shrink database etc but there doesn't seem to be anything that realy re-builds the database.
Anyone got any better ideas?
October 28, 2003 at 6:50 am
quote:
Using Enterprise manager view the database (in 'task pad' view for SQL2000) and select 'Table Info' - Compare the two databases and I suspect you will see one or more tables that are much bigger in the original database. If you then add a field to the table (say 'X' Char(10)) and save it, then delete the field and save it the whole table is re-written and in my experience can reduce space to 10% of the orginal.I have to say I've never understood the size business of SQL. I've studied BOL - truncate transaction log. shrink database etc but there doesn't seem to be anything that realy re-builds the database.
Anyone got any better ideas?
DBCC INDEXDEFRAG
--Jonathan
--Jonathan
October 28, 2003 at 7:28 am
Please run DBCC SHOWCONTIG (dbname) on each database and post the results.
Steve Phelps
SQL Server DBA
American Fidelity Group
October 28, 2003 at 7:54 am
Run sp_spaceused @updateusage = true to your old database.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply