May 2, 2006 at 2:35 am
Morning All,
I am trying to troubleshoot why scripts/jobs that used to take seconds now take several minutes. As part of this I have looked at the tempDb properties and see that the Dbase size is 10.25Mb whilst the Space available is only 8.3Mb. I have looked at the Dbase properties and see that the Dbase is set to grow automatically at 10%. I note that there is a similar situation on other Dbases on this and other servers.
BOL is not very helpful in explaining this area. So, is the above situation normal? Should I be changing anything? Could this account for the slowdown in performance? I have used Trace and View Execution Plan when running a script but that does not help. I suspect that something is runninng wild in Memory but do not know how to identify it.
Apologies for so many questions, but you know how some days can be!
Thanks
Colin
May 2, 2006 at 10:35 am
Yesit can explain the slowdown. Consider tempDb to be a scratch pad. If the database can't expand then more work has to be done in memory. I am surpirsed you haven't seen the server bark about log file full or other conditions due to lack of space. I have also seen SQL Servers just stop processing altogether because of this. Check to see if you have an excessive amount of free space in the database data files or if the log files have grown to large sizes. Might help to shrink the databases down a bit or you need to invest in more drive space.
May 3, 2006 at 10:44 am
May 4, 2006 at 3:38 am
Thanks folks, good ideas. I am currently reviewing and amending the DB sizes on my servers.
The problem that I was originally faced with has now been solved. It seems that the SQL Mail routines were amended by somebody during the day, and hence the routines were having problems generating their Emails. The search for the culprit continues and they will be required to visit the Headmasters office! 😉
Cheers
Colin:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply