Useful commands for MSSQL DBA
In this post we will see some useful handy commands for SQL Server DBA.
1) Backups Commands
For Full Backup
BACKUP DATABASE [SampleDB2020] TO DISK = N'C:\Jhachandan\Backup Files\SampleDB2020_Full.bak' WITH NOFORMAT, NOINIT, NAME = N'SampleDB2020-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
For Differential Backup
BACKUP DATABASE [SampleDB2020] TO DISK = N'C:\Jhachandan\Backup Files\SampleDB2020_Diff.bak' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'SampleDB2020-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
For Transactional Log Backup
BACKUP LOG [SampleDB2020] TO DISK = N'C:\Jhachandan\Backup Files\ SampleDB2020_Log.trn' WITH NOFORMAT, NOINIT, NAME = N'SampleDB2020-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
2) Restore Commands
For Restore Database
RESTORE DATABASE [SampleDB2020] FROM DISK = N'C:\Jhachandan\Backup Files\SampleDB2020Full.bak' WITH FILE = 1, MOVE N'SampleDB2020_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TEST\MSSQL\DATA\SampleDB2020.mdf', MOVE N'SampleDB2020_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.TEST\MSSQL\DATA\SampleDB2020_1.LDF', NOUNLOAD, STATS = 10
Restoration Database with Recovery
RESTORE DATABASE [SampleDB2020] WITH RECOVERY (for bring online database)
SP_HELP_REVLOGIN
Restore Database up to Specific Time only
RESTORE DATABASE [SampleDB2020] FROM DISK = [N'C:\Jhachandan\Backup Files\SampleDB2020Full.bak'] WITH NORECOVERY
GO
RESTORE LOG [SampleDB2020] FROM DISK = N'C:\Jhachandan\Backup Files\ SampleDB2020_Log.trn ' WITH RECOVERY,
STOPAT= ‘MARCH 23, 2009 05:31:00 PM’
GO
3) Checking to make sure a SQL Server backup is useable
RESTORE VERIFYONLY FROM DISK = C:\AdventureWorks.BAK
GO
4) For used space on the particular database
SP_SPACEUSED
5) Check the update statistics if SQL Server
SP_UPDATESTATS
6) Display the Configuration Setting of the Current Server
SP_CONFIGURE
7) Check the Log Space of All Databases of Server
DBCC SQLPERF(LOGSPACE)
8) Check for the Drive Size of the Physical Server
XP_FIXEDDRIVES
9) For Check the Error Log
SP_READERRORLOG
10) Data File and Log File Location for All Server
SELECT DB_NAME (DATABASE_ID), NAME, TYPE_DESC, PHYSICAL_NAME, [SIZE(MB)] = (SIZE * 8) / 1024.0 FROM SYS.MASTER_FILES
11) For Missing Index Details
SELECT *FROM SYS.DM_DB_MISSING_INDEX_DETAILS (MISSING INDEX)
12) Check Recovery Model
SELECT NAME, RECOVERY_MODEL_DESC FROM SYS.DATABASES
WHERE NAME = ['DB_NAME']
13) Getting All Table Name from particular database
USE [DB_NAME]
GO
SELECT * FROM SYS.TABLES
GO
Number of Cores on the Server
SELECT ( cpu_count / hyperthread_ratio )AS NumberOfPhysicalCPUs, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) )
END AS NumberOfCoresInEachCPU, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE ( cpu_count / hyperthread_ratio ) * ( ( cpu_count - hyperthread_ratio ) / ( cpu_count / hyperthread_ratio ) ) END AS TotalNumberOfCores, cpu_count AS NumberOfLogicalCPUs
, CONVERT(MONEY, Round(physical_memory_in_bytes / 1073741824.0, 0)) AS TotalRAMInGB
FROM
sys.dm_os_sys_info
14) Check Dead Lock on Server
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id
15) Checking Locking Process
EXEC SP_LOCK
EXEC SP_WHO2
USE [DB_NAME]
GO
SELECT * FROM SYS.DM_TRAN_LOCKS
GO
16) Bring Database Offline
ALTER DATABASE [DB_Name] SET OFFLINE WITH
ROLLBACK IMMEDIATE
17) Bring Database Online
ALTER DATABASE [DB_Name] SET ONLINE
18) Check which Backup Failed on when
EXEC sp_readerrorlog 0, 1, 'BACKUP failed'; -- current
EXEC sp_readerrorlog 1, 1, 'BACKUP failed'; -- .1 (previous)
EXEC sp_readerrorlog 2, 1, 'BACKUP failed'; -- .2 (the one before that)
19) Most Recent Database Backup for Each Database
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON sdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' GROUP BY msdb.dbo.backupset.database_name ORDER BY
msdb.dbo.backupset.database_name
20) Database Backups for all databases For Previous Week
SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type, msdb.dbo.backupset.backup_size, msdb.dbo.backupmediafamily.logical_device_name, msdb.dbo.backupmediafamily.physical_device_name, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
21) Check Database ISOLATION Level
DBCC USEROPTIONS
22) Buffer Cache Hit Ratio
SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Buffer cache hit ratio'
23) Who is disable the job on sql server
EXEC msdb.dbo.sp_helpjob
24) Physical Location for the database files (data file and log file)
SELECT NAME, PHYSICAL_NAME AS CURRENT_FILE_LOCATION FROM SYS.MASTER_FILES
25) Checking the Log shipping Last Backup LSN Number for backups of primary and secondary server.
SELECT SECONDARY_DATABASE, LAST_COPIED_FILE, LAST_RESTORED_FILE FROM MSDB..LOG_SHIPPING_MONITOR_SECONDARY
26) Find SQL Server Startup Time.
SELECT SQLSERVER_START_TIME FROM SYS.DM_OS_SYS_INFO
27) Find SQL Server Database Size.
USE DATABASE [DATABASE_NAME]
GO;
SELECT DBNAME, NAME, [FILENAME], SIZE AS 'SIZE(MB)', USEDSPACE AS 'USEDSPACE(MB)', (SIZE - USEDSPACE) AS 'AVAILABLEFREESPACE(MB)'
FROM
( SELECT DB_NAME(S.DATABASE_ID) AS DBNAME, S.NAME AS [NAME], S.PHYSICAL_NAME AS [FILENAME], (S.SIZE * CONVERT(FLOAT,8))/1024 AS [SIZE], (CAST(CASE S.TYPE WHEN 2 THEN 0 ELSE CAST(FILEPROPERTY(S.NAME, 'SPACEUSED') AS FLOAT)* CONVERT(FLOAT,8) END AS FLOAT))/1024 AS [USEDSPACE], S.FILE_ID AS [ID]
FROM
SYS.FILEGROUPS AS G INNER JOIN SYS.MASTER_FILES AS S ON ((S.TYPE = 2 OR S.TYPE = 0) AND S.DATABASE_ID = DB_ID() AND (S.DROP_LSN IS NULL)) AND (S.DATA_SPACE_ID=G.DATA_SPACE_ID)
) DBFILESIZEINFO
That's all in this article. Please Like, Share and Subscribe to learn more.
Recent Posts
See AllThe tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to...
댓글