Deleting files

Hello there
On every w by Enterprise manager i:
1. Detatch the database
2. delete the log file
3. reattaching it again
I would like to do this by code.
Is there a way to do that?Roy Goldhammer wrote:

> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
Yes. Don't. Regularly detaching and deleting the log in an operational
system is certainly foolish, unnecessary and/or dangerous.
Firstly, set the correct recovery model - that means "Simple Recovery"
if you don't require log backups. Secondly, ensure you implement the
right tran log backups if you do need them. Finally, set the log file
to the right size, turn off autogrow and LEAVE IT ALONE.
More info:
http://support.microsoft.com/?id=110139
http://support.microsoft.com/?id=317375
http://support.microsoft.com/?id=315512
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
David Portas
SQL Server MVP
--|||Hi
Why?
What you are doing is unsupported and very dangerous. In some cases, you
will not be able to re-attach the database again and kiss your DB goodbye.
Rather put your database into the appropriate recovery mode, and backup the
log if it is not in simple mode.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OnjYA8GCGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
>|||I agree with David and Mike. Don't do this. It will bite you in the
backside.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OnjYA8GCGHA.4076@.TK2MSFTNGP14.phx.gbl...
> Hello there
> On every w by Enterprise manager i:
> 1. Detatch the database
> 2. delete the log file
> 3. reattaching it again
> I would like to do this by code.
> Is there a way to do that?
>

Delete OF 2.5 GB (Cont.)

Hi,
Thanks for your earlyer comments, but now i have a problem:
1. I have backed up the database
2. i've made 2.5 GB deletes in small batches
3. This table only has a clustered index on an PK Column,
i have performed a DBREINDEX on that Pk clustered index.
Results:
name
Table_name
rows reserved data index_size unused
221392 4902000 KB 2724296 KB 112 KB 2177592 KB
How can i free the unused table space ?? Why the
dbreindex didin't release this space ?
Sorry Can you help me ?
Thanks again
Mike
..
Delete OF 2.5 GB (Cont.)

Delete of 2.5 GB

Hi,
Thanks for your comments, but now i have a problem:
1. I backed up the database
2. i've made the delete in small batches
3. This table only has a clustered index on an PK Column,
i have performed a DBREINDEX on that Pk clustered index.
Results:
name
C29___BINARIES
rows reserved data index_size unused
221392 4902000 KB 2724296 KB 112 KB 2177592 KB
How can i free the unused table space ?? Why the
dbreindex didin't release this space ?
Sorry Can you help me ?
Thanks again
Mike
dbcc updateusage (0) with count_rows
it may actually be freed but not recorded yet.
Mike wrote:

> Hi,
> Thanks for your comments, but now i have a problem:
> 1. I backed up the database
> 2. i've made the delete in small batches
> 3. This table only has a clustered index on an PK Column,
> i have performed a DBREINDEX on that Pk clustered index.
> Results:
> name
> C29___BINARIES
> rows reserved data index_size unused
> 221392 4902000 KB 2724296 KB 112 KB 2177592 KB
> How can i free the unused table space ?? Why the
> dbreindex didin't release this space ?
> Sorry Can you help me ?
> Thanks again
> Mike

Delete of 2.5 GB

Presonally I would set up a job to
1. Backup the database
2. Delete the data
3. CLEANDB
4. DBREINDEX
5. ShrinkFile
The major inpact will be on any batch jobs happening on
that table at the same time i.e it may not be using it but
it may be linked to the process, which could cause locking.
J

>--Original Message--
>Hi,
>I have to perform a delete on a table. About 73.000
>records that actually need 2.5 GB of storage. I simply
>will perform a delete from table where .... Should i
>consider doing this after hours ? What impact should i
>consider for this action ? this is a 24x7 DB Server.
>I'm planning the delete and after do a dbcc clean table
to
>organize the table and index.
>Any suggestions or recommendations ?
>Thanks in advance
>Mike
>.
>
I agree with Julie on some points but not all. Always backup the db first.
I would do the deletes in smaller batches and you might need to backup the
tran log in between. If the rows take up 2.5GB of space then you will have
at least that much in the tran log. DBCC CLEANTABLE may not buy you
anything here since you aren't dropping any columns. I would do a DBREINDEX
but DO NOT issue a Shrinkfile unless you absolutely need that space. A
Shrinkfile will most likely undo all you just accomplished with the
DBREINDEX.
Andrew J. Kelly
SQL Server MVP
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:12c0d01c4433e$ffa93890$a301280a@.phx.gbl...[vbcol=seagreen]
> Presonally I would set up a job to
> 1. Backup the database
> 2. Delete the data
> 3. CLEANDB
> 4. DBREINDEX
> 5. ShrinkFile
> The major inpact will be on any batch jobs happening on
> that table at the same time i.e it may not be using it but
> it may be linked to the process, which could cause locking.
> J
>
> to
|||Agreed, you only need to do the shrink file if want to
want to get your disk space back.
Totally agree with cleantable.
Deletion not too sure I agree, personally prefer to get
it al over at once.
Shrinkfile affecting DBREINDEX, i didn't know that
thanks, but wouldn't it just squeeze the pages together
rather than changing the order ?
J

>--Original Message--
>I agree with Julie on some points but not all. Always
backup the db first.
>I would do the deletes in smaller batches and you might
need to backup the
>tran log in between. If the rows take up 2.5GB of space
then you will have
>at least that much in the tran log. DBCC CLEANTABLE may
not buy you
>anything here since you aren't dropping any columns. I
would do a DBREINDEX
>but DO NOT issue a Shrinkfile unless you absolutely need
that space. A
>Shrinkfile will most likely undo all you just
accomplished with the
>DBREINDEX.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:12c0d01c4433e$ffa93890$a301280a@.phx.gbl...
but[vbcol=seagreen]
locking.[vbcol=seagreen]
table
>
>.
>
|||Julie wrote:

> Shrinkfile affecting DBREINDEX, i didn't know that
> thanks, but wouldn't it just squeeze the pages together
> rather than changing the order ?
it changes the page order dramatically. in fact, it's sort of a goofy
situation. you shrink the db to get all the data to the front of the
file. after you shrink it, it's most likely very heavily fragmented.
so then you defrag it. when you defrag it, it moves pages out into the
free space in the db file(s), so now your pages are no longer all at the
front of the file.
|||Thanks ch,
I never knew that.
J

>--Original Message--
>Julie wrote:
>
>it changes the page order dramatically. in fact, it's
sort of a goofy
>situation. you shrink the db to get all the data to the
front of the
>file. after you shrink it, it's most likely very
heavily fragmented.
>so then you defrag it. when you defrag it, it moves
pages out into the
>free space in the db file(s), so now your pages are no
longer all at the
>front of the file.
>.
>

Delete of 2.5 GB

Hi,
Thanks for your comments, but now i have a problem:
1. I backed up the database
2. i've made the delete in small batches
3. This table only has a clustered index on an PK Column,
i have performed a DBREINDEX on that Pk clustered index.
Results:
name
C29___BINARIES
rows reserved data index_size unused
221392 4902000 KB 2724296 KB 112 KB 2177592 KB
How can i free the unused table space '? Why the
dbreindex didin't release this space '
Sorry Can you help me ?
Thanks again
Mikedbcc updateusage (0) with count_rows
it may actually be freed but not recorded yet.
Mike wrote:

> Hi,
> Thanks for your comments, but now i have a problem:
> 1. I backed up the database
> 2. i've made the delete in small batches
> 3. This table only has a clustered index on an PK Column,
> i have performed a DBREINDEX on that Pk clustered index.
> Results:
> name
> C29___BINARIES
> rows reserved data index_size unused
> 221392 4902000 KB 2724296 KB 112 KB 2177592 KB
> How can i free the unused table space '? Why the
> dbreindex didin't release this space '
> Sorry Can you help me ?
> Thanks again
> Mike