SQL Server : Resolving Identity Fullness

Query that will provide details about each identity column within a database:

SELECT

DB_NAME() AS database_name,

schemas.name AS schema_name,

tables.name AS table_name,

columns.name AS column_name,

types.name AS type_name

FROM sys.tables

INNER JOIN sys.columns

ON tables.object_id = columns.object_id

INNER JOIN sys.types

ON types.user_type_id = columns.user_type_id

INNER JOIN sys.schemas

ON schemas.schema_id = tables.schema_id

WHERE columns.is_identity = 1;

Built-in function IDENT_CURRENT, which returns the last identity value that was allocated for a given identity column. This function accepts a table name (including the schema name) and returns the last identity value used:

SELECT IDENT_CURRENT('dbo.table1') AS current_identity_value;

Reseed the Identity Column

DBCC CHECKIDENT ('Dbo.Identity_Test', RESEED, 2147483646);

To insert value in identity colomn:-

set identity_insert table1 on

SQL Server : How to Apply Patch in AlwaysOn Availability Group Configuration

How to Apply Patch in AlwaysOn Availability Group?

Steps for an Availability Group with one secondary replica is mentioned following and in case there are more than 1 replica too, almost same steps will be followed. 

1. Needless to mention that being a DBA, Backup is always a priority task for us. Make sure that we have taken good recent OS backup with system state (or VMware snapshot with SQL services stopped), a good recent backup of all databases and a successful completion of a checkdb on the primary node. {This is not mandatory, but to avoid “Ouch” moment}. 
2. Make note of primary replica node or just capture the screen print of cluster manager. Second, From the node acting as the primary replica (SQL1), change the failover mode to manual to ensure, auto fail-over is not being triggered. It is similar to what we do with classic cluster. In classic cluster, we remove nodes from possible owners list. 
3. Refresh the affected databases on the secondary replica (SQL2) and make sure that everything is green on the dashboard. This way, we can isolate things and troubleshooting is much easier in case we stuck with any issue. 
4. Apply the patch (service pack or CU) on SQL2  and then on SQL 3 in case we have 3 replicas. We have SQL server 2016 SP2 and CU5 to applied but steps are same for any package. 
5. Repeat the Windows Update and/or software updates until all available patches are applied ( In my case, first SP2 and then CU5). Do not move on with the patching steps until all patches and post patch reboot and configuration tasks are completed. As server reboot will be required, please ensure to apply patch on SQL 2 and then reboot that node. Once SQL 2 is up then only proceed to SQL 3.
6. Double check that patches have been applied, the cluster is healthy and AlwaysOn Availability Groups are functional. Also, check build version by running "select @@Version" and it should be the target version, In my case " Microsoft SQL Server 2016 (SP2-CU5) (KB4475776) - 13.0.5264.1 (X64)"
7. Make sure that synchronization state is SYNCHRONIZED.
8. Fail over the availability group to the secondary replica (SQL2) or to replica which was primary before starting patching activity. 
9. Refresh the affected databases on secondary Replica (former primary = SQL1) until the synchronization state is synchronized.
10. Apply the patch (service pack of CU) on SQL1.
11. Repeat the Windows Update and/or software updates until all available patches are applied. Do not move on with the patching steps until all patches and post patch reboot and configuration tasks are completed.
12. Double check that patches have been applied, the cluster is healthy and AlwaysOn Availability Groups are functional.
13. Make sure that synchronization state is SYNCHRONIZED.
14. Fail over the availability group to the primary node (back to SQL1).
15. Change the failover mode to Automatic now (which we changed in Step 2).
16. Validate all databases status and you may need to check SQL agent jobs which would have been cancelled during patching activity. 
17. Last but not least, validate SPNs of involved instances to ensure you kerborase authentication. 

Hope it will be helpful. 

SQL Server : Reallocate SSISDB database file

Steps to move SISDB from default location or Reallocate SSISDB database files

First, update catalog location:-

use master
go
alter database ssisdb modify file (name=data, filename='L:\mpdbs001\sqlappdb\SSISDB.mdf', filegrowth=256MB)
alter database ssisdb modify file (name=log, filename='L:\mplog001\sqlapplog\SSISDB.ldf', filegrowth=512MB)
go
alter database ssisdb set offline with rollback immediate
go

Second, copy files to new location :

exec xp_cmdshell 'copy /Y L:\mpsys001\sqlsysdb\SSISDB.mdf L:\mpdbs001\sqlappdb\'
exec xp_cmdshell 'copy /Y L:\mpsys001\sqlsysdb\SSISDB.ldf L:\mplog001\sqlapplog\'

alter database ssisdb set online
go

Third, change database owner if required

use ssisdb
exec sp_changedbowner 'Domain\User'
go

-----------Back up Master Key for database SSISDB ---------

use SSISDB
go
BACKUP MASTER KEY TO FILE = 'L:\mpsys001\Keys\SSISDB_MasterKey' ENCRYPTION BY PASSWORD = 'Password';

Thanks

SQL Server : Linked server related error while running select against view

Error :-

Msg 7202, Level 11, State 2, Procedure distrib_comb_v, Line 3 [Batch Start Line 0]
Could not find server 'AGN_SQ1_PL_TEST_PR_RW' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Msg 4413, Level 16, State 1, Line 348
Could not use view or function 'Test.dbo.emp_v' because of binding errors.

Solution:

Error is not related to view definition and sp_refreshview will not help here.

If we are using database based linked server, we need to manually choose default DB in option tab of SSMS and it will work fine.

Thanks
Chhavi

SQL Server : To find data and log file size of all DBs

Query to find data and log file size of all DBs
================

SELECT
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB,
    (CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB,
    (CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB
FROM sys.databases db
    LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id
    LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id

SQL Server : Backup path, size

Query to check Backup path, size , time etc..

SELECT          physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'db_name'
ORDER BY backup_finish_date DESC

SQL Server :: Clearing the plan cache for specific proc

It is very common to deal with situation where you have hundreds of procs in your database and you suspect  query plan is corrupted for one specific proc.

Following mentioned script will return plan handles and second script will help removing them one by one.

Please change database context accordingly.

**********************************************

1st, script to find all plan handles for one proc 

SELECT distinct cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%proc_name%';

2nd, script to clear cache just for this proc

SET NOCOUNT ON;
DECLARE @clear AS TABLE (id INT IDENTITY(1,1), p_handle VARBINARY(64))
DECLARE @minID INT = 0
DECLARE @p_handle VARBINARY(64) = NULL
DECLARE @SQL NVARCHAR(400) = '';

INSERT INTO @clear
SELECT distinct cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%proc_name%';

WHILE (SELECT COUNT(*) FROM @clear) > 0
BEGIN
SELECT @minID = MIN(id) FROM @clear;
SELECT @p_handle = p_handle FROM @clear WHERE id = @minID


SET @SQL = 'DBCC FREEPROCCACHE(0x' + CONVERT(VARCHAR(MAX), @p_handle, 2) +');'
EXECUTE sp_executesql @SQL
--PRINT @SQL

DELETE FROM @clear WHERE id = @minID

END;