General discussion

Locked

SQL script to get DB & trans log size

By agibbs ·
Hey All,

I'm desperately in need of an SQL script that will give me a clean reading of the data & trans log file sizes and how much free space is left in each.

I know of two ways to do this (sortof), but neither is working for my situation.
The system stored procedure sp_spaceused returns the TOTAL space used and space available, although the amounts seem a little off from what's in Enterprise Manager. DBCC SQLPERF(LOGSPACE) will return the trans. log size and percent in use for alldatabases on the server. From these two I could theoretically calculate the space used by the datafile and logfile, and percent free space in each. But, it's an enormous pain and I can't do it without writing some custom VB code and using Recordset objects to manipulate the data.

So, my question is this: Does anyone have (or know how to write) a plain SQL script that will display to screen the data & logfile sizes and percent (or bytes) of free space in each? Even better, a script that would write this data to a table would be even better!

Thanks!!

--Aaron

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL script to get DB & trans log size

by agibbs In reply to SQL script to get DB & tr ...

Oh -- in case you hadn't realized already, I'm running MS SQL Server 2000 on Win2K. Enterprise Manager shows such a nice pretty graph of the data & logfile usage, I figure there's gotta be a way to get this data!

--Aaron

Collapse -

SQL script to get DB & trans log size

by abdriver2000 In reply to SQL script to get DB & tr ...

Check if the following link proves helpful:

http://www.swynk.com/downloads/DBDetails.txt

Collapse -

SQL script to get DB & trans log size

by agibbs In reply to SQL script to get DB & tr ...

Obviously you did not actually try this script before sending it to me . . . this script doesn't do anything I need it to. The "size" is in some unknown format (Blocks maybe? Or pages? I need bytes!) and it says only the size in use not actual size on disk.

Collapse -

SQL script to get DB & trans log size

by agibbs In reply to SQL script to get DB & tr ...

This question was closed by the author

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums