[dba-SQLServer] SQL Log file usage

This worked in 2005, so it should work in 2008...


----Diable Index
ALTER INDEX [IX_MyIndexName] ON dbo.TableName DISABLE
GO
----Enable Index
ALTER INDEX [IX_MyIndexName] ON dbo.TableName REBUILD
GO


-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 Thu, Nov 1, 2012 at 4:42 PM, Michael Maddison <
michael at ddisolutions.com.au> wrote:

> Guys,
>
> IIRC you can disable indexes before insert and enable after.
> I don't recall the command but it's there somewhere :-)
>
> Cheers
>
> Michael M
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
> Lawrence
> Sent: Friday, 2 November 2012 6:30 AM
> To: 'Discussion concerning MS SQL Server'
> Subject: Re: [dba-SQLServer] SQL Log file usage
>
> As far as I know, it is always faster, a lot faster, to insert data into
> tables without indexes or keys. (Knew a system that took a week to
> insert all the data but removing the keys first and inserting the data
> took less than 3 hours)
>
> Jim
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, November 01, 2012 11:22 AM
> To: Access Developers discussion and problem solving; Sqlserver-Dba
> Subject: [dba-SQLServer] SQL Log file usage
>
> I attempted to insert 150 million records into a table, from one
> database to another.  The destination table was empty but had a bunch of
> indexes on it.  I ran out of room on the log file disk and the process
> hung.
>
> I ended up stripping off all of the indexes except the PK clustered
> index and the insert used less than 1/2 of the previous log file size
> for the insert.  Furthermore the build of the indexes as a second step
> is essentially not using the log file at all.
>
> So the whole argument about "which is faster" also needs to include a
> component about log file usage.  I have no idea which would have been
> faster - with or without the indexes already in place - but in this case
> at least stripping off the indexes and building them after the fact
> seems to make the whole process work without a (log file size) hitch.
>
> --
> John W. Colby
> Colby Consulting
>
> Reality is what refuses to go away
> when you do not believe in it
>
> _______________________________________________
> 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
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>