1

We have an OLTP database (running SQL Server 2008 R2) that has 6 tempdb files.

All of them have an Autogrowth by 10%, restricted growth to 10000mb. At one point, someone changed one of the growths to restrict by 120000mb which caused that tempdb file to grow exponentially larger than the others. We are trying to track down when this change could have occurred.

Is it possible to see, either, when a setting was changed or a history of tempdb growth?

One issue is that the issue has been fixed and the tempdb file is back down to normal size.

Improve this question
3

2 Answers 2

Reset to default
2

If you have the default trace configured, and if the change was recent enough, you might be able to find the change. The default trace only maintains a certain amount of trace, but I have found unexpected changes by examining its contents.

See: http://msdn.microsoft.com/en-us/library/ms175513(v=sql.105).aspx

This describes how to read the default trace so that you can research problems.

There is no backward looking tool to find database growth and other changes.

If you want to have a persisted history of changes you will need to periodically query the needed data and store it in a database table. With SQL Server 2012 Change Tracking can also answer questions like this.

Improve this answer
2
  • In the log folder, I see 5 trace files and the oldest dates back 1 week... The issue occurred about a month ago. In looking at the log, the earliest it goes is 9/21. Looks like we may be out of luck.
    – Wes
    Commented Sep 30, 2014 at 17:29
  • Although it looks like I won't be able to get the data needed, this is the best answer and, therefore, I will mark it as such.
    – Wes
    Commented Sep 30, 2014 at 17:46
1

I'm not sure if this report will give you any more information. Link

it also uses the default trace mechanism but maybe it may give you hints about who is messing about with the server, even if it doesn't provide evidence about the particular change

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.