Attach database SQL 2005

There is an error when i attach the database

"TITLE: Microsoft SQL Server Management Studio

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

E:\DataBase\Test\MyTest_Data.MDF is not a primary database file. (Microsoft SQL Server, Error: 5171)

"

What I do normally when the log file take much space then i detach the database and delete the log file and attach the database file again. It works in SQL 2000. but generating error in SQL 2005.

Your method of truncating the log file works in SQL2000 but it's not so easy in SQL2005 (like nearly everything in SQL2005 compared to SQL2000). To attach the mdf file after deleting the ldf file, you can use the sp_attach_single_file_db stored procedure. It will build a new log file. I have used it several times when moving DBs to a different server. I did not move the log file but rather rebuilt it on the target server.

However, I suggest changing your setup so that the log file does not grow so big which necessitates manual truncation. There are several ways to do this but let me suggest two:

1. Change the Recovery Model in Database properties in Mgmt Studio to Simple. Then the log file will not grow as transactions occur. The problem with this is that recovery is to the time of the last database backup.

2. If the Recovery Model in Database properties in Mgmt Studio is Full, then transactions remain in the log file until the log file is backed up. That is why it grows so big. Apparently you are not backing it up. A database backup does not truncate the log file. A separate log file backup performs a truncation. We recommend that a maintenance plan be set up to back up the log file at least once daily. This is not the database backup but rather a log file backup. Our standard practice for most of our production DBs is to have the maintenance plan back up the DB at about 11pm each night and back up the log files every 2 hours from 8am to 8pm daily. That way the maximum work that would be potentially lost is 2 hours, which is tolerable in most sites. Believe me, we have used it! Although rare, RAID 5 drives do actually fail.

|||

I guess what u are doing to avoid frequent log full is not the correct way of doing it.

i would suggest

.change the recovery type of database from simple to FULL"

.consider taking frequent Transaction log back ups in between full backups.

otherway..instead of detach the database run the log backup with no_log clause to free up trnsaction log space.