SQL SERVER – Reclaim Space After Dropping Variable – Length Columns Using DBCC CLEANTABLE

All DBA and Developers must have observed when any variable length column is dropped from table, it does not reduce the size of table. Table size stays the same till Indexes are reorganized or rebuild. There is also DBCC command DBCC CLEANTABLE, which can be used to reclaim any space previously occupied with variable length columns. Variable length columns include varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Space can be reclaimed when variable length column is also modified to lesser length.

Read More

SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table

One of my friend involved with large business of medical transcript invited me for SQL Server improvement talk last weekend. I had great time talking with group of DBA and developers. One of the topic which was discussed was how to find out Fragmentation Information for any table in one…
Read More

SQL SERVER – 2005 – Last Ran Query – Recently Ran Query

How many times we have wondered what were the last few queries ran on SQL Server? Following quick script demonstrates last ran query along with the time it was executed on SQL Server 2005. SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest…
Read More

SQL SERVER – Importance of Master Database for SQL Server Startup

I have received following questions many times. I will list all the questions here and answer them together.
What is the purpose of Master database?
Should we backup Master database?
Which database is must have database for SQL Server for startup?
Which are the default system database created when SQL Server 2005 is installed for first time?
What happens if Master database is corrupted?

Read More

SQL SERVER – Get Permissions of My Username / Userlogin on Server / Database

A few days ago, I was invited to one of the largest database company. I was asked to review database schema and propose changes to it. There was special username or user logic was created for me, so I can review their database. I was very much interested to know what kind of permissions I was assigned per server level and database level. I did not feel like asking their Sr. DBA the question about permissions.

Read More