UPDATE STATS - FOR ALL DB's AND ALL OBJECTS

EXEC sp_msForEachDb
@command1='IF ''#'' NOT IN (''master'', ''model'', ''msdb'', ''pubs'', ''tempdb'') BEGIN PRINT ''#'';
EXEC #.dbo.sp_msForEachTable ''UPDATE STATISTICS ? WITH FULLSCAN'', @command2=''PRINT CONVERT(VARCHAR, GETDATE(), 9) + '''' - ? Stats Updated'''''' END',
@replaceChar = '#'

Interview Questions -DAC

1)What is a DAC. How do you connect to server through DAC?

This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.

This dedicated administrator connection (DAC) supports encryption and other security features of SQL Server. The DAC only allows changing the user context to another admin user.

SQL Server makes every attempt to make DAC connect successfully, but under extreme situations it may not be successful.

To connect to a server using the DAC

In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.

In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.

Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.

The connection is made.

If the DAC is already in use, the connection will fail with an error indicating it cannot connect.

Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

http://msdn.microsoft.com/en-us/library/aa337324%28SQL.90%29.aspx

To retrive Data from Excel

1. Step

http://www.microsoft.com/download/en/confirmation.aspx?id=23734

Install the Driver AccessDatabaseEngine.exe

2. Step

select * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=E:\details.xlsx;',
'SELECT * FROM [DServer$]')

More Help on http://blog.hoegaerden.be/2010/03/29/retrieving-data-from-excel/

TEMPDB

The following operations cannot be performed on the tempdb database:

Adding filegroups.

Backing up or restoring the database.

Changing collation. The default collation is the server collation.

Changing the database owner. tempdb is owned by dbo.

Creating a database snapshot.

Dropping the database.

Dropping the guest user from the database.

Enabling change data capture.

Participating in database mirroring.

Removing the primary filegroup, primary data file, or log file.

Renaming the database or primary filegroup.

Running DBCC CHECKALLOC.

Running DBCC CHECKCATALOG.

Setting the database to OFFLINE.

Setting the database or primary filegroup to READ_ONLY.

Enabling TDE

When you enable TDE, the data in a database is encrypted, however two other things occur. All subsequent transactions in the transaction log for that database are encrypted, and tempdb is encrypted.

If you have a 4 CPU socket server that has two NUMA nodes with CPUs (0,1) and (2,3), can you create a soft-NUMA node with CPUs 1 and 2?

No
Explanation:
The soft NUMA nodes that SQL Server can create cannot cross hardware NUMA boundaries. So you cannot create a soft NUMA set with CPUs 1 and 2.

Maximum Instances - SQL Server

50 instances on a stand-alone server for all SQL Server editions.

SQL Server supports 25 instances on a failover cluster.