0

I have some jobs (stored procedures ) running in SQL server. The jobs are working good every day.

The point is that the database should increase the size every day until next month but since 30/09 the size as not increased.

I have seen that the tempdb files have not been modified since 30/09 also.

  1. What could be the reason for this?

  2. What really means that the tempdb files have not been modified since then?

  3. Is this correlated?

Thank you a lot in advance for your help.

Kirill Matrosov
  • 5,564
  • 4
  • 28
  • 39
Luxpbi
  • 23
  • 5
  • tempdb or not tempdb - but is your jobs work done correctly? I would look at generated/modified data, not at some db size. – Arvo Oct 03 '19 at 10:27
  • @Arvo thanks for your reply. You are right but in this case, the database should continue increasing, in any case, the date when the tempdb stoped to be modified is the same when the size stopped increasing. That's why I think they are related. I have to mention that the only thing done in this database are these stored procedures. In which cases the tempdb files are not modified? Every time the stored procedures finish the tempdb should be modified isn't it? – Luxpbi Oct 03 '19 at 10:55
  • Why should it be modified? If tempdb has enough free space, its size will not be increased. About modification date - I have no idea, but from quick google seems that modified date is updated only when file is closed; thereby this date does not indicate SQL database access (mdf files are open all the time). – Arvo Oct 03 '19 at 11:19
  • Sorry, I was not talking about the size of tempdb but of the size of my database where the stored procedures work. Then, how can I know if the tempdb has been modified recently? – Luxpbi Oct 03 '19 at 11:39
  • Tempdb and database files behave in similar manner. What about data[base] modification - you could take a look at sql internal functions; I have not used them and cannot recommend anything specific, sorry :( – Arvo Oct 03 '19 at 11:59
  • As long as a database has sufficient free space, the engine will use the unused space in the file as opposed to growing it. This is normal and expected behavior. If space has been freed up inside the files (by someone deleting a lot of rows, for example) you would not see any size increase when new rows are inserted until this space is filled up again. From Management Studio, you can inspect this by looking at the "Space Available" value in the database properties. – Jeroen Mostert Oct 03 '19 at 12:40

0 Answers0