how data file and log file grow?

[Bing Du]

Greetings,

The following shows how the Properties of a database look like:

Data Files:

File Name: student_dat
Location: e:\data\MSSQL\Data\student.mdf
Space allocated (MB): 62
'Automatically grow file' checked
File growth: 'By percent 10%' checked
Maximum file size: 'Unrestricted file growth' checked

Transaction Log:

File Name: student_log
Location: e:\data\MSSQL\Data\student.ldf
Space allocated (MB): 52
'Automatically grow file' checked
File growth: 'By percent 10%' checked
Maximum file size: 'Unrestricted file growth' checked

The physical files look like this:

Name Size Type Modified
------------------------------------------------
student.ldf 52,416 KB Database File 2/11/2004 10:34PM
student.mdf 63,424 KB Database File 2/11/2004 10:34PM

My question is now that it has been specified 'Unrestricted file growth'
for both the data and the log file, why both haven't increased any in
size since 2/11/2004? Actually, the modified timestamps of some other
databases files are also '2/11/2004 10:34PM'. That's weird.

I found the following message that's relevent to the above timestamp in
the SQL Server Logs:

=====
2004-02-11 22:34:10.12 server SQL Server terminating because of
system shutdown.
=====

I'm pretty sure there have been a lot updates taking place on this
database. We don't hear any complaints from the customers that they
have had any problems (such as, no space left) with the databases.
Did the SQL server write the data and log somewhere else?

Any insight on what's going on would be appreciated.

Bing

[Erland Sommarskog]

Bing Du (bd*@iastate.edu) writes:

The physical files look like this:

Name Size Type Modified
------------------------------------------------
student.ldf 52,416 KB Database File 2/11/2004 10:34PM
student.mdf 63,424 KB Database File 2/11/2004 10:34PM

My question is now that it has been specified 'Unrestricted file growth'
for both the data and the log file, why both haven't increased any in
size since 2/11/2004? Actually, the modified timestamps of some other
databases files are also '2/11/2004 10:34PM'. That's weird.
If the files are said to be modified in November this year, then there
is something fishy.
I found the following message that's relevent to the above timestamp in
the SQL Server Logs:

=====
2004-02-11 22:34:10.12 server SQL Server terminating because of
system shutdown.
=====

But if the date is supposed to be in February, then it seems that files
has not been touched since. Either the files were detached, or the
databases has not been set to autoclose. Or the entire SQL Server instance
has not been running since then.

On my machine, about all my databases has a last modified date of
2004-03-01. I started SQL Server today, but I have not visited all
databases. But on startup, SQL Server performs recovery of all databases,
and thus writes to them.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[comp.databases.ms-sqlserver]

Thanks for the instant response!

No, not November. The files were last modified on 2/11/2004. That's
Feb. 11, 2004. If the files were detached, the users would not be able
to access the databases, right? But we have not heard any complaints.
I don't think those databases haven't been touched since 2/11/2004.
These databases are in production. A lot updates are going on every
day. From my understanding, any updates (insert, update, delete) should
trigger the modified timestamp change on the files. Also, the SQL
server instance has been running fine since then. The only thing I'm
not sure is 'databases has not been set to autoclose' you mentioned.
Where can I find that setting?

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[comp.databases.ms-sqlserver]

I've checked the sysdatabases table. The 'status' is 16 for all our
user databases. Per SQL Server Online Book, '1 = autoclose, set with
sp_dboption' and
'16 = torn page detection, set with sp_dboption'. Do I need to change
'status'?

Another thing was the SQL Server Agent was not set to start
automatically whenever the SQL Server instance is started. Our server
instance terminated and started on 2/11/2004. We started the SQL Server
Agent manually on 2/15/2004. Would this have done anything to make the
database files not been changed since 2/11/2004?

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[comp.databases.ms-sqlserver]

I've checked the sysdatabases table. The 'status' is 16 for all our
user databases. Per SQL Server Online Book, '1 = autoclose, set with
sp_dboption' and
'16 = torn page detection, set with sp_dboption'. Do I need to change
'status'?

Another thing was the SQL Server Agent was not set to start
automatically whenever the SQL Server instance is started. Our server
instance terminated and started on 2/11/2004. We started the SQL Server
Agent manually on 2/15/2004. Would this have done anything to make the
database files not been changed since 2/11/2004?

Bing

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

comp.databases.ms-sqlserver (an*******@devdex.com) writes:

No, not November. The files were last modified on 2/11/2004. That's
Feb. 11, 2004.
Not in any date format I would use. :-)
If the files were detached, the users would not be able to access the
databases, right? But we have not heard any complaints. I don't think
those databases haven't been touched since 2/11/2004. These databases
are in production. A lot updates are going on every day. From my
understanding, any updates (insert, update, delete) should trigger the
modified timestamp change on the files. Also, the SQL server instance
has been running fine since then. The only thing I'm not sure is
'databases has not been set to autoclose' you mentioned. Where can I
find that setting?

Looks like I spoke too soon when I said that my files had a date that
agreed with my latest startup of SQL Server. I forgot that I rarely to
to bed before midnight, so the times I saw was from the last shutdown
as in your case. So I guess that everything's normal.

As for autogrow not setting in, one has to assume that the number of inserts
vs. the number deletes balances each other fairly well.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Bing Du]

So are you saying the time actually shown for those MDF and LDF files
was the time the Server instance last terminated? Any updates (lke
INSERT, DELETE or UPDATE) won't get the timestamps changed?

Bing

Erland Sommarskog wrote:

comp.databases.ms-sqlserver (an*******@devdex.com) writes:

No, not November. The files were last modified on 2/11/2004. That's
Feb. 11, 2004.

Not in any date format I would use. :-)

If the files were detached, the users would not be able to access the
databases, right? But we have not heard any complaints. I don't think
those databases haven't been touched since 2/11/2004. These databases
are in production. A lot updates are going on every day. From my
understanding, any updates (insert, update, delete) should trigger the
modified timestamp change on the files. Also, the SQL server instance
has been running fine since then. The only thing I'm not sure is
'databases has not been set to autoclose' you mentioned. Where can I
find that setting?

Looks like I spoke too soon when I said that my files had a date that
agreed with my latest startup of SQL Server. I forgot that I rarely to
to bed before midnight, so the times I saw was from the last shutdown
as in your case. So I guess that everything's normal.

As for autogrow not setting in, one has to assume that the number of inserts
vs. the number deletes balances each other fairly well.

[Erland Sommarskog]

Bing Du (bd*@iastate.edu) writes:

So are you saying the time actually shown for those MDF and LDF files
was the time the Server instance last terminated? Any updates (lke
INSERT, DELETE or UPDATE) won't get the timestamps changed?

It does not seem like that.

I would guess that if you set the database to autoclose, the date would
be updated each time the database was closed.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp