Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Toadwart
Starting Member
1 Post |
 Posted - 2005-02-01 : 18:48:05
|
| I have been asked to look at a SQL statement running in a stored procedure that is causing the transaction log to grow too large with the directive of reducing its impact on the log file – even at the cost of some performance. The database is set to use Simple recovery model. The log file set to grow in 500MB increments to a max of 10GB. Data file size is 25GBCan anyone suggest ways of re-writing the statement such that it has less impact on the transaction log (and not too much of an impact on performance)?The current statement is: INSERT INTO dbo.TableA ( IntCol1 , IntCol2 , DateCol , SmallintCol) SELECT C.IntCol1 , C.IntCol2 , B.[Date] , B.Value FROM dbo.TableB AS B (NOLOCK) INNER JOIN dbo.TableC AS C (NOLOCK) ON (B.IntCol2 = C.IntCol2) INNER JOIN dbo.TableR AS R (NOLOCK) ON (C.IntCol1 = R.RowID) WHERE (B.[Date] BETWEEN DATEADD(dd, 1, R.MaxPriorDateCol) AND R.MinPostDateCol) The total number records inserted is 1,000,000+ and appears to be causing the logfile to grow beyond it’s 10GB limit and IT is reluctant to make this any bigger for a number of reasons.I tried changing it to this: INSERT INTO dbo.TableA ( IntCol1 , IntCol2 , DateCol , SmallintCol) SELECT TOP 50000 C.IntCol1 , C.IntCol2 , B.[Date] , B.Value FROM dbo.TableB AS B (NOLOCK) INNER JOIN dbo.TableC AS C (NOLOCK) ON (B.IntCol2 = C.IntCol2) INNER JOIN dbo.TableR AS R (NOLOCK) ON (C.IntCol1 = R.RowID) WHERE (B.[Date] BETWEEN DATEADD(dd, 1, R.MaxPriorDateCol) AND R.MinPostDateCol) AND NOT EXISTS (SELECT 1 FROM dbo.TableA AS A2 WHERE A2.IntCol1 = C.IntCol1 AND A2. IntCol2 = C.IntCol2 AND A2. DateCol = B.[Date] AND A2.Value = B.Value)<looping around until the @@ROWCOUNT was = 0> This did help the log file, but not as much as I would have liked, it also made the process significantly slower and so causes too much pain for a little gain!;) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Insert statement blowing 10GB logfile