Thursday, September 3, 2020

Re: SQL Server Notes in brief

1)To know server level information: 

 

SELECT  

          SERVERPROPERTY('MachineName') as Host, 

          SERVERPROPERTY('InstanceName') as Instance, 

          SERVERPROPERTY('Edition') as Edition, /*shows 32 bit or 64 bit*/ 

          SERVERPROPERTY('ProductLevel') as ProductLevel, /* RTM or SP1 etc*/ 

          Case SERVERPROPERTY('IsClustered') when 1 then 'CLUSTERED' else 

      'STANDALONE' end as ServerType, 

          @@VERSION as VersionNumber 

 


2)Server level configuration controls some of the features and performance of SQL Server: 

SELECT * from sys.configurations order by NAME 

àIf you are using SQL Server 2000, you can execute the following command instead. 

SP_CONFIGURE 'show advanced options',1 
go 
RECONFIGURE with OVERRIDE 
go 
SP_CONFIGURE 
go 
 
 
 

3) following SQL Command will show information related to the security admin server role and system admin server role. Refer Fig 1.3 

SELECT l.name, l.denylogin, l.isntname, l.isntgroup, l.isntuser 
  FROM master.dbo.syslogins l 
WHERE l.sysadmin = 1 OR l.securityadmin = 1 



 

T-SQL Statement 4 

Another important bit of information that you need to know as a DBA is all of the traces that are enabled. The following T-SQL statement will list all of the trace flags that are enabled gloabally on the server. Refer Fig 1.4 

DBCC TRACESTATUS(-1); 

The following T-SQL statement will list all the trace flags that are enabled on the current sql server connection. Refer Fig 1.4 

DBCC TRACESTATUS(); 



 

5)   SELECT name,compatibility_level,recovery_model_desc,state_desc  FROM sys.databases 



 

If you are using SQL Server 2000, you could execute the following T-SQL Statement.  

SELECT name,cmptlevel,DATABASEPROPERTYEX(name,'Recovery')AS RecoveryModel 

T-SQL Statement 6 

The next level of information related to database that is needed is the location of the database. The following T-SQL Statement provides the logical name and the physical location of the data/log files of all the databases available in the current SQL Server instance. Referg Fig 1.7 

SELECT db_name(database_id) as DatabaseName,name,type_desc,physical_name FROM sys.master_files 

 

If you are using SQL Server 2000, you could execute the following T-SQL Statement.  

SELECT db_name(dbid) as DatabaseName,name,filename FROM master.dbo.sysaltfiles 
 

7) A database may contain filegroups other than the primary file group. The following T-SQL Statement gets executed in each database on the server and displays the file groups related results.  

EXEC master.dbo.sp_MSforeachdb @command1 = 'USE [?] SELECT * FROM sys.filegroups' 


T-SQL Statement 8 

Backup of a database is bread and butter for database administrators. The following T-SQL Statement lists all of the databases in the server and the last day the backup happened. This will help the database administrators to check the backup jobs and also to make sure backups are happening for all the databases. Refer Fig 1.10 

SELECT db.name,  
case when MAX(b.backup_finish_date) is NULL then 'No Backup' else convert(varchar(100),  
               MAX(b.backup_finish_date)) end AS last_backup_finish_date 
FROM sys.databases db 
LEFT OUTER JOIN msdb.dbo.backupset b ON db.name = b.database_name AND b.type = 'D' 
               WHERE db.database_id NOT IN (2)  
GROUP BY db.name 
ORDER BY 2 DESC 



 

T-SQL Statement 9 

The next level of information that is important for a SQL Server database administrator to know is the location of all the backup files. You don't want the backups to go to the local drive or to an OS drive. The following T-SQL statement gets all the information related to the current backup location from the msdb database. Refer Fig 1.11 

SELECT Distinct physical_device_name FROM msdb.dbo.backupmediafamily 


àwhich database takes a lot of space and which file could be shrunk. Very useful when output is sorted by Drive Letter when "Out of space" occurs. 

USE master GO 
 
SET NOCOUNT ON 
DECLARE @Kb float 
DECLARE @PageSize float 
DECLARE @SQL varchar(max) 
 
SELECT @Kb = 1024.0 
SELECT @PageSize=v.low/@Kb FROM master..spt_values v WHERE v.number=1 AND v.type='E' 
 
IF OBJECT_ID('tempdb.dbo.#FileSize') IS NOT NULL  DROP TABLE #FileSize CREATE TABLE #FileSize (  DatabaseName sysname,  [FileName] varchar(max),  FileSize int,  FileGroupName varchar(max),  LogicalName varchar(max) 
) 
 
IF OBJECT_ID('tempdb.dbo.#FileStats') IS NOT NULL  DROP TABLE #FileStats CREATE TABLE #FileStats (  FileID int,  FileGroup int,  TotalExtents int,  UsedExtents int,  LogicalName varchar(max),  FileName varchar(max) 
) 
 
IF OBJECT_ID('tempdb.dbo.#LogSpace') IS NOT NULL  DROP TABLE #LogSpace CREATE TABLE #LogSpace (  DatabaseName sysname,  LogSize float,  SpaceUsedPercent float,  Status bit 
) 
 
INSERT #LogSpace EXEC ('DBCC sqlperf(logspace)') 
 
DECLARE @DatabaseName sysname 
 
DECLARE cur_Databases CURSOR FAST_FORWARD FOR  SELECT DatabaseName = [name] FROM dbo.sysdatabases WHERE [name] <> 'RVR_FSA' ORDER BY DatabaseName OPEN cur_Databases FETCH NEXT FROM cur_Databases INTO @DatabaseName WHILE @@FETCH_STATUS = 0 
  BEGIN 
 print @DatabaseName 
 SET @SQL = ' 
USE [' + @DatabaseName + ']; 
DBCC showfilestats; 
INSERT #FileSize (DatabaseName, [FileName], FileSize, FileGroupName, LogicalName) SELECT ''' +@DatabaseName + ''', filename, size, ISNULL(FILEGROUP_NAME(groupid),''LOG''), [name]  FROM dbo.sysfiles sf; ' 
PRINT @SQL 
 INSERT #FileStats EXECUTE (@SQL) 
 FETCH NEXT FROM cur_Databases INTO @DatabaseName 
  END 
 
CLOSE cur_Databases 
DEALLOCATE cur_Databases 
 
 
SELECT 
 DatabaseName = fsi.DatabaseName, 
 FileGroupName = fsi.FileGroupName, 
 LogicalName = RTRIM(fsi.LogicalName), 
 [FileName] = RTRIM(fsi.FileName), 
 DriveLetter = LEFT(RTRIM(fsi.FileName),2),  FileSize = CAST(fsi.FileSize*@PageSize/@Kb as decimal(15,2)),  UsedSpace = CAST(ISNULL((fs.UsedExtents*@PageSize*8.0/@Kb), fsi.FileSize*@PageSize/@Kb * ls.SpaceUsedPercent/100.0) as decimal(15,2)),  FreeSpace = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as decimal(15,2)),  [FreeSpace %] = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0) / fsi.FileSize * 100.0), 100-ls.SpaceUsedPercent) as decimal(15,2))  FROM #FileSize fsi  LEFT JOIN #FileStats fs  ON fs.FileName = fsi.FileName  LEFT JOIN #LogSpace ls  ON ls.DatabaseName = fsi.DatabaseName  ORDER BY 5, 8 DESC 
 

 

àDisk Space Used for all databases. 

;WITH DataBase_Size (SqlServerInstance,DatabaseName,DatabaseSize,LogSize,TotalSize) 
AS 
-- Define the CTE query. 
( 
  SELECT      @@SERVERNAME SqlServerInstance, 
            db.name AS DatabaseName, 
            SUM(CASE WHEN af.groupid = 0 THEN 0 ELSE af.size / 128.0E END) AS DatabaseSize, 
            SUM(CASE WHEN af.groupid = 0 THEN af.size / 128.0E ELSE 0 END) AS LogSize, 
            SUM(af.size / 128.0E) AS TotalSize 
FROM        master..sysdatabases AS db 
INNER JOIN  master..sysaltfiles AS af ON af.[dbid] = db.[dbid] 
WHERE       db.name NOT IN ('distribution', 'Resource', 'master', 'tempdb', 'model', 'msdb') -- System databases 
            AND db.name NOT IN ('Northwind', 'pubs', 'AdventureWorks', 'AdventureWorksDW')   -- Sample databases 
GROUP BY    db.name  
) 
-- Define the outer query referencing the  name. 
SELECT *FROM DataBase_Size order by TotalSize desc 
 
àall failed jobs in the last day. 
 
SELECT DISTINCT 
CAST(CONVERT(datetime,CAST(run_date AS char(8)),101) AS char(11)) AS 'Failure Date', 
SUBSTRING(T2.name,1,40) AS 'Job Name', 
T1.step_id AS 'Step_id', 
T1.step_name  AS 'Step Name', 
LEFT(T1.[message],500) AS 'Error Message' 
FROM msdb..sysjobhistory T1 
JOIN msdb..sysjobs  T2 
ON T1.job_id = T2.job_id 
WHERE  T1.run_status NOT IN (1,4) 
AND T1.step_id != 0 
AND run_date >= CONVERT(char(8), (select dateadd (day,(-1), getdate())), 112) 
 

Dead Lock Graph 

 
--Enable the traceflag and server configuration  dbcc traceon(1222,-1)  go  sp_configure xp_cmdshell,1  
 
 RECONFIGURE with override 
 go 
 -- Create the Base table in dba database. 
 USE [dba] 
GO 
 
 
/****** Object:  Table [dbo].[DeadlockEvents]    Script Date: 06/20/2012 12:19:40 ******/ 
SET ANSI_NULLS ON 
GO 
 
SET QUOTED_IDENTIFIER ON 
GO 
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeadlockEvents]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[DeadlockEvents](  [Id] [int] IDENTITY(1,1) NOT NULL,  [AlertTime] [datetime2](7) NOT NULL,  [DeadlockGraph] [xml] NULL 
) ON [PRIMARY] 
END 
GO 
 
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_AlertTime]') AND type = 'D') BEGIN ALTER TABLE [dbo].[DeadlockEvents] ADD  CONSTRAINT [DF_AlertTime]  DEFAULT (sysdatetime()) FOR [AlertTime] END 
 
GO 
--Create a user defined procedure in master db USE [master] GO 
 
/****** Object:  StoredProcedure [dbo].[usp_getdeadlockinfo]    Script Date: 06/20/2012 12:26:44 ******/ 
SET ANSI_NULLS ON 
GO 
 
SET QUOTED_IDENTIFIER ON 
GO 
 
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_getdeadlockinfo]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE procedure [dbo].[usp_getdeadlockinfo] as begin set nocount on 
SELECT TOP 1 [DeadlockGraph].query(''/TextData/deadlock-list'') FROM [DBA].[dbo].[DeadlockEvents] ORDER BY Id DESC   FOR XML RAW, TYPE, ELEMENTS XSINIL  
end'  
END 
GO 
--Set up the WMI alert for deadlock 
USE [msdb] 
GO 
 
/****** Object:  Alert [Respond to DEADLOCK_GRAPH]    Script Date: 06/20/2012 12:20:58 ******/ 
IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Respond to DEADLOCK_GRAPH') EXEC msdb.dbo.sp_add_alert @name=N'Respond to DEADLOCK_GRAPH', 
  @message_id=0, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=120, 
  @include_event_description_in=1, 
  @category_name=N'[Uncategorized]', 
  @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER', 
  @wmi_query=N'SELECT * FROM DEADLOCK_GRAPH', 
  @job_id=N'5ae39cb7-5e46-495c-aea2-7ab2355f77de' 
GO 
--Set up a job to respond for the alert 
--Inside the job replace the db profile, email recipients 
 
USE [msdb] 
GO 
 
/****** Object:  Job [Capture Deadlock Graph]    Script Date: 06/20/2012 12:22:55 ******/ 
BEGIN TRANSACTION 
DECLARE @ReturnCode INT 
SELECT @ReturnCode = 0 
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 06/20/2012 12:22:55 ******/ 
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 
END 
 
DECLARE @jobId BINARY(16) 
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'Capture Deadlock Graph') if (@jobId is NULL) BEGIN EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Capture Deadlock Graph', 
  @enabled=1, 
  @notify_level_eventlog=2, 
  @notify_level_email=0, 
  @notify_level_netsend=0, 
  @notify_level_page=0, 
  @delete_level=0, 
  @description=N'Job for responding to DEADLOCK_GRAPH events', 
  @category_name=N'[Uncategorized (Local)]', 
  @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 
END 
/****** Object:  Step [Insert graph into DeadlockEvents]    Script Date: 06/20/2012 12:22:55 ******/ 
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1) EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Insert graph into DeadlockEvents', 
  @step_id=1, 
  @cmdexec_success_code=0, 
  @on_success_action=3, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'INSERT INTO dba..DeadlockEvents(DeadlockGraph) 
                VALUES (N''$(ESCAPE_SQUOTE(WMI(TextData)))'')', 
  @database_name=N'DBA', 
  @flags=0 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
/****** Object:  Step [create xdl file]    Script Date: 06/20/2012 12:22:55 ******/ 
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 2) EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'create xdl file', 
  @step_id=2, 
  @cmdexec_success_code=0, 
  @on_success_action=1, 
  @on_success_step_id=0, 
  @on_fail_action=2, 
  @on_fail_step_id=0, 
  @retry_attempts=0, 
  @retry_interval=0, 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'DECLARE @FileName varchar(50), 
        @bcpCommand varchar(2000) 
 
SET @FileName = ''D:\dba\temp\xmlfile.xdl'' 
--SET @FileName = ''D:\dba\temp\deadlock ''+rtrim(CONVERT(char(25),GETDATE()))+''.xdl'' 
 
SET @bcpCommand = ''bcp "SELECT TOP 1 [DeadlockGraph].query(''''/TextData/deadlock-list'''') FROM [DBA].[dbo].[DeadlockEvents] ORDER BY Id DESC" queryout "'' 
SET @bcpCommand = @bcpCommand + @FileName + ''" -T -c -q'' 
 
EXEC master..xp_cmdshell @bcpCommand 
 
EXEC msdb.dbo.sp_send_dbmail 
    --@profile_name = ''profile@xyc.com'', 
    @recipients = ''dba@xyc.com;xyz@xyc.com'', 
    @query = ''exec master..usp_getdeadlockinfo'', 
    @subject = ''Please find the attached Dead Lock Reports'', 
    --@body_format = ''HTML'', 
    @attach_query_result_as_file = 1,  
    @query_result_header = 0, 
    @query_attachment_filename = ''DeadLock.xml'', 
    @file_attachments  = ''D:\dba\temp\xmlfile.xdl'', 
    @query_no_truncate = 1, 
    @query_result_width = 32767, 
    @exclude_query_output = 1', 
  @database_name=N'master', 
  @flags=0 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave 
QuitWithRollback: 
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 
 
GO 
àImport the SQL Server Error Log into a Table: 
 
CREATE PROC sp_import_errorlog 
( 
        @log_name sysname, 
        @log_number int = 0, 
        @overwrite bit = 0 
) 
AS 
/************************************************************************************************* 
Purpose:       To import the SQL Server error log into a table, so that it can be queried 
 
Written by:    Anand Mahendra 
         
Tested on:     SQL Server 2000 
 
Limitation:    With error messages spanning more than one line only the first line is included in the table 
 
Email:         anandbox@sify.com 
 
Example 1:     To import the current error log to table myerrorlog 
               EXEC sp_import_errorlog 'myerrorlog' 
 
Example 2:     To import the current error log to table myerrorlog, and overwrite the table 
               'myerrorlog' if it already exists 
               EXEC sp_import_errorlog 'myerrorlog', @overwrite = 1 
 
Example 3:     To import the previous error log to table myerrorlog 
               EXEC sp_import_errorlog 'myerrorlog', 1 
 
Example 4:     To import the second previous error log to table myerrorlog 
               EXEC sp_import_errorlog 'myerrorlog', 2 
 
*************************************************************************************************/ 
 
BEGIN 
        SET NOCOUNT ON 
         
        DECLARE @sql varchar(500) --Holds to SQL needed to create columns from error log 
 
        IF (SELECT OBJECT_ID(@log_name,'U')) IS NOT NULL 
               BEGIN 
                       IF @overwrite = 0 
                               BEGIN 
                                      RAISERROR('Table already exists. Specify another name or pass 1 to @overwrite parameter',18,1) 
                                      RETURN -1 
                               END 
                       ELSE 
                               BEGIN 
                                      EXEC('DROP TABLE ' + @log_name) 
                               END 
               END 
 
         
        --Temp table to hold the output of sp_readerrorlog 
        CREATE TABLE #errlog 
        ( 
               err varchar(1000), 
               controw tinyint 
        ) 
 
        --Populating the temp table using sp_readerrorlog 
        INSERT #errlog  
        EXEC sp_readerrorlog @log_number 
 
        --This will remove the header from the errolog 
        SET ROWCOUNT 4 
        DELETE #errlog 
        SET ROWCOUNT 0 
 
         
        SET @sql =     'SELECT  
                               CONVERT(DATETIME,LEFT(err,23)) [Date],  
                               SUBSTRING(err,24,10) [spid],  
                               RIGHT(err,LEN(err) - 33) [Message],  
                               controw  
                       INTO ' + QUOTENAME(@log_name) +  
                       ' FROM #errlog ' +  
                       'WHERE controw = 0' 
         
        --Creates the table with the columns Date, spid, message and controw 
        EXEC (@sql)     
         
        --Dropping the temporary table 
        DROP TABLE #errlog 
         
        SET NOCOUNT OFF 
PRINT 'Error log successfully imported to table: ' + @log_name 
END 

àDisplay CPU Information: 

'Objective : TO display the CPU information on any windows server. 
'Created by : MAK 
'Created date: Apr 1, 2004 
'Save this code as proc.vbs 
'Usage: cscript proc.vbs ServerName 
'Example: cscript proc.vbs Myserver 
' Result 
'Computer,Architecture,Caption,Manufacturer,Cpu#,CpuStatus,CurrentClockSpeed,MaxClockSpeed,LoadPercentage 
'MyServer,0,x86 Family 6 Model 8 Stepping 8,CPU0,GenuineIntel,1,998,998,16 
 
Set objArgs = WScript.Arguments 
Computer=objArgs(0) 
Set procset = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & Computer & "\root\cimv2").InstancesOf ("Win32_Processor") 
 
Wscript.echo "Computer" & ","& "Architecture" & ","& "Caption"  & ","& "Manufacturer" & ","& "Cpu#" &","&"CpuStatus" & ","& "CurrentClockSpeed" & ","& "MaxClockSpeed" & ","& "LoadPercentage" 
 
for each System in ProcSet 
Wscript.echo Computer & ","& system.Architecture & ","& system.Caption  & ","& system.deviceid &","& system.Manufacturer & ","& system.CpuStatus & ","& system.CurrentClockSpeed & ","& system.MaxClockSpeed & ","& system.LoadPercentage 
next 

Finding all sql server enabled & running jobs: 

SELECT database_name, name AS JobName, step_name AS JobStepName,  

enabled AS IsEnabled, description, command 

FROM msdb.dbo.sysjobs SJ  

JOIN msdb.dbo.sysjobsteps SJS  

ON SJ.job_id = SJS.job_id 

WHERE SJ.enabled = 1   --1 for enabled and 0 for disabled 

 

àFinding jobs and owners in SQL Server: 

SELECT SJ.name AS JobName,SL.name AS Owner 

FROM msdb..sysjobs SJ 

LEFT JOIN master.sys.syslogins SL 

ON SJ.owner_sid = SL.sid 

 

àBackup all systemdb (Masster, model, msdb): 

DECLARE @DB VARCHAR(20) 

DECLARE @BkpFName VARCHAR(100) 

DECLARE @BkpFDate VARCHAR(50) 

  

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','') 

  

DECLARE Cursor_DBs CURSOR FOR 

  

SELECT name 

FROM master.sys.DATABASES 

WHERE name IN ('master','model','msdb') 

  

OPEN Cursor_DBs 

FETCH NEXT FROM Cursor_DBs INTO @DB 

  

WHILE @@FETCH_STATUS = 0 

  

BEGIN 

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak' 

BACKUP DATABASE @DB TO DISK = @BkpFName WITH COMPRESSION 

FETCH NEXT FROM Cursor_DBs INTO @DB 

END 

  

CLOSE Cursor_DBs 

DEALLOCATE Cursor_DBs 

àBackup all Userdb : 

DECLARE @DB VARCHAR(20) 

DECLARE @BkpFName VARCHAR(100) 

DECLARE @BkpFDate VARCHAR(50) 

  

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','') 

  

DECLARE Cursor_DBs CURSOR FOR 

  

SELECT name 

FROM master.sys.DATABASES 

WHERE name NOT IN ('master','model','msdb', 'tempdb') 

  

OPEN Cursor_DBs   

FETCH NEXT FROM Cursor_DBs INTO @DB   

  

WHILE @@FETCH_STATUS = 0   

  

BEGIN   

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'  

BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION 

FETCH NEXT FROM Cursor_DBs INTO @DB   

END   

  

CLOSE Cursor_DBs   

DEALLOCATE Cursor_DBs 

 

àBackup all systemdb & Userdb's: 

DECLARE @DB VARCHAR(20) 

DECLARE @BkpFName VARCHAR(100) 

DECLARE @BkpFDate VARCHAR(50) 

  

SELECT @BkpFDate = REPLACE(CONVERT(VARCHAR(50), GETDATE(), 102),'.','') + REPLACE(CONVERT(VARCHAR(50), GETDATE(), 108),':','') 

  

DECLARE Cursor_DBs CURSOR FOR 

  

SELECT name 

FROM master.sys.DATABASES 

WHERE name != 'tempdb' 

  

OPEN Cursor_DBs   

FETCH NEXT FROM Cursor_DBs INTO @DB   

  

WHILE @@FETCH_STATUS = 0   

  

BEGIN   

SET @BkpFName = 'C:\BackupFolder\' + @DB + '_FullBackup_' + @BkpFDate + '.bak'  

BACKUP DATABASE @DB TO DISK =  @BkpFName  WITH COMPRESSION 

FETCH NEXT FROM Cursor_DBs INTO @DB   

END   

  

CLOSE Cursor_DBs   

DEALLOCATE Cursor_DBs 

 

àThis query shows the local IP as well as the listening Port number 

Select distinct local_net_address, local_tcp_port from sys.dm_exec_connections where local_net_address is not null 

 

1)We can use this for more than one Instance: 

USE MASTER
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO 

Who is consuming CPU on my SQL Server? 

--This is my first shot for this requirement using DMVs: 

 

WITH DB_CPU_Statistics 

AS 

(SELECT pa.DatabaseID, DB_NAME(pa.DatabaseID) AS [Database Name], SUM(qs.total_worker_time/1000) AS [CPU_Time_Ms] 

FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK) 

 CROSS APPLY (SELECT CONVERT(INT, value) AS [DatabaseID] 

FROM sys.dm_exec_plan_attributes(qs.plan_handle) 

WHERE attribute = N'dbid') AS pa 

GROUP BY DatabaseID) 

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Ranking], 

[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)], 

CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent] 

FROM DB_CPU_Statistics 

WHERE DatabaseID <> 32767 -- ResourceDB 

ORDER BY [CPU Ranking] OPTION (RECOMPILE); 

Who are the sysadmins in this sql server? 

04/23/2012 by Marlon Ribunal | 10 Comments 

Here's a quick query that you can run to find out the users with sysadmin fixed server role. Sysadmins have a complete control of your server. So, it is very important to know who these people are. 

Just to give you an example on why it is very important to check who's who in your server from time to time, Team Shatter has recently published an advisory on Privilege Escalation Via Internal SQL Injection In RESTORE DATABASE Command. 

According to the advisory, there is a vulnerability in the RESTORE DATABASE command that allows malicious users to run SQL codes via internal sql injection. This vulnerability can be exploited by users with CREATE DATABASE permission. 

A rogue user can find his way to taking control over your databases by using a backdoor such as the vulnerability described above. Imagine the same user was able to add himself to a server-wide role such as the sysadmin. 

So, going back to the query, here is what it looks like: 

? 

1 

2 

3 

4 

5 

6 

7 

8 

9 

10 

11 

12 

13 

14 

15 

16 

17 

USE master 

GO 

  

SELECT  p.name AS [loginname] , 

        p.type , 

        p.type_desc , 

        p.is_disabled, 

        CONVERT(VARCHAR(10),p.create_date ,101) AS [created], 

        CONVERT(VARCHAR(10),p.modify_date , 101) AS [update] 

FROM    sys.server_principals p 

        JOIN sys.syslogins s ON p.sid = s.sid 

WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP') 

        -- Logins that are not process logins 

        AND p.name NOT LIKE '##%' 

        -- Logins that are sysadmins 

        AND s.sysadmin = 1 

GO 

And that query should give us something like this: 

 

There is a quicker way to do that, though. You'll achieve a similar result by executing the system stored procedure sp_helpsrvrolemember: 

? 

1 

 EXEC sp_helpsrvrolemember 'sysadmin' 

And, you'll get a result that looks like this: 

 

Of course, you can use any of the other server roles with sp_helpsrvrolemember as parameters. For example, if you want to look for users with the database creator role, you use the variable 'dbcreator' instead of 'sysadmin'. 

Server roles are listed somewhere in one of the system tables in the master database. But there's already a system stored procedure for that as well –  sp_helpsrvrole. You don't have to dig in and search for them yourself. SQL Server gives you most of the stored procedures you'll likely need out of the box. 

? 

1 

 EXEC sp_helpsrvrole 

Executing that will give you the following list: 

 

Update from the comments: 

Here's a good one from Eric Russell (see comment below): to return what accounts are members of a domain group, we do this: 

? 

1 

 xp_logininfo 'DEVCOMPUTER\SQLAdmins', 'members'; 

*suppose SQLAdmins is a domain group 

And, it does exactly what it is meant to do – returns the members of the group like this: 

 

 

--------- 

Problem
Many shops do full backups of their databases as part of a job within SQL Agent, typically as part of a maintenance plan. This job will often times do all database backups in secession. As such, while you may know how long the job takes, you may not know how long any one particular database takes to have a full backup taken. When deploying changes it is advised to take a full backup of a database prior to deploying the change and a common question faced will be "how long will it take?". 

Solution
The information is readily available inside the msdb database, making the solution as easy as a few lines of T-SQL. 

The T-SQL provided below allows for you to input the name of a database if desired. I have also added a line that will filter the results, limiting your view to only the databases that are currently listed in the master.dbo.sysdatabases table. If you comment out that line of code you will return information on the last time a database was backed up on the instance, regardless if the database is currently listed in master.dbo.sysdatabases. 

Returning the details 

Here is the T-SQL  

DECLARE @dbname sysname 

SET @dbname = NULL --set this to be whatever dbname you want 

SELECT bup.user_name AS [User], 

 bup.database_name AS [Database], 

 bup.server_name AS [Server], 

 bup.backup_start_date AS [Backup Started], 

 bup.backup_finish_date AS [Backup Finished] 

 ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '  

 + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, ' 

 + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' 

 AS [Total Time] 

FROM msdb.dbo.backupset bup 

WHERE bup.backup_set_id IN 

  (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset 

  WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all 

  AND type = 'D' --only interested in the time of last full backup 

  GROUP BY database_name)  

/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */ 

AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases) 

ORDER BY bup.database_name 

The script will return the following result set:  

Column Name 

Description 

User 

The name of the user that issued the BACKUP DATABASE command. 

Database 

The name of the database. 

Server 

The name of the server instance. 

Backup Started 

The time at which the backup was started. 

Backup Finished 

The time at which the backup was completed. 

Total Time 

The total amount of time it took to complete the backup for that database. 

Images 

Here is a screenshot of a sample result set returned by the script. 

 

If you want to get a list of all backups and not just the most recent you can issue the following: 

DECLARE @dbname sysname 

SET @dbname = NULL --set this to be whatever dbname you want 

SELECT bup.user_name AS [User], 

 bup.database_name AS [Database], 

 bup.server_name AS [Server], 

 bup.backup_start_date AS [Backup Started], 

 bup.backup_finish_date AS [Backup Finished] 

 ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '  

 + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, ' 

 + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds' 

 AS [Total Time] 

FROM msdb.dbo.backupset bup 

/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */ 

WHERE bup.database_name IN (SELECT name FROM master.dbo.sysdatabases) 

ORDER BY bup.database_name 

Next Steps 

  • Take the above code and execute against your instance, making certain to insert the correct database name if you want to filter it to a specific database 

Backup: know that you're probably running regular database backups. Most people do. Some don't, but that's a different discussion. There's an old saying "Your data is only as good as your last backup." That's very true. But, there's a little known corollary to this: "Your backups are only as good as your last restore." It's great that you're backing up your databases, but you need to do more. You need to test your backups.  

The ultimate test for any backup is a restore to a server, but there are a few other things you can do as well. Let's go over them in the order of their importance in validating that the backups you have are good.  

CHECKSUM  

The first thing you can do to ensure you have a good backup is to include CHECKSUM in the WITH clause of your backups, like this:  

BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'c:\bu\MovieManagement.bak'
WITH INIT, CHECKSUM;  

The CHECKSUM is a mathematical construct of a fixed length that gets generated from the page of data. The math will always arrive at the same number for the same page of data. The CHECKSUM value gets written with the page during the backup process and it validates that the page is intact prior to writing it to the backup. This is a good way to validate the media as you do the backup. Then, you can use the CHECKSUMs later during a RESTORE VERIFYONLY operation (see below) to validate the backup media is intact.  

This does come with additional cost. It adds overhead, so if you have a very large backup that currently takes a long time, it'll take longer. But, if you have a very large backup that takes a long time, wouldn't you want to know that the pages being written to disk are actually intact?  

VERIFYONLY  

Another way you can ensure that your backups are good is to use RESTORE VERIFYONLY like this:  

RESTORE VERIFYONLY
FROM DISK = 'c:\bu\MovieManagement.bak';  

VERIFYONLY will process the backup and perform several checks. First, it can find and read the backup file. Believe it or not, that's a good first step. Far too many people will assume that they can restore a file that is either incomplete or inaccessible. It also walks through the CHECKSUM information if you used CHECKSUM in the backup (see above). This will validate that the backup media is in place. That can be a costly operation if the backup file is very large, so I don't know that I'd run this check from my production system if I could help it. Finally, VERIFYONLY checks some of the header information in the backup. It doesn't check all the header information, so it's still possible for a backup to pass VERIFYONLY but still not restore successfully to the server. Which brings up the best way to validate your backups, RESTORE.  

RESTORE  

Like I said at the beginning of this, the best way to know that your backup is intact is to run a RESTORE. It's very much like taking off and nuking the site from orbit, it's the only way to be sure. If you successfully run a RESTORE of a backup, then you know that backup is intact. Yes, other things might happen to the file later, but for a moment in time, you've validated that your backup and storage mechanisms are working. By the way, notice that all these checks are only concerned with the backup structure and the database structure. What if what you're backing up is junk?  

CHECKDB  

You might think that running CHECKDB should be done first, prior to running a backup. And, in most circumstances, you'd be right. However, it is possible to find yourself in a situation where DBCC CHECKDB is extremely expensive. You might not have a big enough maintenance window to run the CHECKDB and get a backup completed. In that case, run the backup, it's the more important operation. But, you can still validate your database. Because the backup is an exact copy of the database, if there are corruption issues (not found by the CHECKSUM) they'll be backed up as well. Running a RESTORE and then running CHECKDB will enable you to ensure that your backup is intact and that the data in the backup is intact as well.  

Summary  

You know that you need to protect the information for your organization. You're already running your backups. Now take the next step and ensure that those backups are good. Test them. Just remember, that the very best test is a complete restore.  

  1. To findout server restart time: 

Method 1: sys.dm_os_sys_info 

This DMV, sys.dm_os_sys_info, contains a column that holds the startup time for SQL Server. You can run the following command to find the start time. 

SELECT sqlserver_start_time FROM sys.dm_os_sys_info 

 

 

SQL Server User Connection count on the databases: to find the db's are in use: 

SELECT @@ServerName AS server 

 ,NAME AS dbname 

 ,COUNT(STATUS) AS number_of_connections 

 ,GETDATE() AS timestamp 

FROM sys.databases sd 

LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid 

WHERE database_id NOT BETWEEN 1 AND 4 

GROUP BY NAME 

 

Another one: 

SELECT @@ServerName AS SERVER 

 ,NAME 

 ,login_time 

 ,last_batch 

 ,getdate() AS DATE 

 ,STATUS 

 ,hostname 

 ,program_name 

 ,nt_username 

 ,loginame 

FROM sys.databases d 

LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid 

WHERE database_id NOT BETWEEN 0 

  AND 4 

 AND loginame IS NOT NULL 

 

  



 
SQL Server DBA Notes:

SSL Certificate: Secure Sockets Layer (SSL) encryption:
The Secure Sockets Layer (SSL) can be used to encrypt data transferred on network between our SQL Server instance and a client application.
Coming to installation:
- Need to go to MM console (Microsoft management: run --> MMC )
- On the File menu, click Add/Remove Snap-in...
- Select Certificates, click Add.
- computer account --> local computer --> finish.
--Configuring SQL Server to accept encrypted connections:
- Expand SQL Server Network Configuration and right-click on Protocols for <YourMSSQLServer>, then click Properties.
- On the Certificate tab, select the certificate you would like to use.
- On the Flags tab, select Yes in the ForceEncryption box, then click OK.
- Restart the SQL Server service.

SQL Server 2012:

Editions:
-------------
Enterprise edition
Satndard edition
Devoloper edition
Web edition
Express editon


-- CAPACITY PLANNING:

Whenever we need to do capacity planing, we need to consider few things like
- Backup space : how much backup space abilbale in the disks
- Snapshot space: If the DB in TBs at that time it will be useful to mitigate the time
- How much Archive space avilable
- ReIndex (Including multiple db's reindexing at the same time)
- Checkdb
- How much TEMPDB action: how much it will grow need to consider
- Enough spindles to perform well

----------------------------------
INSTALLATION ON SQL SERVER 2012 WITH POWER SHELL - GO TO ADMIN TOOLS - POWERSHELL

-- GET ALL THE COMPONETS AVILABLE IN THE SERVER:

GET-WINDOWSFETURE
----------------
-- STATISTICS:
-----------------
sTATISTICS are used by SQL Server to decide which index to use when you perform a query.

- Statistics are needs to maintain to be effective.

Statistics update Modes:
- Synchronous forces queries to wait when stats are out of date
- Asynchronous allows queries to continue with old stats and builds new ones in the background.

Query to check the options are enable in the db:
--SELECT NAME, IS_AUTO-UPDATE-STATS_ON, IS_AUTO_UPDATE_STATS_ASYNC_ON
FROM SYS.DATABASES

===============
Resticted Access:

- Single User: Allows only one User to access the DB
- Resticted User: Allows only db_owner role, sysadmin, or dbcreator
- Multi User: Allows all users

DMV's:
------
sys.dm_exec_sessions - Sessions in SQL Server
sys.dm_exec_connections - Connections to SQL Server
sys.dm_db_index_usage_stats - Seeks, scans, lookups per index
sys.dm_io_virtual_file_stats - IO statistics for databases and log files
sys.dm_tran_active_transactions - Transaction state for an instance of SQL Server
sys.dm_exec_query_plan - Returns query plan
sys.dm_os_wait_stats - Returns information what resources SQL is waiting on
sys.dm_os_performance_counters - Returns performance monitor counters related to SQL Server
-------------------------------------
DBCC: DBCC (Database consistency checker) act as Database console commands for SQL Server to check database consistency.

- DBCC CHECKDB :
CHECKS ALLOCATION AND CONSISTENCY errors
--DBCC CHECKCATALOG
Used to perform consistency check for a table/catalog for a particular database.
--DBCC CHECKALLOC
Used to check page usage and allocation of a database.
--DBCC CHECKTABLE(tablename)
Used to verify data page integrity, page offsets, data page links, page pointers, index pointers, index sort order for a specified table.

- DBCC SQLPERF (LOGPSACE) -
Displays T-log size and space used % for all databases
- DBCC INPUTBUFFER(sessionid)
Used to get the last statement sent by session to SQL server.
-DBCC HELP(<dbcc command>)
Displays the syntax of a DBCC command. DBCC HELP ('?')
To know syntax of DBCC SQLPERF, execute DBCC HELP ('SQLPERF')

- DBCC SHRINKDATABASE ('DBNAME', 10): DONT USE BECAUSE OF FAGMENTAION HAPPEND IN THE Db
Used to shrink data and log files for a particular database.
It release the free space to OS
- DBCC SHRINKFILE ('DBNAME', 5) : Used to shrink individual database files.
- DBCC LOGINFO
Displays virtual log file information contained in a T-log file.
- DBCC SHOWFILESTATS
Display Extent information for a database.
- DBCC TRACEON/TRACEOFF: USED TO ENABLE TRACING/ DISABLE TRACING.
- DBCC TRACESTATUS
Displays the trace status.
- DBCC USEROPTIONS
Displays the active SET options for a particular database
- DBCC MEMORYSTATUS
Displays SQL Server memory allocation status.
- DBCC Opentran
Displays information about oldest active transaction and oldest distributed and non-distributed replication transaction


CLUSTER: A Group of Servers can act as a sigle server.
----------------------
CLUSTER NODE : A Cluster node is a server with in the cluster and it has windows server and the cluster service installed.

CLUSTER Service: The cluster service manages all the activities that is specific to the cluster.
One instance of cluster service runs on each node in the cluster.
the Cluster service does the activities like
- Manages Cluster objects and configurations
- Manages the local restart policy
- Perform failover operations

cluster Group: A Cluster Group is a collection of logically grouped cluster resorces. It may contain cluster-aware application services
like SQL Server Group, file server etc.

Private network is used for cluster communication(LooksAlive and IsAlive checks) and Public network is used for client applications to connect.
When the private network goes down cluster communications will carry on through the public network. Loss of a private network does not initiate a failover.
The loss of the public network will initiate a failover since this is the network clients connect through.

Quorum is a count of voting members— a quorum is a way of taking attendance of cluster members who are present.
The cluster uses a quorum to determine who should be online.


When an instance of SQL Server is running in a Clustered Environment, Microsoft Cluster Service performs two checks to confirm if the SQL Server resource is still available.
One is LooksAlive and the other one is IsAlive check.

LooksAlive is a basic check in which the Cluster service queries the Windows Service Control Manager to check if the SQL Server service is still running.
By default this check happens every 5 seconds.
During IsAlive check the Cluster Service connects to the SQL Server instance with the help of c:\windows\system32\sqsrvres.dll and
runs SELECT @@SERVERNAME against the instance.
This check does not check if the user databases are online or not.
It just checks the SQL Server instance availability. This check happens every 60 seconds by default.

AlwaysOn:
---------
- WFC : Windows failover cluster required
- the SQL Servers what ever needs to participate alwaysOn that needs add in WFC
- configuration manager - AlwaysOn feture needs to enable
- DB fulll recovery, Recent full backup  needs to complete


Performance counters:
-------------------------
- Memory: avilable in bites counter indicates how many bytes of memory currently avilable for use by processes
High means good heath
low means shortage of memory

- Memory: pages\sec:
high: indicates execive paging
low: good health

- buffer manager: beffer cache hit ratio: high > 90 - good health,
- Total Server Memory is almost same as Target Server Memory - Good Health
Total Server Memory is much smaller than Target Server Memory
– There is a Memory Pressure or Max Server Memory is set to too low.

Page Life Expectancy:  the number of seconds the average page of data has been in the buffer pool.
High: Good Health
Low: Memory Pressure or Max Server Memory is not allocated properly



at No comments:

Wednesday, September 2, 2020

To Add New Database into Always-on Group in Existed AGgroup using T-SQL command

1. To connect Primary Replica Instance and copy the below script and paste into the Query window. To change the below are the changes as per your requirement.
PrimaryDBinstancename,
SQLPort
AGname
SecondaryDBinstancename.




--- Example: T-SQL code
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. in the query e
:Connect PrimaryDBinstance,1433
USE [master]
GO
ALTER AVAILABILITY GROUP [AGName]
MODIFY REPLICA ON N'SecondaryDBInstance' WITH (SEEDING_MODE = AUTOMATIC)
GO
USE [master]
GO
ALTER AVAILABILITY GROUP [AGName]
ADD DATABASE [Test];
GO
:Connect SecondaryDBInstance
ALTER AVAILABILITY GROUP [AGName] GRANT CREATE ANY DATABASE;
GO
GO


at No comments:

Database Mirroring login attempt by user 'Domain\Hostname$.' failed with error: 'Connection handshake failed. The login 'Domain\Hostname$' does not have CONNECT permission on the endpoint. State 84.'. [SERVER: XX.XXX.XXX.XX]

Solution:
1. Check whether the SQL server instance is running on a Service account or not.
2. if it is not running make sure to set the service account for all SQL server instances which is a part of Always on group.
3. GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [Domain\user] for all SQL server instances.
4. Make sure to give Sysadmin Privileges to SQL server Service account for all SQL server instances which is a part of Always on group.
at No comments:

Saturday, August 29, 2020

Always on group is not failover from Primary to Secondary replica either with in site or different site (Error:Failed to move a Windows Server Failover Clustering (WSFC) group to the local node (Error code 5016))

Error message:
Failed to move a Windows Server Failover Clustering (WSFC) group to the local node (Error code 5016).  The WSFC service may not be running or may not be accessible in its current state, or the specified cluster group or node handle is invalid.  For information about this error code, see "System Error Codes" in the Windows Development documentation.
Failed to designate the local availability replica of availability group 'AG_Group' as the primary replica.  The operation encountered SQL Server error 41018 and has been terminated.  Check the preceding error and the SQL Server error log for more details about the error and corrective actions. (Microsoft SQL Server, Error: 41018)

Solution details:
1. Go to --> Failover Cluster manager --> Select the particular AG group --> Right-click the listener name--> properties-->Advanced policies. There you'll see the possible owners list.
if the specified server is not part of the owner list. Make sure to select the server name in the owner list
then you will try to failover the AG from Primary to Secondary using SSMS failvoer script.
at No comments:

Tuesday, August 11, 2020

Disk Space report through HTML report

CREATE proc [dbo].[USP_Send_DiskSpace]
--
(
 @To  varchar(200) ,  
 @CRITICAL int  = 10 -- if the freespace(%) is less than @alertvalue, it will send message
)
as
Begin
DECLARE  @HOSTNAME  VARCHAR(20),
    @HEAD  VARCHAR(100),
    @BGCOLOR VARCHAR(50),
    @REC  VARCHAR(50),
    @PRIORITY VARCHAR(10),
    @FREE VARCHAR(20),
    @TOTAL VARCHAR(20),
    @FREE_PER VARCHAR(20),
    @CHART VARCHAR(2000),
    @HTML VARCHAR(MAX),
    @HTMLTEMP VARCHAR(MAX),
    @TITLE VARCHAR(100),
    @DRIVE VARCHAR(100),
    @SQL VARCHAR(MAX)
CREATE TABLE #MOUNTVOL (COL1 VARCHAR(500))
INSERT INTO #MOUNTVOL
EXEC XP_CMDSHELL 'MOUNTVOL'
DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%VOLUME%'
DELETE #MOUNTVOL WHERE COL1 IS NULL
DELETE #MOUNTVOL WHERE COL1 NOT LIKE '%:%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%MOUNTVOL%'
DELETE #MOUNTVOL WHERE COL1 LIKE '%RECYCLE%'
SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL
CREATE TABLE #DRIVES
 (
  DRIVE VARCHAR(500),
  INFO VARCHAR(80)
 )
DECLARE CUR CURSOR FOR SELECT LTRIM(RTRIM(COL1)) FROM #MOUNTVOL
OPEN CUR
FETCH NEXT FROM CUR INTO @DRIVE
WHILE @@FETCH_STATUS=0
BEGIN
    SET @SQL = 'EXEC XP_CMDSHELL ''FSUTIL VOLUME DISKFREE ' + @DRIVE +''''
  
  INSERT #DRIVES
   (
    INFO
   )
  EXEC (@SQL)
  UPDATE #DRIVES
  SET DRIVE = @DRIVE
  WHERE DRIVE IS NULL
        
FETCH NEXT FROM CUR INTO @DRIVE
END        
CLOSE CUR        
DEALLOCATE CUR      
-- SHOW THE EXPECTED OUTPUT
SELECT  DRIVE,
  SUM(CASE WHEN INFO LIKE 'TOTAL # OF BYTES             : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TOTALSIZE,
  SUM(CASE WHEN INFO LIKE 'TOTAL # OF FREE BYTES        : %' THEN CAST(REPLACE(SUBSTRING(INFO, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FREESPACE
INTO #DISKSPACE FROM  (
   SELECT DRIVE,
    INFO
   FROM #DRIVES
   WHERE INFO LIKE 'TOTAL # OF %'
  ) AS D
GROUP BY DRIVE
ORDER BY DRIVE


SET @TITLE = 'DISK SPACE REPROT : '+ @@SERVERNAME
SET @HTML = '<HTML><TITLE>'+@TITLE+'</TITLE>
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=2>
 <TR BGCOLOR=#0070C0 ALIGN=CENTER STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:WHITE''>
  <TD WIDTH=40><B>DRIVE</B></TD>
  <TD WIDTH=250><B>TOTAL</B></TD>
  <TD WIDTH=150><B>FREE SPACE</B></TD>
  <TD WIDTH=150><B>FREE PRECENTAGE</B></TD>
</TR>'
DECLARE RECORDS CURSOR
FOR SELECT CAST(DRIVE AS VARCHAR(100)) AS 'DRIVE', CAST(FREESPACE/1024/1024 AS VARCHAR(10)) AS 'FREE',CAST(TOTALSIZE/1024/1024 AS VARCHAR(10)) AS 'TOTAL',
CONVERT(VARCHAR(2000),'<TABLE BORDER=0 ><TR><TD BORDER=0 BGCOLOR='+ CASE WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 < @CRITICAL 
    THEN 'RED'
WHEN ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 > 70 
    THEN '66CC00'
   ELSE 
    '0033FF'
   END +'><IMG SRC=''/GIFS/S.GIF'' WIDTH='+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0*2 AS INT) AS CHAR(10) )+' HEIGHT=5></TD>
     <TD><FONT SIZE=1>'+CAST(CAST(((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0 AS INT) AS CHAR(10) )+'%</FONT></TD></TR></TABLE>') AS 'CHART'
 FROM #DISKSPACE ORDER BY ((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0
OPEN RECORDS
FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART
  
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @HTMLTEMP =
  '<TR BORDER=0 BGCOLOR="#E8E8E8" STYLE=''FONT-SIZE:8.0PT;FONT-FAMILY:"TAHOMA","SANS-SERIF";COLOR:#0F243E''>
  <TD ALIGN = CENTER>'+@DRIVE+'</TD>
  <TD ALIGN=CENTER>'+@TOTAL+'</TD>
  <TD ALIGN=CENTER>'+@FREE+'</TD>
  <TD  VALIGN=MIDDLE>'+@CHART+'</TD>
  </TR>'
  
  SET @HTML = @HTML + @HTMLTEMP
  
 FETCH NEXT FROM RECORDS INTO @DRIVE , @FREE, @TOTAL, @CHART
END
CLOSE RECORDS
DEALLOCATE RECORDS

SET @HTML = @HTML + '</TABLE><BR>
<P CLASS=MSONORMAL><SPAN STYLE=''FONT-SIZE:10.0PT;''COLOR:#1F497D''><B>THANKS,</B></SPAN></P>
<P CLASS=MSONORMAL><SPAN STYLE=''FONT-SIZE:10.0PT;''COLOR:#1F497D''><B>DBA TEAM</B></SPAN></P>
</HTML>'
--PRINT
 PRINT @HTML
--save data
if(object_id('DBA.dbo.diskdrive_stats') is null)
Begin
 create table DBA.dbo.diskdrive_stats ( 
  Drive varchar(100) ,  
  FreeSpace float null, 
  TotalSize float null,
  Free_per float,
  date_time datetime) 
 
insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
 select Drive,convert(float,freespace),convert(float,totalsize),
   convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE
 --insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
 --select *,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0,getdate() from #DISKSPACE
End
 Else
Begin
 insert into DBA.dbo.diskdrive_stats (Drive,Freespace,TotalSize,Free_Per,date_time)
 select Drive,convert(float,freespace),convert(float,totalsize),
   convert(float,((FREESPACE/1024/1024)/((TOTALSIZE/1024/1024)*1.0))*100.0),getdate() from #DISKSPACE
End 

--############################Send Mail#############################
set @head = '<RED> Disk Space report from SQL Server : '+@@servername
--SELECT * FROM #DISKSPACE
IF EXISTS(SELECT * FROM #DISKSPACE WHERE CAST((FREESPACE/(TOTALSIZE*1.0))*100.0 AS INT) <= @CRITICAL)
 BEGIN
  SET @PRIORITY = 'HIGH'
  
  print @head
  exec msdb.dbo.sp_send_dbmail   
  @profile_name = 'SQLProfile',   
  @recipients = @To,  
  @subject = @head,
  @importance =  @Priority, 
  @body = @HTML,   
  @body_format = 'HTML'
 END 
 ELSE
 BEGIN 
  print''
 END

DROP TABLE #MOUNTVOL
DROP TABLE #DRIVES
DROP TABLE #DISKSPACE
END
at No comments:

SSAS cubes backups through Powershall script


###### Configuration ######
# FQDN of Analysis Services server. If no server name is specified then
# defaults to localhost. example: $server_name = "ssas1.microsoft.com"
$server_name = $null
# UNC path of share or on-disk location to which backups will be stored.
# Do not including trailing slash. If null then defaults to SSAS BackupDir
# example: $backup_location = "\\storage.microsoft.com\ssas-backup"
$backup_location = "C:\BACKUPS\SASDEV02"
# Array of databases that will be backed-up. If $null then all databases
# will be backed up.
$user_requested_databases = $null
# How long backups will be retained
$retention_period_in_days = 30
###### End Configuration ######
trap [Exception] {
                write-error $("TRAPPED: " + $_.Exception.GetType().FullName)
                write-error $("TRAPPED: " + $_.Exception.Message)
                if ($server) {
                                $server.disconnect()
                }
                exit 1
}
if ($server_name -eq $null) {
                $server_name = "SQLH023\SASDEV02"
}
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | out-null
$server = New-Object Microsoft.AnalysisServices.Server
$server.connect($server_name)
# Set the directory for backups to the server property
# "BackupDir" if it's not otherwise specified
if ($backup_location -eq $null) {
                $backup_location = ($server.get_ServerProperties() | Where {$_.Name -eq "BackupDir"}).Value}
elseif (!(Test-Path -path $backup_location)) {
                throw "Specified path ($backup_location) does not exist."
}
# Generate an array of databases to be backed up
$available_databases = ($server.get_Databases() | foreach {$_.Name})
if ($user_requested_databases -eq $null) {
                $databases = $available_databases}
else {
                $databases = $user_requested_databases.Split(",")
                # Check that all specified databases actually exist on the server.
                foreach ($database in $databases) {
                                if ($available_databases -notcontains $database) {
                                                throw "$database does not exist on specified server."
                                }
                }
}
foreach ($database in ($server.get_Databases() | Where {$databases -contains $_.Name})) {
                $directory_path = $backup_location + "\" + $database.Name
                if (!(Test-Path -Path $directory_path)) {
                                New-Item $directory_path -type directory | out-null
                }
                [string] $timestamp = date
                $timestamp = $timestamp.Replace(':','').Replace('/','-').Replace(' ','-')
                $database.Backup("$directory_path\$database-$timestamp.abf")
                # Cleanup Old Backups
                Get-ChildItem $directory_path | where {
                                $_.LastWriteTime -le (Get-Date).AddDays(-$retention_period_in_days)
                } | remove-item
}
$server.disconnect()
at No comments:

Scripting Out the Logins, Server Role Assignments, and Server Permissions

-- Scripting Out the Logins, Server Role Assignments, and Server Permissions
-- ************************************************************************************************************************
-- CRITICAL NOTE: You'll need to change your results to display more characters in the query result.
-- Under Tools –> Options –> Query Results –> SQL Server –> Results to Text to increase the maximum number of characters
-- returned to 8192 the maximum or to a number high enough to prevent the results being truncated.
-- ************************************************************************************************************************
SET NOCOUNT ON
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
      CASE
     WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = '
      + CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
     ELSE ' FROM WINDOWS WITH'
    END
    +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
  ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
  AND SP.name NOT LIKE '##%##'
  AND SP.name NOT LIKE 'NT AUTHORITY%'
  AND SP.name NOT LIKE 'NT SERVICE%'
  AND SP.name <> ('sa');
-- Scripting Out the Role Membership to Be Added
SELECT
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- Server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
 JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
 JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
  AND SL.name NOT LIKE '##%##'
  AND SL.name NOT LIKE 'NT AUTHORITY%'
  AND SL.name NOT LIKE 'NT SERVICE%'
  AND SL.name <> ('sa');

-- Scripting out the Permissions to Be Granted
SELECT
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
  THEN SrvPerm.state_desc
  ELSE 'GRANT'
 END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' +
 CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION'
  THEN ''
  ELSE ' WITH GRANT OPTION'
 END collate database_default AS [-- Server Level Permissions to Be Granted --]
FROM sys.server_permissions AS SrvPerm
 JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id
WHERE   SP.type IN ( 'S', 'U', 'G' )
  AND SP.name NOT LIKE '##%##'
  AND SP.name NOT LIKE 'NT AUTHORITY%'
  AND SP.name NOT LIKE 'NT SERVICE%'
  AND SP.name <> ('sa');
SET NOCOUNT OFF
at No comments:

To find Orphaned users in database and all databases


/*fix orphan users in one particular database */
declare @query varchar(1000)
declare @executequery cursor
set @executequery=cursor for
select ' sp_change_users_login  '+CHAR(39)+'update_one'+CHAR(39)
+','+CHAR(39)+name+CHAR(39)+','+CHAR(39)+name+CHAR(39)
from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
open @executequery
fetch next from @executequery into @query
while @@fetch_status=0
begin
 exec (@query)
 print (@query)
fetch next from @executequery into @query
end
close @executequery;
deallocate @executequery;
/*fix orphan users in all databases except system databases */

DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(2000)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM  sys.databases where database_id>4
and name not like '%master%'
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
                        
SELECT @Command ='
                            use '+ @DB_Name+';
                            declare @query varchar(1000)
                            declare @executequery cursor
                            set @executequery=cursor for
                            select '' sp_change_users_login ''+CHAR(39)+''update_one''+CHAR(39)+'',''+CHAR(39)+name+CHAR(39)+'',''+CHAR(39)+name+CHAR(39)
                            from sysusers
                            where issqluser = 1 and (sid is not null and sid <> 0x0) AND SUSER_SNAME(sid) IS NULL
                            open @executequery
                            fetch next from @executequery into @query
                            while @@fetch_status=0
                            begin
                             exec (@query)
                             print (@query)
                            fetch next from @executequery into @query
                            end
                            close @executequery;
                            deallocate @executequery;
                            go'
                           
                            print @Command
                         
  FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
at No comments:
Subscribe to: Posts (Atom)