Changing Default Database File Locations in SQL

I missed to specify the default location for the data file and the log file while installing SQL SERVER . So when I created a database and did not specify the default location for the files it will be created on the location defined in the server settings. So if we want  always the data files and log files to be created on a particular location we can
change the server properties through SSMS - > Database Settings -> Database default locations .

However this requires the sql instance to be restarted for the changes to take effect.

Adding missing permission to a folder for the MSSQL$instance account

I had to move the data files from one driver to another. After moving the data files when I brought the database online it gave"Access denied" error and I found out that the reason was that the MSSQL$instance account did not have permission to the data file.
I right clicked on the folder and on the security tab tried to add the user to give the missing permission but could not locate the user. I had to do the following to find the user in the user list

To add the missing permissions
chose the local computer and type in "NT SERVICE\MSSQL$instance

CDC error: 'The specified '@server' is invalid (valid values are returned by sp_helpserver)

I encountered the following error when I was enabling the CDC for the db1.dbo.pat_table


Could not update the metadata that indicates table [dbo].[pat_Table] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database db1 to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_jobstep_internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.

After researching around I  understood that the above error is caused due to mismatch in value between SERVERPROPERTY(‘ServerName’)) and master.dbo.sysservers

I solved the issue as follows -

select @@SERVERNAME --oldname

select SERVERPROPERTY('ServerName') --defaultname


--1 drop the old servername
  exec sp_dropserver 'oldservername'




--2 add new servername
  exec sp_addserver 'newservername', 'local'




--3 see changes
  exec sp_helpserver

Monitor disk space

/** This script checks the current space of the disks and if the available space is lower that the threshold (GB in this case) sends out email**/


use MyAdminDB
GO

declare @from varchar(100),
@to varchar(200),
@subject varchar(100),
@threshold int -- number of MB under which to launch an alert
　
　
　
SET NOCOUNT ON

DECLARE @list nvarchar(2000) = '';

WITH core AS (
　
　
SELECT DISTINCT

　
　
s.volume_mount_point [Drive],
　
　
CAST(s.available_bytes / 1048576 as decimal(12,2)) [AvailableMBs]
　
　
FROM
　
　
sys.master_files f
　
　
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) s
　
　
)
　
　
　
　
　
　
　
　
　
SELECT @list = @list + ' ' + Drive + ', current space available =' + cast(cast(round([AvailableMBs]*1.000/1024.000,2) as numeric(36,2)) as varchar) + 'GB'
　
　
FROM core
　
　
WHERE AvailableMBs < @threshold
　
　
IF LEN(@list) > 3 BEGIN
　
　
DECLARE @msg varchar(500) = 'Disk Space les than 8GB Notification. The following drives are currently reporting less than '
　
　
+ CAST(@threshold/1024 as varchar(12)) + ' GB free: ' + @list
　
　
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'myprofile@mydomain.com',
@recipients = @to,
@subject = @subject,
@body = @msg
END

RETURN 0
　
　
　
　
　
GO
　
　
　
　
　
　
　
　
　

Check if a table name exists in stored procedures

 use [yorDB]
 GO


SELECT [Scehma]=schema_name(o.schema_id), o.Name, o.type

FROM sys.sql_modules m

INNER JOIN sys.objects o

ON o.object_id = m.object_id

WHERE m.definition like '%tablename%'









GO

List permission on a databse objects

use current_userdb
GO

select o.name , pm.permission_name,pr.name, user_name(pm.grantee_principal_id) from sys.database_permissions pm inner join

sys.database_principals pr

on pm.grantee_principal_id = pr.principal_id

inner join sys.objects o

on o.object_id = pm.major_id

where object_name(pm.major_id) = 'object1'

Script: Performance Counters

--Buffer cache hit ratio : - close to 100%

--PLE : 300 or more

--Compilations : -- for every 10 batch requests / sec there is 1 compilations

--REcompilations : - less than 10% of compilations

-- Locks : -- Total ( 0)

--page splits : Less thann 20% of batch req/secs  

 
  
DECLARE @old_cntr_value INT;

DECLARE @first_sample_date DATETIME;



　

SELECT object_name, counter_name, ((cntr_value*1)/1024)*1.00/1024 AS 'Total Server Memory (GB)'

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Total Server Memory (KB)'



　

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

FROM (SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio'

AND object_name LIKE '%:Buffer Manager%') a

CROSS JOIN

(SELECT * FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio base'

and object_name LIKE '%:Buffer Manager%') b



　

　

　

SELECT @old_cntr_value = cntr_value,

@first_sample_date = getdate()

select *

FROM sys.dm_os_performance_counters

where counter_name = 'page splits/sec'





-- Time frame to wait before collecting second sample


WAITFOR DELAY '00:00:01'

select *

FROM sys.dm_os_performance_counters

where counter_name = 'page splits/sec'




-- Collect second sample and calculate per-second counter


SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as PageSplitsPerSec

FROM sys.dm_os_performance_counters

WHERE counter_name = 'page splits/sec'



　

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec'

AND object_name LIKE '%SQL Statistics%';

WAITFOR DELAY '00:00:01'



　

SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as [Batch Requests/sec]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec'

AND object_name LIKE '%SQL Statistics%';

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Batch Requests/sec'

AND object_name LIKE '%SQL Statistics%';

WAITFOR DELAY '00:00:01'



　

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Compilations/sec'

AND object_name LIKE '%SQL Statistics%';

WAITFOR DELAY '00:00:01'

SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as [compilations per sec]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Compilations/sec'

AND object_name LIKE '%SQL Statistics%';



　

　

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Re-Compilations/Sec'

AND object_name LIKE '%SQL Statistics%';

WAITFOR DELAY '00:00:01'

SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as [RE-compilations per sec]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'SQL Re-Compilations/Sec'

AND object_name LIKE '%SQL Statistics%';



　

　

SELECT @old_cntr_value = cntr_value,@first_sample_date = getdate()

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Lock Waits/sec'

AND object_name LIKE '%SQLServer:Locks%'

and instance_name = '_Total' ;

WAITFOR DELAY '00:00:01'



　

　

　

　

SELECT (cntr_value - @old_cntr_value) /

DATEDIFF(ss,@first_sample_date, GETDATE()) as [Lock Waits/sec]

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Lock Waits/sec'

AND object_name LIKE '%SQLServer:Locks%'

and instance_name = '_Total' ;



　

　

　

SELECT numa_node = ple.instance_name, ple_sec = ple.cntr_value, db_node_mem_GB = dnm.cntr_value/1048576,

ple_per_4gb = ple.cntr_value * 4194304 / dnm.cntr_value

FROM sys.dm_os_performance_counters ple join sys.dm_os_performance_counters dnm

on ple.instance_name = dnm.instance_name

and ple.counter_name='Page life expectancy' -- PLE per NUMA node

and dnm.counter_name='Database Node Memory (KB)' -- buffer pool size per NUMA node