When were the statistics for a table last updated?

The below query returns the statistics for a table last updated. It makes use of the sys.indexes and sys.tables catalog views, along with the STATS_DATE() function, to retrieve the date that each index was last updated for every user table in the current database.

SELECT 

t.name AS Table_Name, i.name AS Index_Name,
i.type_desc AS Index_Type,

dex_id) AS Date_Updated
--,sp.rows --if you want t
STATS_DATE(i.object_id,i.i
no know how many rows unrem this
FROM 

ject_id = i.object_id JOIN
sys.partitions sp ON t.object_id = sp.
sys.indexes i JOIN sys.tables t ON t.o
bobject_id 
and i.index_id = sp.index_id --new 
WHERE i.type > 0 and --if you want to see heap rem this 


sp.rows > 0
ORDER BY t.name ASC, i.type_desc ASC, i.name ASC

This little query can be useful in troubleshooting and diagnosing performance-related issues. Sometimes, it's as simple as outdated statistics.

Change SQL Server Password

There are many method through which you can change SQL Server Password.


Method 1
You can use Query windows of Management Studio to change password. 
Open SQL Server Management Studio, Open New Query Window and type the below command and press Execute:


Alter Login [sa] With Default_Database=[Master]
GO
Use [Master]
GO
ALTER Login [sa] With Password = N'Change Password' Must_Change
GO


After executing this 'Change Password' is set for your sa account.


Method 2
You can use Command Prompt to change Password.


Open Command Prompt (Start - Run - cmd) and type the below Command:


Osql –S <Server Name> –E 

1. EXEC sp_password NULL, ’<Password>’, ’sa’
2. GO

<Server Name> is the name of the SQL Server and <Password> is the password you want to set.


Method 3
You can also change Password by Windows Authentication. Open SQL Server Management Studio. Login to Management Studio with Windows Authentication. Expand Server - Security - Logins. Now double click on sa login. Here type a new Password, Confirm Password, and click on Finish. Restart SQL Server. Now you can login to SQL Server with new sa account password.

The backup set holds a backup of a database other than the existing ‘dbName’ database

Sometime when you try to restore the backup file on an existing database and receive the following error message:

Restore failed for Server ‘SQL Server name‘. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘dbName‘ database. (Microsoft.SqlServer.Smo)

Above error message indicates that you are going to restore a database on another database which are not the same database. For example, you have backup Northwind database and try to restore the backup to AdventureWorks database, the error will occur.

You can use the overwrite the existing database option while you’re restoring to solve the problem.

On Restore Database, select Options tab on the left menu. Then, check Overwrite the existing database on Restore options.

Now Restore the database. and problem is solved.

You can also delete the database and again restore that.

SQL 2008 shrink log file size with no_log alternative

Microsoft SQL Server 2008 does not allow you to truncate the transaction log using the no_log syntax.


Use the below code to shrink the log file for your database


USE MASTER
GO
ALTER DATABASE DB_Name SET RECOVERY SIMPLE
GO
USE DB_Name
GO
DBCC SHRINKFILE (DB_Name_log, 1)
GO
ALTER DATABASE DB_Name SET RECOVERY FULL
GO


SQL 2008 returns a table of shrinkfile related results plus the following confirmation:


(1 row(s) affected)
DBCC execution completed. If DBCC printed error
messages, contact your system administrator.

Configuring SQL Server to listen on alternative Port

If you need SQL Server to listen on some alternative port or to share additional port for remote access, please follow these directions:


1) Login to your server using Terminal Services Client
2) Go to Start -> Programs -> SQL Server -> Server Network Utility
3) Under Enabled Protocols, select TCP/IP and go to properties
4) In the default port field, enter “1433, 2512” without the quotes. This will force SQL Server to listen on both port 1433 and 2512. 
If you would like to have SQL listen on a different port, simply enter additional port numbers in a comma delimited format.
5) Now restart the SQL Server service so that it can rebind it’s network connections.