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 |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2003-01-07 : 12:15:37
|
| I want to run this statement everytime the data changes in one of the user tables. The statement is "dbcc shrinkdatabase(tempdb,10)".So I created a trigger on this user table as follows:-======================================create TRIGGER Tr_shrinktempdatabaseon msc_processday FOR UPDATEAS dbcc shrinkdatabase(tempdb,10)======================================But when this trigger gets executed, I get an error======================================Server: Msg 8920, Level 16, State 1, Procedure sp_shrinktempdatabase, Line 2Cannot perform a ShrinkDatabase operation inside a user transaction. Terminate the transaction and reissue the statement.DBCC execution completed. If DBCC printed error messages, contact your system administrator.======================================Is there a way around it? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-07 : 12:20:35
|
I want to run this statement everytime the data changes in one of the user tables. The statement is "dbcc shrinkdatabase(tempdb,10)".??????????Start a job from the trigger which does the shrink.orPut a flag into a table from the trigger and have a job which looks at that table periodically and executes the shrink and deletes the entry.The ?????????? means 'I really don't want to know where this comes from'  ==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 01/07/2003 12:22:14 |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-07 : 12:25:00
|
| Yeah, I gotta agree with Nigel on that: ????????If I spent a year researching methods to ruin SQL Server performance, NONE of them would equal what you want to do here. |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2003-01-07 : 12:28:31
|
| thanks for the help, the problem is, after the nightly batch runs, the tempdb swells to over 2.5 to often 15 GB.This kinda chews up all space on our Servers.We do not have the luxury of stopping and restarting the sql server so often, hence I need to automate this process.Hope this helps you help me further....I have also tried creating a stored procedure which would run the same command, but that did not work as well. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-07 : 13:04:53
|
| Make a scheduled job out of it, there's no need to put it into a trigger. Books Online has details about creating jobs and scheduling them.Basically that's just a stopgap measure though, what you REALLY need to do is look at why tempdb is getting so huge. If your batch processes use lots of temp tables, rewrite them so that they don't. Also, very important, DON'T RELY ON AUTOMATIC CLEANUP. Drop temp tables when you're done with them, don't just assume that SQL Server will drop them for you. That alone might solve the problem. |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2003-01-07 : 13:11:07
|
| thanks guys...Really appreciate that..I am just talking this over to my colleagues...and we thought, we should know of it rather than let it be cleaned automatically. |
|
|
|
|
|
|
|
|
Trigger question