Category Archives: SQL Server

MS SQL server

Tempdb has grown and disk is almost full

Most of us must have encountered this problem when tempdb data file has grown and the disk is almost full.You can follow below steps. 1.It may be possible that by the time you have logged in into the server,those temporary objects or the queries which caused data file to grown are gone.You can try to shrink the tempdb data file… Read More »

Query to Check Fragmentation and rebuild index in a database

Below query checks fragmentation level (>30%) per table which has clustered or non-clustered index and provides you the query to perform ONLINE rebuild of that index.The fragmentation level of the table which has the highest number of rows is displayed on top. Use [database_name] SELECT OBJECT_SCHEMA_NAME(ind.OBJECT_ID) + ‘.’ + OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType,… Read More »

How to move resource database

Resource Database: Resource database is available from the SQL Server 2005 and higher level versions. Resource database is read only and hidden database. Resource database contains all the system objects that shipped with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of… Read More »

SQL Server buffer pool

The primary purpose of the SQL buffer pool is to reduce database file I/O and improve the response time for data retrieval. In SQL Server 2014, administrators can extend the buffer pool to a nonvolatile storage device such as a solid-state drive (SSD) or an SSD array. SQL Server writes only clean pages to the buffer extension in… Read More »