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
>sql

Deleting and recreating database with same name

Question...
I created a database, but I want to totally blow that database away
and recreate it. I right-clicked on the DB and deleted it, no longer
there. Now when I go to recreate the database with the same name, I
get the following error:
Error 5170: Cannot create file 'C:\...MDF' because it already exists.
CREATE DATABASE failed. Some file names listed could not be created.
Check previous errors.
Okay, when I tell SQL I want to delete a database, I want it 100%
deleted. Apparently SQL's idea of Delete and mine are different, so
any suggestions on how I can totally remove this database? Since MS
is known to do plenty hidden the background, I don't want to just
delete the .MDF file, since I'm sure this will confuse SQL quite a
bit.
So back to the root question... how do I 100% delete a database where
I can create another DB with the same name?
Thanks,
Alex.You'll need to delete the MDF file it's complaining about from the OS.
HTH
Ryan Waight, MCDBA, MCSE
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.|||> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted.
This is what DROP DATABASE does, which is the command called from QA and EM. Unless you detach it,
of course.
> Apparently SQL's idea of Delete and mine are different
No, something strange must have happened.
Just try to delete the database files. If the database still exists in the eyes of SQL Server, then
the files are locked and you can't delete them.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310270803.5a6751fa@.posting.google.com...
> Question...
> I created a database, but I want to totally blow that database away
> and recreate it. I right-clicked on the DB and deleted it, no longer
> there. Now when I go to recreate the database with the same name, I
> get the following error:
> Error 5170: Cannot create file 'C:\...MDF' because it already exists.
> CREATE DATABASE failed. Some file names listed could not be created.
> Check previous errors.
> Okay, when I tell SQL I want to delete a database, I want it 100%
> deleted. Apparently SQL's idea of Delete and mine are different, so
> any suggestions on how I can totally remove this database? Since MS
> is known to do plenty hidden the background, I don't want to just
> delete the .MDF file, since I'm sure this will confuse SQL quite a
> bit.
> So back to the root question... how do I 100% delete a database where
> I can create another DB with the same name?
> Thanks,
> Alex.

delete or drop table then create table

which one is smarter, where there is no indexing on the table which is really simple table delete everything or recreate table. I got an argument with one of my coworker. He says it doesnt matter i say do delete. Any opinions.DELETE is a logged transaction, and so incurs overhead that DROP and RECREATE do not.
For another option, try TRUNCATE table, which is also unlogged.|||well said i did research truncate table and you are of course correct on this. I'll change the code to that :D|||delete *

You crack me up|||Why don't you show us what you're doing, what volume, ect

Where for example are you getting the data from...and if you got into an argument, what did your coworker say?|||Personally I would not allow the drop/create table here, as it carries too many permission implications. Take the matter of the logs up with the DBA involved, and see if the transaction logs will hold up. He may need to add more diskspace, more backups, or both.|||delete *
You crack me upThat's MS Access syntax...|||Heres the code i made

if 0< (select count(*) from dbo.sysobjects where id = object_id(N'[dbo].[_Bootfiles_Summary_Work_Table]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
TRUNCATE TABLE _Bootfiles_Summary_Work_Table
end
else
begin
Create Table _Bootfiles_Summary_Work_Table
(Agency smallint,
Plate_State varchar(20),
Plate varchar(8),
Cite_Count int,
CiteBalance Money)
end

its really simple actually no index no nothing, and in case it gets deleted some code that i dont know this is will recreate the table.

and o yea no star on the delete my mistake.