Re: Insert Statement Bloating Log
From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 01/18/05
- Next message: William: "Help with an ADOCE SQL statement"
- Previous message: oj: "Re: SQL Server Agent Proxy Error"
- In reply to: David R: "Re: Insert Statement Bloating Log"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 18 Jan 2005 16:23:41 -0600
Yeah, I am not sure that anyone can help you any better without better
details, which would be very hard to give over the newsgroups like this.
-- ---------------------------------------------------------------------------- Louis Davidson - drsql@hotmail.com SQL Server MVP Compass Technology Management - www.compass.net Pro SQL Server 2000 Database Design - http://www.apress.com/book/bookDisplay.html?bID=266 Note: Please reply to the newsgroups only unless you are interested in consulting services. All other replies may be ignored :) "David R" <nospam@nospam.com> wrote in message news:4D353B22-35F6-4AF9-BC00-EA0189A134BB@microsoft.com... > Hi Louis, > Thanks for your'e time. > I already broke it down to smaller chunks. That's the workaround I'm using > now, I do a few small chunks then I truncate the log and do another set of > small chunks. But I would really like to do it all at once, and to > understand > why the log gets so overblown. I guess the only get to the root of this > problem is to use Log Explorer from Lugiment but that's a bit pricey. > > David. > > "Louis Davidson" wrote: > >> Can you segment your inserts into smaller chunks? This will alleviate >> some >> of the problem. Like 20 10000 row inserts? Or something smaller? The >> larger the operation the more painful it can become. No comfort I >> suppose, >> but if you can do this pretty easily it might help quite a bit. >> >> (sorry I didn't see your earlier posts about checking the result set, my >> bad.) >> >> -- >> ---------------------------------------------------------------------------- >> Louis Davidson - drsql@hotmail.com >> SQL Server MVP >> >> Compass Technology Management - www.compass.net >> Pro SQL Server 2000 Database Design - >> http://www.apress.com/book/bookDisplay.html?bID=266 >> Note: Please reply to the newsgroups only unless you are interested in >> consulting services. All other replies may be ignored :) >> >> "David R" <nospam@nospam.com> wrote in message >> news:F7D5BCE3-B3B8-467A-8626-2C44F0FA706A@microsoft.com... >> >I already tried that. It returns < 200,000. >> > Thanks, >> > David >> > >> > "Louis Davidson" wrote: >> > >> >> Yeah, and have you checked to output of the select statement to be >> >> certain >> >> of the data you are trying to insert. Try doing: >> >> >> >> select count(*) >> >> from --the rest of your query. >> >> >> >> I have occasionally missed some criteria in larger joins like this >> >> that >> >> aren't always easy to see. >> >> >> >> -- >> >> ---------------------------------------------------------------------------- >> >> Louis Davidson - drsql@hotmail.com >> >> SQL Server MVP >> >> >> >> Compass Technology Management - www.compass.net >> >> Pro SQL Server 2000 Database Design - >> >> http://www.apress.com/book/bookDisplay.html?bID=266 >> >> Note: Please reply to the newsgroups only unless you are interested in >> >> consulting services. All other replies may be ignored :) >> >> >> >> "Alejandro Mesa" <AlejandroMesa@discussions.microsoft.com> wrote in >> >> message >> >> news:6F3A71C2-9DD7-482D-9164-7AEF6B677A06@microsoft.com... >> >> > Are you using INSERT INTO ... SELECT ... or SELECT ... INTO ...? >> >> > >> >> > >> >> > >> >> > AMB >> >> > >> >> > "David R" wrote: >> >> > >> >> >> Hi Mike, >> >> >> Thanks for your answer. >> >> >> My table is a heap (nonclustered) and does not have any indexes at >> >> >> all >> >> >> as >> >> >> it was a table created for a one time research. >> >> >> Do you have any other ideas? >> >> >> Thanks, >> >> >> David >> >> >> >> >> >> Here is the DDL >> >> >> CREATE TABLE [MyTable] ( >> >> >> ColumnA char (8), >> >> >> ColumnB varchar (10) , >> >> >> ColumnC varchar (50) , >> >> >> ColumnD char (10), >> >> >> ColumnE varchar (4000) , >> >> >> ColumnF char (10) , >> >> >> ColumnG varchar (255), >> >> >> ColumnH char (10) , >> >> >> ColumnI char (10) , >> >> >> ColumnJ varchar (25) , >> >> >> ColumnK varchar (25) >> >> >> ) ON [PRIMARY] >> >> >> >> >> >> >> >> >> >> >> >> "Mike Epprecht (SQL MVP)" wrote: >> >> >> >> >> >> > Hi >> >> >> > >> >> >> > Please post the DDL and DML. >> >> >> > >> >> >> > In theory, if each row was on it's own page, the data portion >> >> >> > would >> >> >> > be >> >> >> > 1.6Gb >> >> >> > in size. Then add overhead for indexes. >> >> >> > >> >> >> > Check that your query really retuns only 200'000 rows. What can >> >> >> > really >> >> >> > bloat >> >> >> > an operation is page splits as they are fully logged. Use an >> >> >> > order >> >> >> > by >> >> >> > clause >> >> >> > in your select that matches the clustered index on the >> >> >> > destination >> >> >> > table. >> >> >> > This will result in the data bieng written in a manner that >> >> >> > eliminates >> >> >> > page >> >> >> > splits. >> >> >> > >> >> >> > Regards >> >> >> > Mike >> >> >> > >> >> >> > "David R" wrote: >> >> >> > >> >> >> > > I have an Insert statement that gets data from 8 joined tables. >> >> >> > > The >> >> >> > > actual >> >> >> > > result set has about 200,000 records in 11 char/varchar columns >> >> >> > > according to >> >> >> > > my calculations this result set should be less than 1 GB. But >> >> >> > > when >> >> >> > > I >> >> >> > > execute >> >> >> > > this query the log file grows to over 80 GB! Then I get the >> >> >> > > following error >> >> >> > > Server: Msg 1105, Level 17, State 2, Line 5 >> >> >> > > Could not allocate space for object 'MyTable' in database >> >> >> > > 'MyDB' >> >> >> > > because the >> >> >> > > 'PRIMARY' filegroup is full. >> >> >> > > It looks like all the data that SQL Server used in the FROM >> >> >> > > clause >> >> >> > > is >> >> >> > > also >> >> >> > > being logged and even then 80 GB sounds too high to me. >> >> >> > > Does anyone have an idea what causes this behavior and how I >> >> >> > > can >> >> >> > > stop >> >> >> > > this >> >> >> > > from happening? >> >> >> > > Thanks, >> >> >> > > David >> >> >> >> >> >> >> >> >>
- Next message: William: "Help with an ADOCE SQL statement"
- Previous message: oj: "Re: SQL Server Agent Proxy Error"
- In reply to: David R: "Re: Insert Statement Bloating Log"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
