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.
-
5History of growth can be seen in the default trace (see this question for details) if it happened recently. You really shouldn't be using % for auto growth on any database. Tempdb with multiple files should have the same initial size, the same growth setting, and you should be using trace flags 1117 and 1118 to reduce contention and to ensure that all files grow at the same time.Aaron Bertrand– Aaron Bertrand2014年09月30日 17:14:12 +00:00Commented Sep 30, 2014 at 17:14
-
might be similar to dba.stackexchange.com/questions/13911/…Shiwangini– Shiwangini2019年06月09日 09:09:24 +00:00Commented Jun 9, 2019 at 9:09
-
might similar to:dba.stackexchange.com/questions/13911/…Shiwangini– Shiwangini2019年06月09日 09:10:44 +00:00Commented Jun 9, 2019 at 9:10
2 Answers 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– Wes2014年09月30日 17:29:08 +00:00Commented 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– Wes2014年09月30日 17:46:49 +00:00Commented Sep 30, 2014 at 17:46
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