473,903 Members | 5,902 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how data file and log file grow?

Greetings,

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

Data Files:

File Name: student_dat
Location: e:\data\MSSQL\D ata\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\D ata\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

Jul 20 '05 #1
7 7944
Bing Du (bd*@iastate.ed u) 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
Jul 20 '05 #2
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!
Jul 20 '05 #3
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!
Jul 20 '05 #4
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!
Jul 20 '05 #5
comp.databases. ms-sqlserver (an*******@devd ex.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
Jul 20 '05 #6
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*******@devd ex.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.


Jul 20 '05 #7
Bing Du (bd*@iastate.ed u) 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
Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
9639
by: Thomas Lotze | last post by:
Hi, another question: What's the most efficient way of copying data between two file-like objects? f1.write(f2.read()) doesn't seem to me as efficient as it might be, as a string containing all the contents of f2 will be created and thrown away. In the case of two StringIO objects, this means there's a point when the contents is held in memory three times.
0
2866
by: JohnLH | last post by:
Hi, I am having an issue with the LOAD DATA LOCAL FILE command on the latest 3.0.9 JDBC Driver. The command works on Windows, but doesn't seem to work on OSX and Linux. The error that I get on Linux and OSX is ... Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FILE
14
5859
by: Luiz Antonio Gomes Pican?o | last post by:
How i can store a variable length data in file ? I want to do it using pure C, without existing databases. I'm thinking to use pages to store data. Anyone has idea for the file format ? I want to store data like a database: ---------------------------------- Custumer:
5
2627
by: Joe Van Dyk | last post by:
I have a file that looks like this: 25.000000 55.000000 23.000000 51.000000 5 8 8700 6000 <and then, at byte 128, starts binary stuff> What's the proper way to make sure that I'm reading in valid data? Should I check after each cin that the input stream isn't eof?
2
2460
by: Bill | last post by:
I have an Access 2003 database application that I split into an application component MDB file (i.e., forms, etc) and a data component MDB file (i.e., tables, etc.). I used the 2003 developer extensions Package Wizard program to create an installation package. (Note: I included the Access 2003 runtime because the users of my application don't have Access on their machines). My problem is that when a user runes the installation program,...
4
42748
by: tlcvetan158 | last post by:
Hi, I'm a newbie at C++. I am just trying to read a file with three columns of data into arrays and write it back out, to see how arrays work. This runs with no errors, but the output doesn't look like the input, so I think I just have something specified incorrectly, but not sure what. My input: 1999 1 3 1999 2 6 1999 4 5 My program:
2
4838
abdoelmasry
by: abdoelmasry | last post by:
hi any one can help me plz ?? my code is about opening file as binary then get data from file to edit and rewrite data to file this is the code: #include <cstdlib> #include <iostream> #include<fstream>
3
3267
by: psbasha | last post by:
Hi , When ever we read any data from file ,we read as a single line string ,and we convert the respective field data available in that string based on the data type ( say int,float ). Please suggest me which is the best way of handling the file data. I- Method: ---------------- Store as single line string data's(upto end of file ) in a list and make use of this string list for the entire application.
0
14314
ADezii
by: ADezii | last post by:
Rather than using CurrentProject.Connection or entering your own Connection information, ADO supports storing Connection information in an external file called a Data Link File (which normally has a *.UDL extension). Data Link Files provide two very important capabilities: They implement a graphical interface for constructing what can be complex and confusing OLE DB Connection Strings. They offer a way to allow Users to edit Connection...
6
6798
by: Dave067 | last post by:
Hi This is my first attempt at writing Python script - it's probably a bit ambitious, but there again, whatever doesn't kill you makes you stronger... ;-) I'm trying to write a script for a datalogger, which will import data from a laboratory flow meter at regular user-defined intervals via a serial port connection, then save it as a CSV file which can then be opened in Excel for analysis. The imported data is in the form of an...
0
9849
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10878
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10986
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9688
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7208
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5894
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6094
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4308
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3324
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.