November 29, 2007 at 2:58 am
Hello
Am trying to create a script that will determine the amount of space is been used on both my file server and sql server for all jobs residing on an instance. Once this result has been determined, I will like to populate them into another table, which will be used to develop a report model, using SSRS.
Am using the xp_fixeddrives query, but it only returns the drive letter and the amount of free space.
Can anyone provide me with any ideas?
November 29, 2007 at 3:28 am
use "sp_spaceused" command, for more information, see SQL BOL.
November 29, 2007 at 3:45 am
I'm sure that if you search in the scripts section of this site you will find at least 4 or 5 scripts dealing with spaceused etc..
Here's one which deals more with the diskspace side of it:
http://www.sqldbatips.com/showcode.asp?ID=4
Markus
[font="Verdana"]Markus Bohse[/font]
November 29, 2007 at 10:05 am
even if i use the sp_spaceused, how will i populate hem into a table that i created?
November 29, 2007 at 11:46 am
INSERT INTO myTable
Exec sp_spaceused
[font="Verdana"]Markus Bohse[/font]
November 29, 2007 at 12:46 pm
MarkusB (11/29/2007)
INSERT INTO myTableExec sp_spaceused
Since sp_SpaceUsed returns 2 result sets, I'm thinking that's just not going to do everything you'd expect. Right idea, though. 🙂
--Jeff Moden
November 29, 2007 at 1:13 pm
Take a look at the output from these scripts.
Get Server Database File Information
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058
Script to analyze table space usage
November 29, 2007 at 1:33 pm
I used the following script to create the table for the results to be inputed into the space_used table, but doesnt to work as i get the following error msg; Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 128
Insert Error: Column name or number of supplied values does not match table definition.
create table Space_Used
(
database_name varchar (50),
database_size varchar (10),
unallocated_space varchar (10),
primary key (database_name)
);
INSERT INTO space_used
Exec sp_spaceused
November 29, 2007 at 2:48 pm
I do it like this and then call this into a temp table
for each server
--exec sp_diskspace
CREATE PROCEDURE sp_diskspace
AS
/*
Displays the free space,free space percentage
plus total drive size for a server
*/
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 @@servername,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
November 30, 2007 at 11:08 am
b_boy (11/29/2007)
I used the following script to create the table for the results to be inputed into the space_used table, but doesnt to work as i get the following error msg; Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 128Insert Error: Column name or number of supplied values does not match table definition.
create table Space_Used
(
database_name varchar (50),
database_size varchar (10),
unallocated_space varchar (10),
primary key (database_name)
);
INSERT INTO space_used
Exec sp_spaceused
Had a little "free time" on my hands... perhaps this will help...
--====================================================================================================================
-- Presets
--====================================================================================================================
--===== Setup the environment to prevent blocking, etc
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --Allows "dirty" reads
SET NOCOUNT ON --Suppresses auto-display of rowcounts for appearance/speed
--===== Declare local variables
DECLARE @DBCount INT --Number of databases names to process
DECLARE @Counter INT --General purpose loop counter
DECLARE @SQLExec VARCHAR(8000) --Holds executable dynamic SQL
DECLARE @SQLTmpl VARCHAR(8000) --Holds dynamic SQL template
--===== Preset Values
SET @SQLTmpl ='
--===== Identify the database to use
USE
--===== Make sure usage info is up to date for each DB
DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS
--===== Get the database information similar to sp_SpaceUsed but in a set based fashion.
-- (Returns only 1 row for current database for each call)
INSERT INTO #Results
(DBName,DBID,DBTotalMB,LogFileMB,DataFileMB,UnallocatedMB,ReservedMB,TotalUsedMB,UnusedMB,DataMB,IndexMB)
SELECT DBName = DB_NAME(),
DBID = DB_ID(),
DBTotalMB = fs.DataFileMB + fs.LogFileMB,
LogFileMB = fs.LogFileMB,
DataFileMB = fs.DataFileMB,
UnallocatedMB = fs.DataFileMB - r.ReservedMB,
ReservedMB = r.ReservedMB,
TotalUsedMB = r.TotalUsedMB,
UnusedMB = r.ReservedMB - r.TotalUsedMB,
DataMB = r.DataMB,
IndexMB = r.TotalUsedMB - r.DataMB
FROM (--==== Derived table "fs" finds total file sizes (Status 64 = Log Device, 128 = Pages per MB)
SELECT DataFileMB = SUM(CASE WHEN Status & 64 = 0 THEN Size ELSE 0 END)/128.0,
LogFileMB = SUM(CASE WHEN Status & 64 <> 0 THEN Size ELSE 0 END)/128.0
FROM dbo.SysFiles
)fs
,
(--==== Derived table "r" finds types of space
SELECT ReservedMB = SUM(Reserved)/128.0,
TotalUsedMB = SUM(Used)/128.0,
DataMB = SUM(CASE WHEN IndID < 2 THEN DPages
WHEN IndID = 255 THEN Used
ELSE 0
END)/128.0
FROM dbo.SysIndexes
WHERE IndID IN (0,1,255)
)r'
--====================================================================================================================
-- Create Temp Tables
--====================================================================================================================
--===== Temp table to hold database names to work with and remember how many there are
IF OBJECT_ID('TempDB..#DatabaseNames','U') IS NOT NULL
DROP TABLE #DatabaseNames
SELECT RowNum = IDENTITY(INT,1,1),
Name
INTO #DatabaseNames
FROM Master.dbo.SysDatabases
ORDER BY Name
SET @DBCount = @@ROWCOUNT
--===== Temp table to hold results to be displayed
IF OBJECT_ID('TempDB..#Results','U') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results
(
DBName SYSNAME PRIMARY KEY CLUSTERED,
DBID INT,
DBTotalMB DECIMAL(19,1),
LogFileMB DECIMAL(19,1),
DataFileMB DECIMAL(19,1),
UnallocatedMB DECIMAL(19,1),
ReservedMB DECIMAL(19,1),
TotalUsedMB DECIMAL(19,1),
UnusedMB DECIMAL(19,1),
DataMB DECIMAL(19,1),
IndexMB DECIMAL(19,1)
)
--====================================================================================================================
-- Loop through the databases and save the size information for each
--====================================================================================================================
--=====
SET @Counter = 1
WHILE @Counter <= @DBCount
BEGIN
--===== Get the next database name to work on an insert the dynamic SQL using the template as a master
SELECT @SQLExec = REPLACE(@SQLTmpl,' ',Name)
FROM #DatabaseNames
WHERE RowNum = @Counter
--===== Execute the dynamic SQL to get the size information for the current database name
EXEC (@SQLExec)
--===== Bump the loop counter
SET @Counter = @Counter + 1
END
--====================================================================================================================
-- Return the results
--====================================================================================================================
SELECT * FROM #Results
Could easily be turned into a SPROC.
--Jeff Moden
December 6, 2007 at 6:07 am
Hello am running this script and am getting this error message:
Msg 137, Level 15, State 2, Line 10
Must declare the scalar variable "@db_name".
trying to figure out where am going wrong?
/*
**********************************************************************
* Collect database space
***********************************************************************
*/
insert
into #dbspace (database_name,total_space,used_db_space,total_log_space)
EXEC ('use [' + @db_name + ']
select
db_name = db_name(),
total_space =
(select
sum(convert(decimal(35,2),size)) / convert( float, (1048576 /
(select low from master.dbo.spt_values where number = 1 and type =
''E'')))
from dbo.sysfiles),
total_db_used =
(select
(sum(convert(float, case type when 2 then used_pages else
data_pages end)) *
(select
low
from
master..spt_values
where number = 1 and
type = ''e''))/1024/1024
from
sys.allocation_units),
total_log_space =
(select
sum(convert(decimal(35,2),size)) / convert( float, (1048576 /
(select
low
from
master.dbo.spt_values
where
number = 1 and
type = ''E'')))
from
dbo.sysfiles
where (status & 0x40)=0x40)' )
-- end collection of database space
December 7, 2007 at 10:51 pm
Yes... you haven't DECLARED @db_name...
--Jeff Moden
December 8, 2007 at 1:02 pm
Ran that script you posted and i got the following error messages:
Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '0'.
Msg 103, Level 15, State 7, Line 32
The identifier that starts with 'mastermastermastermastermastermastermastermastermastermastermastermastermastermastermastermasterDataMBmastermastermastermasterma' is too long. Maximum length is 128.
December 8, 2007 at 1:09 pm
What do you mean by DECLARE@db_name, and how can i get this thing sorted out?
December 8, 2007 at 6:13 pm
In your code, you never say...
DELCLARE @db_Name SYSNAME
That means you have an undefined variable and it's gonna give you an error. Here's the beginning of your code... and there's no declaration for the variable @db_name...
*
**********************************************************************
* Collect database space
***********************************************************************
*/
insert
into #dbspace (database_name,total_space,used_db_space,total_log_space)
EXEC ('use [' + @db_name + ']
select
db_name = db_name(),
total_space = ...
Further, once you declare the variable, you'll need to assign a value, with a correct database name, to it.
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply