How to: Delete Data or Log Files from a Database

Deleting Files

Deleting a data or transaction log file removes the file from the database. You cannot remove a file from the database unless the file has no existing data or transaction log information; the file must be completely empty before it can be removed. To empty a data file by moving data from the data file to other files in the same filegroup, use the DBCC SHRINKFILE statement and specify the EMPTYFILE clause. Because the Database Engine no longer allows data to be placed on the file after the shrink operation, the empty file can be removed by using the ALTER DATABASE statement or SQL Server Management Studio.

You cannot move transaction log data from one log file to another to empty a transaction log file. To remove inactive transactions from a transaction log file, the transaction log must be truncated or backed up. When the transaction log file no longer contains any active or inactive transactions, the log file can be removed from the database. For more information, see Managing the Transaction Log.

Steps to Remove Data File

Step 1

-- Empty the data file
DBCC SHRINKFILE (Test1data, EMPTYFILE)

Step 2

-- Backup the Database / Transaction Log so the file can be cleared out completely
BACKUP DATABASE AdventureWorks2008R2 TO DISK = '\\backupshare\AdventureWorks2008R2.bak'

or

-- Backup the Database / Transaction Log so the file can be cleared out completely
BACKUP LOG AdventureWorks2008R2 TO DISK = '\\backupshare\LOG_AdventureWorks2008R2_0600.bak'

Step 3

-- Remove the data file from the database
ALTER DATABASE AdventureWorks2008R2 REMOVE FILE Test1data

References:

How to: Delete Data or Log Files from a Database
How to: Delete Data or Log Files from a Database (SQL Server 2005)
Adding and Deleting Data and Transaction Log Files

How to: Find the Last Modified Date of The Database

This one will list all online databases on the server and the last modified date

DECLARE @sqlString NVARCHAR(max)
DECLARE @union NVARCHAR(max)
SET @sqlString = ''
SET @union = ''
DECLARE @name nvarchar(50);

DECLARE crs CURSOR FOR 
SELECT Name FROM sys.databases WHERE  state = 0
OPEN crs
FETCH NEXT FROM crs INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlString = @sqlString + @union 
   SET @sqlString = @sqlString + '
    SELECT 
    TOP 1
  ''' + @name + ''' as DBName, modify_date
  FROM
   [' + @name + '].sys.tables'

 SET @union = ' UNION '

    FETCH NEXT FROM crs INTO @name
END 

SET @sqlString = @sqlString + ' ORDER BY DBName ASC'

CLOSE crs;
DEALLOCATE crs;

EXEC(@sqlString)

How to: Examine Table Created Date and Modified Date

If you would like to examine when each table was created in your database, you probably come to the right place. The following T-SQL will list all your tables in your database with the created date and last modified date. However you can change the order by clause as well as the where clause according to your requirements.

SELECT sys.schemas.name + '.' + sys.objects.name,
	create_date,
	modify_date
FROM sys.objects
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE type = 'P'
	OR type = 'U'
	OR type = 'FN'
	OR type = 'V'
ORDER BY modify_date DESC