SQL Scripts
1 ) Find the Size of Database & Log File
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'tempdb'
GO
2) Using query analyzer, name 3 ways to get an accurate count of the number of records in a table?
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
3 Say some DBCC Commands?
DBCC OPENTRAN - What are the transactions are going
DBCC CHECKDB - Consistency Check & Allocation Errors
DBCC FREEPROCCACHE - Clears the procedure cache removing all execution
Plans, all procedures are recompiled
DBCC DROPCLEANBUFFERS -Drops all the data that was cached in memory
DBCC SHOWCONTIG - Shows fragmentation within tables / indexes
DBCC DBREINDEX - Performs a complete reorganization of the index.
DBCC SQLPERF (LOGSPACE)
DBCC IND
DBCC PAGE
DBCC CHECKTABLE
DBCC CHECKCATALOG
DBCC CHECKALLOC
DBCC SHRINKDATABASE
DBCC SHRINKFILE
DBCC LOGINFO
DBCC LOGTRUNCATEONLY
DBCC INPUTBUFFER
DBCC CHECKCONSTRAINTS - Runs after repair allow data loss to find referential integrity issues
DBCC MEMMORYSTATUS – It will show the VM Committed,reserved,AWE allocated etc.
DBCC OUTPUTBUFFER - Returns the current output buffer in hexadecimal and ASCII format
DBCC CHECKDB WITH ESTIMATE – It will show the estimated Tempdb space required for checktable and checkalloc.
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
3 Say some DBCC Commands?
DBCC OPENTRAN - What are the transactions are going
DBCC CHECKDB - Consistency Check & Allocation Errors
DBCC FREEPROCCACHE - Clears the procedure cache removing all execution
Plans, all procedures are recompiled
DBCC DROPCLEANBUFFERS -Drops all the data that was cached in memory
DBCC SHOWCONTIG - Shows fragmentation within tables / indexes
DBCC DBREINDEX - Performs a complete reorganization of the index.
DBCC SQLPERF (LOGSPACE)
DBCC IND
DBCC PAGE
DBCC CHECKTABLE
DBCC CHECKCATALOG
DBCC CHECKALLOC
DBCC SHRINKDATABASE
DBCC SHRINKFILE
DBCC LOGINFO
DBCC LOGTRUNCATEONLY
DBCC INPUTBUFFER
DBCC CHECKCONSTRAINTS - Runs after repair allow data loss to find referential integrity issues
DBCC MEMMORYSTATUS – It will show the VM Committed,reserved,AWE allocated etc.
DBCC OUTPUTBUFFER - Returns the current output buffer in hexadecimal and ASCII format
DBCC CHECKDB WITH ESTIMATE – It will show the estimated Tempdb space required for checktable and checkalloc.
4) How to identify blocking in SQL Server
i) select * from sys.sysprocesses where blocked<>0
ii) USE Master
GO
EXEC sp_who2
GO
iii) Using DMVS
USE Master
GO
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
GO
GO
EXEC sp_who2
GO
iii) Using DMVS
USE Master
GO
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
GO
5) How to take the databas online & Offline
Bring the database online using below query
alter database TEST set online with rollback immediate
Take the database offline using below query
alter database TEST set offline with rollback immediate
6) SQL server info query
SELECT @@servername as [SQL Instance],SERVERPROPERTY('productversion') as [SQL Versiaon], SERVERPROPERTY('productlevel') As [Service Pack] , SERVERPROPERTY('edition') as [SQL Server Edition]
