deleting giant database

I have been asked if it's possible to delete a big database - over 40GB. My
initial reaction was "sure, why not?" but now I am wondering if there might
be any issues?What kind of issues? If you're worried about a performance hit to the rest
of the server, I don't think that's a problem; the files will be deallocated
by the file system, but not overwritten, so it's generally an extremely fast
process (like, virtually instantaneous).
Dropping databases is generally a lot easier than re-creating them, so back
it up first just in case! Or, another option is instead of dropping it,
detach it and move it offline. Look up sp_detach_db in BOL.
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:9208D5DE-5D3A-431B-BD77-6126A15D5CD0@.microsoft.com...
> I have been asked if it's possible to delete a big database - over 40GB.
My initial reaction was "sure, why not?" but now I am wondering if there
might be any issues?|||also 40 GB is not that big. On these days you can easily
find databases over 500GB
>--Original Message--
>What kind of issues? If you're worried about a
performance hit to the rest
>of the server, I don't think that's a problem; the files
will be deallocated
>by the file system, but not overwritten, so it's
generally an extremely fast
>process (like, virtually instantaneous).
>Dropping databases is generally a lot easier than re-
creating them, so back
>it up first just in case! Or, another option is instead
of dropping it,
>detach it and move it offline. Look up sp_detach_db in
BOL.
>
>
>"dave" <anonymous@.discussions.microsoft.com> wrote in
message
>news:9208D5DE-5D3A-431B-BD77-
6126A15D5CD0@.microsoft.com...
database - over 40GB.
>My initial reaction was "sure, why not?" but now I am
wondering if there
>might be any issues?
>
>.
>|||Execpt for defraging your hard disk, there is none really.
You may be better of doing it though EA as it will automatically remove any
Agent Jobs, and occasionally it does not delete the logs files you you may n
eed to do it by hand, other than that have fun ;)
J
-- dave wrote: --
I have been asked if it's possible to delete a big database - over 40GB. My
initial reaction was "sure, why not?" but now I am wondering if there might
be any issues?|||Thanks for catching that - I meant 400 GB. Does that make a difference?|||No.
Dropping a DB doesn't really do anything to the server. Drop it from SQL EM
and it will automatically remove the files. You might take a small hit when
that happens. But it woudl be small. A detach or a manual drop would have
close to zero impact on the DB server.
You might want to think about simply detaching it and leaving it lilke that
for a few days. That way you can get it back easy if you decide it was an
ooops idea to begin with.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"dave" <anonymous@.discussions.microsoft.com> wrote in message
news:A790AFC1-0E30-4829-B314-1BC173BCD8C7@.microsoft.com...
> Thanks for catching that - I meant 400 GB. Does that make a difference?

delete one secondary data file and increase size of primary data f

SQL server 2000 sp3
Hi
I have a sql server 2000 database. In one application initial database size
was 13 gb. We created primary data file for required size. Application later
required 430 gb more space. We added another datafile (secondary) with 430
gb. The problem is the application does not allow us to specify where to put
the file. Looks like it just load the data in the primary datafile and then
does not try to load files in secondary datafile and secondary datafile is
not being used.
Application developer wants me to just have one datafile (Primary data file
with 450 gb). What is the best method to delete the secondary datafile and
add that much space to primary datafile.
ontario, canada
I'm just curious as to what happens when you empty the database files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>
> 1. Make sure the primary file can accomodate the data. Expand if
> neccesary. Something like
> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> 3. Remove that now empty file from the database:
> ALTER DATABASE dbname REMOVE FILE logical_file_name
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>
|||Thanks Tibor.
Bass: In my case pages were not moved as the secondary file was empty, and
file was deleted.
ontario, canada
"Tibor Karaszi" wrote:

> DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
> autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
> with the transaction log if lots of pages will be "moved" to other files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bass_player" <bass_player@.mvps.org> wrote in message
> news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
>
>

delete one secondary data file and increase size of primary data f

SQL server 2000 sp3
Hi
I have a sql server 2000 database. In one application initial database size
was 13 gb. We created primary data file for required size. Application later
required 430 gb more space. We added another datafile (secondary) with 430
gb. The problem is the application does not allow us to specify where to put
the file. Looks like it just load the data in the primary datafile and then
does not try to load files in secondary datafile and secondary datafile is
not being used.
Application developer wants me to just have one datafile (Primary data file
with 450 gb). What is the best method to delete the secondary datafile and
add that much space to primary datafile.
--
ontario, canada> What is the best method to delete the secondary datafile and
> add that much space to primary datafile.
1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
3. Remove that now empty file from the database:
ALTER DATABASE dbname REMOVE FILE logical_file_name
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"db" <db@.discussions.microsoft.com> wrote in message
news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
> SQL server 2000 sp3
> Hi
> I have a sql server 2000 database. In one application initial database size
> was 13 gb. We created primary data file for required size. Application later
> required 430 gb more space. We added another datafile (secondary) with 430
> gb. The problem is the application does not allow us to specify where to put
> the file. Looks like it just load the data in the primary datafile and then
> does not try to load files in secondary datafile and secondary datafile is
> not being used.
> Application developer wants me to just have one datafile (Primary data file
> with 450 gb). What is the best method to delete the secondary datafile and
> add that much space to primary datafile.
>
> --
> ontario, canada|||I'm just curious as to what happens when you empty the database files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>
> 1. Make sure the primary file can accomodate the data. Expand if
> neccesary. Something like
> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> 3. Remove that now empty file from the database:
> ALTER DATABASE dbname REMOVE FILE logical_file_name
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>> SQL server 2000 sp3
>> Hi
>> I have a sql server 2000 database. In one application initial database
>> size
>> was 13 gb. We created primary data file for required size. Application
>> later
>> required 430 gb more space. We added another datafile (secondary) with
>> 430
>> gb. The problem is the application does not allow us to specify where to
>> put
>> the file. Looks like it just load the data in the primary datafile and
>> then
>> does not try to load files in secondary datafile and secondary datafile
>> is
>> not being used.
>> Application developer wants me to just have one datafile (Primary data
>> file
>> with 450 gb). What is the best method to delete the secondary datafile
>> and
>> add that much space to primary datafile.
>>
>> --
>> ontario, canada
>|||DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
with the transaction log if lots of pages will be "moved" to other files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player" <bass_player@.mvps.org> wrote in message
news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
> I'm just curious as to what happens when you empty the database files
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
>> What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>>
>> 1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
>> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
>> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
>> 3. Remove that now empty file from the database:
>> ALTER DATABASE dbname REMOVE FILE logical_file_name
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
>> SQL server 2000 sp3
>> Hi
>> I have a sql server 2000 database. In one application initial database size
>> was 13 gb. We created primary data file for required size. Application later
>> required 430 gb more space. We added another datafile (secondary) with 430
>> gb. The problem is the application does not allow us to specify where to put
>> the file. Looks like it just load the data in the primary datafile and then
>> does not try to load files in secondary datafile and secondary datafile is
>> not being used.
>> Application developer wants me to just have one datafile (Primary data file
>> with 450 gb). What is the best method to delete the secondary datafile and
>> add that much space to primary datafile.
>>
>> --
>> ontario, canada|||Thanks Tibor.
Bass: In my case pages were not moved as the secondary file was empty, and
file was deleted.
--
ontario, canada
"Tibor Karaszi" wrote:
> DBCC SHRINKFILE using the EMPTYFILE option will "push" pages over to other file, causing them to
> autogrow if they can't accommodate this. These operation is logged so one need to be very cautious
> with the transaction log if lots of pages will be "moved" to other files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bass_player" <bass_player@.mvps.org> wrote in message
> news:%235OUzhZiIHA.5820@.TK2MSFTNGP04.phx.gbl...
> > I'm just curious as to what happens when you empty the database files
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> > news:F2A7EDC3-DBD2-46BB-854E-B1E27BAB2651@.microsoft.com...
> >> What is the best method to delete the secondary datafile and
> >> add that much space to primary datafile.
> >>
> >>
> >> 1. Make sure the primary file can accomodate the data. Expand if neccesary. Something like
> >> ALTER DATATBASE dbname MODIFY FILE (name = logical_filename, size = xGB)
> >>
> >> 2. Empty the secondary file. Use DBCC SHRINKFILE and the EMPTYFILE option.
> >>
> >> 3. Remove that now empty file from the database:
> >> ALTER DATABASE dbname REMOVE FILE logical_file_name
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "db" <db@.discussions.microsoft.com> wrote in message
> >> news:042E6067-EB2D-4AF9-9A2A-8998172D16B8@.microsoft.com...
> >> SQL server 2000 sp3
> >> Hi
> >> I have a sql server 2000 database. In one application initial database size
> >> was 13 gb. We created primary data file for required size. Application later
> >> required 430 gb more space. We added another datafile (secondary) with 430
> >> gb. The problem is the application does not allow us to specify where to put
> >> the file. Looks like it just load the data in the primary datafile and then
> >> does not try to load files in secondary datafile and secondary datafile is
> >> not being used.
> >>
> >> Application developer wants me to just have one datafile (Primary data file
> >> with 450 gb). What is the best method to delete the secondary datafile and
> >> add that much space to primary datafile.
> >>
> >>
> >>
> >> --
> >> ontario, canada
> >>
>
>