Deleting Log file

Hello
Is there any time / way /procedure to delte the log file and let SQL
recreate it without losing any data. In other words - at what point in time
can one be safely assured that all data in the log file is committed to the
database.
reason - have 1.2gb database and 9gb logfile - caused by duplicating
another database ( importing thru ODBC ) each night and would like to kill
the log file each morning after the transfer - the SQL database is only
used for reporting as the other package has lots of limitations - yep - we
are working on converting it all to SQL so this replication doesn't have to
happen but that is still a fair way off
TIA
Pete
If your recovery plan is to restore from your last full database backup (or
rerun your import), you can set the database recovery model to SIMPLE so
that committed transactions are automatically removed from the log.
You can run DBCC SHRINKFILE to release unused log space back to the OS. The
log will still need to be large enough to accommodate your largest
transaction so you probably don't want to do this routinely.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pete" <p0911@.hotmail.com> wrote in message
news:eAMZK0PSEHA.1568@.TK2MSFTNGP11.phx.gbl...
> Hello
> Is there any time / way /procedure to delte the log file and let SQL
> recreate it without losing any data. In other words - at what point in
time
> can one be safely assured that all data in the log file is committed to
the
> database.
> reason - have 1.2gb database and 9gb logfile - caused by duplicating
> another database ( importing thru ODBC ) each night and would like to kill
> the log file each morning after the transfer - the SQL database is only
> used for reporting as the other package has lots of limitations - yep - we
> are working on converting it all to SQL so this replication doesn't have
to
> happen but that is still a fair way off
> TIA
> Pete
>

Deleting Log file

Hello
Is there any time / way /procedure to delte the log file and let SQL
recreate it without losing any data. In other words - at what point in time
can one be safely assured that all data in the log file is committed to the
database.
reason - have 1.2gb database and 9gb logfile - caused by duplicating
another database ( importing thru ODBC ) each night and would like to kill
the log file each morning after the transfer - the SQL database is only
used for reporting as the other package has lots of limitations - yep - we
are working on converting it all to SQL so this replication doesn't have to
happen but that is still a fair way off
TIA
PeteIf your recovery plan is to restore from your last full database backup (or
rerun your import), you can set the database recovery model to SIMPLE so
that committed transactions are automatically removed from the log.
You can run DBCC SHRINKFILE to release unused log space back to the OS. The
log will still need to be large enough to accommodate your largest
transaction so you probably don't want to do this routinely.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pete" <p0911@.hotmail.com> wrote in message
news:eAMZK0PSEHA.1568@.TK2MSFTNGP11.phx.gbl...
> Hello
> Is there any time / way /procedure to delte the log file and let SQL
> recreate it without losing any data. In other words - at what point in
time
> can one be safely assured that all data in the log file is committed to
the
> database.
> reason - have 1.2gb database and 9gb logfile - caused by duplicating
> another database ( importing thru ODBC ) each night and would like to kill
> the log file each morning after the transfer - the SQL database is only
> used for reporting as the other package has lots of limitations - yep - we
> are working on converting it all to SQL so this replication doesn't have
to
> happen but that is still a fair way off
> TIA
> Pete
>

deleting backup history in order to allow for restore

Hello:
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980s
The first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>
|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:

> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
>