Showing posts from September, 2017

Query to monitor disk space

One of the most common DBA tasks is to monitor disk usage. You don't want your main user databases, or even worse your whole instance being down because of a full disk. It is your duty to avoid that, and even this is something basic, a lot of DBAs fail on this. An  easy way to determine disk usage, is using the following query, since it gave me info about each datafile in each drive, and its free space: SELECT DB_NAME(VStats.database_id) DBName, mf.physical_name PhysicalFileLocation, VStats.volume_mount_point AS Drive, CONVERT (int,VStats.total_bytes/1048576.0) AS TotalSpaceInMB, CONVERT (int,VStats.available_bytes/1048576.0) AS FreeSpaceInMB, round(( convert (float,VStats.available_bytes)/ convert (float,VStats.total_bytes))*100,2) as PercentFreeSpace FROM sys.master_files mf CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) VStats ORDER BY PercentFreeSpace You can then use it in a job or custom report so you can receive an alert when usage is l

Tutorial: Create a database in Azure

This basic tutorial shows the way to create an Azure SQL Server database as PaaS (Platform as a Service) , with the default options and how to connect to it from your Management Studio. First, open your Azure dashboard Select SQL Databases> Create SQL Databases The following Box appears: Database Name: the name you want for you Database Subscription: the account on where the DB usage will be billed Resource group: the container of the database (if you have several projects can have distinct resource groups for each one) Select source: a blank database or a backup for an existing db   Server: acts as the “instance” you will connect, you can select an existing one or create a new SQL elastic pool: for testing purposes I have not activated it. In pricing information you select the size and resources assigned to your database based on the capacity you want for it. For this example I selected the lowest option. For more information about the DTU, visi