List database objects modified in the last ‘X’ days

Use below query to get the list of databases modified in last 'X' days:

USE <database_Name>; 
GO 
SELECT Name AS object_Name,  
  Create_Date, Modify_Date, 
  Type_Desc,
  SCHEMA_Name(Schema_ID) AS Schema_Name 
FROM sys.Objects 
WHERE Modify_Date > GETDate() - <X_Days> 
ORDER BY Modify_Date; 
GO 

Notes: Replace <database_Name> with Database Name and <X_Days>  with No of days.

Weekend count between two dates

Use below query to find the weekend count between two dates:

DECLARE @DateFrom DateTime, @DateTo DateTime, @Total int, @Number int, @Counter int
SELECT @DateTo = GetDate(), @DateFrom = GetDate() - 22, @Total = DateDiff(dd, @DateFrom, @DateTo), @Number = 1, @Counter = 0
WHILE (@Number <= @Total)
BEGIN
IF DatePart(dw, @DateFrom) = 1 OR DatePart(dw, @DateFrom) = 7
BEGIN
SET @Counter = @Counter +1
END
SET @DateFrom = @DateFrom+1
SET @Number = @Number + 1
END
PRINT @Counter

Find LOB Columns Script

Sometime you want to identify the tables which could not reindex online. For this you had to scan all tables in SQL Server database and list the columns which are large objects (VarChar(MAX), NVarChar(MAX), XML, VarBinary, Text, NText, Image).

There are various method to get above information:

Method 1

SELECT * FROM Information_Schema.Columns

WHERE Table_Name IN 

    (SELECT Table_Name 

     FROM Information_Schema.Tables 

     WHERE Table_Type = 'Base Table')

AND DATA_TYPE IN ('VarChar', 'NVarChar', 'VarBinary', 'Text', 'NText', 'Image', 'XML')

AND Character_Maximum_Length = -1

ORDER BY Table_Name

Method 2

SELECT 

    C.Object_ID, 

    Object_Name(C.Object_ID) AS [Object Name], 

    C.Name AS [Column Name], 

    T.Name AS [Column Type]

FROM Sys.Columns C

INNER JOIN Sys.Types T ON C.System_Type_ID = T.System_Type_ID 

WHERE C.Object_ID IN (SELECT Object_ID FROM Sys.Objects WHERE Type_Desc = 'User_Table')

AND C.max_length = -1

AND T.Name IN ('VarChar', 'NVarChar', 'VarBinary', 'Text', 'NText', 'Image', 'XML')

Unable to modify table.

I was trying to modify a table of approx 5 million records setting a field on the table to not allow null values. However, while saving changes in table, I got below error:

'Account ' table
- Unable to modify table. 
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

After that I click ok button and I got below error:

User cancelled out of save dialog.

This error is coming because table designer have a default timeout value of 30 secs. And as I am saving a table with huge records, it is taking time more than 30 secs, so above error coming.

So the solution is to increase the timeout value of designer, (the maximum value for timeout is 65535).
Click on tools menu - Options - Expand designers. Now here change the timeout value in "Table and Database designers" on the right side.

Record Retrieval Error Connection Read

You may receive below error while running third party application with backend SQL Server.

Record Retrieval Error
Error: (Connection Read(recv()).(01000)) attempting to access a record from the MERCAN file. Returning to previous window.

The application is showing this error because it lost the network connectivity with the SQL Server instance for a short period of time.

So, restart the application once the network issue is solved.

Attach Database Failed

Sometime you may get below error while attaching a database:

Attach database failed for Server 'SERVERNAME\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)

Unable to open the physical file "filename.mdf". Operating system error 5: "5(error not found)".(Microsoft SQL Server, Error: 5120)

To solve above error, make sure that the SQL Server Service account has modify permissions over the physical file on the hard drive that you are trying to attach to a SQL Server instance, and modify permissions also over the folder that contains the file.

Query to get details of permissions on Database objects

Use below query to get details of permissions on Database objects

SELECT ulogin.name AS [User Name],
CASE princ.type WHEN 'U' THEN 'Windows User' WHEN 'G' THEN 'Windows Group' WHEN 'S' THEN 'SQL User' END AS [User Type],
princ.name AS [Database User Name], perm.permission_name AS [Permission Type], perm.state_desc AS [Permission State],
CASE perm.class WHEN 1 THEN obj.type_desc ELSE perm.[class_desc] END AS [Object Type],
CASE perm.class WHEN 1 THEN OBJECT_NAME(perm.major_id) WHEN 3 THEN schem.[name] WHEN 4 THEN imp.[name] END AS [Object Name],
col.name AS [Column Name]
FROM sys.database_principals princ LEFT JOIN sys.server_principals ulogin on princ.sid = ulogin.sid LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id  LEFT JOIN sys.database_principals imp ON imp.principal_id = perm.major_id LEFT JOIN sys.columns col ON col.object_id = perm.major_id AND col.column_id = perm.minor_id  LEFT JOIN sys.schemas schem ON schem.schema_id = perm.major_id LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id
WHERE princ.[type] IN ('S','U','G') AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

Log backup to replace maintenance plan

Use below script to restore maintenance plan from log backup:

USE [Master]
GO
DECLARE @BackupExtention nVarChar(4); SET @BackupExtention = '.trn'
DECLARE @DB nVarChar(128); SET @DB = ''
DECLARE @BackupDir nVarChar(138);

--loop through each databASe in full recovery mode
WHILE @DB < ( SELECT MAX(Name) FROM sysDatabases WHERE DATABASEPROPERTYEX(Name,'RECOVERY') = 'FULL' )
BEGIN
SELECT @DB = MIN (Name)  FROM sysDatabases WHERE DATABASEPROPERTYEX(Name,'RECOVERY') = 'FULL' AND Name > @DB
DECLARE @DatabaseName nVarChar(128); SET @DatabaseName = @DB --SET @DatabaseName = 'TFSWarehouse'
DECLARE @DateTimestamp nVarChar(14); SET @DateTimestamp = '_' + CONVERT(VarChar, GetDate(), 112) + '_' + replace(CONVERT(VarChar, GetDate(), 108),':','')
DECLARE @RemoteBackupPath nVarChar(260);
SET @BackupDir = N'D:\Backup\' + @DatabaseName
SET @RemoteBackupPath = @BackupDir + N'\' + @DatabaseName + @DateTimestamp + @BackupExtention
DECLARE @BackupDevice nVarChar(128); SET @BackupDevice = N'local_' + @DatabaseName + @BackupExtention
--SELECT @DatabaseName, @BackupDevice, @RemoteBackupPath
--drop AND recreate the backup device
IF Exists(SELECT NULL FROM sys.Backup_Devices WHERE Name = @BackupDevice)
EXEC Master..sp_DropDevice @LogicalName = @BackupDevice
EXEC Master.dbo.sp_AddumpDevice @DevType = N'disk', @LogicalName = @BackupDevice, @PhysicalName = @RemoteBackupPath
--create the subdirectory if not already preset
EXECUTE Master.dbo.xp_Create_SubDir @BackupDir
--execute the backup
BACKUP LOG @DatabaseName TO  DISK = @RemoteBackupPath WITH NOFORMAT, NOINIT,  Name = @BackupDevice, SKIP, REWIND, NOUNLOAD,  STATS = 10
END
GO

Unable to open DTS package in SQL 2008 Management Studio

Sometime when we try to import a SQL Server 2000 DTS package into SQL Server 2008 R2 using SSMS, the following error appears:

Managing DTS packages in SQL Server Management Studio requires the SQL Server 2000 Data Transformation Services (DTS) runtime. To install the DTS runtime, on your SQL Server 2008 R2 installation media, locate and run the Windows Installer package for SQL Server 2005 Backward Compatibility (SQLServer2005_BC*.msi). You can also download the DTS runtime from the SQL Server Feature Pack page on the Microsoft Download Center. (Microsoft.SqlServer.DtsObjectExplorerUI)

As suggested, the “Microsoft SQL Server 2005 Backward compatibility” package must be installed. It can be found on this location: http://www.microsoft.com/en-us/download/details.aspx?id=3522

After the installation of “Microsoft SQL Server 2005 Backward compatibility” package the DTS package is imported successfully. Now when we try to open the DTS package, the following error appears:

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

To solve this error the “Sql Server 2000 DTS Designer Components” package must be installed. It can be found on this location: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11988

After the installation try to open again the DTS package in SSMS.   

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, “SQL Server 2000 DTS Designer Components” to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI)

If the same error appears, copy the DLL and RLL files as described in below Microsoft link: http://msdn.microsoft.com/en-us/library/ms143755.aspx

To ensure that the DTS designer can be used in SQL Server Management Studio

1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder.

2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id% folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id% folder.

For example, for U.S. English, the lang_id subfolder will be "1033".
To ensure that the DTS designer can be used in Business Intelligence Development Studio

1. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE folder.

2. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% folder. For example, for U.S. English, the lang_id subfolder will be "1033".

List Table with Identity Column

You can use below queries to get the list of all the tables with Identity column:

SELECT OBJECT_NAME(ID) AS [Table], Name AS [Column]
FROM syscolumns WHERE COLUMNPROPERTY(ID, Name, 'IsIdentity') = 1

Or use can use below query also

SELECT OBJECT_NAME(ID) AS [Table], Name AS [Column]
FROM syscolumns WHERE STATUS = 0x80

You can use blow query to check whether the table has Identity column:

SELECT Name AS [Table], OBJECTPROPERTY(ID, 'TableHasIdentity') AS [Has_Identity]
FROM SysObjects WHERE xType = 'U'

Cannot alter the table 'Table_Name' because it is being published for replication.

Today I was altering Primary key columns datatype in a table and got below error in Transaction replication.

Msg 4929, Level 16, State 1, Line 1
Cannot alter the table 'Table_Name' because it is being published for replication.

I had taken following steps to solve the problem

• Remove table from the replication.
• Change the datatype of Primary key column.
• Add again add table to the replication and reinitialize the publication.

Backup not starting for Database with Full Text Catalog

Today when I was taking backup of a database got below error:

Failed to change the status to RESUME for full-text catalog “Test_FullTextCatalog” in database “Test”. Error: 0×80043607(An internal interface is being used after the corresponding catalog has been shutdown. The operation will be aborted.).

When I checked, backup keeps pending on 0% without any progress. In SQL server error log, I also found error related to Full text catalog that SQL server is facing issue in setting Full test catalog status. These is some issue with FTS service due to which when backup ask FTS service to change Full text catalog status, it failed. SQL server backup change status between PAUSE & RESUME before & after backup. 

Than I started Full text Service to resolve the error. And than try to backup and now it worked successfully.

Start, Stop and Disable the job in SQL Server 2008

1) Use below procedure to start the Job in SQL

Exec msdb.dbo.sp_start_job @job_name = N'Job_Name'

Example:
Exec msdb.dbo.sp_start_job @job_name = N'Job_Name'

OR

Exec msdb.dbo.sp_start_job  N'Job name'

2) Use below procedure to stop the Job in SQL

Exec msdb.dbo.sp_stop_job @job_name = 'Job_Name'

Example:
Exec msdb.dbo.sp_stop_job @job_name = N'Job_Name'

OR

Exec msdb.dbo.sp_stop_job  N'Job name'

3) Use below procedure to enable or disable the Job in SQL

To enable the Job:

EXEC msdb.dbo.sp_update_job @job_name = N'Job_Name', @enabled = 1

To disable the Job:

EXEC msdb.dbo.sp_update_job @job_name = N'Job_Name', @enabled = 0

Clear recent server list from SSMS

Today someone has asked me a question "How to clear the Most Recently Used (MRU) server names from the connect screen in SSMS"? 
There is no feature in SSMS to clear the MRU from the Connect to Server screen. 

When I launched SSMS 2008 the Connect screen, It has a lot servers listed in it. This information is stored in a file "SQLStudio.bin". This file is located in the below folder.
C:\Documents and Settings\[UserName]\Application Data\Microsoft\Microsoft SQL Server\100\Tools\Shell

Now to clear the Server Names from the connect screen, First close the SSMS and than delete above file. If SSMS is left open while deleting file than again you will get the old Server name list. This is because SSMS update above file just before closing completely.

Queries waiting for memory

You can use below query to find out the queries that require a memory grant to execute or have acquired a memory grant. 
The queries that do not have to wait on a memory grant will not appear in the result. 
If this query returns a lot of rows than it could be a indication of internal memory pressure. This will help you to identify the queries which are requsting larger memory grants. There are various reason for this. The quiry might be poorly written. That may require some index for optimization. You should run this query periodically and check for the resource hungry queries.  The user who runs below query must have View SErver STate permission on the server.

SELECT DB_NAME(ST.DBID) AS [Database Name], MG.Requested_Memory_KB AS [Requested memoty in KB], MG.Ideal_Memory_KB AS [Ideal Memory in KB], MG.Request_Time AS [Request Time], MG.Grant_Time AS [Grant Time], MG.Query_Cost AS [Query Cost], MG.DOP, ST.[TEXT], QP.Query_Plan AS [Query Plan]
FROM SYS.DM_EXEC_QUERY_MEMORY_GRANTS AS MG CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST CROSS APPLY SYS.DM_EXEC_QUERY_PLAN(MG.PLAN_HANDLE) AS QP 
ORDER BY MG.REQUESTED_MEMORY_KB DESC

Who are connected to SQL Server

Use below query to check who are connected to SQL Server

SELECT C.Session_ID AS [Session], C.Most_Recent_Session_ID [Recent Session], C.Connect_Time, C.Last_Read, C.Last_Write, C.Num_Reads, C.Num_Writes, C.Net_Transport, C.Encrypt_Option, C.Auth_Scheme, C.Protocol_Type, C.Protocol_Version, C.Net_Packet_Size, C.Endpoint_ID, C.Client_Net_Address, C.Client_TCP_Port, C.Local_Net_Address, C.Local_TCP_Port, C.Node_Affinity, C.Connection_ID, C.Parent_Connection_ID, C.Most_Recent_SQL_HANDLE, ST.Text AS Query_Text, 
CASE WHEN ST.DBID = 32767 THEN 'RESOURCEDB' ELSE DB_Name(ST.DBID) END AS DATABASE_Name,
CASE WHEN ST.DBID IS NULL THEN NULL ELSE Object_Schema_Name(ST.ObjectID, ST.DBID) END AS Object_Schema_Name,
CASE WHEN ST.DBID IS NULL THEN NULL ELSE Object_Name(ST.ObjectID, ST.DBID) END AS Object_Name
FROM SYS.DM_Exec_Connections C CROSS APPLY SYS.DM_Exec_SQL_Text(C.Most_Recent_SQL_HANDLE) ST

Modify / Find SQL Server Agent log file

Use below queries to find the location of of SQLServer Agent log file, the log file is called SQLAGENT.out

DECLARE @AGENT_ERRORLOG NVARCHAR(255)
EXECUTE MASTER.DBO.XP_INSTANCE_REGREAD N'HKEY_LOCAL_MACHINE', N'SOFTWAREMICROSOFTMSSQLSERVERSQLSERVERAGENT', N'ERRORLOGFILE', @AGENT_ERRORLOG OUTPUT, N'NO_OUTPUT'

SELECT @@SERVERNAME SERVERNAME, @AGENT_ERRORLOG AGENTERRORLOGLOCATION

Use below queries to modify the location and Name of SQLServer Agent log file

USE [MSDB]
GO
EXEC MSDB.DBO.SP_SET_SQLAGENT_PROPERTIES @ERRORLOG_FILE=N'D:TEMPSQLAGENT.OUT'
GO

Use below queries to recycle the SQLServer Agent log file

EXEC MSDB.DBO.SP_CYCLE_AGENT_ERRORLOG

Performance Killers in MSSQL

• Frequent recompilation of execution plans
• Inaccurate statistics
• Excessive use or improper configuration of tempdb
• Excessive blocking and deadlocks
• Excessive fragmentation
• Improper use of cursors
• Improper configuration of the database log
• Non-reusable execution plans
• Non-set-based operations, usually T-SQL cursors
• Poor database design
• Poor query design
• Poor indexing
• Poor execution plans, usually caused by parameter sniffing

Sp_rename fails : Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong

You can get below error if you have ‘DOT’ in between your constraint name. SP_rename fails because object_id(ContraintnamewithDOT) returns NULL for constraint objects with DOT (.) in their name.

Sp_rename fails with Msg 15248, Level 11, State 1, Procedure sp_rename, Line 357
Either the parameter @objname is ambiguous or the claimed @objtype (object) is wrong.

The solution to above problem is to drop and recreate the constraint without DOT

Unused Index

Here is a useful script to get the unused index in a Database. This may be inaccurate sometime. So you had to apply commonsense before running DROP index query:

SELECT DB_Name() AS [Database], S.Name AS [Schema], O.Name AS [Object], C.Name AS [Column], I.Name AS [Index],
        (CASE WHEN I.IS_Disabled = 1 THEN 'Yes' ELSE 'No' END) AS [Disabled],
        (CASE WHEN I.IS_Hypothetical = 1 THEN 'Yes' ELSE 'No' END) AS Hypothetical,
        Rows = (SELECT SUM(P.Rows) FROM sys.Partitions P WHERE P.INDEX_ID = I.INDEX_ID 
                        AND P.OBJECT_ID = I.OBJECT_ID GROUP BY P.INDEX_ID, P.OBJECT_ID),
        N'USE ' + DB_Name() + N'; DROP INDEX ' + QuoteName(I.Name) + ' ON ' + QuoteName(S.Name) + 
                    '.' + QuoteName(OBJECT_Name(I.OBJECT_ID)) AS 'Drop Statement'
FROM [sys].[Objects] O 
        INNER JOIN [sys].[Indexes] I ON O.[OBJECT_ID] = I.[OBJECT_ID] AND O.[Type] = 'U' AND O.IS_MS_Shipped = 0 AND O.Name <> 'sysDiagrams'
        INNER JOIN [sys].[Tables] T ON T.[OBJECT_ID] = I.[OBJECT_ID]
        INNER JOIN [sys].[Schemas] S ON S.[Schema_ID] = T.[Schema_ID]
        INNER JOIN [sys].[Index_Columns] IC ON IC.[OBJECT_ID] = I.[OBJECT_ID] AND IC.INDEX_ID = I.INDEX_ID
        INNER JOIN [sys].[Columns] C ON C.[OBJECT_ID] = IC.[OBJECT_ID] AND C.COLUMN_ID = IC.COLUMN_ID
WHERE I.[Type] > 0 AND I.IS_Unique_Constraint = 0 AND I.IS_Primary_Key = 0 
        AND NOT EXISTS (SELECT * FROM [sys].[Foreign_Key_Columns] FKC INNER JOIN [sys].[Index_Columns] XIC ON FKC.Parent_COLUMN_ID = XIC.COLUMN_ID AND FKC.Parent_OBJECT_ID = XIC.[OBJECT_ID] WHERE XIC.[OBJECT_ID] = I.[OBJECT_ID] AND XIC.INDEX_ID = I.INDEX_ID)
        AND NOT EXISTS (SELECT * FROM [master].[sys].[DM_db_Index_Usage_Stats] IUS WHERE IUS.Database_ID = DB_ID(DB_Name()) AND IUS.[OBJECT_ID] = I.[OBJECT_ID] AND IUS.INDEX_ID = I.INDEX_ID)

SQL Agent DCOM error Event ID 10016

I got this error in my event log ID 10016 of my database server installed with Windows Server 2012, in which i have only installed SQL server 2012 database. It was running fine since last 15 days but since yesterday I have got a new problem in it is that i am unable to shut down this very server although i am doing the same with administrator account. It is in domain and none of the policies are implemented in it. I searched a lot on all blogs and even tried some nut none worked. The error is mentioned herein under.

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 
{6DF8CB71-153B-4C66-8FC4-E59301B8011B}
 and APPID 
{961AD749-64E9-4BD5-BCC8-ECE8BA0E241F}
 to the user NT SERVICE\SQL Server Distributed Replay Client SID (S-1-5-80-3249811479-2167633679-2115734285-1138413726-166979568) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

After googling i found the below  solutions and that worked for me.

Component services –> computers –> my computer –>DCOM config->MSDTSServer100 
    Right click and select Properties 
    Security 
    Launch and Activate permissions – edit 
Add the local SQL AgentUser and enable local launch and Local activation.

Refresh Intellisense in SSMS(SQL Server Management Studio) 2008 & above

Intellisense is a new feature that comes with SSMS(SQL Server Management Studio) 2008 onwards. Intellisense makes coding quite easy. Sometimes I find that intellisense becomes stale and you start getting the wavy red lines when writing code. Even though object exists in database but Intellisense is not able to recognize it.

Refreshing the cache is quite easy but not necessarily strictly required.

Go to Edit -> IntelliSense -> Refresh Local Cache OR you can use shortcut Ctrl + Shift + R