Getting rid of Primary Log file - SQL 2K 
Author Message
 Getting rid of Primary Log file - SQL 2K
Due to growth, we've created a second transaction log file on another drive.
I did a dbcc shrinkfile on the original transaction log and have shrunk it
down to the minimum size. When I try and delete it, I get error 5020 -
Primary data or log file can't be removed from database.

Is there any way we can make the new transaction log file the primary and
free up the other drive?

Thanks,

Bob



Mon, 26 Jul 2004 01:35:54 GMT
 Getting rid of Primary Log file - SQL 2K

Bob,

You have to use DBCC SHRINKFILE(... emptyfile) to get all data off the old
log file.
1.Make sure that there isnt any open transactions...DBCC OPENTRAN.
2.Detach the database using sp_detach_db.(this isnt a online
operation...means users shouldnt be accessing the database)
3.Attach the database back using sp_attach_db by mentioning the new log
file.

Dinesh.


Quote:
> Due to growth, we've created a second transaction log file on another
drive.
> I did a dbcc shrinkfile on the original transaction log and have shrunk it
> down to the minimum size. When I try and delete it, I get error 5020 -
> Primary data or log file can't be removed from database.

> Is there any way we can make the new transaction log file the primary and
> free up the other drive?

> Thanks,

> Bob



Mon, 26 Jul 2004 01:54:12 GMT
 Getting rid of Primary Log file - SQL 2K

Quote:
> Bob,

> You have to use DBCC SHRINKFILE(... emptyfile) to get all data off the old
> log file.

Thanks for pointing that out. For some bizarre reason, I thought the
emptyfile argument could only be used with data files, not log files.

Quote:
> 1.Make sure that there isnt any open transactions...DBCC OPENTRAN.
> 2.Detach the database using sp_detach_db.(this isnt a online
> operation...means users shouldnt be accessing the database)
> 3.Attach the database back using sp_attach_db by mentioning the new log
> file.

> Dinesh.

Oh boy, a 2:00 Sunday morning trip to the office:-(

Thanks for the info.

Bob



Mon, 26 Jul 2004 04:29:30 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Getting rid of the IDENTITY property on a column withou getting rid of the column

2. Getting Rid of a log segment

3. Bloat in SQL 2k - run times, BAK files and trans logs

4. SQL 2000 transaction log file getting very big

5. Getting rid of trailing spaces in VB or SQL

6. Getting rid of the SQL hour glass

7. Getting rid of confirmation dialog when executing an SQL query

8. Help with Primary Key Field in SQL 2K

9. Replicating primary keys on SQL 2K

10. Message on error log of SQL Server 7.0 when accessing from SQL 2K tools

11. Q: How to remove the primary log file from a database

12. Remove primary log file?


 
Powered by phpBB® Forum Software