Shrinking the files will cause fragmentation at the file level. The
structures inside the database should be unaffected. This is what
SHOWCONTIG reports on. The pages inside the database could be fine
while the files are a mess (or vice versa).
Shrinking is something that should be avoided. But, if you must, then
shrink as infrequently as possible. Never use autoshrink. Never use a
regularly scheduled job (like daily or weekly) to shrink the files.
Both of those will create a huge fragmentation problem within a year
(or less).
File size management often requires some analysis and understanding
of how the database is used. The best physical file structure for
performance would be a single allocation for the data segment and one
for the log. Obviously, that is near impossible to achieve. So, when
growing a file, ensure that you use fewer, larger allocations (like 1
or 2GB or whatever) rather than a huge number of smaller ones (like
50 or 100MB or whatever).
Jay
On Jun 28, 2006, at 6:41 PM, Gordon Rayburn wrote:
> You can check for and deal with internal database fragmentation using
> DBCC commands and rebuilding indexes or defragging them with DBCC
> INDEXDEFRAG commands. If there are high levels of fragmentation
> internally you can gain some space by defragging indexes.
>
> Look up DBCC SHOWCONTIG in BOL.
>
> -----Original Message-----
> From: Frank Lau/IT
> [mailto:mssqldba-ezmlmshield-x2988182.[Email address protected]
> Sent: Wednesday, June 28, 2006 3:37 PM
> To: LazyDBA Discussion
> Subject: Degrading database performance !!!!
>
>
> Hi,
>
> Ok... Let's say to shrink it. However, most DBAs do not recommend
> shrinking the databases because the shrinking operation will
> degrade the
> performance of the databases.
>
>
> How can I shrink the database without degrading the performance??
>
>
>
>
> Frank Lau
>
>
>
> -----Original Message-----
> From: Mordechai Danielov
> [mailto:mssqldba-ezmlmshield-x43862586.[Email address protected]
> Sent: Wednesday, June 28, 2006 3:20 PM
> To: LazyDBA Discussion
> Subject: RE: Reduce the initial sizes !!!
>
> What do you mean by "trying to reduce the initial sizes"... Once these
> data files are created the concept on "initial size" does not apply
> any
> more. If the file has empty space you can shrink it with a "DBCC
> shrinkfile" or "DBCC shrinkdatabase" commands. If it does not, the
> only
> way you will reduce the size if you actually delete data.
>
> -----Original Message-----
> From: Frank Lau/IT
> [mailto:mssqldba-ezmlmshield-x14789821.[Email address protected]
> Sent: Wednesday, June 28, 2006 6:08 PM
> To: LazyDBA Discussion
> Subject: Reduce the initial sizes !!!
>
>
>
> Hi all,
>
> I have been trying to reduce the initial sizes of the database data
> file
> and
> the log file, it does not matter how many times I changed, it
> always go
> back
> to their original sizes before the changes, WHY?
>
>
> Any other ways I can reduce the initial sizes??
>
>
>
> Thanks,
>
>
> Frank Lau
>
>
>
> -----Original Message-----
> From: Darin Tully
> [mailto:mssqldba-ezmlmshield-x41507547.[Email address protected]
> Sent: Wednesday, June 28, 2006 11:09 AM
> To: LazyDBA Discussion
> Subject: RE: Waiting for some answers !!
>
> To know how much space would be required you would need to know how
> many
> rows are being inserted/updated/ etc. How long it row is, how many
> will
> fit
> on a page, how many indexes they are and how much they would grow,
> etc.
> Not
> easy. Capacity planning is a task (and book) onto itself.
>
> Maybe just give it plenty of room and allow it to grow since there
> does
> appear to be a DBA on site.
>
>
> Darin Tully
> Director, Database Technology
> SHOP.COM
> 1 Lower Ragsdale Drive
> Building 1, Suite 210
> Monterey, CA 93940
> 831/647-4797
>
> -----Original Message-----
> From: Frank Lau/IT
> [mailto:mssqldba-ezmlmshield-x59009160.[Email address protected]
> Sent: Wednesday, June 28, 2006 10:50 AM
> To: LazyDBA Discussion
> Subject: Waiting for some answers !!
>
>
>
> Hi,
>
>
> All the hard drives where the data files and the log files located are
> fine,
> the hard drives right now have more than 10 G for each database.
>
> A job associated with two databases was running every 4 hours,
> yesterday
> it
> failed starting from noon time. I used DTS package to run again and
> check
> what error it could be, then the error showed that one of the primary
> filegroup was full.
>
> So I kept increasing up the initial sizes and re-run the job until it
> was
> successful at 6:30pm (after 4 failures)
>
> Then I got into the office today and check the history, the job failed
> three
> more times after the last success, my question is:
>
> 1) Even the error shows the primary filegroup is full, how do I
> know the
> sizes required for the data files?
>
> 2) What are some of the other reasons that may cause this type of
> error?
>
>
>
>
>
>
>
>
>
> Frank Lau
>
>
>
>
>
>
> -----Original Message-----
> From: Harsh Kumar
> [mailto:mssqldba-ezmlmshield-x12347771.[Email address protected]
> Sent: Tuesday, June 27, 2006 11:00 PM
> To: LazyDBA Discussion
> Subject: RE: Urgency: Primary filegroup is FULL !!
>
> Hi Frank,
> Check the space available in drive where database file Available.
>
> Regards,
> Harsh Kumar
> Database Administrator - IT Dept.
> 9213469176
> -----Original Message-----
> From: Frank Lau/IT
> [mailto:mssqldba-ezmlmshield-x70254439.[Email address protected]
> Sent: Tuesday, June 27, 2006 10:56 PM
> To: LazyDBA Discussion
> Subject: Urgency: Primary filegroup is FULL !!
>
>
>
> Urgency!!
>
>
> When one of the jobs failed because the primary filegroup is FULL,
>
> What does it mean the "filegroup is FULL"? The data file size is
> FULL??
>
> What should I do?
>
>
>
>
>
>
>
>
> Frank Lau
>
>
>
>
>
>
> -----Original Message-----
> From: Gordon Rayburn
> [mailto:mssqldba-ezmlmshield-x41457154.[Email address protected]
> Sent: Tuesday, June 27, 2006 6:51 AM
> To: LazyDBA Discussion
> Subject: RE: Urgent for a solution
>
> Make sure you take a full backup after you reset your recovery back to
> FULL.
>
> -----Original Message-----
> From: Michael Phillips
> [mailto:mssqldba-ezmlmshield-x56897612.[Email address protected]
> Sent: Monday, June 26, 2006 6:58 PM
> To: LazyDBA Discussion
> Subject: RE: Urgent for a solution
>
> You probably have recovery mode on the db set to Full and you tried to
> shrink the MDF's, but then all that cool shrinking was logged by
> default. If so you could run the following in Query Analyzer in an
> emergency (backup first). This worked for me.
>
> alter database [YourDBName] set recovery simple
> dbcc shrinkfile (YourDBName_Log)
> checkpoint
> dbcc shrinkfile (YourDBName_Log)
> alter database [YourDBName] set recovery full
>
> Michael
>
>
> -----Original Message-----
> From: Frank Lau/IT
> [mailto:mssqldba-ezmlmshield-x66391863.[Email address protected]
> Sent: Monday, June 26, 2006 6:28 PM
> To: LazyDBA Discussion
> Subject: Urgent for a solution
>
>
>
>
>
> The transaction log of SQL 2005 is growing too fast, anyone can
> provide
> some tips??
>
>
>
>
>
>
> Frank Lau
>
>
>
>
>
> -----Original Message-----
> From: Lucey Christine
> [mailto:mssqldba-ezmlmshield-x30963954.[Email address protected]
> Sent: Monday, June 26, 2006 9:08 AM
> To: LazyDBA Discussion
> Subject: RE: Database shrinking
>
> Hi, Frank. Quick question: Is it actually your database that is
> growing
> too fast, or the database's transaction log?
>
> Another question: What version of SQL are you running here?
>
> --Chris
>
> Christine H. Lucey MCSE MCP+I CCNA
> Assistant Director of Network Services
> The Kingston Hospital
> 396 Broadway, 4th Floor
> Kingston, New York 12401
> Tel. (845) 334-2700, ext. 2406
> [Email address protected]
>
>
> -----Original Message-----
> From: Frank Lau/IT
> [mailto:mssqldba-ezmlmshield-x63035230.[Email address protected]
> Sent: Monday, June 26, 2006 12:04 PM
> To: LazyDBA Discussion
> Subject: Database shrinking
>
>
> Hi all,
>
> When one of the databases is growing too fast within the a hard drive,
> what kind of the solutions do you guys recommend to make sure that
> there
> are always enough spaces for the databases??
>
> I want to shrink the database, do you guys think it is ok to do so?
> What
> is proper procedure to shrink a database and prevent the lost of the
> data???
>
>
>
>
> Thanks,
>
>
> Frank Lau
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
> NOTICE: This transmission may contain confidential health information
> that is protected by special federal or state law or regulation. It is
> intended only for the use of the individual to whom it has been
> addressed. Only the person named in this transmission is authorized to
> view any information contained herein. Re-disclosure without proper
> consent is prohibited.
> Unauthorized use or disclosure or failure to maintain confidentiality
> may subject you to penalties under both federal and state law.
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website :
> http://www.LazyDBA.com To unsubscribe:
> http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ----------------------------------------------------------------------
> --
> ----
> -----------------------
> NOTICE OF CONFIDENTIALITY
> The information contained in this communication and any accompanying
> document(s) is proprietary and confidential and is intended solely for
> the
> above-named individual or entity. If you are not the intended
> receiver,
> recipient or entity, you are advised that any distribution, copying,
> disclosure or communication of this email is strictly prohibited.
> If you
> have received this email in error, please contact me at the telephone
> number
> listed above or 858.716.1500.
> ======================================================================
> ==
> ====
> ==
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> The information contained in this email message may be privileged,
> confidential and protected from disclosure. If you are not the
> intended recipient, any dissemination, distribution or copying is
> strictly prohibited. If you think that you have received this email
> message in error, please notify the sender by reply email and delete
> the message and any attachments.
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
> ----------------------------------------------------------------------
> -----------------------------
> NOTICE OF CONFIDENTIALITY
> The information contained in this communication and any
> accompanying document(s) is proprietary and confidential and is
> intended solely for the above-named individual or entity. If you
> are not the intended receiver, recipient or entity, you are advised
> that any distribution, copying, disclosure or communication of this
> email is strictly prohibited. If you have received this email in
> error, please contact me at the telephone number listed above or
> 858.716.1500.
> ======================================================================
> ========
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
MS Sql Server LazyDBA home page