[dba-SQLServer] log file size

Asger Blond ab-mi at post3.tele.dk
Fri Oct 5 18:31:16 CDT 2012


Then the problem seems to narrows down to the way JC is inserting the
rows. To prevent excessive logging and execution time I would suggest
executing this statement before the insert:ALTER TABLE <tablename>
NOCHECK CONSTRAINT ALLAnd after the insert use:ALTER TABLE <tablename>
CHECK CONSTRAINT ALL
Asger
----- Original meddelelse -----

> Fra: Francisco Tapia <fhtapia at gmail.com>
> Til: Discussion concerning MS SQL Server
> <dba-sqlserver at databaseadvisors.com>
> Dato: Lør, 06. okt 2012 00:53
> Emne: Re: [dba-SQLServer] log file size
> 
> excellent point... I didn't remember he was all SSD'ed out... in that
> case
> simply defragging the indexes should suffice, but if that is not
> important
> just peforming a db shrinkfile will be enough and he can save all the
> overhead of droping data into a new container...
> 
> doing an defrag on SSD's isn't good practice, but I have noticed that
> index
> defragging on these volumes still does yield improved performance, we
> use a
> NetApp SAN, which is really a hybrid SSD w/ spining disk array,
> generally a
> SAN handles all defragmentation internally and away from the OS, but
> as I
> stated index defragging is huge in keeping performance.
> 
> -Francisco
> --------------------------
> You should follow me on twitter here <http://twitter.com/seecoolguy>
> Blogs: SqlThis! <http://bit.ly/sqlthis> |
> XCodeThis!<http://bit.ly/xcodethis>
> 
> <http://db.tt/JeXURAx>
> 
> 
> 
> 
> On Fri, Oct 5, 2012 at 3:06 PM, Stuart McLachlan
> <stuart at lexacorp.com.pg>wrote:
> 
> > JC says he is using 900GB o\f SSD
> >
> > In which case, the whole concept of fragmentation is immaterail.
> >
> > He shouldn't even think doing an OS defrag.
> >
> >
> > --
> > Stuart
> >
> > On 5 Oct 2012 at 11:51, Francisco Tapia wrote:
> >
> > > John,
> > > While performing a shrinkfile can cause OS file level
> fragmentation,
> > > unless you are pre-growing your database files, you've already
> causing
> > file
> > > fragmentation, everytime Windows creates a new file, it's NTFS
> system
> > will
> > > be super efficient and begin writing to all available blocks,
> even when
> > > they are not contiguous... it's the inherent nature of the
> > filesystem...in
> > > order to avoid fragmentation at all cost, you will want to
> perform an OS
> > > level defragmentation prior to creating any new large file on the
> OS in
> > > windows.
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com



More information about the dba-SQLServer mailing list