Wednesday, March 18, 2015

Get size of all databases on a server

Following are three scripts to get
  • Get size of all databases on a server
  • Get size of all databases in terms of data and log
  • Get size of all data and log files 
 -- Get Size of all databases  
 SELECT DB_NAME(database_id) AS DatabaseName,   
 cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]  
 FROM sys.master_files  
 GROUP BY database_id  
 ORDER BY database_id  

 -- Get Size of all database in data and log category  
 SELECT DB_NAME(database_id) AS DatabaseName, type_desc,   
 cast(sum( size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]  
 FROM sys.master_files  
 GROUP BY database_id, type_desc  
 ORDER BY database_id, type_desc DESC 
 -- Get Size of all data and log files  
 SELECT DB_NAME(database_id) AS DatabaseName, type_desc, name, physical_name,  
 cast( (size* 8.0 / 1024)/1024 as DECIMAL(18,3)) [Size(GB)]  
 FROM sys.master_files  
 -- WHERE DB_NAME(database_id) = 'DBToFiletr'  
 ORDER BY database_id, type_desc DESC, name  

Tuesday, February 10, 2015

SQL Server versions, editions, service packs, OS and deployment

Like other Microsoft technologies SQL Server follows the categorization of versions and editions. Updates are applied mainly through service packs and patches that are released by Microsoft for each version. Understanding of these concepts is important for DBAs as they have to make use of this information for deployment, upgrades and proposals. It is better for beginner level DBAs to explore this information prior to work on these during their jobs.
Click here to download presentation on this topic.

Wednesday, November 12, 2014

Generate object level permissions in a database

Following script can be used to generate object level permissions in a database. We can filter the results for specific user or type.
 SELECT AS username,  
     us.type_desc AS UserType,  
     schema_name(obj.schema_id)+'.' AS objectName,  
     dp.permission_name AS permission ,  
     dp.state_desc AS PermissionStatus  
 FROM sys.database_permissions dp  
 JOIN sys.database_principals Us ON dp.grantee_principal_id = Us.principal_id  
 JOIN sys.objects Obj ON dp.major_id = Obj.object_id --where us.type_desc <> 'DATABASE_ROLE'  
 -- get user permissions on whole DB  
 EXECUTE AS USER = 'userName';  
 SELECT * FROM fn_my_permissions(NULL, 'DATABASE')   
 -- get user permissions on whole DB  
  a.class_desc, permission_name, state_desc,  
 from sys.database_permissions a  
 inner join sys.database_principals b  
  on a.grantee_principal_id = b.principal_id  
  and permission_name not in ('connect','view definition','SELECT')  
  and b.type_desc in ('SQL_USER','WINDOWS_USER')  
  and state_desc = 'GRANT'  
  and class_desc = 'DATABASE'  

Wednesday, October 1, 2014

Get number of reads and writes for each database on SQL Server

We can get the number of read and write operations for each database on our server. This information id being fetched by a DMV so analysis data depends upon up time of server.
 -- total I/O for each database  
 SELECT name AS 'Database Name'  
 ,SUM(num_of_reads) AS 'Number of Read'  
 ,SUM(num_of_writes) AS 'Number of Writes'   
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) I  
 INNER JOIN sys.databases D   
 ON I.database_id = d.database_id  
 GROUP BY name ORDER BY 'Number of Read' DESC;  

Get read write latency faced by SQL Server for your drives

DBAs has often limited access to disk structure of their servers. Sys Admins keep it secret from DBAs to avoid any issue raised. However SQL Server may provide latency while it is reading/writing on a specific drive. It may be helpful to identify the READ or WRITE load on a drive and we can reduce it by moving specific tables to other data files.
 -- Calculate drive latency faced by SQL Server READ/WRITE  
 SELECT LEFT(physical_name, 1) AS drive,  
 CAST(SUM(io_stall_read_ms) /   
   (1.0 + SUM(num_of_reads)) AS NUMERIC(10,1))   
           AS 'avg_read_disk_latency_ms',  
 CAST(SUM(io_stall_write_ms) /   
   (1.0 + SUM(num_of_writes) ) AS NUMERIC(10,1))   
           AS 'avg_write_disk_latency_ms',  
 CAST((SUM(io_stall)) /   
   (1.0 + SUM(num_of_reads + num_of_writes)) AS NUMERIC(10,1))  
           AS 'avg_disk_latency_ms'  
 FROM  sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs  
 JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id  
                 AND mf.file_id = divfs.file_id  
 GROUP BY LEFT(physical_name, 1)  
 ORDER BY avg_disk_latency_ms DESC;  

Increase or shrink file size for SQL Server database files

Following handy scripts help to manage the SQL Server database files. You may increase the max size and current size as well.
 -- Increase file max size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', maxsIZE = 1500MB)  
 -- Increase file size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', sIZE = 1500MB)  

Shrink operation may be up to a specific extent or just shrink by removing the space available ay the end of file without manipulating data in file.If file shrink operation is cancelled during execution the current status is not rolled back. TRUNCATEONLY operation is fast.
 -- Shrink to specific limit  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 200)  
 -- Shrink to space available at the end of file  
 USE DatabaseNameHere;  

Get all file groups and file details in a SQL Server database

Following script will provide information about the filegroups and files in a given database. It includes names, location, allocated size, used size and percent free.
 -- get data file space and locations  
 Use DatabaseNameHere;  
 SELECT b.groupname AS 'File Group'  
   ,CONVERT(INT, a.Size / 128.000, 2) AS [Currently Allocated Space (MB)]  
   ,CONVERT(INT, FILEPROPERTY(a.NAME, 'SpaceUsed') / 128.000, 2) AS [Space Used (MB)]  
   ,CONVERT(INT, a.max_Size / 128.000, 2) [Maximum Space (MB)]  
       THEN CONVERT(VARCHAR, CONVERT(DECIMAL(15, 2), ROUND(a.growth / 128.000, 2))) + ' MB'  
     ELSE CONVERT(VARCHAR, a.growth) + ' PERCENT'  
     END [Growth]  
   ,CONVERT(INT, (a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2) AS [Available Space (MB)]  
   ,(CONVERT(INT, ROUND((a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2)) * 100) / (CONVERT(INT, ROUND(a.Size / 128.000, 2))) AS PercentFree  
 FROM sys.database_files a(NOLOCK)  
 LEFT OUTER JOIN sysfilegroups b(NOLOCK) ON a.data_space_id = b.groupid  
 ORDER BY PercentFree