Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

used space and free space

are there any useful SQL queries to list for all relevant database and log files relating to an instance (mssql 2005 and 2008 express), used space and free space, into a single query?

also, aside from the log file and database size, do you monitor anything else on the sql server as part of your capacity monitoring procedures?
0
pma111
Asked:
pma111
  • 4
  • 3
2 Solutions
 
Surendra NathTechnology LeadCommented:
0
 
pma111Author Commented:
can i ask with mssql database and log files, do you provision an amount of space, or do they just continue to grow as long as their is sufficient disk space left. then i read of autogrow polciies, so was unsure what "free space" is exactly reporting on?
0
 
Surendra NathTechnology LeadCommented:
No, the logs will be flushed out and the space will be released by SQL Server, it has its own internal mechanism for that.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
pma111Author Commented:
so what are auto grow settings for then
0
 
pma111Author Commented:
Auto-growth
 
What exactly are auto-growth events? An auto-growth event is the process by which the SQL Server engine expands the size of a database file when it runs out of space. The amount by which a database file grows is based on the settings that you have for the file growth options for your database.

how does it run out of space if you dont set any storage capacity for the database? surely it could then just keep growing for as long as their is sufficient disk space?
0
 
pma111Author Commented:
any view surendra?
0
 
Surendra NathTechnology LeadCommented:
Ok, there are two different things here

1) data file
2) log file

data file --> if the data in a file grows more than allocated size, then it will grow accrodingly if the auto growth is set.

log file --> this is not a permenant place for data store, it only stores the log change of the data.... The log can be removed when the data is pushed to the permenant place such as data file...
So the log files are quite a bit different....
0
 
Scott PletcherSenior DBACommented:
>> do you provision an amount of space, or do they just continue to grow as long as their is sufficient disk space left[?]   ... so what are auto grow settings for then << 

You decide which you want to do.  When you create a database, you specify a file size limit for each file.  You can set a specific maximum size, such as 10GB, or specify "unlimited", in which case that file keeps growing until/unless the underlying disk drives don't have space.

You can also tell SQL how much space to automatically add when the file needs more space.  For example, you can tell SQL to add 10MB at a time or 250MB at a time or any other amount.  Don't use too small an amount, as too many small disk extents will hurt performance.


>> then i read of autogrow polciies, so was unsure what "free space" is exactly reporting on?<<

"Free space" is allocated but unused space in the database.  For example, say a file ran out of space, your query was INSERTing data and needed more space.  You've specified 50MB as the autogrow amount, so SQL adds 50MB of disk to the file.  But say your INSERT only writes 5 more MB.  The remaining 45MB is "free space" that can be used later by that file in that db (but not by another file or another db).



>> No, the logs will be flushed out and the space will be released by SQL Server, it has its own internal mechanism for that. <<

The log is a separate file, and thus has a separate autogrow amount and total size specified for it.  SQL will reuse space in the log file once that space is no longer required for any internal SQL reasons (such as log backups or replication).

But SQL will not return any space allocated to any data or log file back to the disk system until/unless you explicitly shrink the file.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now