SQL SERVER DBA and Azure SQL

--TSQL Script to get SQL Data & Log Files Free Space & Space Used of all databases

Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL,

filName varchar(520) NULL, sizeMB decimal(10,2) null,

usedMB decimal(10,2) null, freeMB decimal(10,2) null,

pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)

Declare @sSql varchar(1000)

Set @sSql = 'Use [?];

Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMB, usedMB)

Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)),

Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))

From dbo.sysfiles Order By groupId Desc;'

Exec sp_MSforeachdb @sSql

Update #dbInfo Set

freeMB = sizeMB - usedMB,

pcntUsed = (usedMB/sizeMB)*100,

pcntFree = ((sizeMB-usedMB)/sizeMB)*100

select * from #dbInfo

--where dbname in ('works')

--where filname like '%G:\%' order by freemb desc -- Put drive name here for files residing on specific drive

drop table #dbInfo

sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

sudo systemctl restart mssql-server

……………………………………………………………………………………………………………………………………………………………………………….

Error 22022: SQLServerAgent is not currently running so it cannot be notified of this action.”

If you receive this error message, first make sure that the SQL Server Agent service is running. To do this, follow these steps:

……………………………………………………………………………………………………………………………………………………………………………….

1.Click Start, click Run, type Services.msc, and then click OK.

2.In the Services window, locate the SQL Server Agent service.

3.Make sure that the value of the Status column of the SQL Server Agent service is Running.

then

EXEC sp_configure ‘show advanced’, 1;

RECONFIGURE;

EXEC sp_configure ‘allow updates’, 0;

RECONFIGURE;

EXEC sp_configure ‘Agent XPs’, 1;

RECONFIGURE;

Pages