Disconnect/Kill all the SPID's in the database

Some times a situation like to restore the database immediately.
When you are trying to do that, It will not let you install and throwing the error saying Database is in use....
Once you find the SPID's running under the database, It may be hard to disconnect all of them in single short (Of course you can use Single user mode, It has its own problems though).

By running the following script you can kill all the SPID's running under the desired database and you can restore the database by adding restore database script.
Please take a look at it,


DECLARE @ID INT
DECLARE @MSG CHAR(8), @DataName SYSNAME = 'newdb'

WHILE 1 = 1
BEGIN
 SET ROWCOUNT 1
 SELECT  @id = spid
        FROM Master..Sysprocesses P,
                Master..Sysdatabases D
         WHERE D.Name=@DataName AND
                D.dbid = P.dbid
IF @@rowcount = 0
 BREAK

 SELECT @msg = 'KILL ' + CONVERT(CHAR(8) ,@ID)

PRINT @msg
EXEC( @msg)

END

-- Please continue your restore script here.

How can we stop pulling data from a table in SQL server ?

It looks funny when you think about it but its true.
One of the way  you can stop users to pull data from a table is not a Heap should be disable Cluster Index.

If you disable the Cluster Index as it has the data, You can't be accessible though you hold required permissions.

ALTER INDEX IndexName ON TableName DISABLE.

How to find all the users in my windows group?

 You have an extended stored procedure to get the information, which you have requested.

xp_logininfo 'Your windows Group','members'

Database Read_Only Vs Read_Write

Some times, You may get a situation like need to make your database as read_only time being...
Please follow the below..

If you want to make you database set to read only from (READ_WRITE) Normal DB,Then you can follow below
Note: Conditions apply, You will have to disconnect users from respected database.
USE [master]
GO
ALTER DATABASE [DatabaseName] SET  READ_ONLY WITH NO_WAIT
GO
If you want to make your database set to REGULAR(READ and WRITE), Then you can follow below...
Note: Conditions apply, You will have to disconnect users from respected database.

USE [master]
GO
ALTER DATABASE [DatabaseName] SET  READ_WRITE WITH NO_WAIT
GO

SQL Commands related to Database Mail

Basic SQL commands to know related to Database Mail, I am just keeping them for quick reference.

SELECT * FROM sysmail_log
           
-- Get all Sys mail accounts by running the below statement       
        SELECT * FROM sysmail_account
       
--If you want to find all the mails which by a particular Login...
        SELECT * FROM sysmail_sentitems
        WHERE send_request_user = 'LoginName'
        -- You can use above for today item List too..

-- If you want to see all failed items through Database mail                        
        SELECT * FROM sysmail_faileditems
        --WHERE send_request_user = 'LoginName'
       

-- List all the principle profiles in the server
SELECT * FROM sysmail_principalprofile
-- List all the profiles in the server
SELECT * FROM sysmail_profile

SELECT * FROM sysmail_profileaccount
-- You can see the existed SMPT server names, Port number by running the following
SELECT * FROM sysmail_server

IDENTITY Property in SQL server

-- This will be all about Identity Column...

* Identity Column will generate the number automatically for the row..Based on where you are starting the numbers and declaring the increment as below
Identity(Seed,Increment): IDENTITY(1,1)
So it starts the number with 1 and for the next number it will increment the orginal number by 1 So it should be 2
1
2
3
.
.
.
n
-- Can you insert manually the number?
Yes, by turning of the IDENTITY_INSERT ON

--Is there any precausions we need to follow to insert manually?
Yes, Make sure mention all columns/ Identity column(depends on requirement) in the insert statement

Correct_Version : INSERT INTO IDTable (IDNo, Name) SELECT 1,'pan'

Wrong_Version : INSERT INTO IDTable SELECT 1,'Pan'

-- Sample table ? and sample data in Identity Table?

CREATE TABLE IDTable( IDNo INT IDENTITY(1,1), Name VARCHAR(20))
INSERT INTO IDTable VALUES ('1st record')
-- How to reset Identity ?
Syntax : DBCC CHECKIDENT ('TableName',RESEED,NewstartingPosition)
Ex : DBCC CHECKIDENT ('IDTable',RESEED,15)

-- How to find the current identity value of the table?
        SELECT IDENT_CURRENT( 'IDTable' )
-- If you truncate the identity Column table, what would be the next identity value?
        TRUNCATE Table IDTable
          
Truncate will reset the Values to starting position of the Create statement.
So It populate the value what ever the seed value you declared during the table creation and follows as the increment for the next rows.

-- If you Delete the identity Column table, what would be the next identity value?

        DELETE FROM IDTable

It will delete the records. When you are inserting the values next time It will start from where it stopped last time.
It does not matter those records already been deleted.

SQL server is not starting in SQL 2012

Problem: The SQL Server (SQL2012) service failed to start due to the following error:
The service did not start due to a logon failure.

Solution:

Reference: http://support2.microsoft.com/kb/282254

To correct the password in Microsoft Windows 2000 Server and Microsoft Windows XP:

1. Click Start, point to Settings, and then click Control Panel.
2. Double-click Administrative Tools, and then double-click Services.
3. Use one of the following steps based on your instance type:
   • For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.
   • For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
   • For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.
   • For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log Ontab. Replace YourInstanceName with the actual name of the SQL Server instance.
4. Type the correct password in the Password and Confirm password textbox, and then click OK.

To correct the password in Microsoft Windows NT 4.0:

1. Click Start, point to Settings, and then click Control Panel.
2. Use one of the following steps based on your instance type:
   • For a default instance of SQL Server, double-click MSSQLServer, and then click the Log On tab.
   • For a named instance of SQL Server, double-click MSSQL$YourInstanceName, and then click the Log On tab. Replace YourInstanceName with the actual name of the SQL Server instance.
   • For a default instance of SQL Server Agent, double-click SQLAgent, and then click the Log On tab.
   • For a named instance of SQL Server Agent, double-click SQLAgent$YourInstanceName, and then click the Log Ontab. Replace YourInstanceName with the actual name of the SQL Server instance.
3. Type the correct password in the Password and Confirm password textbox, and then click OK.

NOTE: Changing the password through the Services applet allows SQL Server to start successfully; however, if Microsoft Search (Full-Text Service) is installed, it requires a subsequent password change through SQL Enterprise Manager (SEM).

Note If you are using SQL Server 2005, see the "Changing passwords and user accounts" topic in SQL Server 2005 Books Online

If it is on your Local machine, I rather choose the 'Log on As'  'Local System Account'

HeapTable

Heap Table:   A table which doesn't have a clustered index is called Heap table.

We can describe a 'heap table' let SQL server do a table scan on it while retrieving data, which obviously not good. So try to avoid heap tables in your database to give 100% chances to get the performance.

You can find the heap tables by using following T-SQL statement.

USE [YourDataBaseName]
GO

SELECT SCH.name + '.' + TBL.name AS TableName
    FROM sys.tables AS TBL
    INNER JOIN sys.schemas AS SCH
         ON TBL.schema_id = SCH.schema_id
    INNER JOIN sys.indexes AS IDX
         ON TBL.object_id = IDX.object_id
            AND IDX.type = 0 -- = Heap
    ORDER BY TableName   

Creating "Dynamic TABLE" by using T-SQL Script

Some times there will be a situation, Your manager/front end colleges feel how good our code generate a dynamic table to load the appropriate
data on the fly.
I want my script to generate like MyTestTable_TodayDate with few columns as requested...
It looks as below
        CREATE TABLE MyTestTable_20140403
                        (
                             Sno INT
                            ,InsertDate DATETIME
                            ,EntryBy VARCHAR(30)
                        )
                      
-- You can use the following script to do that.
Script :
        -- Declare Dynamic Value, In this scenario it is date
        DECLARE @DynamicValue VARCHAR(10)
        -- Just formatting how I would like to see the dynamic value
        SET @DynamicValue = Replace(CONVERT(VARCHAR(10),GETDATE(),120),'-','')
        -- Check what date format will be adding...
   -- Note Test only:     SELECT @DynamicValue

        DECLARE @str VARCHAR(1000)  -- Make sure the length of the string you are passing

        SET @str = 'CREATE TABLE MyTestTable_'+@DynamicValue+'
                    (     Sno INT
                        ,InsertDate DATETIME
                        ,EntryBy VARCHAR(30)
                    )'
    -- Note Test only: It tells the final creating table syntax
        PRINT (@str)
        -- Which is the execution command, Creates the dynamic Table
        EXEC (@str)
      
       

when did last time a stored procedure executed?

SELECT    Database_id,
        OBJECT_NAME(object_id, Database_id) AS 'ProcedureName',
        Cached_Time,
        Last_Execution_Time,
        Total_Elapsed_Time,
        Total_Elapsed_Time/Execution_Count AS Avg_Elapsed_Time,
        Last_Elapsed_Time, Execution_Count
FROM sys.dm_exec_procedure_stats
WHERE --DB_Name(database_Id)= 'YourDB'  and -- your database ID
OBJECT_NAME(object_id, Database_id) = 'Your ProcedureName'
ORDER BY [Total_Worker_Time] DESC;