October 6, 2011 at 11:39 pm
how to delete unnecessary data and log file in a database
use abc
sp_helpfile
team_Data
team_Log
abc_1_Data ---need to remove this file(extra added) not imp
abc_1_Log-----need to remove this file(extra added)not imp
please suggest to remove this files in a database
October 7, 2011 at 4:13 am
Got the solution from google
Deleting Files
Deleting a data or a transaction log file removes the file from the database. However, you cannot remove a file from a database if the file contains data or transaction log information. You can only remove a file if the file is empty. If you have data that you want to keep, you can migrate the data from a data file to other files in the same filegroup. To migrate the data, you can use a DBCC SHRINKFILE statement and specify the EMPTYFILE clause. Then, SQL Server no longer allows data to be inserted in the file, and you can delete the file by using an ALTER DATABASE statement.
You cannot migrate the transaction log data from one log file to another to delete a transaction log file.
Purging Inactive Transactions
To purge inactive transactions from a transaction log file, you must truncate or back up the transaction log. When a transaction log file no longer contains any active or inactive transactions, you can remove the log file from the database. To remove the log file from the database, use these steps:
To shrink data or information in a file and to make the file empty, run the following Transact-SQL statement:
DBCC SHRINKFILE ('<logical file name>', EMPTYFILE )
To delete a file from a database, run the following Transact-SQL statement:
ALTER DATABASE <Database name> REMOVE FILE <logical file name>
Thanks
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply