By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database, system tables/views/procedures and so on. SQL Server Management Studio provides an option to hide these objects from Object Explorer to prevent *accidental* use. To enable this option follow below steps. Step1: Go to Tools > Options Step2: Navigate to Environment > Startup Step3: Check Hide system objects in Object Explorer and click OK: Step4: An confirmation message will be displayed saying that changes will take effect after Management Studio is restarted, click on OK to continue: Once you restart SQL Server Management Studio, you will notice that system objects are no longer listed in… Read more
Category: SQL DBA
How to Find SQL Server Database Size
if convert(varchar(20),SERVERPROPERTY(‘productversion’)) like ‘8%’ SELECT [name], fileid, filename, [size]/128 AS ‘Total Size in MB’, [size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’, CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’, (100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’ FROM sysfiles else SELECT [name], file_id, physical_name, [size]/128 AS ‘Total Size in MB’, [size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’, CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’, (100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’ FROM sys.database_files
SQL SERVER Performance bottlenecks & Trouble shooting
To start troubleshooting, you have to define the symptoms first. The most common SQL Server performance symptoms are CPU, memory, network, and I/O bottlenecks, and slow running queries CPU bottlenecks are caused by insufficient hardware resources. Troubleshooting starts with identifying the biggest CPU resource users. Occasional peaks in processor usage can be ignored, but if the processor is constantly under pressure, investigation is needed. Adding additional processors or using a more powerful one might not fix the problem, as badly designed processes can always use all CPU time. Query tuning, improving execution plans, and system reconfiguration can help. To avoid… Read more
Query to find space occupied by each database in Data cache
Every database in SQL Server which is getting read by the user or application is retrieved from the buffer cache. If the pages which are not in buffer cache are read by the user/application, those pages will be loaded into buffer cache first and then retrieved to the user/application. Database Pages which occupied in the cache are retrieved by the following query:
Top 10 processes running in the SQL SERVER
select top 10 * from master.dbo.sysprocesses where status <> ‘background’ AND cmd NOT IN (‘signal handler’,’lock monitor’,’log writer’,’lazy writer’,’checkpoint sleep’,’awaiting command’,’TASK MANAGER’) and spid > 50 and spid <> @@spid order by CPU desc
SQL SERVER 2012 Hierarchy of Database startup
1.Master 2. MSDB 3. mssqlsystemresource 4. User Databases 5. Distribution 6. Model 7. TempDB
How to Find SQL Server Started date & Time – 4 Scripts to Find SQL Server Started Time
There are so many way of finding the sql server started date time. 1. SQL Server Log 2. using DMV 3. Using TempDB Creation date (as it is recreated when SQL Server is started every time) 4. Start Time of Default Trace Examples : 1. SQL Server Log : SQL Server Management Studio –> Management –> SQL Server Logs –> We need to Search for the “SQL SERVER Starting….” or sp_readerrorlog 0,1,’Copyright (c)’ 2. Using DMV : SELECT sqlserver_start_time FROM sys.dm_os_sys_info; 3. Using TempDb Creation Date select crdate from sys.sysdatabases where name=’tempdb’ 4. Default Trace : SELECT login_time FROM sys.dm_exec_sessions… Read more
SQL SERVER 2012 System Databases
Master Purpose – Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects. Functionality Instance configurations Databases residing on the instance Files for each database Logins Linked\Remote servers Endpoints MSSQLResource Purpose – The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL… Read more