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, is listening '
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.
?
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.
- 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
