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.

Deleting a Log file and recreating it...

I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why this
log file grew so much; originally the DB was set to SIMPLE so Trans logs
weren't being backed up. This DB is in a Dev environment, so I think someone
changed it to full, and the Trans logs never got backed up. In any case, is
it okay to delete a log file? If so, how do you go about recreating a new
one? Through the properties of the database in Enterprise Mgr? I am told
that you really only need log files for say a midday backup or something; we
do full backups of the dbs daily. Anyway, if anyone can let me know if what
I want to do is okay or not, I would appreciate it.
I'm pretty new to SQL and don't want to start doing things that really are
not "correct" procedures. I did try backing up the Trans Log, but it didn't
budge the size of it at all.
Saral6978 wrote:
> I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why this
> log file grew so much; originally the DB was set to SIMPLE so Trans logs
> weren't being backed up. This DB is in a Dev environment, so I think someone
> changed it to full, and the Trans logs never got backed up. In any case, is
> it okay to delete a log file? If so, how do you go about recreating a new
> one? Through the properties of the database in Enterprise Mgr? I am told
> that you really only need log files for say a midday backup or something; we
> do full backups of the dbs daily. Anyway, if anyone can let me know if what
> I want to do is okay or not, I would appreciate it.
> I'm pretty new to SQL and don't want to start doing things that really are
> not "correct" procedures. I did try backing up the Trans Log, but it didn't
> budge the size of it at all.
I don't want to re-type the whole explanation again, so I'll just refer
you to this thread:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/ba55a63c258ae646/a3338eddc9fc8b46
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||So, the answer in whether I can just delete the log file and recreate it then
is simply no? I did back up the transaction log, then I backed up the DB,
but the log still didn't go down in size. I do not have the disk space to
keep the log this big - I'm down to 9MB of space on the paritition that
stores the log.
I guess I can try to figure out how to truncate it, then shrink it, then
back everything up.
"Tracy McKibben" wrote:

> Saral6978 wrote:
> I don't want to re-type the whole explanation again, so I'll just refer
> you to this thread:
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/ba55a63c258ae646/a3338eddc9fc8b46
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||Saral6978 wrote:
> So, the answer in whether I can just delete the log file and recreate it then
> is simply no? I did back up the transaction log, then I backed up the DB,
> but the log still didn't go down in size. I do not have the disk space to
> keep the log this big - I'm down to 9MB of space on the paritition that
> stores the log.
> I guess I can try to figure out how to truncate it, then shrink it, then
> back everything up.
>
Backing up the log simply truncates it, and as I stated clearly in the
referenced thread, TRUNCATING IS NOT SHRINKING. Now that you've
truncated the log, you can use DBCC SHRINKFILE to shrink it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi,
you cant delete a log file. Seems that your log has to be shrinked
not completly deleted.
http://support.microsoft.com/kb/272318
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Thanks for the MS KB article...this is a little easier to understand how I'm
supposed to do this.
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
|||I got shrunk down to approx 1GB. Thanks, again!
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Deleting a Log file and recreating it...

I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why thi
s
log file grew so much; originally the DB was set to SIMPLE so Trans logs
weren't being backed up. This DB is in a Dev environment, so I think someon
e
changed it to full, and the Trans logs never got backed up. In any case, is
it okay to delete a log file? If so, how do you go about recreating a new
one? Through the properties of the database in Enterprise Mgr? I am told
that you really only need log files for say a midday backup or something; we
do full backups of the dbs daily. Anyway, if anyone can let me know if what
I want to do is okay or not, I would appreciate it.
I'm pretty new to SQL and don't want to start doing things that really are
not "correct" procedures. I did try backing up the Trans Log, but it didn't
budge the size of it at all.Saral6978 wrote:
> I have a 2GB database in SQL 2000 that has a 24GB log file; Not sure why t
his
> log file grew so much; originally the DB was set to SIMPLE so Trans logs
> weren't being backed up. This DB is in a Dev environment, so I think some
one
> changed it to full, and the Trans logs never got backed up. In any case,
is
> it okay to delete a log file? If so, how do you go about recreating a new
> one? Through the properties of the database in Enterprise Mgr? I am told
> that you really only need log files for say a midday backup or something;
we
> do full backups of the dbs daily. Anyway, if anyone can let me know if wh
at
> I want to do is okay or not, I would appreciate it.
> I'm pretty new to SQL and don't want to start doing things that really are
> not "correct" procedures. I did try backing up the Trans Log, but it didn
't
> budge the size of it at all.
I don't want to re-type the whole explanation again, so I'll just refer
you to this thread:
http://groups.google.com/group/micr...3338eddc9fc8b46
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||So, the answer in whether I can just delete the log file and recreate it the
n
is simply no? I did back up the transaction log, then I backed up the DB,
but the log still didn't go down in size. I do not have the disk space to
keep the log this big - I'm down to 9MB of space on the paritition that
stores the log.
I guess I can try to figure out how to truncate it, then shrink it, then
back everything up.
"Tracy McKibben" wrote:

> Saral6978 wrote:
> I don't want to re-type the whole explanation again, so I'll just refer
> you to this thread:
> http://groups.google.com/group/micr...3338eddc9fc8b46
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Saral6978 wrote:
> So, the answer in whether I can just delete the log file and recreate it t
hen
> is simply no? I did back up the transaction log, then I backed up the DB,
> but the log still didn't go down in size. I do not have the disk space to
> keep the log this big - I'm down to 9MB of space on the paritition that
> stores the log.
> I guess I can try to figure out how to truncate it, then shrink it, then
> back everything up.
>
Backing up the log simply truncates it, and as I stated clearly in the
referenced thread, TRUNCATING IS NOT SHRINKING. Now that you've
truncated the log, you can use DBCC SHRINKFILE to shrink it.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
you can=B4t delete a log file. Seems that your log has to be shrinked
not completly deleted.
http://support.microsoft.com/kb/272318
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks for the MS KB article...this is a little easier to understand how I'm
supposed to do this.
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||I got shrunk down to approx 1GB. Thanks, again!
"Jens" wrote:

> Hi,
> you can4t delete a log file. Seems that your log has to be shrinked
> not completly deleted.
> http://support.microsoft.com/kb/272318
> HTH, Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>