How do you know how much memory has been allocated to sql server using AWE? Ans: We can use DBCC MEMORYSTSTUS command to know the memory allocation information. But it’s trick to understand the results. We can use a DMV called “sys.DM_OS_Memory_Clerks”....
What is the option ”Lock Pages in Memory”? Ans: Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems,...
Can we install SQL Server using a configure file? Ans: Yes! We can prepare a configuration file. While installing SQL Server the path to the configuration file is specified in the “Ready to Install” page in the configuration file path section. Cancel...
What is the cluster node limitation? The number of allowable nodes in the SQL Server cluster depends on your SQL Server version and your Windows Server version. For SQL Server 2008 Standard edition, you can only have two cluster nodes. If you have SQL...
What is the maximum limit of SQL Server instances for a standalone computer? (2008 R2) 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.
Can we uninstall/rollback a service packs from SQL Server 2005?
Can we uninstall/rollback a service packs from SQL Server 2005? No not possible for SQL Server 2005. To rollback a SP you have to uninstall entire product and reinstall it. For Sql Server 2008 you can uninstall a SP from Add/Remove programs. Some people...
How to recover a database that is in suspect stage? ALTER DATABASE test_db SET EMERGENCY After you execute this statement SQL Server will shutdown the database and restart it without recovering it. This will allow you to view / query database objects,...
Start SQL Server in different modes? Single User Mode (-m) : sqlcmd –m –d master –S PAXT3DEVSQL11 –c –U sa –P ******* DAC (-A): sqlcmd –A –d master –S PAXT3DEVSQL11 –c –U sa –P ******* Emergency: ALTER DATABASE test_db SET EMERGENCY
What is the port numbers used for SQL Server services? The default SQL Server port is 1433 but only if it’s a default install. Named instances get a random port number. The browser service runs on port UDP 1434. Reporting services is a web service – so...
