Error 5020 Sql
Contents |
of Sql Server Storage Engine: Advanced Scanning (Enterprise Editiononly) » Sql Server Error: The primary data or log file cannot be removed from adatabase May 31, 2013 by Sql Times Another quick one: Sometimes when you sql server change primary log file are try to move data/log files from one location to another you employ the how to remove log file in sql server 2008 r2 option of moving all the objects from the file and emptying it out. Then using ALTER DATABASE we could try to msg 5020, level 16, state 1, line 1 the primary data or log file cannot be removed from a database. delete it. But when you do that we run into this error. Server: Msg 5020, Level 16, State 1, Line 1 The primary data or log file cannot be removed from a database. This
How To Clear Log File In Sql Server
is because, by default Sql Server will not allow deleting the first data/log file created when you created the database. Since deleting the primary data/log file is not allowed, we can move the data/log file to a new location by bringing the database offline. This is not a favourite option for everyone, but this is a solution. Step 1: Bring the database OFFLINE ALTER DATABASE DBADB SET OFFLINE Step 2: Move the file to the new location. Step 3: Add the new file location to the metadata ALTER DATABASE DBADB MODIFY FILE ( NAME = N'DBADB_Log' , FILENAME = N'L:\MSSQL\Log\DBADB_Log.LDF' ) Step 4: Bring the database ONLINE ALTER DATABASE DBADB SET ONLINE If you have a better solution, please share it in the comments section. Hope this helps, _Sqltimes Rate this:Like this:Like Loading... Posted in DBA | 2 Comments 2 Responses on June 2, 2015 at 3:10 pm | Reply Azam then how to deal with large LDF file ? on June 2, 2015 at 3:21 pm | Reply Sql Times Than you for your comment. This post is not about dealing with large LDF files, but about moving them. For large LFD files, take frequent log backups. This will keep the file small enough (or back to original size). Allocate enough space for LDF file so there is no need for frequent file ‘growth'. But in rare cases, when file growth too large, take LOG backup. Then as needed perform a SHRINKFILE operation. Note: Do not do these on production machines; Play with the idea in your lab and then proceed accordingly. Comments RSS Leave a Reply Cancel reply Enter your comment here... Fill in
von GoogleAnmeldenAusgeblendete FelderNach Gruppen oder Nachrichten suchen
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business http://serverfault.com/questions/88854/re-assigning-changing-the-primary-log-file-sql-2000 Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Re-assigning / changing the Primary Log File - SQL 2000 up log file vote 2 down vote favorite I've got a large db (called Navision4) which currently has 2 log files: Navision4_Log Navision4_Log3 There was a log called Navision4_Log2, but i managed to remove this. Navision4_Log is located on the C:\ drive - and is not used. Navision4_Log3 is located on the G:\ drive - but is huge - and this appears to be where all transactions are written. However, I want to remove one, leaving just one log file. log file in How should I do this? I tried to remove Navision4_Log - but got: Error 5020: The primary data or log file cannot be removed from a database log-files sql-server share|improve this question edited Nov 30 '09 at 11:26 Nick Kavadias 9,74372743 asked Nov 27 '09 at 17:31 alex 801113260 add a comment| 2 Answers 2 active oldest votes up vote 1 down vote What recovery model is your database using? are you taking regular log backups? If your in full recovery & not taking log backups, then this may explain why Navision4_Log3 is so large. You should fix this by either taking log backups, or switch to simple recovery. I would move Navision4_Log to the G drive rather then try to remove it. My guess is that Navision4_Log3 exists because the server was running out of space on C: & the admin didn't know how to move it. To move the log file to G: use the following: ALTER DATABASE Navision4 SET OFFLINE; --move log file to G: ALTER DATABASE Navision4 MODIFY FILE ( NAME='Navision4_Log', FILENAME='G:\MSSQL\TLOG\Navision4_Log.ldf'); ALTER DATABASE Navision4 SET ONLINE; EDIT:SQL Server 2000 doesn't support those options, so you'll have to use sp_detachdb & sp_attachdb, you can also detach & attach in the Enterprise Manager GUI. Just make sure you attach the db & specify the owner as sa. Then you can remove Navision4_Log3 if you