SQL Server – Basic SQL Commands for day to day SQL DBA


===== Number of SQL Connections & login name ====

SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame

===== Check for database compatibility =====

ALTER DATABASE DB_Caching_SearchFacets SET COMPATIBILITY_LEVEL = 110
SELECT compatibility_level FROM sys.databases WHERE name = ‘DBName’

— Enabling SQL Agent notification – SQL Server 2005, 2008, 2008 R2

USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_properties
@email_save_in_sent_folder=1

EXEC master.dbo.xp_instance_regwrite
N’HKEY_LOCAL_MACHINE’
, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’
, N’UseDatabaseMail’
, N’REG_WORD’
, 1

EXEC master.dbo.xp_instance_regwrite
N’HKEY_LOCAL_MACHINE’
, N’SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent’
, N’DatabaseMailProfile’
, N’REG_SZ’
, N’01_mail’

=========================================================

/***Run this query and save the full results – who is doing what at this moment*****/

SELECT r.session_id
,r.scheduler_id
,r.status
,s.text
,r.start_time
,r.blocking_session_id
,r.wait_type
,r.wait_time
,r.last_wait_type
,r.wait_resource
,r.open_transaction_count
,r.cpu_time
,r.total_elapsed_time
,r.reads
,r.writes
,r.logical_reads
,r.percent_complete
,r.estimated_completion_time
,wt.*
FROM sys.dm_exec_requests r
LEFT OUTER JOIN sys.dm_os_waiting_tasks wt ON r.session_id = wt.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
ORDER BY r.session_id ;

=========================================================

=== Check orphaned users for all DBs ====

CREATE TABLE ##ORPHANUSER
(
DBNAME VARCHAR(100),
USERNAME VARCHAR(100),
CREATEDATE VARCHAR(100),
USERTYPE VARCHAR(100)
)

EXEC SP_MSFOREACHDB’ USE [?]
INSERT INTO ##ORPHANUSER
SELECT DB_NAME() DBNAME, NAME,CREATEDATE,
(CASE
WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ”SQL LOGIN”
WHEN ISNTGROUP = 1 THEN ”NT GROUP”
WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ”NT LOGIN”
END) [LOGIN TYPE] FROM sys.sysusers
WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND
SID NOT IN (SELECT SID FROM sys.syslogins)’

SELECT * FROM ##ORPHANUSER

DROP TABLE ##ORPHANUSER

=========================================================

—–When will the Backup Finish / Backup status & FInish—-

SELECT command, percent_complete,
‘elapsed’ = total_elapsed_time / 60000.0,
‘remaining’ = estimated_completion_time / 60000.0
FROM sys.dm_exec_requests
WHERE command like ‘BACKUP%’

=========================================================
—- Backup details for single database —-

SELECT TOP 50 s.database_name, m.physical_device_name, CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(100)) + ‘ ‘ + ‘Seconds’ TimeTaken, s.backup_start_date, CASE s.[type] WHEN ‘D’ THEN ‘Full’ WHEN ‘I’ THEN ‘Differential’
wHEN ‘L’ THEN ‘Transaction Log’ END AS BackupType, s.server_name, s.recovery_model FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id — WHERE s.database_name = DB_NAME() — Remove this line for all the ORDER BY backup_start_date DESC

=========================================================

–== Determine Locks held in database ==–

SELECT request_session_id AS [spid], DB_NAME(resource_database_id) AS [dbname],
CASE WHEN resource_type = ‘OBJECT’ THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_associated_entity_id = 0 THEN ‘n/a’
ELSE OBJECT_NAME(p.object_id) END AS [entity_name], index_id, resource_type AS [resource],
resource_description AS [description], request_mode AS mode, request_status AS [status]
FROM sys.dm_tran_locks t
LEFT OUTER JOIN sys.partitions p ON p.partition_id = t.resource_associated_entity_id
WHERE t.resource_type <> ‘DATABASE’;

=========================================================

/******* Find the SESSIONS that are currently blocking ******/

CREATE TABLE #who2
(SPID int NOT NULL,
[Status] varchar(100),
Login varchar(50) ,
HostName varchar(100),
[BlkBy] varchar(20) ,
DBName varchar(55) ,
Command varchar(256),
CPUTime varchar(20),
DiskIO varchar(20),
LastBatch varchar(100),
ProgramName varchar(100),
SPID2 varchar(20),
RequestID varchar(20)
)
INSERT INTO #who2
EXEC master.sys.sp_who2 active
SELECT r.session_id AS SPID
,(SELECT MAX(HostName) FROM #who2 WHERE SPID = r.session_ID) AS Server
,(select max(sysprocesses.hostprocess) from master.dbo.sysprocesses (nolock) where sysprocesses.spid = r.session_id) as TaskManagerPID
,(SELECT MAX(LOGIN) FROM #who2 WHERE SPID = r.session_ID) AS Login
–,p.cpu as CPUTime, p.physical_io as DiskIO
,(SELECT SUM(cast(CPUTime as bigint)) FROM #who2 WHERE SPID = r.session_ID) AS CPUTime
,(SELECT SUM(cast(DiskIO as int)) FROM #who2 WHERE SPID = r.session_ID) AS DiskIO
,CONVERT(VARCHAR(16),start_time,120) AS start_time
,(SELECT MAX(DBName) FROM #who2 WHERE SPID = r.session_ID) AS DBName
— ,(SELECT MAX(Command) FROM #who2 WHERE SPID = r.session_ID) AS Command
— ,qt.text,r.statement_start_offset, r.statement_end_offset
,substring(qt.text,r.statement_start_offset/2,
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2
else r.statement_end_offset end – r.statement_start_offset)/2 + 1) as query_text — this is the statement executing right now
— ,qt.dbid
— ,qt.objectid
,blocking_session_id AS BlockingSPID
— ,substring(convert(char(23),DATEADD(ms ,r.cpu_time, 0),121 ),12,8) AS cpu_time
,RIGHT(‘0’+CONVERT(VARCHAR,(DATEDIFF(ss,start_time,GETDATE()))/(3600)),2) + ‘:’+RIGHT(‘0’+CONVERT(VARCHAR,(DATEDIFF(ss,start_time,GETDATE()))%(3600)/60),2)+’:’+RIGHT(‘0’+CONVERT(VARCHAR,(DATEDIFF(ss,start_time,GETDATE()))%60),2) AS elapsed_time
,SUBSTRING(CONVERT(VARCHAR,CAST(r.reads AS MONEY),1),1,LEN(CONVERT(VARCHAR,CAST(r.reads AS MONEY),1))-3) AS reads
,SUBSTRING(CONVERT(VARCHAR,CAST(r.writes AS MONEY),1),1,LEN(CONVERT(VARCHAR,CAST(r.writes AS MONEY),1))-3) AS writes
,SUBSTRING(CONVERT(VARCHAR,CAST(r.logical_reads AS MONEY),1),1,LEN(CONVERT(VARCHAR,CAST(r.logical_reads AS MONEY),1))-3) AS logical_reads
,(SELECT MAX(ProgramName) FROM #who2 WHERE SPID = r.session_ID) AS ProgramName
,wait_type ,wait_time –,wait_resource
,r.status
–,*
–,r.scheduler_id
FROM sys.dm_exec_requests r (NOLOCK)
–inner join master.dbo.sysprocesses p with (nolock)
–on p.spid = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt
WHERE r.session_id > 50
AND qt.text NOT LIKE ‘%SELECT r.session_id%’
–AND r.session_id IN (SELECT SPID FROM #who2 WHERE LOGIN LIKE ‘%siitusr%’)
ORDER BY 3,1
go
drop table #who2
go

=========================================================

——- KILL all the connections for a database ——

Use Master
Go

Declare @dbname sysname

Set @dbname = ‘databaseName’

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute (‘Kill ‘ + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End

—— Simple script to close all connections to a MSSQL database. —-

USE master;
GO
ALTER DATABASE dbName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE dbName
SET MULTI_USER;
GO

=========================================================

—– get all DB sizes in MB———————-

SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 — Skip system databases
GROUP BY d.name
ORDER BY d.name

=========================================================

———————————————————————–
———————————————————————–

–1.To find out which recovery model your database is using

SELECT databasepropertyex(‘database’, ‘recovery’)

——————————————————————————

–2.To change the recovery option for a database

ALTER DATABASE database name SET RECOVERY {FULL | SIMPLE | BULK_LOGGED}

——————————————————————————
–3.To know the compatability level of all databases

select * from sys.databases

——————————————————————————

–4.To know the compatability of a particular database

select * from sys.databases where name=’database name’

——————————————————————————

–5.To know the space availability in a log file

dbcc sqlperf(logspace)

——————————————————————————

–6.To know the space availability in a data file

sp_spaceused
——————————————————————————

–7.To know the details of particular database (db name,filename,filegroup,size)

sp_helpfile
——————————————————————————

–8.To know the details of all databases (db name,size,owner,date created,status,compatability level)

sp_helpdb

——————————————————————————

–9.To know the details of database (name,filename)

select * from sys.sysaltfiles

——————————————————————————

–10.To know the details of database (db name,login name,status,command)

sp_who2

——————————————————————————

–11.command for FULL BACKUP

backup database ‘database name’ to disk=’path.bak’

——————————————————————————

–12.command for differential backup

backup database ‘aaa’ to disk=’path.bak’ with differential

——————————————————————————

–13.command for transaction log backup

backup log ‘aaa’ to disk=’d:\aaalog.trn’

——————————————————————————

–14.command for file & filegroup backup

backup database ‘aaa’ file=’aaa’, filegroup=’primary’ to disk=’d:\aaafile.bak’ with noformat

——————————————————————————

–15.command for tail log backup

backup log ‘aaa’ to disk=’d:\aaatail.trn’ with no_truncate

——————————————————————————

–16.command for mirrored backup

backup database aaa to disk=’d:\aaamirror1.bak’ mirror to disk=’d:\aaamirror2.bak’ with format

——————————————————————————

–17.command for striped backup

backup database aaa to disk=’d:\aaastriped_part1.bak’,disk=’d:\aaastriped_part2.bak’

——————————————————————————
–18.command for partial backup

backup database aaa read_write_filegroups to disk=’d:\aaapartial.bak’

——————————————————————————

–19.command for partial differential backup

backup database aaa read_write_filegroups to disk=’d:\aaapartialdiff.bak’ with differential

——————————————————————————

–20. command for copyonly backup for (data files)

backup database aaa to disk=’d:\aaa_copyonly.bak’ with copy_only

——————————————————————————

–21.command for copyonly backup for (log files)

backup log aaa to disk=’d:\aaa_copyonly_log.bak’ with copy_only

——————————————————————————

–22.To know the server role permissions

exec sp_srvrolepermission
——————————————————————————

–23.To know the particular server role permission

exec sp_srvrolepermission sysadmin

——————————————————————————

–24.To create built in administrator account

create login[builtin administrators] from windows

——————————————————————————

–25.To know the details of protocols

select * from sys.endpoints

——————————————————————————

–26.To know rows in a table

select count (*) from tablename

——————————————————————————

–27.command for restore

restore database dbname from disk=’path’ with norecovery,replace

——————————————————————————

–28.command to know the validity of a backup

restore verifyonly from disk=’path’

——————————————————————————

–29.command to know the header of backup

restore headeronly from disk=’path’

——————————————————————————

–30.command to verify the contents of backup

restore filelistonly from disk=’path’

——————————————————————————

–31.command to restore a full backup into a different location

restore DATABASE [NEW_DATABASE_NAME] FROM DISK = ‘F:\pathFULL.bak’ WITH FILE=1,
MOVE
‘OLD_FILE_NAME_mdf’ TO ‘C:\NEW_FILE_NAME….mdf’,
MOVE
‘OLD_FILE_NAME_ldf’ TO ‘C:\NEW_FILE_NAME…..ldf’;
GO

——————————————————————————

–32.commands to keep database in offline mode

alter database dbname set offline with rollback immediate

–the above option kills all the current transactions &put the database immediately in offline state

alter database dbname set offline with rollback after 5 seconds

–the above option kills the transactions after 5 seconds & then puts the db in offline state

alter database dbname set offline with no_wait

–the above option can be used when we dont want the command to be blocked

——————————————————————————

–33.command to keep the database in online state

alter database dbname set online

——————————————————————————

–34.command for backup of database with PASSWORD protected?

BACKUP DATABASE [DATABASE NAME] TO DISK=’C:\path.BAK’ WITH MEDIAPASSWORD=’password’

——————————————————————————

–35.Restoring database with specifying PASSWORD

RESTORE DATABASE [DATABASE NAME] FROM DISK=’C:\path.BAK’ WITH MEDIAPASSWORD=’password’

——————————————————————————

–36.command to create database

create database dbname

——————————————————————————

–37.command to change the collation settings

alter database dbname collate sql_latin1_general_cp1_cs_as

——————————————————————————

–38.command to set the database in a single user mode

alter database dbname set single_user

——————————————————————————

–39.command to create a file group

alter database dbname add filegroup filegroup name

——————————————————————————

–40.command to change the logical name of a file

alter database dbname modify file(name=’old filename’,newname=’new filename’)

——————————————————————————

–41.command to disable autogrowth of a file

alter dbname modify file(name=’filename’,filegrowth=0)

——————————————————————————

–42.command to resize a file

alter dbname modify file(name=’filename’,size=’kb’)

——————————————————————————

–43.command to make database read only

alter database dbname set read_only

——————————————————————————

–44.command to remove file from database

alter database dbname remove file filename

——————————————————————————

–45.command to change the path of a file

alter database dbname modify file(name=’logical filename’,filename=’path.mdf’)
alter database dbname modify file(name=’logical filename’,filename=’path.ldf’)

——————————————————————————

–46.command to put database in a restricted mode

alter database dbname set restricted_user

——————————————————————————

–48.command to know the edition

select @@version
select serverproperty (‘edition’)

——————————————————————————

–49.command to know how much space is available in logfile

dbcc sqlperf(logspace)

——————————————————————————

–50.command to know how much space is available in datafile

sp_spaceused

——————————————————————————

–51.command to know database name and size

sp_databases

——————————————————————————

–52.command to know the database id

select db_id(databasename)

——————————————————————————

–53.command for point in time recovery

restore log dbname from disk=’path’ stopat ‘dd:mm:yy hh:mm:ss’

——————————————————————————

–54.command to delete older backups

use msdb
go
exec sp_delete_database_backuphistory ‘dbname’

——————————————————————————

–55.command to delete older backups beyond a date

use msdb
go
exec sp_delete_backuphistory’date’

——————————————————————————

–56.command to know job information

use msdb
select * from sysjobs

——————————————————————————

–57.command to know database is really in suspect mode or not

select databasepropertyex(‘dbname”status’)

——————————————————————————

— 58. SHrink TempDB, log files

USE [tempdb]
–Check Database File Size before shrink
SELECT name, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

–Shrink tempdb Log File (.ldf)
DBCC SHRINKFILE(templog,1) — run this line > SQL 2005
BACKUP LOG tempdb WITH TRUNCATE_ONLY — Truncate_Only, not available > SQL 2005
DBCC SHRINKFILE(templog,1)

–Shrink tempdb Data File (.mdf)
DBCC SHRINKFILE(tempdb7,1) — run this line > SQL 2005
BACKUP LOG tempdb WITH TRUNCATE_ONLY — Truncate_Only, not available > SQL 2005
DBCC SHRINKFILE(tempdev,1)

–Check Database File Size after shrink
SELECT name, size/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: