Solved

used space and free space

Posted on 2014-02-05
8
570 Views
Last Modified: 2014-02-11
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
Comment
Question by:pma111
  • 4
  • 3
8 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 250 total points
ID: 39835145
0
 
LVL 3

Author Comment

by:pma111
ID: 39835176
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
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39835226
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
 
LVL 3

Author Comment

by:pma111
ID: 39835230
so what are auto grow settings for then
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:pma111
ID: 39835234
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
 
LVL 3

Author Comment

by:pma111
ID: 39835382
any view surendra?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39835457
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 39836869
>> 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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now