Database size, cutting it down shrink reindex

Our database seems to be getting to big too fast.
Since January our database has grown to almost  600 mb, and it grows about 
10-20 mb per day.
I have decided to delete some old journal entries , old Quicksell journals .
I have done this multiple times on test machine and was getting different 
database /backup sizes depending on order of the steps.

This seems to be the best way but makes me concern about the database.
I started with a backup file with size of 569,747
I restored it to ne database on test machine 

 i ran SQL query 

DELETE Journal
WHERE time < getdate()-15    this deletes all register journals older than 
15 days
than i ran 

run delete oldjournal              delete old qs2000 journals
i backed up the database and the backup file was cut down to 346,963
than a run 
dbcc shrinkdatabase  and reindex 
the backup size went down to 286, 420
ran dbcc shrinkdatabase  and reindex  again 
backup went 252, 307
ran it two more times and it went to 216, 083

This makes me wonder if any other data get lost while running shring and 
reindex.

As far as i know shink and reindex do not remove any data so why does the 
database file keeps getting smaller each time i run those commands?

Has anyone ran into this problem ?
Should i be concerned about running shrink and reindex ?

Is there a better way to manage database size ? 
Thanks






0
Robert4087 (350)
8/21/2008 5:30:01 AM
pos 14173 articles. 0 followers. Follow

5 Replies
754 Views

Similar Articles

[PageSpeed] 21

This is a multi-part message in MIME format.

------=_NextPart_000_0031_01C903A3.FB386720
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

Robert,=20

Understand that the database grows in chunks.  When you created the =
database in SO Admin, you were asked how to increase in size when =
needed, by a percentage (10%) or by a fixed size (in MB).  If you went =
by defaults, it increases by the percentage.

Each time the database needs to grow, its by 10% (if you accepted the =
defaults).  This does not mean that data now exists in the entire =
database it just means that the database added extra space to be filled =
with data, kind of a buffer.

When you do the shrink, it may decrease the size of the data and trims =
the empty space, so the database shrinks back to its previous level. =
Remember that 10% rule

Also you didn't mention if you are running MSDE (RMS 1.3 and below) or =
SQL2k5 Express (1.3R and above).  MSDE has a size limit of 2 GB whereas =
SQL2k5 has a 4 GB limit.  If you need more, the are 4 additional =
versions of SQL without size limits, none are free though.


--=20

Jeff=20
Check Point Software

Search this newsgroup -- http://tinyurl.com/2lmk4w  (Stolen from DRS)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D

You must be using Outlook Express/Windows Mail or some other type of =
newsgroup reader to
see and download the file attachment(s).  If you are not using a reader, =
follow
the link below to setup Outlook Express.  Click on "Open with =
newsreader"
under the MS Retail Management System on the right.

http://tinyurl.com/75bgz
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D


  "Robert" <Robert@discussions.microsoft.com> wrote in message =
news:10FBF7B8-99EC-4277-9D4E-D05C256DD024@microsoft.com...
  Our database seems to be getting to big too fast.
  Since January our database has grown to almost  600 mb, and it grows =
about=20
  10-20 mb per day.
  I have decided to delete some old journal entries , old Quicksell =
journals .
  I have done this multiple times on test machine and was getting =
different=20
  database /backup sizes depending on order of the steps.

  This seems to be the best way but makes me concern about the database.
  I started with a backup file with size of 569,747
  I restored it to ne database on test machine=20

   i ran SQL query=20

  DELETE Journal
  WHERE time < getdate()-15    this deletes all register journals older =
than=20
  15 days
  than i ran=20

  run delete oldjournal              delete old qs2000 journals
  i backed up the database and the backup file was cut down to 346,963
  than a run=20
  dbcc shrinkdatabase  and reindex=20
  the backup size went down to 286, 420
  ran dbcc shrinkdatabase  and reindex  again=20
  backup went 252, 307
  ran it two more times and it went to 216, 083

  This makes me wonder if any other data get lost while running shring =
and=20
  reindex.

  As far as i know shink and reindex do not remove any data so why does =
the=20
  database file keeps getting smaller each time i run those commands?

  Has anyone ran into this problem ?
  Should i be concerned about running shrink and reindex ?

  Is there a better way to manage database size ?=20
  Thanks






------=_NextPart_000_0031_01C903A3.FB386720
Content-Type: text/html;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3Dtext/html;charset=3Dutf-8>
<META content=3D"MSHTML 6.00.6001.18099" name=3DGENERATOR></HEAD>
<BODY id=3DMailContainerBody=20
style=3D"PADDING-RIGHT: 10px; PADDING-LEFT: 10px; PADDING-TOP: 15px"=20
bgColor=3D#ffffff leftMargin=3D0 topMargin=3D0 CanvasTabStop=3D"true"=20
name=3D"Compose message area">
<DIV><FONT color=3D#008000>Robert, </FONT></DIV>
<DIV><FONT color=3D#008000></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#008000>Understand that the database grows in =
chunks.&nbsp;=20
When you created the database in SO Admin, you were asked how =
to&nbsp;increase=20
in size when needed, by a percentage (10%) or by a fixed size (in =
MB).&nbsp; If=20
you went by defaults, it increases by the percentage.</FONT></DIV>
<DIV><FONT color=3D#008000></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#008000>Each time the database needs to grow, its by =
10% (if=20
you accepted the defaults).&nbsp; This does not mean that data now =
exists in the=20
entire database it just means that the database&nbsp;added extra space =
to be=20
filled with data, kind of a buffer.</FONT></DIV>
<DIV><FONT color=3D#008000></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#008000>When you do the shrink, it may decrease the =
size of the=20
data and trims the empty space, so the database shrinks back to its =
previous=20
level. Remember that 10% rule</FONT></DIV>
<DIV><FONT color=3D#008000></FONT><FONT color=3D#008000></FONT><FONT=20
color=3D#008000></FONT>&nbsp;</DIV>
<DIV><FONT color=3D#008000>Also you didn't mention if you are running =
MSDE (RMS=20
1.3 and below) or SQL2k5 Express (1.3R and above).&nbsp; MSDE has a size =
limit=20
of 2 GB whereas SQL2k5 has a 4 GB limit.&nbsp; If you need more, the are =
4=20
additional versions of SQL without size limits, none are free=20
though.</FONT></DIV>
<DIV><FONT color=3D#008000></FONT>&nbsp;</DIV>
<DIV><BR>-- <BR><BR>Jeff <BR>Check Point Software</DIV>
<DIV><FONT color=3D#008000></FONT>&nbsp;</DIV>
<DIV>Search this newsgroup -- <A=20
href=3D"http://tinyurl.com/2lmk4w">http://tinyurl.com/2lmk4w</A>&nbsp; =
(Stolen=20
from =
DRS)<BR>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D</DIV>
<DIV>&nbsp;</DIV>
<DIV>You must be using Outlook Express/Windows Mail or some other type =
of=20
newsgroup reader to<BR>see and download the file attachment(s).&nbsp; If =
you are=20
not using a reader, follow<BR>the link below to setup Outlook =
Express.&nbsp;=20
Click on "Open with newsreader"<BR>under the MS Retail Management System =
on the=20
right.</DIV>
<DIV>&nbsp;</DIV>
<DIV><A=20
href=3D"http://tinyurl.com/75bgz">http://tinyurl.com/75bgz</A><BR>=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
</DIV>
<DIV><FONT color=3D#008000></FONT>&nbsp;</DIV>
<DIV>&nbsp;</DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #008000 2px solid; MARGIN-RIGHT: 0px">
  <DIV>"Robert" &lt;<A=20
  =
href=3D"mailto:Robert@discussions.microsoft.com">Robert@discussions.micro=
soft.com</A>&gt;=20
  wrote in message <A=20
  =
href=3D"news:10FBF7B8-99EC-4277-9D4E-D05C256DD024@microsoft.com">news:10F=
BF7B8-99EC-4277-9D4E-D05C256DD024@microsoft.com</A>...</DIV>Our=20
  database seems to be getting to big too fast.<BR>Since January our =
database=20
  has grown to almost&nbsp; 600 mb, and it grows about <BR>10-20 mb per=20
  day.<BR>I have decided to delete some old journal entries , old =
Quicksell=20
  journals .<BR>I have done this multiple times on test machine and was =
getting=20
  different <BR>database /backup sizes depending on order of the=20
  steps.<BR><BR>This seems to be the best way but makes me concern about =
the=20
  database.<BR>I started with a backup file with size of 569,747<BR>I =
restored=20
  it to ne database on test machine <BR><BR>&nbsp;i ran SQL query =
<BR><BR>DELETE=20
  Journal<BR>WHERE time &lt; getdate()-15&nbsp;&nbsp;&nbsp; this deletes =
all=20
  register journals older than <BR>15 days<BR>than i ran <BR><BR>run =
delete=20
  =
oldjournal&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;=20
  delete old qs2000 journals<BR>i backed up the database and the backup =
file was=20
  cut down to 346,963<BR>than a run <BR>dbcc shrinkdatabase&nbsp; and =
reindex=20
  <BR>the backup size went down to 286, 420<BR>ran dbcc =
shrinkdatabase&nbsp; and=20
  reindex&nbsp; again <BR>backup went 252, 307<BR>ran it two more times =
and it=20
  went to 216, 083<BR><BR>This makes me wonder if any other data get =
lost while=20
  running shring and <BR>reindex.<BR><BR>As far as i know shink and =
reindex do=20
  not remove any data so why does the <BR>database file keeps getting =
smaller=20
  each time i run those commands?<BR><BR>Has anyone ran into this =
problem=20
  ?<BR>Should i be concerned about running shrink and reindex =
?<BR><BR>Is there=20
  a better way to manage database size ?=20
<BR>Thanks<BR><BR><BR><BR><BR><BR></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_0031_01C903A3.FB386720--

0
cptsoft (1248)
8/21/2008 10:38:38 PM
Hello
thanks for your reply
I'm running RMS 2.0.115


I understand how the database grows and that not all of the space is used by 
data.

What i do not understand is why when i reindex and shrink the database 
multiple  times it keeps getting smaller.




"Jeff @ Check Point Software" wrote:

> Robert, 
> 
> Understand that the database grows in chunks.  When you created the database in SO Admin, you were asked how to increase in size when needed, by a percentage (10%) or by a fixed size (in MB).  If you went by defaults, it increases by the percentage.
> 
> Each time the database needs to grow, its by 10% (if you accepted the defaults).  This does not mean that data now exists in the entire database it just means that the database added extra space to be filled with data, kind of a buffer.
> 
> When you do the shrink, it may decrease the size of the data and trims the empty space, so the database shrinks back to its previous level. Remember that 10% rule
> 
> Also you didn't mention if you are running MSDE (RMS 1.3 and below) or SQL2k5 Express (1.3R and above).  MSDE has a size limit of 2 GB whereas SQL2k5 has a 4 GB limit.  If you need more, the are 4 additional versions of SQL without size limits, none are free though.
> 
> 
> -- 
> 
> Jeff 
> Check Point Software
> 
> Search this newsgroup -- http://tinyurl.com/2lmk4w  (Stolen from DRS)
> =====================================================
> 
> You must be using Outlook Express/Windows Mail or some other type of newsgroup reader to
> see and download the file attachment(s).  If you are not using a reader, follow
> the link below to setup Outlook Express.  Click on "Open with newsreader"
> under the MS Retail Management System on the right.
> 
> http://tinyurl.com/75bgz
> =====================================================
> 
> 
>   "Robert" <Robert@discussions.microsoft.com> wrote in message news:10FBF7B8-99EC-4277-9D4E-D05C256DD024@microsoft.com...
>   Our database seems to be getting to big too fast.
>   Since January our database has grown to almost  600 mb, and it grows about 
>   10-20 mb per day.
>   I have decided to delete some old journal entries , old Quicksell journals .
>   I have done this multiple times on test machine and was getting different 
>   database /backup sizes depending on order of the steps.
> 
>   This seems to be the best way but makes me concern about the database.
>   I started with a backup file with size of 569,747
>   I restored it to ne database on test machine 
> 
>    i ran SQL query 
> 
>   DELETE Journal
>   WHERE time < getdate()-15    this deletes all register journals older than 
>   15 days
>   than i ran 
> 
>   run delete oldjournal              delete old qs2000 journals
>   i backed up the database and the backup file was cut down to 346,963
>   than a run 
>   dbcc shrinkdatabase  and reindex 
>   the backup size went down to 286, 420
>   ran dbcc shrinkdatabase  and reindex  again 
>   backup went 252, 307
>   ran it two more times and it went to 216, 083
> 
>   This makes me wonder if any other data get lost while running shring and 
>   reindex.
> 
>   As far as i know shink and reindex do not remove any data so why does the 
>   database file keeps getting smaller each time i run those commands?
> 
>   Has anyone ran into this problem ?
>   Should i be concerned about running shrink and reindex ?
> 
>   Is there a better way to manage database size ? 
>   Thanks
> 
> 
> 
> 
> 
> 
0
Robert4087 (350)
8/22/2008 3:39:01 PM
Hi Robert,

You may want to know which file is growing ?
It may be the transaction log file. If so -
You may want to check ths database recover model and set the database 
recovery mode to Simple.
Do a backup before anything.

Best regards,

Mihir Shah
Diviasoft, Inc.
www.diviasoft.com


"Robert" <Robert@discussions.microsoft.com> wrote in message 
news:506C4335-D333-456B-8F2B-2F6E3A281CFC@microsoft.com...
>
> Hello
> thanks for your reply
> I'm running RMS 2.0.115
>
>
> I understand how the database grows and that not all of the space is used 
> by
> data.
>
> What i do not understand is why when i reindex and shrink the database
> multiple  times it keeps getting smaller.
>
>
>
>
> "Jeff @ Check Point Software" wrote:
>
>> Robert,
>>
>> Understand that the database grows in chunks.  When you created the 
>> database in SO Admin, you were asked how to increase in size when needed, 
>> by a percentage (10%) or by a fixed size (in MB).  If you went by 
>> defaults, it increases by the percentage.
>>
>> Each time the database needs to grow, its by 10% (if you accepted the 
>> defaults).  This does not mean that data now exists in the entire 
>> database it just means that the database added extra space to be filled 
>> with data, kind of a buffer.
>>
>> When you do the shrink, it may decrease the size of the data and trims 
>> the empty space, so the database shrinks back to its previous level. 
>> Remember that 10% rule
>>
>> Also you didn't mention if you are running MSDE (RMS 1.3 and below) or 
>> SQL2k5 Express (1.3R and above).  MSDE has a size limit of 2 GB whereas 
>> SQL2k5 has a 4 GB limit.  If you need more, the are 4 additional versions 
>> of SQL without size limits, none are free though.
>>
>>
>> -- 
>>
>> Jeff
>> Check Point Software
>>
>> Search this newsgroup -- http://tinyurl.com/2lmk4w  (Stolen from DRS)
>> =====================================================
>>
>> You must be using Outlook Express/Windows Mail or some other type of 
>> newsgroup reader to
>> see and download the file attachment(s).  If you are not using a reader, 
>> follow
>> the link below to setup Outlook Express.  Click on "Open with newsreader"
>> under the MS Retail Management System on the right.
>>
>> http://tinyurl.com/75bgz
>> =====================================================
>>
>>
>>   "Robert" <Robert@discussions.microsoft.com> wrote in message 
>> news:10FBF7B8-99EC-4277-9D4E-D05C256DD024@microsoft.com...
>>   Our database seems to be getting to big too fast.
>>   Since January our database has grown to almost  600 mb, and it grows 
>> about
>>   10-20 mb per day.
>>   I have decided to delete some old journal entries , old Quicksell 
>> journals .
>>   I have done this multiple times on test machine and was getting 
>> different
>>   database /backup sizes depending on order of the steps.
>>
>>   This seems to be the best way but makes me concern about the database.
>>   I started with a backup file with size of 569,747
>>   I restored it to ne database on test machine
>>
>>    i ran SQL query
>>
>>   DELETE Journal
>>   WHERE time < getdate()-15    this deletes all register journals older 
>> than
>>   15 days
>>   than i ran
>>
>>   run delete oldjournal              delete old qs2000 journals
>>   i backed up the database and the backup file was cut down to 346,963
>>   than a run
>>   dbcc shrinkdatabase  and reindex
>>   the backup size went down to 286, 420
>>   ran dbcc shrinkdatabase  and reindex  again
>>   backup went 252, 307
>>   ran it two more times and it went to 216, 083
>>
>>   This makes me wonder if any other data get lost while running shring 
>> and
>>   reindex.
>>
>>   As far as i know shink and reindex do not remove any data so why does 
>> the
>>   database file keeps getting smaller each time i run those commands?
>>
>>   Has anyone ran into this problem ?
>>   Should i be concerned about running shrink and reindex ?
>>
>>   Is there a better way to manage database size ?
>>   Thanks
>>
>>
>>
>>
>>
>> 


0
mihirs (128)
8/22/2008 4:24:05 PM
How can i tell what is gowning ?
What files should i look for

Thanks
Robert


"Mihir Shah" wrote:

> Hi Robert,
> 
> You may want to know which file is growing ?
> It may be the transaction log file. If so -
> You may want to check ths database recover model and set the database 
> recovery mode to Simple.
> Do a backup before anything.
> 
> Best regards,
> 
> Mihir Shah
> Diviasoft, Inc.
> www.diviasoft.com
> 
> 
> "Robert" <Robert@discussions.microsoft.com> wrote in message 
> news:506C4335-D333-456B-8F2B-2F6E3A281CFC@microsoft.com...
> >
> > Hello
> > thanks for your reply
> > I'm running RMS 2.0.115
> >
> >
> > I understand how the database grows and that not all of the space is used 
> > by
> > data.
> >
> > What i do not understand is why when i reindex and shrink the database
> > multiple  times it keeps getting smaller.
> >
> >
> >
> >
> > "Jeff @ Check Point Software" wrote:
> >
> >> Robert,
> >>
> >> Understand that the database grows in chunks.  When you created the 
> >> database in SO Admin, you were asked how to increase in size when needed, 
> >> by a percentage (10%) or by a fixed size (in MB).  If you went by 
> >> defaults, it increases by the percentage.
> >>
> >> Each time the database needs to grow, its by 10% (if you accepted the 
> >> defaults).  This does not mean that data now exists in the entire 
> >> database it just means that the database added extra space to be filled 
> >> with data, kind of a buffer.
> >>
> >> When you do the shrink, it may decrease the size of the data and trims 
> >> the empty space, so the database shrinks back to its previous level. 
> >> Remember that 10% rule
> >>
> >> Also you didn't mention if you are running MSDE (RMS 1.3 and below) or 
> >> SQL2k5 Express (1.3R and above).  MSDE has a size limit of 2 GB whereas 
> >> SQL2k5 has a 4 GB limit.  If you need more, the are 4 additional versions 
> >> of SQL without size limits, none are free though.
> >>
> >>
> >> -- 
> >>
> >> Jeff
> >> Check Point Software
> >>
> >> Search this newsgroup -- http://tinyurl.com/2lmk4w  (Stolen from DRS)
> >> =====================================================
> >>
> >> You must be using Outlook Express/Windows Mail or some other type of 
> >> newsgroup reader to
> >> see and download the file attachment(s).  If you are not using a reader, 
> >> follow
> >> the link below to setup Outlook Express.  Click on "Open with newsreader"
> >> under the MS Retail Management System on the right.
> >>
> >> http://tinyurl.com/75bgz
> >> =====================================================
> >>
> >>
> >>   "Robert" <Robert@discussions.microsoft.com> wrote in message 
> >> news:10FBF7B8-99EC-4277-9D4E-D05C256DD024@microsoft.com...
> >>   Our database seems to be getting to big too fast.
> >>   Since January our database has grown to almost  600 mb, and it grows 
> >> about
> >>   10-20 mb per day.
> >>   I have decided to delete some old journal entries , old Quicksell 
> >> journals .
> >>   I have done this multiple times on test machine and was getting 
> >> different
> >>   database /backup sizes depending on order of the steps.
> >>
> >>   This seems to be the best way but makes me concern about the database.
> >>   I started with a backup file with size of 569,747
> >>   I restored it to ne database on test machine
> >>
> >>    i ran SQL query
> >>
> >>   DELETE Journal
> >>   WHERE time < getdate()-15    this deletes all register journals older 
> >> than
> >>   15 days
> >>   than i ran
> >>
> >>   run delete oldjournal              delete old qs2000 journals
> >>   i backed up the database and the backup file was cut down to 346,963
> >>   than a run
> >>   dbcc shrinkdatabase  and reindex
> >>   the backup size went down to 286, 420
> >>   ran dbcc shrinkdatabase  and reindex  again
> >>   backup went 252, 307
> >>   ran it two more times and it went to 216, 083
> >>
> >>   This makes me wonder if any other data get lost while running shring 
> >> and
> >>   reindex.
> >>
> >>   As far as i know shink and reindex do not remove any data so why does 
> >> the
> >>   database file keeps getting smaller each time i run those commands?
> >>
> >>   Has anyone ran into this problem ?
> >>   Should i be concerned about running shrink and reindex ?
> >>
> >>   Is there a better way to manage database size ?
> >>   Thanks
> >>
> >>
> >>
> >>
> >>
> >> 
> 
> 
> 
0
Robert4087 (350)
8/22/2008 4:38:01 PM
dbcc shrinkdatabase picks a target size and then works to get that ending 
size
In your case the first dbcc shrinkfile was this formula: 286 = (data + (10% 
of 600)) * 600
So your data size was roughly about 226   (actually it was about 200 with 
10% space added to that which is final dbcc shrinkfile size after all 
logfiles adjusted).
The next dbcc shrinkfile resulted in about 254 = (226 + (10% of 286))  * 286
Note: the percentage is not applied on the whole database, but on each 
individual database and logfile so it does not compute out exactly.
We are now getting down close to actual file size  with the next running of 
dbcc shrinkfile.
- Dave

"Robert" <Robert@discussions.microsoft.com> wrote in message 
news:10FBF7B8-99EC-4277-9D4E-D05C256DD024@microsoft.com...
> Our database seems to be getting to big too fast.
> Since January our database has grown to almost  600 mb, and it grows about
> 10-20 mb per day.
> I have decided to delete some old journal entries , old Quicksell journals 
> .
> I have done this multiple times on test machine and was getting different
> database /backup sizes depending on order of the steps.
>
> This seems to be the best way but makes me concern about the database.
> I started with a backup file with size of 569,747
> I restored it to ne database on test machine
>
> i ran SQL query
>
> DELETE Journal
> WHERE time < getdate()-15    this deletes all register journals older than
> 15 days
> than i ran
>
> run delete oldjournal              delete old qs2000 journals
> i backed up the database and the backup file was cut down to 346,963
> than a run
> dbcc shrinkdatabase  and reindex
> the backup size went down to 286, 420
> ran dbcc shrinkdatabase  and reindex  again
> backup went 252, 307
> ran it two more times and it went to 216, 083
>
> This makes me wonder if any other data get lost while running shring and
> reindex.
>
> As far as i know shink and reindex do not remove any data so why does the
> database file keeps getting smaller each time i run those commands?
>
> Has anyone ran into this problem ?
> Should i be concerned about running shrink and reindex ?
>
> Is there a better way to manage database size ?
> Thanks
>
>
>
>
>
> 

0
dc9902 (13)
8/25/2008 12:26:06 PM
Reply:

Similar Artilces:

Query to text file cuts my leading zeros off.
Hello, this is my third attempt to post this question. I have a query that has a value of "012810" in one of the fields. When I run the query it shows up as 012810. When I export to excell it shows up as 012810. When I export to text file it always cuts off the zero and I get 12810. If I change the value to say 212810 and then export to a text file it works fine and returns 212810. Why is the zero being chopped off during a text file export? Thank you, VADIMBAR Its being treated as a numeric value. Is this column defined as numeric or text? It needs to be defin...

Copying access 2002 to create access 2000 database
I have Access 2002 with a database I created for a friend. My friend has Access 2000 at work. I need to make a copy of the database and convert it to Access 2000. Then I will send it via Internet and have it copied to the PC at work. What is the procedure to convert a copy of an Access 2002 database to Access 2000? Thanks It's entirely possible that it's already in Access 2000 format: that's actually the default format for Access 2002. Look under Tools | Database Utilties | Convert Database. If it's already in Access 2000 format, the option to convert to 2000 will be...

Links Causing File Size Jump
It seems that when I paste links the file size jumps a lot. The sheets are laid out like this - all the sheets have "lookup" formulas which reference a master sheet. There are no macros or anything unusual. I wanted to create an easy print sheet which references all of the other sheets (except the master sheet). So I created a sheet, copied the format of the others and pasted a link to them. The file size jumps immediately. All I need is a print sheet, which makes it easy to print 25 other sheets at once. Any ideas? Using CTRL + Click or SHIFT + Click, select...

Compact database
We are a gift shop with many, many new items each year and only a few reorders. Thus, we have lots of item codes that are not being used. Each year we try to clean up the items database and delete those item codes that have not been used for over 2 years and for which the Qty=0. I have just completed deleting the items no longer in use. Our database is huge. Is there a way to compact the database after deleting these items no longer in use each year? We transferred from QuickSell 2000 and there was a way to compact the database in the Doctor program. ...

How to prevent Cut&Paste to change style and data validation on protected sheet
Hi I would like to prevent that pasting to unlocked cells on a protected sheet changes the data validation and style. Is this possible? Thanks, PC Did you try going to 'Tools' choose 'Options' then choose the 'Edit' tab? then unclick what you don't want anyone to be able to do on your spreadsheet. Hope this helps "Paulo Costa (Live)" wrote: > Hi > > I would like to prevent that pasting to unlocked cells on a protected sheet > changes the data validation and style. Is this possible? > > Thanks, > PC &g...

how do I set outlook to open full screen size?
I have outlook set to open when my computer opens, but it starts in a small window, and I want it to opwn full screen size. What do I have to do? Right-click on the Outlook icon because assuming you've dropped the Outlook icon in the Start folder, it's a shortcut. Scroll to properties. In the run box, use the drop down menu to choose Maximized. Click OK. "Aileen Hewat" <AileenHewat@discussions.microsoft.com> wrote in message news:E8DA7589-8240-409D-8F71-D2C781CA8A09@microsoft.com... > I have outlook set to open when my computer opens, but it starts in a ...

Increase Size Of Auto Fliter Drop Down Box
Good evening all, I am looking for some code so that I can increase the Depth of the auto -fliter drop down box, ie to increase the number of entries visible, but without changing the font size. Having searched Google, there are many reccomendations for increasing the apparent size of the font ( by using zoom), but cannot find anything to help with increasing the size of the box itself. I have found some very useful code to increase the size (width) of the Name Box, and would appreciate some help to similarly increase the depth of the Auot filter drop down box. I would be grateful for any ...

Exchange 2003 Message size limits not working
Hi, we have 2 Exchange 2003 SP1 Cluster for Mailboxes and 2 Bridgehead Server for SMTP transfer. I have to grow up the maximum message size limit for attachments to 20 MB / 20480 KB. I set the limits described in knowledge base article 322679. In detail : Global setting : send & recive : 50000 kB SMTP Connector setting : 20480 kB SMTP virtual server setting : SMTP bridgehead server : 20480 kB mailbox cluster server : 50000 kB User mailbox setting : unlimited I send a 17 MB pdf file from a user on the mailbox cluster and I get an error: <xxxxxxx #5.2.3 smtp;450 5.2.3 Msg Size ...

How to reduce excel file size ?
I 'm working with many photo inside excel file, my problem now the file size become to big and difficult to edit. How to make file become smaller without affecting the photo quality? Pls help I would not store pictures in Excel if there were more than ten. better to call them up from file. Debra Dalgliesh shows how to insert pictures into a comment using VB at: http://www.contextures.com/xlcomments03.html#Picture HTH Peter "geniusideas" wrote: > I 'm working with many photo inside excel file, my problem now the > file size become to big and difficult to edit. Ho...

How can I change the current default size for formatting a picture
When I paste a screen print the format size automatically defaults to 4.5" height. How can I change this to default to another size. Ordinarily, Word will restrain the size of an inline object to margin width. If the screen shot is taken at a 3x4 ratio, and your margin width is the default 6", then the height is going to be 4.5". If you paste into a landscape page, it will be larger. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Curious George" <Curious George@discussions.microsoft.com&...

Printing database model
Hi All I am using VISIO 2007 and have created a database model and would like to print it out with table properties, notes, column properties, information etc. However when I print I see only a picture of the tables and nothing about the detailed database properties that I spent so much time entering. Also how do I build SQL/ make a database with the model I created here? Is this tool not capable of doing this basic stuff? Very frustrated! TIA Hi Arun, You must have the Visio for enterprise architects version for generate a database from you visio drawing and for having more repor...

Access Database 04-26-07
I have a database on a server for our entire team to make entries. We have a problem that has arisen in the past couple of weeks on the input form. Only several people on the team encounter the problem. When these people open the input form, the date block (which should automatically contain the current date) contains "Name#" in lieu of the date. Once the individual overwrites the error with the date and moves to another record, the input form functions correctly. I am at a loss as to why this happens and why it only happens to several people and not everyone. You can ...

Publisher 2007 and PDF size problems
I created a booklet in Publisher 2007 and save as a PDF. I then put it online for view. It works well, but with one problem They always open with a magnification of 200% Can this be changed to a lower magnification without needing to buy Acrobat? Or is the setting in Reader? Thanks. cuig wrote: > They always open with a magnification of 200% > Or is the setting in Reader? I think that it is a Reader setting rather than a setting saved in the document. Although I might be wrong. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org The setting is in the Reader. -- Mary...

Exchange Standard 16gb size limit monitoring
I hit the 16gb limit the other day in my Exchange Standard installation. I figured out how to fix the problem (dismount and use ESutil), but I don't want to hit that size limitation again. Is there a free script or tool out there to notify me when the size hits 15gb? Where can I find out the size of the Storage group? "JohnS" <JohnSPAM@hotmail.com> wrote in message news:ud07%23WkjFHA.3300@TK2MSFTNGP15.phx.gbl... >I hit the 16gb limit the other day in my Exchange Standard installation. I >figured out how to fix the problem (dismount and use ESutil), but ...

Ex5.5 IS
Hello! I have a problem with an Exchange 5.5 server: The Information Store stopped with the logged event 1160, Source: Exchange IS, Database resource failure error Out of memory occurred in function JTAB_BASE::EcCreateIndex while accessing the database. It starts with that when users sort their mail in an folder, they get invisible... Users also use PGP... I noticed that there are two KB articles that describe this event. But according to these articles this is only a problem with earlier SPs but we use SP4 on that server. The version of store.exe is: 5.5.2657.74 and from Perfiz.ex...

Database Size Not Reducing
I ran mailbox management and removed about 4GB of data from my information store but my file size seems to remain the same even days later. Is there a way to force an online defrag because it must not be happening and I am a little worried that since I only have 1.8GB left on that drive it is going to continue to fill up. Will it just overwrite the newly created white space (if thats the right term), or will it continute to write to the end of the database? Thank you "Ketta" <no@post.net> wrote: >I ran mailbox management and removed about 4GB of data from my informati...

ListBox Size and Screen Resolution
Hi! I am having difficulty with an ActiveX listbox on a worksheet and differing screen resolutions. The listbox keeps changing sizes depending on the screen resolution. For example, I created a new workbook while in 1440x900 mode, added a listbox control, and saved the workbook and closed Excel. Next, I changed to 1920x1200 and reopened the workbook. Now, clicking in and out of "Design Mode" causes the font size in the box to continually get bigger until all that is left is a black box where the listbox was. Next, I saved the workbook, changed back to 1440x90...

Cell Contents Cut Off in Print and Print Preview
We are using Excel 2002 version 10.6501.6626 on Windows 2000 and XP. We have a spreadsheet where the longest vertical cell in each row when printing (and using print preview) has the bottom cut off by the line separarating the cells on some PC's. The problem has nothing to do with operating system (we use both XP and 2000 and have seen the problem on both), print drivers or user settings. Spreadsheet prints or does not print correctly on a particular pc regardless of user logged in. Excel build is same on working and non-working pc's. I have seen two workarounds mentioned on f...

"Edit" feature in Issues Database for Access 2003
Under Search Issues tab when a individual conducts a search and returns the choices, there is an "edit" feature that allows the individual to click on "edit", then they are able to "edit" the entry. What I am trying to do is change that "edit" to just "open" the entry and view it. The only place the "edit" field appears is in the Browse Issues Table under forms, and it's a subform. Could someone please help. Thank you in advance. "Rosebud" <Rosebud@discussions.microsoft.com> wrote in message news:DB0B...

database problem.
I create a mfc dialog programe in vs.net 2003 use vc++ . In the dialog I add a datagrid(sp4,com) and a adodc(sp5,com) but when I close the dialog ,it give me a Error,why?(I had set datagrid's datafield) in VC 6.0(sp5),Everything is OK!why,who can give me a answer? "lxg" <lixngang@163.com> wrote in message news:OP5yi4W6DHA.2748@TK2MSFTNGP09.phx.gbl... > I create a mfc dialog programe in vs.net 2003 use vc++ . > In the dialog I add a datagrid(sp4,com) and a adodc(sp5,com) but when I > close the dialog ,it give me a Error,why?(I had set datagrid's datafield) >...

importing reports from another database
I am importing reports from one database to another. After importing, I have to change the report source to the correct query name and the field names to match the correct query--This seems to work fine except-- I have an expression in this report that will not change. When I go into the expression builder, the fields from the new query are not there. It contains the names of the fields from the report from which it was imported. Am I going about this the wrong way? Do I need to create this report from scratch? Or, is there something else I should be changing.-- Ronnie...

sql 2005 local database lost without ethernet
I'm running rms 1.3 with sql 2005. If my router reboots or if I unplug my ethernet cable I get kicked out of POS or Manager and receive a database connection lost error message. I can understand this on a remote machine but why on a local database machine. I wouldn't think the local database would need an ethernet connection...i'm assuming I have something wrong in SQL...is there an sql 2005 database implementation guide for rms 1.3...the version I have only covers msde 2000...any help would be appreciated David - this is normal for the RMS application, even though you ar...

What is the maximum size of an Outlook 2003 pst.
What is the maximum size of an Outlook 2003 pst. TIA Mario Castillo According to MS there is not a limit. The new file format (Unicode) does not have the same limitations that ANSI .PST's had -- ~jason k please reply in group. emails directly to me will be printed and laughed at in the breakroom "Mario Castillo" <mcastillo@nospam.com> wrote in message news:uLGzI28zDHA.1744@TK2MSFTNGP12.phx.gbl... > What is the maximum size of an Outlook 2003 pst. > > TIA > Mario Castillo > > The theoretical limit with a unicode .pst file is 32 terrabytes but the...

No size limit but user gets an error
The message in her Outlook says "The message being sent exceeds the message size established for this user." but we do not have any limitations set for anyone's mailbox or for the message transfer. Any ideas why this is popping up? I am thinking may be it is trying to pop up another error message such as memory low on the personal computer or something but the Outlook is showing this error message. Is it possible? Thanks. On Wed, 30 Nov 2005 11:31:08 -0800, "h gregorian" <hgregorian@discussions.microsoft.com> wrote: >The message in her Outlook says &...

SW_SHOW does not restore to correct size
I have a window that is already open, I execute a m_cwnd- >ShowWindow(SW_SHOW) and the window activates and then changes to the unmaximized size, so it's not minimized, but it changes from it's maximized size. According to MSDN, SW_SHOW is supposed to activate the window and display it in its current size and position...Any thoughts? "j_eagle" <jason.eagle@gmail.com> wrote in message news:1186831361.063399.235460@x35g2000prf.googlegroups.com... >I have a window that is already open, I execute a m_cwnd- >>ShowWindow(SW_SHOW) and the window activates and ...