[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

How to get rid of .ldf files and create a new one

I am using SQL Server 2005. Since i do some development work so i create the replica of my live databases to run for trial. When i restore the data from the live one to the test one it increases the size of the log file very much which is occupying the space unnecessarily. I am trying to get rid of the log file and create a new one. What I have done so far is detach the database, delete the .ldf file and when i try to attach the database it says the .ldf file is not present. Could any body help me with this
0
shieldguy
Asked:
shieldguy
1 Solution
 
Chris MangusDatabase AdministratorCommented:
Restore the database as usual.  Put the database in Simple recovery mode.  Then, run DBCC SHRINKFILE and shrink the .ldf.

You can't just get rid of it and create a new one.
0
 
MrNeticCommented:
shieldguy,

Put the database in the developement stage in SIMPLE RECOVERY MODEL, This will truncate the Transaction Log everytime the engine do checkpoint.

Best Regards,
Paulo Condeça
0
 
MrNeticCommented:
Don't forget to shrink the transaction log ( DBCC SHRINKFILE ('LOGICAL_NAME_OF_TRANSACTION_LOG') )

Best Regards,

Paulo Condeça
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Anthony PerkinsCommented:
If you are going to use Full Recovery Model, than you need to get in the habit of backing up the Transaction Log as well as the database.
0
 
shieldguyAuthor Commented:
Could any body please explain me clearly what is 'Simple Recovery Model' and 'Full Recovery Model'. What option do I need to select for that.

Kind Regards
0
 
MrNeticCommented:
shieldguy,

Don't have time to explain, but you can consult this site http://msdn2.microsoft.com/en-us/library/ms189275.aspx .

Hope it helps.
0
 
Chris MangusDatabase AdministratorCommented:
Is there anything else we can do for you on this question?
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now