SQL Server - Query for Possible bad Indexes

 --Possible bad Indexes (writes > reads)

SELECT  OBJECT_NAME(s.object_id) AS 'Table Name',sca.name  , i.name AS 'Index Name',

'DROP INDEX ' + i.name + ' ON ' + sca.name + '.' + OBJECT_NAME(s.object_id)  ,

        i.index_id, user_updates AS 'Total Writes',

        user_seeks + user_scans + user_lookups AS 'Total Reads',

        user_updates - ( user_seeks + user_scans + user_lookups ) AS 'Difference'

FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )

        INNER JOIN sys.indexes AS i WITH ( NOLOCK ) ON s.object_id = i.object_id

                                                       AND i.index_id = s.index_id

INNER JOIN sys.tables AS t ON s.object_id = t.object_id

INNER JOIN sys.schemas As sca on t.schema_id = sca.schema_id

WHERE   OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1

        AND s.database_id = DB_ID()

        AND user_updates > ( user_seeks + user_scans + user_lookups )

        AND i.index_id > 1

ORDER BY 'Difference' DESC,    'Total Writes' DESC,     'Total Reads' ASC ;

SQL Server - Drop indexes and stats

 

Query to get the 'drop query for Stats'

SELECT distinct 'DROP STATISTICS ' + sca.name + '.' + t.name + '.' + s.name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
INNER JOIN sys.tables AS t
ON s.object_id = t.object_id
INNER JOIN sys.schemas As sca on t.schema_id = sca.schema_id
where s.name like '_dta_stat_%'

Query to get the 'drop query for Index'

SELECT distinct 'DROP INDEX ' + s.name + ' ON ' + sca.name + '.' + t.name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
INNER JOIN sys.tables AS t
ON s.object_id = t.object_id
INNER JOIN sys.schemas As sca on t.schema_id = sca.schema_id
where s.name like '_dta_index_%'

SQL Server - Deleting the contents from log file

 You can shrink the log with the following Statements:

USE [MyDatabase]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([MyDatabase_log], 1)
ALTER DATABASE [MyDatabase] SET RECOVERY FULL WITH NO_WAIT
GO

This snippet can be found on various sites in the net, but be aware that it's not a good practice to kill the log like this because of recovery. After doing this, you can only recover your database to the last full or full+incremental backup.

Sql-Server the Select permission was denied on the object 'types', Error 229

Please check that you didn't check the following  DB role:
1. db_denydatareader
2. db_denydatawriter

Please uncheck both of them, this will solve your problem.

Steps to uncheck the roles:

1. Open SQL Management Studio
2. Expand your database
3. Expand the "Security" Folder
4. Expand "Users"
5. Right click the user (the one that's trying to perform the query)
6. Make sure you uncheck

Ms Sql Server - The Visual Studio Component is out of date. Please restart Visual Studio. (Mscorlib)

SSMS Error: "The Visual Studio Component is out of date. Please restart Visual Studio. (Mscorlib)"

When you are trying to open Query or execute query in Microsoft SQL Server Management Studio, you may see the following message:

The Visual Studio Component is out of date. Please restart Visual Studio. (Mscorlib).

Cause: Sometimes cache of the Sql Server corrupted due to Updates in the visual studio or Sql-Server components updates.

Solution : 

STEP 1: Delete the Temp Files. Shortcut to delete the temp files

STEP 2: Restart Microsoft SQL Server Management Studio and the error message will have disappeared.