Hi,
We have many databases in production on our server. Each are generating log
files which have grown very large. I'd like to delete them & basically start
'growing them' again.
Basically, I want to stop tracing, delete the log files then start tracing
again in order to generate new ones. We have nightly backups & don't need an
y
point of time restores prior to today.
What are the dangers in this. Should I simply go ahead & do this?
Thanks for any advice on this
AntHi
Please refer to the documentation on Backup in SQL Server:
http://msdn2.microsoft.com/en-us/library/ms175477.aspx
Do no just delete the log files as you may end up with a database that is
not usable and this process is not supported by Microsoft, rather set the
correct recovery mode and/or do regular log backups.
Regards
Michel Epprecht [MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
> Hi,
> We have many databases in production on our server. Each are generating
> log
> files which have grown very large. I'd like to delete them & basically
> start
> 'growing them' again.
> Basically, I want to stop tracing, delete the log files then start tracing
> again in order to generate new ones. We have nightly backups & don't need
> any
> point of time restores prior to today.
> What are the dangers in this. Should I simply go ahead & do this?
> Thanks for any advice on this
> Ant|||Hello,
Are you talking about the Transaction log file (LDF) or the SQL profiler
output files. If it Transaction log files and if the data is production
you should take the Transaction log backup in regular intervals. Thsi will
keep you your LDF file in control.
If it is trace files you could only trace the required events.
Thanks
Hari
"Michael Epprecht [MSFT]" <michael.epprecht@.online.microsoft.com> wrote
in
message news:eOn9K7%23SHHA.920@.TK2MSFTNGP05.phx.gbl...
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>|||Hi Michael,
Thanks for the response,
The problem we are facing is we are running out of data on our drive.
"...and/or do regular log backups."
We backup every evening. Would it then be safe to delete these log files?
How can the data base be left unusable if we delete them? Is it just a
matter of not being able to do a point in time restore or will it have more
dire effects.
If I do go ahead & delete them, will the transaction logs just start from
the point that tracing begins again?
Thanks very much for your help.
Ant
"Michael Epprecht [MSFT]" wrote:
> Hi
> Please refer to the documentation on Backup in SQL Server:
> http://msdn2.microsoft.com/en-us/library/ms175477.aspx
> Do no just delete the log files as you may end up with a database that is
> not usable and this process is not supported by Microsoft, rather set the
> correct recovery mode and/or do regular log backups.
> --
> Regards
> Michel Epprecht [MSFT]
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9F4368FF-DA0F-43E1-ADB6-44082AEB2E7C@.microsoft.com...
>|||Don't just delete the ldf files. They are an integral part of the database.
It's like saying that
you need more space on the hd and want to remove the windows folder. Shrink
the file (if really
needed), then do a manual grow to the necessary size. And then either put th
e database in simple
recovery mode, or do regular transaction log backups. See
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have mor
e
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>|||> We backup every evening.
What types of backups?. Note that a database backup does not remove
uncommitted transactions from the log so the log files will grow
indefinitely. The only way to remove committed log data is with a LOG
backup or by keeping the database in the SIMPLE recovery model. In the FULL
or BULK_LOGGED model, one normally schedules periodic LOG backups in between
to full backups.
Once you've backed up your logs, you can shrink the log file back to a
reasonable size as Tibor suggested.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>|||Hello,
If your recovery model is FULL or BULK_LOGGED you need to perform
Transaction Log Backup in frequent intervals. This will make sure that LDF
file will not grow.
Follw the below steps:-
1. Schedule a Daily full backup once a day.
2. Schedule a Transaction Log backup every 30 minutes
3. After the completion of daily full database backup you could delete all
the previous transction log backup files
This approach will help you to recover the database fully.
If your current LDF file is huge then use DBCC SHRINKFILE to reduce the
size. Before shrink either truncate the Log or Backup the log.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:7B4C5F45-01A3-4CD6-8CB8-A0E28D86E41E@.microsoft.com...[vbcol=seagreen]
> Hi Michael,
> Thanks for the response,
> The problem we are facing is we are running out of data on our drive.
> "...and/or do regular log backups."
> We backup every evening. Would it then be safe to delete these log files?
> How can the data base be left unusable if we delete them? Is it just a
> matter of not being able to do a point in time restore or will it have
> more
> dire effects.
> If I do go ahead & delete them, will the transaction logs just start from
> the point that tracing begins again?
>
> Thanks very much for your help.
> Ant
>
> "Michael Epprecht [MSFT]" wrote:
>
No comments:
Post a Comment