| Author |
Topic |
|
MrTexasFreedom
Starting Member
13 Posts |
 Posted - 2011-12-13 : 20:33:05
|
| There are several stored procs available that provide tabular results identifying space used, unused space, etc. in the data and transaction log files. I'm trying to track the amount of space over time that my DB takes up and store it in an audit table. How can I query these values individually?On a side note, does anyone have a package that I can use as guidance for tracking my DB size(s) over time? I was at a SQL Saturday in October (Austin) where a presenter described something he built, but he never made any of his stuff available for me to review after the event.Appreciatively,mtf |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MrTexasFreedom
Starting Member
13 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
MrTexasFreedom
Starting Member
13 Posts |
Posted - 2011-12-14 : 11:47:21
|
quote:
Originally posted by tkizer
I've got a tool that does this at the database level: http://weblogs.sqlteam.com/tarad/archive/2010/07/09/Database-Growth-Tracker-Tool-ndash-New-Version.aspxTara Kizer
I reviewed the docs and attempted to install your package. No information on how the credentials are to be set for connecting to the remote server. As such, when I execute your stored procedure, I get an error message about an attempted anonymous login.[CODE]A .NET Framework error occurred during execution of user-defined routine or aggregate "isp_DatabaseGrowth": System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.[/CODE]In my environment, I've only got three servers, they're all linked, and I can write stored procedures that will execute queries against datbases on each server remotely. I looked through your C# code and found this query, which delivers a result close to what I am trying to achieve:[CODE]select DATABASE_NAME = db_name(s_mf.database_id), DATABASE_SIZE = convert(bigint, convert(bigint, sum(s_mf.size))*8), REMARKS = convert(varchar(254),null) from sys.master_files s_mf where s_mf.state = 0 and has_dbaccess(db_name(s_mf.database_id)) = 1 group by s_mf.database_id order by 1[/CODE]The other details I'm interested in are the unused space and transaction log size. Ideas?Appreciatively,mtf |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pnash
Starting Member
26 Posts |
Posted - 2011-12-14 : 16:06:40
|
quote:
The other details I'm interested in are the unused space and transaction log size. Ideas?
This query can help you to get free space and log file size also.select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) , NAME = left(a.NAME,15), FILENAME = left(a.FILENAME,100) from sys.sysfiles a |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-12-14 : 16:27:48
|
| pnash, thank you for posting this. This is a great start. The problem with the script is that FREE_SPACE_MB isn't exactly accurate as you could have a ton of space in the unused bucket. It's the unallocated space that represents free space. I've got a system where the ghost cleanup process is lagging, and thus we've got a ton of space in unused. It is not yet free space, but your query indicates it is. But like I said, it's a great start. Let me see if I can break it up.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
pnash
Starting Member
26 Posts |
Posted - 2011-12-18 : 09:13:41
|
| Thanks Tara, for valuable inputs. |
|
|
|
|
Need query that returns just DB file size