Rick
asked on
SQL Log Files
Hello,
I'm a .Net developer who not long ago also received a Microsoft SQL DBA hat...
A SQL 2005 server here has a few databases on it with huge log files.
One has a 85 GB log file, another has a 20 GB, etc...
Forgive me my ignorance, but do I need those log files?
If yes, how do I "clean them up"?
If not, how do I get rid of them?
Thank you.
I'm a .Net developer who not long ago also received a Microsoft SQL DBA hat...
A SQL 2005 server here has a few databases on it with huge log files.
One has a 85 GB log file, another has a 20 GB, etc...
Forgive me my ignorance, but do I need those log files?
If yes, how do I "clean them up"?
If not, how do I get rid of them?
Thank you.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
The log files truncation process is associated to the log backup or replication. If you don't use this functionallity, change the recovery model to simple, because a truncation is a puntual solution, but on time the log will be huge again.
tell us if you need to know how to change the recovery mode.
tell us if you need to know how to change the recovery mode.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
I found how to change the recovery mode...
I need to know if I need the transaction log files or not.
What are they used for?
If I need them, I will use lsavidge's suggestion (back them up and truncate them).
If I don't need them, how do I get rid of them?
I need to know if I need the transaction log files or not.
What are they used for?
If I need them, I will use lsavidge's suggestion (back them up and truncate them).
If I don't need them, how do I get rid of them?
No, after change the rec mode to simple, right click the database an select shrink file, select the log an clic ok.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
I would always keep the transaction logs. They are teh logs of what happened in the database and if you ever needed to restore, they will be your best friend. If you backup the database, they will not be that large. Use the suggestion. And tehn create a maintenance plan.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
>>No, after change the rec mode to simple, right click the database an select shrink file, select the log an clic ok.<<
By doing that, they have the potential of losing all the work they have done since the last backup.
By doing that, they have the potential of losing all the work they have done since the last backup.
If you never makes log backups, or if you have your database in simple recovery modem, then the log file can be truncated at any time without data loss,
ASKER
@ 35940493:
Shrink a 85 GB log file to 1 MB?
@ 35940489:
What would be a safe "more rational size" if the log file size is 85 GB?
Shrink a 85 GB log file to 1 MB?
@ 35940489:
What would be a safe "more rational size" if the log file size is 85 GB?
Review the size of your data files plus log files and compare it with the size of your last full backup. If the size of the log doesn't use space in the full backup, then the 85 Gb of transactions recorded in the log are already apliyed to the Data File, and the truncation don't affect the information of the database. Make a full backup after shrink
ASKER
Thank you all.
I will follow your advice:
Make full backups
Change recovery model to simple
Shrink the log files
Change recovery model back to full
Create a maintenance plans
I will follow your advice:
Make full backups
Change recovery model to simple
Shrink the log files
Change recovery model back to full
Create a maintenance plans
>>What would be a safe "more rational size" if the log file size is 85 GB?<<
It is difficult to know in your particular case. It largely depends on transactions that you do during the day and how often you backup. I would try 4GB, but that is a wild guess and your miles may vary. However, if you set it to a fairly conservative size (say 2GB) make sure auto-grow is enabled you will soon see what is the optimum size based on your usage and frequency of transaction log backups.
It is difficult to know in your particular case. It largely depends on transactions that you do during the day and how often you backup. I would try 4GB, but that is a wild guess and your miles may vary. However, if you set it to a fairly conservative size (say 2GB) make sure auto-grow is enabled you will soon see what is the optimum size based on your usage and frequency of transaction log backups.
ASKER