Collection of useful Commands for SQL Server DBA

In this article I have tried to give as much as I can from my real time experience. You may try these commands on one of your test/pre-production server.
Continue reading

Advertisements

Lock pages in memory – SQL Server

This security setting determines which accounts can use a process to keep data in physical memory, which prevents the system from paging the data to virtual memory on disk. Exercising this privilege could significantly affect system performance by decreasing the amount of available random access memory (RAM). Continue reading

SP_recompile

SP_recompile needs a single parameter which is the table name after it.

ie: sp_recompile table_name Continue reading

Routine SELECT Queries

The below query to get result for SQL server Installation information Continue reading

Routine Backup & Restore Queries

The following is the query for recent 100 records for database backups.
you get results by changing backup type below

D= Full backup
L= T-Log backup
I=Incremental or Differential Continue reading

How to Change Server Name in SQL server

How to use ‘SP_addserver’ and ‘SP_dropserver’

We often use to change server name for SQL server 2000, 2005 or 2008 instance when we migrate sql instance from one place to another or while changing the name of the computer running the SQL Server.

We have to perform following steps to ensure that SQL Server also uses the new updated servername. Continue reading

Report Services instance could not be found

If you are getting this error message when trying to connect to Reporting Services from Management Studio.

“The Report Services Instance could not be found (Microsoft.SqlServer.Management.UI.RSClient)”
Continue reading