Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 DB Size Grows after bcp

Author  Topic 

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 13:00:38
OK

I'm involved in scrambling data to sanitize it for development from prod

I bcp out the tables that have sentive data, scramble it, truncate the data, then bcp it back in

The original db is 4GB

After the process is done, the db doubles in size

The row counts are excatly the same for every table

Does anyone, anywhere, have any ideas that could point me in a direction as to why this would happen

I'm at a dead end

I've shrunk the db and files, but it say the data is still twice the size

Help, please

I'll take ANY advice



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-05 : 13:12:10
First run dbcc updateusage on that database, just in case you've got some inaccuracies in the system tables.

Then run sp_spaceused against the database before and after the bcp. Post the results for both.

It's too bad you aren't on SQL Server 2005 yet as we could run a SSMS built-in report that would show space usage for all of the tables without having to write any code. I'm referring to the top table usage report.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 13:16:02
You're a gem

I'll get back



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 13:21:03
Usage report

quote:

DBCC UPDATEUSAGE: sysindexes row updated for table 'sysobjects' (index ID 2):
USED pages: Changed from (2) to (51) pages.
RSVD pages: Changed from (2) to (57) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'sysobjects' (index ID 3):
USED pages: Changed from (2) to (15) pages.
RSVD pages: Changed from (2) to (17) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'sysobjects' (index ID 1):
DATA pages: Changed from (163) to (110) pages.
USED pages: Changed from (272) to (178) pages.
RSVD pages: Changed from (299) to (187) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'sysindexes' (index ID 255):
USED pages: Changed from (48) to (50) pages.
RSVD pages: Changed from (112) to (113) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'sysindexes' (index ID 1):
DATA pages: Changed from (118) to (92) pages.
USED pages: Changed from (120) to (94) pages.
RSVD pages: Changed from (169) to (97) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'syscolumns' (index ID 2):
USED pages: Changed from (2) to (55) pages.
RSVD pages: Changed from (2) to (57) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'syscolumns' (index ID 1):
DATA pages: Changed from (179) to (95) pages.
USED pages: Changed from (261) to (152) pages.
RSVD pages: Changed from (362) to (170) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'syscomments' (index ID 1):
DATA pages: Changed from (61) to (49) pages.
USED pages: Changed from (63) to (51) pages.
RSVD pages: Changed from (89) to (57) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'sysdepends' (index ID 1):
DATA pages: Changed from (3) to (2) pages.
USED pages: Changed from (7) to (6) pages.
RSVD pages: Changed from (7) to (6) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_L_Deletes' (index ID 2):
USED pages: Changed from (2) to (61) pages.
RSVD pages: Changed from (2) to (65) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_L_Deletes' (index ID 1):
RSVD pages: Changed from (5929) to (5930) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_L_Operation' (index ID 0):
USED pages: Changed from (7) to (6) pages.
RSVD pages: Changed from (7) to (6) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_L_OwnerInfo' (index ID 2):
USED pages: Changed from (2) to (267) pages.
RSVD pages: Changed from (2) to (273) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_L_OwnerInfo' (index ID 0):
RSVD pages: Changed from (2889) to (2890) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_C_Detail' (index ID 2):
USED pages: Changed from (2) to (1056) pages.
RSVD pages: Changed from (2) to (1057) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_C_Detail' (index ID 3):
USED pages: Changed from (2) to (3538) pages.
RSVD pages: Changed from (2) to (3545) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_C_Detail' (index ID 4):
USED pages: Changed from (2) to (2087) pages.
RSVD pages: Changed from (2) to (2089) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_C_Detail' (index ID 1):
RSVD pages: Changed from (85930) to (85940) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_C_Comments' (index ID 0):
USED pages: Changed from (5) to (4) pages.
RSVD pages: Changed from (5) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_L_Changes' (index ID 2):
USED pages: Changed from (84) to (171) pages.
RSVD pages: Changed from (87) to (185) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_L_Changes' (index ID 1):
DATA pages: Changed from (586) to (293) pages.
USED pages: Changed from (931) to (466) pages.
RSVD pages: Changed from (961) to (482) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_L_Operation' (index ID 0):
USED pages: Changed from (6) to (4) pages.
RSVD pages: Changed from (6) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_L_OwnerInfo' (index ID 2):
USED pages: Changed from (2) to (16) pages.
RSVD pages: Changed from (2) to (17) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_L_Deletes' (index ID 2):
USED pages: Changed from (2) to (20) pages.
RSVD pages: Changed from (2) to (25) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PEG_C_Detail' (index ID 3):
USED pages: Changed from (2) to (4) pages.
RSVD pages: Changed from (2) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_C_Detail' (index ID 2):
USED pages: Changed from (2) to (41) pages.
RSVD pages: Changed from (2) to (41) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_C_Detail' (index ID 3):
USED pages: Changed from (2) to (133) pages.
RSVD pages: Changed from (2) to (137) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_C_Detail' (index ID 4):
USED pages: Changed from (2) to (79) pages.
RSVD pages: Changed from (2) to (81) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_C_Detail' (index ID 1):
RSVD pages: Changed from (3219) to (3220) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_C_Comments' (index ID 0):
USED pages: Changed from (6) to (5) pages.
RSVD pages: Changed from (6) to (5) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_H_Detail' (index ID 2):
USED pages: Changed from (2) to (82) pages.
RSVD pages: Changed from (2) to (89) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_H_Detail' (index ID 3):
USED pages: Changed from (2) to (266) pages.
RSVD pages: Changed from (2) to (273) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_H_Detail' (index ID 4):
USED pages: Changed from (2) to (158) pages.
RSVD pages: Changed from (2) to (161) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_H_Detail' (index ID 1):
RSVD pages: Changed from (6403) to (6404) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_H_Detail' (index ID 2):
USED pages: Changed from (2) to (25) pages.
RSVD pages: Changed from (2) to (25) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_H_Detail' (index ID 3):
USED pages: Changed from (2) to (78) pages.
RSVD pages: Changed from (2) to (81) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_H_Detail' (index ID 4):
USED pages: Changed from (2) to (46) pages.
RSVD pages: Changed from (2) to (49) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_H_Detail' (index ID 1):
RSVD pages: Changed from (1843) to (1844) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBC_L_Operation' (index ID 0):
USED pages: Changed from (5) to (4) pages.
RSVD pages: Changed from (5) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRI_C_Detail' (index ID 3):
USED pages: Changed from (2) to (4) pages.
RSVD pages: Changed from (2) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_L_Changes' (index ID 2):
USED pages: Changed from (1) to (496) pages.
RSVD pages: Changed from (1) to (496) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_L_Changes' (index ID 1):
DATA pages: Changed from (1435) to (763) pages.
USED pages: Changed from (2371) to (1265) pages.
RSVD pages: Changed from (2384) to (1272) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_L_Operation' (index ID 2):
USED pages: Changed from (6) to (19) pages.
RSVD pages: Changed from (6) to (32) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_L_Operation' (index ID 0):
DATA pages: Changed from (21) to (44) pages.
USED pages: Changed from (65) to (64) pages.
RSVD pages: Changed from (117) to (89) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_L_OwnerInfo' (index ID 2):
USED pages: Changed from (2) to (13626) pages.
RSVD pages: Changed from (2) to (13633) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_L_OwnerInfo' (index ID 0):
RSVD pages: Changed from (148040) to (148058) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_L_Deletes' (index ID 2):
USED pages: Changed from (2) to (29) pages.
RSVD pages: Changed from (2) to (33) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_L_Changes' (index ID 2):
USED pages: Changed from (1) to (25) pages.
RSVD pages: Changed from (1) to (32) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_L_Changes' (index ID 1):
DATA pages: Changed from (60) to (34) pages.
USED pages: Changed from (113) to (61) pages.
RSVD pages: Changed from (161) to (80) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PEG_L_Operation' (index ID 0):
USED pages: Changed from (6) to (4) pages.
RSVD pages: Changed from (6) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PEG_L_OwnerInfo' (index ID 2):
USED pages: Changed from (2) to (5) pages.
RSVD pages: Changed from (2) to (5) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_L_Deletes' (index ID 2):
USED pages: Changed from (2) to (4) pages.
RSVD pages: Changed from (2) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_L_OwnerInfo' (index ID 2):
USED pages: Changed from (2) to (227) pages.
RSVD pages: Changed from (2) to (233) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_C_Detail' (index ID 2):
USED pages: Changed from (2) to (16) pages.
RSVD pages: Changed from (2) to (17) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_C_Detail' (index ID 3):
USED pages: Changed from (2) to (46) pages.
RSVD pages: Changed from (2) to (49) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_C_Detail' (index ID 4):
USED pages: Changed from (2) to (29) pages.
RSVD pages: Changed from (2) to (33) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_C_Comments' (index ID 0):
USED pages: Changed from (5) to (4) pages.
RSVD pages: Changed from (5) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_H_Detail' (index ID 2):
USED pages: Changed from (2) to (37) pages.
RSVD pages: Changed from (2) to (41) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_H_Detail' (index ID 3):
USED pages: Changed from (2) to (118) pages.
RSVD pages: Changed from (2) to (121) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_H_Detail' (index ID 4):
USED pages: Changed from (2) to (70) pages.
RSVD pages: Changed from (2) to (73) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_H_Comments' (index ID 0):
USED pages: Changed from (6) to (5) pages.
RSVD pages: Changed from (6) to (5) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_H_Detail' (index ID 2):
USED pages: Changed from (2) to (5) pages.
RSVD pages: Changed from (2) to (5) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_H_Detail' (index ID 3):
USED pages: Changed from (2) to (8) pages.
RSVD pages: Changed from (2) to (8) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_H_Detail' (index ID 4):
USED pages: Changed from (2) to (6) pages.
RSVD pages: Changed from (2) to (6) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBC_L_Changes' (index ID 1):
DATA pages: Changed from (2) to (1) pages.
USED pages: Changed from (8) to (5) pages.
RSVD pages: Changed from (8) to (5) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_L_Changes' (index ID 2):
USED pages: Changed from (1) to (35786) pages.
RSVD pages: Changed from (1) to (35793) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_L_Changes' (index ID 1):
DATA pages: Changed from (87599) to (47576) pages.
USED pages: Changed from (154783) to (83659) pages.
RSVD pages: Changed from (154649) to (83674) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_L_Operation' (index ID 0):
USED pages: Changed from (6) to (4) pages.
RSVD pages: Changed from (6) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_L_OwnerInfo' (index ID 2):
USED pages: Changed from (2) to (167) pages.
RSVD pages: Changed from (2) to (169) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_L_Deletes' (index ID 2):
USED pages: Changed from (2) to (4140) pages.
RSVD pages: Changed from (2) to (4146) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_L_Deletes' (index ID 1):
RSVD pages: Changed from (415681) to (415732) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PEG_L_Changes' (index ID 2):
USED pages: Changed from (2) to (5) pages.
RSVD pages: Changed from (2) to (5) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PEG_L_Changes' (index ID 1):
DATA pages: Changed from (11) to (6) pages.
USED pages: Changed from (21) to (13) pages.
RSVD pages: Changed from (21) to (13) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_C_Detail' (index ID 2):
USED pages: Changed from (2) to (93) pages.
RSVD pages: Changed from (2) to (97) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_C_Detail' (index ID 3):
USED pages: Changed from (2) to (307) pages.
RSVD pages: Changed from (2) to (313) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_C_Detail' (index ID 4):
USED pages: Changed from (2) to (181) pages.
RSVD pages: Changed from (2) to (185) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_C_Detail' (index ID 12):
USED pages: Changed from (2) to (211) pages.
RSVD pages: Changed from (2) to (217) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRIAC_C_Detail' (index ID 1):
RSVD pages: Changed from (7652) to (7653) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_C_Comments' (index ID 2):
USED pages: Changed from (2) to (7) pages.
RSVD pages: Changed from (2) to (7) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_C_Comments' (index ID 255):
USED pages: Changed from (19) to (20) pages.
RSVD pages: Changed from (20) to (21) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_C_Comments' (index ID 0):
DATA pages: Changed from (14) to (29) pages.
USED pages: Changed from (27) to (37) pages.
RSVD pages: Changed from (44) to (39) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_L_Changes' (index ID 2):
USED pages: Changed from (225) to (322) pages.
RSVD pages: Changed from (231) to (329) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_L_Changes' (index ID 1):
DATA pages: Changed from (1208) to (604) pages.
USED pages: Changed from (1859) to (930) pages.
RSVD pages: Changed from (1888) to (938) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_L_Operation' (index ID 0):
USED pages: Changed from (5) to (4) pages.
RSVD pages: Changed from (5) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLNJ_L_OwnerInfo' (index ID 2):
USED pages: Changed from (2) to (75) pages.
RSVD pages: Changed from (2) to (81) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_L_Deletes' (index ID 2):
USED pages: Changed from (2) to (48) pages.
RSVD pages: Changed from (2) to (49) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_L_Deletes' (index ID 1):
RSVD pages: Changed from (4609) to (4610) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_C_Detail' (index ID 2):
USED pages: Changed from (2) to (8) pages.
RSVD pages: Changed from (2) to (8) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_C_Detail' (index ID 3):
USED pages: Changed from (2) to (20) pages.
RSVD pages: Changed from (2) to (25) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_ASI_C_Detail' (index ID 4):
USED pages: Changed from (2) to (13) pages.
RSVD pages: Changed from (2) to (17) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PEG_C_Comments' (index ID 0):
USED pages: Changed from (5) to (4) pages.
RSVD pages: Changed from (5) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_C_Comments' (index ID 0):
USED pages: Changed from (5) to (4) pages.
RSVD pages: Changed from (5) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_H_Detail' (index ID 2):
USED pages: Changed from (2) to (5526) pages.
RSVD pages: Changed from (2) to (5530) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_H_Detail' (index ID 3):
USED pages: Changed from (2) to (18440) pages.
RSVD pages: Changed from (2) to (18449) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_H_Detail' (index ID 4):
USED pages: Changed from (2) to (10894) pages.
RSVD pages: Changed from (2) to (10897) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRUINS_H_Detail' (index ID 1):
RSVD pages: Changed from (447142) to (447198) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PEG_H_Detail' (index ID 3):
USED pages: Changed from (2) to (5) pages.
RSVD pages: Changed from (2) to (5) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PEG_H_Detail' (index ID 4):
USED pages: Changed from (2) to (4) pages.
RSVD pages: Changed from (2) to (4) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PRI_L_Changes' (index ID 1):
DATA pages: Changed from (4) to (2) pages.
USED pages: Changed from (10) to (6) pages.
RSVD pages: Changed from (10) to (6) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_H_Detail' (index ID 2):
USED pages: Changed from (2) to (63) pages.
RSVD pages: Changed from (2) to (65) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_H_Detail' (index ID 3):
USED pages: Changed from (2) to (206) pages.
RSVD pages: Changed from (2) to (209) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PLAZ_H_Detail' (index ID 4):
USED pages: Changed from (2) to (122) pages.
RSVD pages: Changed from (2) to (129) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_L_Changes' (index ID 2):
USED pages: Changed from (35) to (350) pages.
RSVD pages: Changed from (39) to (361) pages.
DBCC UPDATEUSAGE: sysindexes row updated for table 'TRK_PBT_L_Changes' (index ID 1):
DATA pages: Changed from (679) to (546) pages.
USED pages: Changed from (1121) to (901) pages.
RSVD pages: Changed from (1161) to (922) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 13:25:27
space used before bcp in, data was truncated

quote:

database_name database_size unallocated space
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ------------------
Tracker_Data 691.06 MB -7.59 MB


reserved data index_size unused
------------------ ------------------ ------------------ ------------------
714136 KB 407664 KB 304840 KB 1632 KB




The bcps's will take an hour an 1/2



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 13:28:16
Oh, initial size is about 4GB and the final went to 15



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 13:29:49
quote:
Originally posted by tkizer

First run dbcc updateusage on that database, just in case you've got some inaccuracies in the system tables.

Then run sp_spaceused against the database before and after the bcp. Post the results for both.

It's too bad you aren't on SQL Server 2005 yet as we could run a SSMS built-in report that would show space usage for all of the tables without having to write any code. I'm referring to the top table usage report.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/


Just curious, how did you know to do this?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-05 : 13:33:06
Your sp_spaceused report is showing that your database is only 700MB, rather than 4GB. And the 700MB is the total size, which includes the transaction log file and free space in the files.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-05 : 13:36:48
quote:
Originally posted by X002548

quote:
Originally posted by tkizer

First run dbcc updateusage on that database, just in case you've got some inaccuracies in the system tables.

Then run sp_spaceused against the database before and after the bcp. Post the results for both.

It's too bad you aren't on SQL Server 2005 yet as we could run a SSMS built-in report that would show space usage for all of the tables without having to write any code. I'm referring to the top table usage report.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/


Just curious, how did you know to do this?



Which part, the dbcc updateusage? It's just a command that I was taught to use when I was a lowly student worker in the DBA team. I didn't know what it did back then though. Anytime that I see conflicting sizes in a database, I run that command or sp_spaceused with the @updateusage = TRUE to make sure I'm not seeing the inaccuracies that it fixes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 13:37:20
yeah that's after I truncated the data and shrunk the db, I'm importing the data now

Right now it's grown to 1.7 gb



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-05 : 13:37:23
"report that would show space usage for all of the tables without having to write any code."

Dunno if it covers what you need, but this may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-05 : 13:38:13
Do you have the sp_spaceused report before the data was truncated?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-05 : 13:40:11
quote:
Originally posted by Kristen

"report that would show space usage for all of the tables without having to write any code."

Dunno if it covers what you need, but this may help:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762


I'm almost purely on SQL Server 2005 (only one legacy system on 2000), so that SSMS report is utilized rather than running any code. It's great that MS finally added some useful tools for us to quickly observe things in the database rather than figuring out what command to run and how to get it to loop through the objects.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-10-05 : 13:48:19
say.. where can i find those reports in ssms?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 13:50:41
quote:
Originally posted by tkizer

Do you have the sp_spaceused report before the data was truncated?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/


No, would that be different than EM? EM said 15gb

EM said the same as sp_spaceused after truncate



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-05 : 13:52:31
EM doesn't show the specifics of what I'm wanting to see. sp_spaceused will show exactly where things changed.

Are you able to do a restore to a test database to check sp_spaceused before the bcp out and truncate?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-05 : 13:54:22
quote:
Originally posted by spirit1

say.. where can i find those reports in ssms?



Right click on the database, go to reports. I frequently use Disk Usage and Disk Usage by Top Tables. Just make sure dbcc updateusage has been recently run to get more accurate data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-10-05 : 13:58:32
hmm.. since i don't have that option in my context menu this must be a feature added by a service pack?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-05 : 14:01:33
quote:
Originally posted by spirit1

hmm.. since i don't have that option in my context menu this must be a feature added by a service pack?



Your client tools aren't service packed?!

Yes I believe they were added with sp1. You should get on sp2 + GDR3 though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 14:04:09
Just curious, why would my tranny log ballon to over 2,8 gb for a bcp in???



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2007-10-05 : 14:05:03
quote:
Originally posted by tkizer

EM doesn't show the specifics of what I'm wanting to see. sp_spaceused will show exactly where things changed.

Are you able to do a restore to a test database to check sp_spaceused before the bcp out and truncate?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/


Yes, I'm on it



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site