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'

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

Tuesday, August 26, 2014

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Today one of our development team member reported following error on newly restored database in staging environment

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Error was generated in a stored procedure. First of all i found that collation of subject database is different than SQL_Latin1_General_CP1_CI_AS. Using this method i changed the collation of subject database to required SQL_Latin1_General_CP1_CI_AS.
I was hopeful that changing collation of database will solve the issue but it was not the case. I opened the stored procedure code and got the tables being used in it. 
Suppose we have table1, table2 and table3 in USP. Now we have to get all those columns where collation is different than our required collation. Following script did task and returned the columns with different collation.

SELECT object_name(object_id) as TableName, 
name as columnName, collation_name
FROM sys.columns
FROM sys.objects where name in ('table1','table2','table3'))
and collation_name <> 'SQL_Latin1_General_CP1_CI_AS'

Once we have the list of columns then we can change their collation to match with required collation. This can be done with following script.

ALTER TABLE table1 ALTER COLUMN ColumnNameHere nvarchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL
ALTER TABLE table2 ALTER COLUMN ColumnNameHere varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
ALTER TABLE table3 ALTER COLUMN ColumnNameHere varchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

This time i found that issue was resolved and there was no error.

Tuesday, May 6, 2014

Using 'odsole70.dll' version '2009.100.1600' to execute extended stored procedure 'sp_OACreate'

Recently we came across unexpected cluster fail over of one of our servers. It was required to get the exact reason for it. I analyzed the sql server and windows logs. There was no traces of failure neither i found any major issue in the logs that may lead to fail over. However an entry in the logs caught my attention and it was following message

Using 'odsole70.dll' version '2009.100.1600' to execute extended stored procedure 'sp_OACreate'. This is an informational message only; no user action is required.

I googled it and came to know that it is culprit of event. According to its BOL page

You call some Automation procedures from a SQL Server common language runtime (CLR) object, such as sp_OACreate. In this situation, SQL Server may unexpectedly crash.
Note This issue also occurs when a CLR object calls a Transact-SQL procedure that calls Automation procedures.
 It applies to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. More detail can be found here.
Now we have to trace the call and modify it to avoid the accidental fail over.