To see top Segments
=======================
SELECT CAST(object_name(id) as varchar(50)) AS object_name,
SUM(CASE WHEN indid<2 THEN rows END) AS rows,
ROUND(SUM(reserved)*8/1024,0) AS reserved_Mb,
ROUND(SUM(dpages)*8/1024,0) AS Size_Mb,
ROUND(SUM(used-dpages)*8/1024,0) AS Used_Mb,
ROUND(SUM(reserved-used)*8/1024,0) AS Unused_Mb
FROM sysindexes with (nolock)
WHERE indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY sum(dpages)*8 desc;
object_name rows reserved_Mb Size_Mb Used_Mb Unused_Mb
------------------ -------- ----------- ------- ------- ---------
NULL 34550143 120475 7626 82027 30822
email_contact 9439214 3094 3084 10 0
email_user_details 3845153 1008 987 13 7
email_message 1236052 112406 587 81804 30014
USER_CompanyStatus 11786380 466 460 0 5
email_association 1445918 355 350 3 2
IncidentAuditLog 682479 390 345 43 1
UserActivityLog 2194807 390 296 0 94
IncidentWorkNote 306004 291 278 12 0
EmailsLOG 265786 237 236 0 0
=======================
To free up unused space:
=======================
DBCC SHRINKFILE('cdb_Data',125000)
cdb_Data is the file logical name.
The output:
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Dbid Field CurrentSize MinimumSize UsedPages EstimatedPagaed
7 1 16000000 128 15760728 15760728
15760728*8=126085824 Kb = 123130 Mb
So in theory we can shrink data file only to 123,130Mb
Lets try to shrink to 120,000Mb
DBCC SHRINKFILE('Starhome_Data',120000)
This should not work...
After long long time, several hours, the command was still running...
I had to kill it.
No comments:
Post a Comment