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.

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.

  • 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 Sep 30 '14 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 Sep 30 '14 at 17:46
1

I'm not sure if this report will give you any more information. http://blogs.msdn.com/b/buckwoody/archive/2007/10/12/sql-server-management-studio-standard-reports-schema-changes-history.aspx

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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