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 |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2010-02-22 : 05:39:29
|
| Hi,The log file for my database is growing when I run the last step of my job. The last step is an insert into and select. What can i do about this ? when i run the select on it's own it take ages to retrun back .. |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-02-22 : 05:51:16
|
| Post the select query. If you've identified it as the bottleneck.Have you had a look at the execution plan? do you have good indices -- are you querying a lot of views / have triangular joins / scaler function calls?more info please!Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2010-02-22 : 06:55:01
|
| Nope not looked at the execution plan? yet. There are no indexs on the table and yes it used a vie as a look up join. This code was written a long time ago as a fix so the comments say on it. Am going to add index to the table a have a look into the view. |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2010-02-22 : 07:06:04
|
| Coderc_promotion_gbs is a view took me a while to figure this out.. and there are no index on any tablesinsert into pro_line (pmc_pa_no,prd_CODE)select pmc_pa_no, prd_CODE from product p inner join rc_promotion_gbs g on p.gbs_prd_code = g.gbs_prd_codewhere not exists (select * from pro_line l where l.pmc_pa_no = g.pmc_pa_no and l.prd_code=p.prd_code) |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-02-22 : 07:20:17
|
| what's the view like?No index of any kind is going to lead to table scan's. If the view does something complicated on more base tables with no indices....The NOT EXISTS clause also accesses the view.What does the execution plan look like for the SELECT statement. You may get lucky and have 1 table scan taking up 99% of the time.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2010-02-22 : 07:45:55
|
| First of all i tried to shrink my log file and i can't it ask me to do a backup of it so i tried to do the backup statement belowdbcc shrinkfile (filename_log,1)backup log filename_log with truncate_only It tell me that it can't find the log file filename_log when i run the backup statement but when i run a select on the sysfile i see it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-22 : 08:50:54
|
| You should not shrink the log - except is if it had grown much larger than normal because of a single abnormal action (large delete to purge data).1) Backup the TLog2) Then shrink itYou can't Shrink it until backed up because it won't release the transactions in the log which are Committed but not yet backed up.Alternative is to change the DB to Simple recovery model and then back to Full and IMMEDIATELY take a Full Backup.But don't do that for a Production database.T.C. you worrying about performance instead of Log space used?Rookie_SQL : How many rows is your query going to insert? If it is a huge number then they will take up a lot of transaction log - more so if there are indexes on the table, or views on the table that have indexes, or triggers on the table that insert/update data in other tables. (You could split the INSERT into smaller batches, and backup TLog between each batch) |
|
|
|
|
|
|
|
|
log file growning on insert into