Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

used space and free space

Posted on 2014-02-05
8
Medium Priority
?
620 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 1000 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 3

Author Comment

by:pma111
ID: 39835230
so what are auto grow settings for then
0
 
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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

972 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