Pages

Sunday, May 13, 2018

SQL Server. Top Segments and shrink datafile

=======================
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