MS SQL Server Tips

Unable to Shrink the DataFile

If you are unable to shrink the datafile then try the following

Error :

File ID 3 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Solution:

Add at least 2-3 MB to the data file and start shrinking again.

Index Fragmentation Level

SELECT distinct dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count,

i.rowcnt as 'totalrows'

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

inner join sysindexes i on i.id=dbindexes.[object_id]

WHERE indexstats.database_id = DB_ID() and i.rowcnt !=0

AND dbtables.[name] like 'tblOrder'

ORDER BY indexstats.avg_fragmentation_in_percent desc, i.rowcnt desc

Top Resource Consuming Procs

SELECT

TOP 50 DB_NAME(st.dbid) DBName

,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName

,'sp_recompile '''+OBJECT_NAME(st.objectid,dbid) + '''' StoredProcedure

,max(cp.usecounts) Execution_count

,sum(qs.total_worker_time) total_cpu_time

,sum(qs.total_worker_time) / (max(cp.usecounts) * 1.0) avg_cpu_time

,creation_time

FROM sys.dm_exec_cached_plans cp join sys.dm_exec_query_stats qs on cp.plan_handle = qs.plan_handle

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'

group by DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid), OBJECT_NAME(objectid,st.dbid) , creation_time

order by sum(qs.total_worker_time) DESC

Check Query Plans in Cache

SELECT TOP 25

databases.name,

dm_exec_sql_text.text AS TSQL_Text,

dm_exec_query_plan.query_plan,

CAST(CAST(dm_exec_query_stats.total_worker_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as cpu_per_execution,

CAST(CAST(dm_exec_query_stats.total_logical_reads AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as logical_reads_per_execution,

CAST(CAST(dm_exec_query_stats.total_elapsed_time AS DECIMAL)/CAST(dm_exec_query_stats.execution_count AS DECIMAL) AS INT) as elapsed_time_per_execution,

dm_exec_query_stats.creation_time,

dm_exec_query_stats.execution_count,

dm_exec_query_stats.total_worker_time AS total_cpu_time,

dm_exec_query_stats.max_worker_time AS max_cpu_time,

dm_exec_query_stats.total_elapsed_time,

dm_exec_query_stats.max_elapsed_time,

dm_exec_query_stats.total_logical_reads,

dm_exec_query_stats.max_logical_reads,

dm_exec_query_stats.total_physical_reads,

dm_exec_query_stats.max_physical_reads,

dm_exec_cached_plans.cacheobjtype,

dm_exec_cached_plans.objtype,

dm_exec_cached_plans.size_in_bytes

FROM sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)

CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)

INNER JOIN sys.databases

ON dm_exec_sql_text.dbid = databases.database_id

INNER JOIN sys.dm_exec_cached_plans

ON dm_exec_cached_plans.plan_handle = dm_exec_query_stats.plan_handle

WHERE databases.name = 'dbname' -- Database Name

ORDER BY dm_exec_query_stats.max_logical_reads DESC , dm_exec_query_stats.total_worker_time desc

CPU % by Each Database

WITH CPU_Per_Db
AS
(SELECT
dmpa.DatabaseID
, DB_Name(dmpa.DatabaseID) AS [Database]
, SUM(dmqs.total_worker_time) AS CPUTimeAsMS
FROM sys.dm_exec_query_stats dmqs
CROSS APPLY
(SELECT
CONVERT(INT, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(dmqs.plan_handle)
WHERE attribute = N'dbid') dmpa
GROUP BY dmpa.DatabaseID)

SELECT
[Database]
,[CPUTimeAsMS]
,CAST([CPUTimeAsMS] * 1.0 / SUM([CPUTimeAsMS]) OVER() * 100.0 AS DECIMAL(5, 2)) AS CPUTimeAs%]
FROM CPU_Per_Db
ORDER BY [CPUTimeAsMS] DESC;

Search for any table

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME like ''% item %'''

List of All Database Uers

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT
@@SERVERNAME as Server_Name,dbname,username ,logintype ,create_date ,modify_date ,
STUFF(
(
SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2
WHERE
user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH('')
)
,1,1,'') AS Permissions_user

FROM @DB_USers user1
GROUP BY
dbname,username ,logintype ,create_date ,modify_date
ORDER BY DBName,username

Read SQL Error Log Using Date Range

EXEC xp_ReadErrorLog 0, 1, Null, Null, '2018-09-11 02:29:32.280', '2018-09-12 02:29:32.280'

SSRS - Maximum Length Exceeded

Error “Maximum request length exceeded" in Reporting Services -

Modify the web.config file in the source server -

Location : C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServer.

Update: maxRequestLength="8388608"

look at the article https://www.mssqltips.com/sqlservertip/4688/resolving-the-maximum-request-length-exceeded-exception-in-sql-server-reporting-services/

Get Last Backup Date

;with backup_cte as( select database_name, backup_type = case type when 'D' then 'database'

when 'L' then 'log'
when 'I' then 'differential'
else 'other'

end, backup_finish_date, rownum = row_number() over ( partition by database_name, type order by backup_finish_date desc ) from msdb.dbo.backupset)select database_name, backup_type, backup_finish_datefrom backup_ctewhere rownum = 1ANDbackup_cte.backup_type='database'order by backup_cte.backup_finish_date DESC

High CPU Utilization

During high CPU utilization, we can clear the cache to bring SQL Server to a normal state. The following procedure will help you find the same.

The below procedure finds the high cpu utilization and collects the information of the queries running for a long time and consuming max cpu and clears of cache using dbcc freeproccache.

ALTER PROCEDURE [dbo].[FREEPROCCACHE]

AS
BEGIN
SET NOCOUNT ON;

DECLARE @retention int = 60, @destination_table varchar(500) = 'WhoWasActive', @destination_database sysname = 'MyWebGrocerUtility',
@schema varchar(max), @SQL nvarchar(4000), @parameters nvarchar(500), @exists bit;

DECLARE @ts_now BIGINT = (
SELECT cpu_ticks / (cpu_ticks / ms_ticks) FROM sys.dm_os_sys_info
);
DECLARE @threshold INT = 85; -- percent
DECLARE @count INT;

SET @destination_table = @destination_database + '.dbo.' + @destination_table;

WITH cte
AS (SELECT TOP (7)
SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM
(
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
[timestamp]
FROM
(
SELECT [timestamp],
CONVERT(XML, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%'
) AS x
) AS y
ORDER BY record_id DESC)
--SELECT * from cte
SELECT @count = COUNT(*)
FROM cte
WHERE [SQL Server Process CPU Utilization] > @threshold;

IF @count >=6
BEGIN

--collect activity into logging table
EXEC('EXEC master.dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @destination_table = @destination_table; ')

--purge older data
SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS varchar(10)) + ', GETDATE());';
EXEC (@SQL);

DBCC FREEPROCCACHE;
END

END

Get Session_ID with SQL Text

SELECT a.session_id, a.blocking_session_id, text

FROM sys.dm_exec_requests a

CROSS APPLY sys.dm_exec_sql_text(a.sql_handle)

join sys.dm_exec_sessions b

on a.session_id=b.session_id

WHERE a.session_id > 50

Executing SQL scripts from SQLCMD

sqlcmd -S myServer\instanceName -i C:\myScript.sql

save output to a file:

sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt

Failed to Bring SQL Services Online in Cluadmin

Troubleshooting steps:

1. 1. Went through the cluster.log to find out the reasons for failure

000011dc.000029d0::2018/06/26-15:01:49.082 INFO [RES] SQL Server <SQL Server>: [sqsrvres] Connect to SQL Server ...

000011dc.000029d0::2018/06/26-15:01:49.111 ERR [RES] SQL Server <SQL Server>: [sqsrvres] ODBC Error: [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'RHDIPROD\DCPSQLNODE18$'. (18456)

2. I have added the user 'RHDIPROD\DCPSQLNODE18$' to the sql instance

3. Started the services, it is still unable to connect to the sql server using that account though it has the sysadmin privileges.

Login failed for user 'RHDIPROD\DCPSQLNODE18$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 151.138.6.152]

4. It is failing because we disabled the connectivity to the built-in administrators

a. Use the below query to find out

SELECT sp.[name],sp.type_desc

FROM sys.server_principals sp

INNER JOIN sys.server_permissions PERM ON sp.principal_id = PERM.grantee_principal_id

WHERE PERM.state_desc = 'DENY'

5. Granted connect permissions to the built-in accounts

6. Started the sql services.. it worked !!!

Cluster Not recognized as an internal command

From Powershell -> Install-WindowsFeature -name RSAT-Clustering-CmdInterface

cluster log /g

Check the last executed statements:

SELECT dest.TEXT AS [Query],
deqs.execution_count [Count],
deqs.last_execution_time AS [Time]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

Check user permissions to connect to a database

SELECT name, hasdbaccess FROM sys.sysusers WHERE name = 'guest'

Drop all tables from a database

Use the below script :

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FORSELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)BEGINExec sp_executesql @SqlFETCH NEXT FROM @Cursor INTO @SqlEND
CLOSE @Cursor DEALLOCATE @CursorGO
EXEC sp_MSforeachtable 'DROP TABLE ?'GO

Trace Flag Information

How to Enable a Trace Flag:

DBCC TRACEON (3226,-1) - stop logging successful backup information

Insert trace log into a table

Select * into mytrace from ::fn_trace_gettable('location',numberoffiles)

Various Trace Flags are:

Trace Flag 1222:

Write the information about resources and types of locks in an XML format.

Trace Flag 1204:

Write the information about the deadlock in a text format.

Trace Flag 7806:

Enables a dedicated administrator connection on SQL Server Express Edition.

Trace Flag 1806:

You can disable the instant file initialization.

Trace Flag 4616:

The Application can access server level roles.

Trace Flag 3625:

It limits the information for those users who are not part of the sysadmin role and it prevents sensitive information.

Trace Flag 3608:

It stops the SQL Server to start automatically backup and restore for all Databases except the Master database.

Trace Flag 3226:

When we are taking log backups frequently, we can avoid some unnecessary additional log information.

Trace Flag 3014:

Trace more information of error log during the backup operation.

Trace Flag 3505:

It disables all information about the instant file initialization.

Database Growth

The below scripts helps you find database growth rate by number of days

SET NOCOUNT ON

Author: Kishore

Date: 4th Aug 2017

Desc: Calculates Growth Info for all databases on a server that are being backed up. Relies on the backup tables, and as a result will only contain as many

days history as do the backup tables(@iNoSamples). If a database is not being backup up the results will be NULL. (For example the Tempdb)

This is a rewrite of something I did a few years ago, as I dont know where I saved the other code. bummer.

CREATE TABLE ##tbl_DataSize

(

Size DECIMAL(38)

)

CREATE TABLE #tbl_GrowthData

(

DatabaseName

nvarchar(200)

,NoSampleDays

DECIMAL(20,3)

,DataSizeMB

DECIMAL(20,3)

,LogSizeMB

DECIMAL(20,3)

,BackupSizeMB

DECIMAL(20,3)

,TotalSpaceMB

DECIMAL(20,3)

,DataGrowth

DECIMAL(20,3)

,LogGrowth

DECIMAL(20,3)

,GrowthPercentage

DECIMAL(20,3)

)

DECLARE

@iNoSamples

INT

,@nMaxBackupSize

DECIMAL

,@nMinBackupSize

DECIMAL

,@nMaxLogSize

DECIMAL

,@nMinLogSize

DECIMAL

,@nMaxDataSize

DECIMAL

,@nMinDataSize

DECIMAL

,@vcDatabaseName

nvarchar(200)

,@dtMaxBackupTime

DATETIME

,@dtMinBackupTime

DATETIME

,@iMinBackupID

INT

,@iMaxBackupID

INT

DECLARE file_cursor CURSOR FOR

SELECT [name] FROM master.dbo.sysdatabases

ORDER BY [name]

OPEN file_cursor

FETCH NEXT FROM file_cursor

INTO @vcDatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @dtMaxBackupTime = (SELECT MAX(backup_finish_date)FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D')

SET @dtMinBackupTime = (SELECT MIN(backup_finish_date)FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D')

SET @iNoSamples =

DATEDIFF

(

,@dtMinBackupTime

,@dtMaxBackupTime

)

SET @nMaxBackupSize = (SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime)

SET @nMinBackupSize = (SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime)

SET @iMaxBackupID = (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime)

SET @iMinBackupID = (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime)

SET @nMaxLogSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile

WHERE backup_set_id = @iMaxBackupID AND file_type = 'L')

SET @nMinLogSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile

WHERE backup_set_id = @iMinBackupID AND file_type = 'L')

SET @nMaxDataSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile

WHERE backup_set_id = @iMaxBackupID AND file_type = 'D')

SET @nMinDataSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile

WHERE backup_set_id = @iMinBackupID AND file_type = 'D')

EXEC ('

INSERT INTO ##tbl_DataSize

SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM '+'['+@vcDatabaseName+']'+'.dbo.sysfiles'

)

INSERT INTO #tbl_GrowthData

SELECT

@vcDatabaseName DatabaseName

,@iNoSamples NoSampleDays

,@nMaxDataSize

,@nMaxLogSize

,@nMaxBackupSize / 1048576

,((size * 8192) / 1048576) TotalSpaceUsed

,@nMaxDataSize - @nMinDataSize

,@nMaxLogSize - @nMinLogSize

,(((@nMaxDataSize + @nMaxLogSize) - (@nMinDataSize+ @nMinLogSize)) / (@nMinDataSize+ @nMinLogSize)) * 100.00

--growth percentage is calculated based upon the original data size, before the growth. as a result it may look a little funny, but it is accurate. or at least I think so :)

FROM ##tbl_DataSize

TRUNCATE TABLE ##tbl_DataSize

FETCH NEXT FROM file_cursor

INTO @vcDatabaseName

END

CLOSE file_cursor

DEALLOCATE file_cursor

SELECT

FROM #tbl_GrowthData

DROP TABLE ##tbl_DataSize

DROP TABLE #tbl_GrowthData

SET NOCOUNT OFF

Search This Blog

kishor