What would you solve the is scenario [modified]
-
Your website is getting so many hits and visitors are running so many queries Temp DB logs starts growing exponentially thus your sites starts slowing down and even crashing. What would be the most effecient way to quickly clear temp DB without restarting the servers. I wrote this script which worked but it took a while, but it helped. Would you use another method is this script the best its gonna get? DBCC SHRINKFILE(‘YourDataBase_log’) BACKUP LOG YourDataBase WITH TRUNCATE_ONLY After that is done if the log files still have not shrunken then I do this: SET NOCOUNT ON CREATE TABLE dummy (a VARCHAR(8000)) WHILE (0 < 1) BEGIN INSERT INTO dummy SELECT REPLICATE(‘a’, 8000) DELETE FROM dummy END -- modified at 0:45 Tuesday 8th August, 2006
Greg Coding makes the world go round!!!
-
Your website is getting so many hits and visitors are running so many queries Temp DB logs starts growing exponentially thus your sites starts slowing down and even crashing. What would be the most effecient way to quickly clear temp DB without restarting the servers. I wrote this script which worked but it took a while, but it helped. Would you use another method is this script the best its gonna get? DBCC SHRINKFILE(‘YourDataBase_log’) BACKUP LOG YourDataBase WITH TRUNCATE_ONLY After that is done if the log files still have not shrunken then I do this: SET NOCOUNT ON CREATE TABLE dummy (a VARCHAR(8000)) WHILE (0 < 1) BEGIN INSERT INTO dummy SELECT REPLICATE(‘a’, 8000) DELETE FROM dummy END -- modified at 0:45 Tuesday 8th August, 2006
Greg Coding makes the world go round!!!
Put tempdb on a seperate diskdrive if possible and this will also improve performance.
Look where you want to go not where you don't want to crash. Bikers Bible