Category: SQL Server
-
SSRS Conf. Manager is not working, then how to take Reporting Services Encryption Key backup.
The rskeymgmt utility can be found in the binn sub-directory of your SQL Server install directory. On my local server, it resides in: C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn. Of course depending on the install process, your location may be different. Opening the command prompt and navigating to this directory, we can run rskeymgmt -? Extract…
//
-
Scripts Attach and Detach DBs
USE [master] GO EXEC master.dbo.sp_detach_db @dbname = N’AdventureWorks2012′ GO–Script for detach all dbs.select distinct ‘USE [master]GOEXEC master.dbo.sp_detach_db @dbname = N”’+db_name(database_id) +”’GO’ from sys.master_files where database_id > 5 order by 1/*Be careful for report db*/ ———————————————————————– /*USE [master]GOEXEC master.dbo.sp_detach_db @dbname = N’D1′GO*/ create table #t111([database_id] int, physical_name_count int)insert into #t111([database_id],physical_name_count)select d.[database_id], count(mf.physical_name) from sys.databases d, sys.master_files…
//
-
Database Mirroring Configuration
–step 1—————————————————————————————————————————––On Secondary ( Mirror Server ) ,–Server_Name2 USE MASTER;GOALTER DATABASE [DB_Name] SET PARTNER = ‘TCP://Server_Name1.domain.com:5022’GO—————————————————————————————————————————– –step 2—————————————————————————————————————————––On Primary (Principal Server) ,–Server_Name1 USE MASTER;GOALTER DATABASE [DB_Name] SET PARTNER = ‘TCP://Server_Name2.domain.com:5022’GO—————————————————————————————————————————– –step 3—————————————————————————————————————————– –Configure Witness (Server_Name3) ALTER DATABASE [DB_Name] SET WITNESS = ‘TCP://Server_Name3.domain.com:5022’;GO
//
-
Measure IOPs from SQL Server
Measure IOPs from SQL Server Investigating performance problems, We may need to look at I/O stats as well. Find how the Monitoring I/O on storage system is performing. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on…
//
-
Top SQL Server Memory Pressure Counters…
Below are the top SQL Server Memory Pressure Counters and their recommended values. Page Life Expectancy – It is an age of a data page in seconds in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory. Page Life Expectancy value indicates the memory pressure in allocated…
//
-
Free List Stall per Second
Free List Stall per Second: Its a counter available in SQL Buffer Manager. If SQL Server don’t have free pages in buffer cache then a processing request goes in stall and it must wait until pages get free in buffer cache. The recommended value of Free List per Seconds counter is below 2. If there…
//
-
SQL Server Services
Below are the SQL Server services which might come after SQL installation based on your selection… • SQL Server • SQL Server Agent • Other SQL Services • SQL Server Browser • SQL Server Full Text Search • SQL Server Integration Services • SQL Server Reporting Services • SQL Server Analysis Services
//
-
SQL Server Database Transaction Log Truncation
Transaction Log Truncation If log records were never deleted from the transaction log, it would eventually fill all the disk space that is available to the physical log files. Log truncation automatically frees space in the logical log for reuse by the transaction log. Except when delayed for some reason, log truncation occurs automatically…
//
-
T-SQL For SQL Server Memory Details…
SQL Server Buffer Cache details:- ———————————————————————————————- select [DateTimePerformed] = SYSDATETIMEOFFSET() , Server_Physical_Mem_MB = os.[Server Physical Mem (MB)] — SQL2012+ only , c.[Min_Server_Mem_MB] , c.[Max_Server_Mem_MB] , p.PLE_s , p.Stolen_Mem_MB , ‘Churn (MB/s)’ = cast((p.Total_Server_Mem_GB)/1024./NULLIF(p.PLE_s,0) as decimal(19,2)) , Server_Available_physical_mem_GB = (SELECT cast(available_physical_memory_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_sys_memory) , SQL_Physical_memory_in_use_GB = (SELECT cast(physical_memory_in_use_kb / 1024. / 1024. as decimal(19,2)) from sys.dm_os_process_memory) , p.Total_Server_Mem_GB –May be more or less than memory_in_use because it , p.Target_Server_Mem_GB from ( select InstanceName = @@SERVERNAME , Target_Server_Mem_GB = max(case counter_name when ‘Target Server Memory (KB)’ then convert(decimal(19,3), cntr_value/1024./1024.) end) , Total_Server_Mem_GB = max(case counter_name when ‘Total Server Memory…
//
-
Important DMVs for Database Mirroring…
Below are few important DMVs for Database mirroring…. select * from sys.dm_db_mirroring_connectionsselect connection_id,* from sys.dm_exec_connectionsselect * from sys.database_mirroringselect * from sys.database_mirroring_endpointsselect * from sys.database_mirroring_witnesses
//
-
SQL Server AlwaysON Health Check…
–Below SQL Script can we use for check SQL Server AG HEALTH USE [TempDB] SET NOCOUNT ON GO SELECT CurrentTime_UTC = SYSUTCDATETIME() DECLARE @XELTarget VARCHAR(MAX); DECLARE @XELPath VARCHAR(MAX); DECLARE @XELFile VARCHAR(max); IF EXISTS(SELECT name FROM sys.dm_xe_sessions WHERE name = ‘AlwaysOn_Health’) BEGIN SELECT @XELTarget = cast(xet.target_data AS XML).value(‘(EventFileTarget/File/@name)[1]’, ‘VARCHAR(MAX)’) FROM sys.dm_xe_sessions xes INNER JOIN sys.dm_xe_session_targets xet ON xes.address = xet.event_session_address WHERE xet.target_name = ‘event_file’ and xes.name = ‘AlwaysOn_Health’ SELECT @XELPath = REVERSE(SUBSTRING(REVERSE(@XELTarget), CHARINDEX(‘\’, reverse(@XELTarget)), LEN(@XELTarget)+1- CHARINDEX(‘\’, REVERSE(@XELTarget)))) SELECT @XELFile = @XELPath + ‘AlwaysOn_health*.xel’ IF @XELFile IS NULL BEGIN PRINT ‘Unable to find XEVent target files for AlwaysOn_Health XEvent session’ PRINT ‘Expected AOHealth XEvent files in this location:’ PRINT @XELPath RETURN END END ELSE BEGIN PRINT ‘No AlwaysOn Health XEvent…
//
-
SQL Server tempDB usage…
–Below script is for check tempDB usage ——————————————————————————– use tempdb go select * from Sys.dm_db_file_space_usage –Is the version store taking up a lot of space? SELECT version_store_Gb = (SUM(version_store_reserved_page_count)*8/1024./1024.) , total_tempdb_Gb = sum(total_page_count)*8/1024./1024. , pct_of_tempdb_dedicated_to_version_store = round((sum(version_store_reserved_page_count)*1./sum(total_page_count)*1.)*100.,2) FROM sys.dm_db_file_space_usage; –If version store is very large, need to find out what transactions are holding onto it. –It is not possible to associate versionstore allocation to individual sessions –But these queries…
//