|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > Determining Database Size
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Determining Database Size
|
|
|
| Would anyone happen to know of a way to get back both the database size and
log file size (in MB)? For example, I'd need to know the mdf file size for
a db and the ldf file size for the db?
Thanks a lot.
| |
| Alejandro Mesa 2002-10-30, 2:24 pm |
| See "sp_helpfile" in BOL.
AMB
| |
|
| Thanks. Does sp_helpfile also cover logfile size? It doesn't mention it in BOL.
"Alejandro Mesa" <AlejandroMesa@GardensAmerica.com> wrote in message news:ubENigEgCHA.2116@tkmsftngp08...
See "sp_helpfile" in BOL.
AMB
| |
| Alejandro Mesa 2002-10-30, 3:23 pm |
| Yes, do not pass any parameter.
Example:
USE Northwind
GO
EXEC sp_helpfile
AMB
| |
|
| Thanks a lot. Would you happen to know of a way to via tsql to get back the size of a hard drive, how much space is used and how much is free?
"Alejandro Mesa" <AlejandroMesa@GardensAmerica.com> wrote in message news:#LgxSoEgCHA.2120@tkmsftngp10...
Yes, do not pass any parameter.
Example:
USE Northwind
GO
EXEC sp_helpfile
AMB
| |
| John F. 2002-10-30, 3:23 pm |
| xp_fixeddrives shows physical drives and space free.
| |
|
| Thanks. Do you know if it's possible to use xp_fixeddrives (or another tsql method) to get disks/mb free from another server on the network that does not have any sql components installed at all?
"John F." <jfichera@[NOSPAM].nc.rr.com> wrote in message news:OoMQE0EgCHA.2636@tkmsftngp08...
xp_fixeddrives shows physical drives and space free.
| |
| Jasper Smith 2002-10-30, 4:23 pm |
| Just a follow up with a slight improvement to xp_fixeddrives
The procedure below also gives you the total size of the drives
and the percent free (like disk manager). You might run into
permission issues trying to get diskinfo on other servers however
you can probably use WMI to do it in a VBScript , not something
TSQL would be much use for (except to store the results)
HTH
Jasper Smith
use master
go
CREATE PROCEDURE sp_diskspace
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
go
"Jay" <msnews.microsoft.com> wrote in message
news:u28FLCFgCHA.2700@tkmsftngp09...
Thanks. Do you know if it's possible to use xp_fixeddrives (or another tsql
method) to get disks/mb free from another server on the network that does
not have any sql components installed at all?
"John F." <jfichera@[NOSPAM].nc.rr.com> wrote in message
news:OoMQE0EgCHA.2636@tkmsftngp08...
xp_fixeddrives shows physical drives and space free.
| |
| John F. 2002-10-31, 11:23 am |
| Nice proc. Haven't use sp_OACreate much. Learn something new every day!
Thanks
| |
|
| Is there a way that this can be adapted to get drive size info from drivers
on other servers on the network? For example \\servername\E$ ?
"Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
news:uhb9NkFgCHA.2512@tkmsftngp10...
> Just a follow up with a slight improvement to xp_fixeddrives
> The procedure below also gives you the total size of the drives
> and the percent free (like disk manager). You might run into
> permission issues trying to get diskinfo on other servers however
> you can probably use WMI to do it in a VBScript , not something
> TSQL would be much use for (except to store the results)
>
> HTH
> Jasper Smith
>
> use master
> go
>
> CREATE PROCEDURE sp_diskspace
> AS
> SET NOCOUNT ON
>
> DECLARE @hr int
> DECLARE @fso int
> DECLARE @drive char(1)
> DECLARE @odrive int
> DECLARE @TotalSize varchar(20)
> DECLARE @MB bigint ; SET @MB = 1048576
>
> CREATE TABLE #drives (drive char(1) PRIMARY KEY,
> FreeSpace int NULL,
> TotalSize int NULL)
>
> INSERT #drives(drive,FreeSpace)
> EXEC master.dbo.xp_fixeddrives
>
> EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
> IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
>
> DECLARE dcur CURSOR LOCAL FAST_FORWARD
> FOR SELECT drive from #drives
> ORDER by drive
>
> OPEN dcur
>
> FETCH NEXT FROM dcur INTO @drive
>
> WHILE @@FETCH_STATUS=0
> BEGIN
>
> EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
> IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
>
> EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
> IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
>
> UPDATE #drives
> SET TotalSize=@TotalSize/@MB
> WHERE drive=@drive
>
> FETCH NEXT FROM dcur INTO @drive
>
> END
>
> CLOSE dcur
> DEALLOCATE dcur
>
> EXEC @hr=sp_OADestroy @fso
> IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
>
> SELECT drive,
> FreeSpace as 'Free(MB)',
> TotalSize as 'Total(MB)',
> CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
> FROM #drives
> ORDER BY drive
>
> DROP TABLE #drives
>
> RETURN
> go
>
>
> "Jay" <msnews.microsoft.com> wrote in message
> news:u28FLCFgCHA.2700@tkmsftngp09...
> Thanks. Do you know if it's possible to use xp_fixeddrives (or another
tsql
> method) to get disks/mb free from another server on the network that does
> not have any sql components installed at all?
> "John F." <jfichera@[NOSPAM].nc.rr.com> wrote in message
> news:OoMQE0EgCHA.2636@tkmsftngp08...
> xp_fixeddrives shows physical drives and space free.
>
>
|
|
|
|
|