Web Development

Ask something
Discussion Locked

SQL script to get DB & trans log size

0 helpful
4 replies

by agibbs

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

4 replies

by agibbs

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

by agibbs

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.

This discussion is closed to new replies.