Configure SQL Server Agent Mail to Use SQL Mail

We can configure SQL Server to send email. First you had to enable SQL Mail and set up a mail profile. SQL Mail can be enabled by opening the Properties dialog box from the SQL Mail node, by using the Surface Area Configuration facet of Policy-Based Management, or by the using the sp_configure stored procedure.

• Expand a server in  Object Explorer
• Right-click on SQL Server Agent, and click on Properties.
• On the Alert System page, click Enable mail profile. If this option is not available, enable SQL Mail and set up a mail profile.
• In the Mail system list, choose SQL Mail.
• In the Mail profile list, select the Extended MAPI mail profile you created for SQL Mail. For more information about configuring a mail profile, see How to: Configure Mail Profiles for Use by SQL Mail (Windows).
• Restart the SQL Server Agent service for your changes to take effect. For more information, see How to: Restart the SQL Server Agent Service (SQL Server Management Studio).
• To test SQL Mail, send a test message using xp_sendmail.

Scheduling database backup in SQL Server Management Studio

Follow the below steps to schedule database backup in SSMS.

1) Start SQL Server Management Studio.

2) In the Connect to Server dialog box, click the appropriate values in the Server type list, in the Server name list, and in the Authentication list.

3) Click Connect.

4) In Object Explorer, expand Databases.

5) Right-click the database that you want to back up, click Tasks, and then click Back Up.

6) In the Back Up Database - DatabaseName dialog box, type the name of the backup set in the Name box, and then click Add under Destination.

7) In the Select Backup Destination dialog box, type a path and a file name in the Destinations on disk box, and then click OK.

8) In the Script list, click Script Action to Job.

9) In the New Job dialog box, click Steps under Select a page, and then click Edit if you want to change the job parameters.

Note In the Job Step Properties - 1 dialog box, you can see the backup command.

10) Under Select a page, click Schedules, and then click New.

11) In the New Job Schedule dialog box, type the job name in the Name box, specify the job schedule, and then click OK.

Note If you want to configure alerts or notifications, you can click Alerts or Notifications under Select a page.

Click OK two times.

You receive the following message:

The backup of database 'DatabaseName' completed successfully.

Note To verify the backup job, expand SQL Server Agent, and then expand Jobs. When you do this, the SQL Server Agent service must be running.

Error The trasaction log for database is full.

Sometime we may face an error in SQL Server while performing some transaction

ERROR - (Error 9002)

The following SQL Server Error or errors occured when accessing the "Table Name" table:
9002,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server] The trasaction log for 
database 'Database name' is full. To find out why space in the log cannot be reused, see the
log_reuse_Wait_desc column in sys.database.

SQL:
Update "database name"."dbo"."Table Name" with (RepeatTableRead) set something where 
query.

Reason For Error-
The error is due to space avilability in the disk where Log file reside.

Resoultion -

There are multiple options for the resoultion of the same, choose one that best suite you-
1) Backing up the Log.
2) Freeing Disk Space.
3) Moving the Log File to a Different Disk
4) Increasing the size of a log file.
5) Adding a log file on a different disk.
6) Completing or killing a long-running transaction.

Index statistics in a database

Below query use and DMV and shows you the general state of the indexes in a database. It shows you the table name, the number of indexes on that table, how many of those indexes have been unused since either the last restart or the index was (re)created, and how many indexes SQL Server thought it would like to have on the table. In a well tuned database, the unused and missing counts would both be 0. If they are, you have exactly the right number of indexes for the workloads hitting your tables.



SET Transaction Isolation Level Read Uncommitted


SELECT CONVERT(VARCHAR(30),so.name) AS TableName,
       COALESCE(Unused.IndexCount, 0) AS IndexCount,
       COALESCE(Unused.UnusedIndexCount, 0) AS UnusedIndexCount,
       COALESCE(Missing.MissingCount, 0) AS MissingIndexCount,
       COALESCE(CONVERT(DECIMAL(6,1), (CONVERT(DECIMAL(10,2),Unused.UnusedIndexCount)/CONVERT(DECIMAL(10,2),Unused.IndexCount)) * 100), 0) AS UnusedPercent


FROM sys.objects so
LEFT JOIN
    (SELECT s.OBJECT_ID, COUNT(*) AS IndexCount, SUM(CASE WHEN s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 THEN 1 ELSE 0 END) AS UnusedIndexCount
            FROM sys.dm_db_index_usage_stats s JOIN sys.indexes i
            ON s.OBJECT_ID     = i.OBJECT_ID AND s.index_id = i.index_id
            WHERE s.database_id   = DB_ID() AND OBJECTPROPERTY(s.OBJECT_ID, 'IsMsShipped') = 0
            GROUP BY s.OBJECT_ID
        ) AS Unused
ON Unused.OBJECT_ID = so.OBJECT_ID
LEFT JOIN (SELECT  d.OBJECT_ID, COUNT(*) AS MissingCount
            FROM sys.dm_db_missing_index_groups  g JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
            JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
            WHERE d.database_id = DB_ID()
            GROUP BY d.OBJECT_ID
            ) AS Missing
ON Missing.OBJECT_ID = so.OBJECT_ID
WHERE so.type_desc = 'USER_TABLE' AND (Missing.MissingCount > 0 OR Unused.UnusedIndexCount > 0)
ORDER BY UnusedPercent DESC 


SET Transaction Isolation Level Read Committed

SQL SERVER EXPRESS 2008 R2 – INSTALLING ON WINDOWS 7 WITH VISUAL STUDIO 2010

Today I was trying to install the SQL Server 2008 R2 Developer Edition on my system. Operating system is Windows 7 with Visual Studio 2010. When I run the setup up file I got the follow error message:

System.Configuration.ConfigurationErrorsException: An error occurred creating the configuration section handler for userSettings/Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified. (C:\Users\Arun\AppData\Local\Microsoft_Corporation\LandingPage.exe_StrongName_ryspccglaxmt4nhllj5z3thycltsvyyx\10.0.0.0\user.config line 5) ---> System.IO.FileNotFoundException: Could not load file or assembly 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.
File name: 'System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'

I pressed continue and SQL Server installed without any error.