mibuso.com

Microsoft Business Solutions online community
It is currently Sat May 25, 2013 2:37 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: [Solved] Drop Table?
PostPosted: Wed Oct 26, 2011 9:19 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Fri Jun 11, 2004 9:05 pm
Posts: 4858
Location: Los Angeles, CA
Country: United States (us)
We have a database with a huge amount of data in the Change Log table (60GB). Is there a way to use SQL command to drop the table without having it write to the transaction log?

_________________
Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work


Last edited by Alex Chow on Fri Oct 28, 2011 1:06 am, edited 1 time in total.

Top
 Profile E-mail WWW  
 
 Post subject: Re: Drop Table?
PostPosted: Wed Oct 26, 2011 9:28 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
You could use "TRUNCATE TABLE" instead. It logs much less and also leaves the table intact.

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Drop Table?
PostPosted: Wed Oct 26, 2011 9:30 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
Another option is a temporary switch to Bulk Logged Recovery Method.

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Drop Table?
PostPosted: Wed Oct 26, 2011 9:42 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Fri Feb 03, 2006 10:15 pm
Posts: 2272
Location: Houston, TX
Country: United States (us)
I always do TRUNCATE TABLE for this one. So much faster :D

_________________
Matt Traxinger - ArcherPoint
Microsoft Dynamics NAV 2009 Programming Cookbook
Do you have an idea to improve NAV? Submit it at I Love NAV


Top
 Profile  
 
 Post subject: Re: Drop Table?
PostPosted: Thu Oct 27, 2011 4:06 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7504
Location: Milan
Country: Italy (it)
Or you can run a procedure that runs every N minutes and deletes the N oldest records. You will have to run that for a long time. I always do something like that instead of deleting all in 1 big go.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting


Top
 Profile  
 
 Post subject: Re: Drop Table?
PostPosted: Thu Oct 27, 2011 4:12 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Fri Feb 03, 2006 10:15 pm
Posts: 2272
Location: Houston, TX
Country: United States (us)
Yeah, usually the a job is run to archive the data into a different database / table. If you don't need to preserve the data, though, I recommend the TRUNCATE.

_________________
Matt Traxinger - ArcherPoint
Microsoft Dynamics NAV 2009 Programming Cookbook
Do you have an idea to improve NAV? Submit it at I Love NAV


Top
 Profile  
 
 Post subject: Re: Drop Table?
PostPosted: Thu Oct 27, 2011 4:14 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2574
Location: MA
Country: United States (us)
BTW - You cannot use "Truncate Table" on a table that is used by an Indexed View. So if you have defined any SumIndexes on the table you won't be able to use "Truncate Table".

_________________
There are no bugs - only undocumented features.


Top
 Profile E-mail  
 
 Post subject: Re: Drop Table?
PostPosted: Fri Oct 28, 2011 1:06 am 
Offline
MVP Microsoft Dynamics NAV

Joined: Fri Jun 11, 2004 9:05 pm
Posts: 4858
Location: Los Angeles, CA
Country: United States (us)
Truncate table worked beautifully!

_________________
Confessions of a Dynamics NAV Consultant = my blog
AP Commerce, Inc. = where I work


Top
 Profile E-mail WWW  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: