MySQL Forums
Forum List  »  Microsoft SQL Server

SQL Server 2000 code RRS feed
Posted by: ridhiman alloys
Date: May 17, 2021 06:31AM

Hi,

My name is Ajay owner of Ridhimanalloys.com my question is

Anyone could help me to correct the script on SQL Server 2000? Thanks in advance

--------------------

USE [Data_ADM]
GO
/****** Object: StoredProcedure [dbo].[CTRL_Spaceused] Script Date: 11/02/2021 10:59:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CTRL_Spaceused]
AS
BEGIN
/*
Title : CTRL_Spaceused

*/
IF OBJECT_ID('tempdb..#Temp1') IS NOT NULL
DROP TABLE #Temp1
DECLARE @drive varchar(100);
CREATE TABLE #Temp1
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size] FLOAT NULL,
[File Used Space] FLOAT NULL,
[Max_size] Int,
[physical_name] nvarchar(260) NOT NULL
)
DECLARE @Rundate datetime = getdate()
EXEC sp_MSforeachdb ' USE [?];
INSERT INTO #Temp1
SELECT
DB_NAME() [Database] ,
''FileGroupName''= CASE
WHEN fg.name IS NULL THEN MF.type_desc
ELSE fg.name
END ,
MF.size / 128.0 ,
MF.size / 128.0 - ( ( size / 128.0 ) - CAST(FILEPROPERTY(MF.name, ''SPACEUSED'') AS INT) / 128.0 ),
MF.Max_size,
MF.physical_name
FROM sys.database_files MF
LEFT JOIN sys.filegroups fg
ON MF.data_space_id = fg.data_space_id
LEFT JOIN sys.data_spaces ds
ON ds.data_space_id = MF.data_space_id
WHERE MF.Type in (1,0)
'
DECLARE @temp2 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size] FLOAT NULL,
[File Used Space] FLOAT NULL,
[Max_size] Int,
[physical_name] nvarchar(260) NOT NULL
)
INSERT INTO @temp2
SELECT
[Database] ,
[FileGroupName] ,
Sum([File Size]) as [File Size] ,
Sum([File Used Space]) AS [File Used Space],
[Max_size],
[physical_name]
FROM #temp1
WHERE [Database] NOT IN ('distribution', 'master', 'model', 'msdb')
GROUP BY
[Database] ,
[FileGroupName],
[Max_size],
[physical_name]
DECLARE @temp4 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size_MB] FLOAT NULL,
[File Used Space_MB] FLOAT NULL,
[Percent_Free_%] nvarchar (6),
[Autogrowth] nvarchar(260) NOT NULL,
[Drive_Letter] Nvarchar(2) NOT NULL
)

INSERT INTO @temp4
SELECT
[Database] ,
[FileGroupName] ,
[File Size] as [File Size_MB] ,
[File Used Space] AS [File Used Space_MB],
100-(CONVERT(decimal(5,2),(CONVERT(DECIMAL(10, 2),[File Used Space])/CONVERT(DECIMAL(10, 2),[File Size])*100))) AS [% Free Space],
"Autogrowth" = CASE
WHEN (Max_size) <0 THEN 'File will grow until the disk is full'
WHEN (Max_size) = 0 THEN 'No growth is allowed'
ELSE cast(Max_size/128 as varchar (255))
END,
LEFT([physical_name] ,1) AS Drive_Letter
FROM @Temp2
DROP TABLE #Temp1
DECLARE @temp3 TABLE
(
[Drive] varchar(100) NOT NULL,
[Total MB] varchar(1000) NULL,
[Total Free MB] varchar(1000) NULL,
[Total Available MB] varchar(1000) NULL,
[Percent Free] varchar(32)
)

INSERT INTO @temp3
SELECT distinct LEFT ((volume_mount_point),1),
total_bytes/1048576 as Size_in_MB,
available_bytes/1048576 as Free_in_MB,
available_bytes/1048576 as Total_Available_MB,
(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
available_bytes/1048576 order by 1

DECLARE @Temp5 TABLE
(
[Database] varchar(128) NOT NULL,
[FileGroupName] [sysname] NULL,
[File Size_MB] FLOAT NULL,
[File Used Space_MB] FLOAT NULL,
[Percent_Free_%] nvarchar (6),
[Autogrowth] nvarchar(260) NOT NULL,
[Drive_Letter] Nvarchar(2) NOT NULL,
[Total MB] varchar(1000) NULL,
[Total Free MB] varchar(1000) NULL,
[Total Available MB] varchar(1000) NULL,
[Percent Free] varchar(32)
)
INSERT INTO @Temp5
SELECT T4.*,T3.[Total MB] ,T3.[Total Free MB] ,T3.[Total Available MB] ,T3.[Percent Free] FROM @temp3 T3
INNER JOIN @temp4 T4
ON T3.Drive=T4.Drive_Letter
DECLARE @Temp6 TABLE
(
[Msg] varchar(256) NOT NULL,
[Status] varchar(1000) NULL
)
INSERT INTO @Temp6
SELECT [Database] +' '+[FileGroupName],
"STATUS" = CASE
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND (CONVERT(DECIMAL(5,2),[Percent_Free_%]) >10 OR CONVERT(DECIMAL(5,2),[Percent Free]) > 10) THEN '0'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),[Percent Free]) > 10 THEN '0'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) < 90 THEN '0'
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND CONVERT(DECIMAL(5,2),[Percent_Free_%]) <10 AND CONVERT(DECIMAL(5,2),[Percent Free]) <10 AND (CONVERT(DECIMAL(5,2),[Percent_Free_%]) > 5 OR CONVERT(DECIMAL(5,2),[Percent Free]) > 5) THEN '1'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),LEFT([Percent Free],Charindex('%',[Percent Free])-1)) >5 AND CONVERT(DECIMAL(5,2),[Percent Free]) <10 THEN '1'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) >= 90 AND ([File Used Space_MB]/[Autogrowth]*100)< 95 THEN '1'
WHEN ([Autogrowth]) ='File will grow until the disk is full' AND CONVERT(DECIMAL(5,2),[Percent_Free_%]) <5 AND CONVERT(DECIMAL(5,2),[Percent Free]) <5 THEN '2'
WHEN ([Autogrowth]) ='No growth is allowed' AND CONVERT(DECIMAL(5,2),[Percent Free]) < '5' THEN '2'
WHEN ([Autogrowth]) <> 'File will grow until the disk is full' and Autogrowth <> 'No growth is allowed' AND ([File Used Space_MB]/[Autogrowth]*100) >= 95 THEN '2'
END
FROM @Temp5
SELECT top (1) [Status] as State,[Msg] FROM @temp6
ORDER BY STATUS DESC
END

Result :

Server: Msg 139, Level 15, State 1, Procedure Check_Spaceused, Line 29
Cannot assign a default value to a local variable.
Server: Msg 170, Level 15, State 1, Procedure Check_Spaceused, Line 125
Line 125: Incorrect syntax near 'APPLY'.
Server: Msg 170, Level 15, State 1, Procedure Check_Spaceused, Line 174
Line 174: Incorrect syntax near '('.

Options: ReplyQuote


Subject
Written By
Posted
SQL Server 2000 code RRS feed
May 17, 2021 06:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.