SQL Server - Clearing/Emptying a Datafile and Removing It

If you have more than one datafile and you'd like to get rid of one, you can clear the data out and remove the file. Here's how:

-----------------------------------------
Moving Data Out of a Datafile
-----------------------------------------

DBCC SHRINKFILE ('[logicalFileName]', EMPTYFILE )

-----------------------------------------
Monitoring the Move of Data
-----------------------------------------

use PADBCC showfilestats

------------------------------
Removing the Datafile
------------------------------

use master
ALTER DATABASE PA REMOVE FILE "[LogicalFileName]"

SQL Server - Space Being Used

---------------------------
For Datafiles
---------------------------

use [databaseName]
DBCC showfilestats

An "Extent" in SQL Server is 64 K. So the amount of space in megabytes allocated to Data file can be calculated by using the following formula:

Space Allocated for Data = TotalExtents * 64.0 / 1024.0

And the amount of space in megabytes used by Data can be calculated using the following formula:

Space Used for Data = UsedExtents * 64.0 / 1024.0

---------------------------

For Logfiles
---------------------------

DBCC sqlperf(logspace)

Transaction Log Space Used = Log Size (MB) * Log Space Used (%).

Creating a Backup Copy of a Table in SQL 2005

I used the following sql to create a backup copy of a table in SQL Server 2005:

select *
into tablename_bkup
from tablename

Wasted

NOTE: If you are completely reloading data warehouse tables in an Oracle database then you should truncate the tables first instead of deleting the rows because just deleting the rows doesn't adjust the high water mark and your table can just keep expanding and expanding.

Here are some suggestions for finding out if a table is wasting space:

The following script came from http://www.dbasupport.com/oracle/scripts/ and it will show you the allocated, used and unused space for a particular object.

set echo off
set serveroutput on
set verify off
accept v_user prompt 'Schema: '
accept v_object prompt 'Object: '
accept v_object_type prompt 'Object Type: '
declare
VAR1 number;
VAR2 number;
VAR3 number;
VAR4 number;
VAR5 number;
VAR6 number;
VAR7 number;
VAR8 number;
vtable varchar2(30);

begin
dbms_space.unused_space(upper('&v_user'),upper('&v_object'),upper('&v_object_type'),VAR1,VAR2,VAR3,VAR4,V

AR5,VAR6,VAR7);
VAR2 := (VAR2)/1024/1024;
VAR4 := (VAR4)/1024/1024;
VAR8 := (VAR2-VAR4);
dbms_output.put_line('OBJECT_NAME = ' upper('&v_object'));
dbms_output.put_line('------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = 'VAR1);
dbms_output.put_line('TOTAL_MBYTES = 'VAR2);
dbms_output.put_line('UNUSED_BLOCKS = 'VAR3);
dbms_output.put_line('UNUSED_MBYTES = 'VAR4);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = 'VAR5);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = 'VAR6);
dbms_output.put_line('LAST_USED_BLOCK = 'VAR7);
dbms_output.put_line('TOTAL_USED_MBYTES = 'VAR8);
end;
/
set verify on
set echo on

+++

If you have enough space in the current tablespace, you can clear up the wasted space like this:

alter table table_name move;

This will invalidate the indexes and you will have to rebuild them like this:

alter index index_name rebuild;

I'll meet you further on up the road.

Jim