USE tpce
GO
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO
 
---
--- Set connection attributes
---
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO


-- extend growing_fg temporary with another file on different disk drives before index creation growing
-- to be deleted at the end of create index growing, that is here
PRINT 'start shrink tpce extention'
GO

-- Empty the data file
-- and remove the data file from the database

EXEC sp_dboption tpce,'trunc. log on chkpt.',true
go
ALTER DATABASE tpce SET RECOVERY BULK_LOGGED
go

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files
go

SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO
PRINT 'shrink file01'
GO
DBCC SHRINKFILE (tpceaddon01, EMPTYFILE);
GO
ALTER DATABASE tpce
REMOVE FILE tpceaddon01
GO

SELECT  CONVERT(CHAR(30), GETDATE(), 21)
PRINT 'delete tpce extention done'
GO

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files
go

EXEC sp_dboption tpce,'trunc. log on chkpt.',false
go
ALTER DATABASE tpce SET RECOVERY FULL
go

