I think the DMV you're looking for is called something like sys.dm_index_physical_stats.  There'll be plenty of sample queries out there if you search for them.

John

The referenced page mentions that the connection must identify itself as the application in question.  I answered that this isn't possible, since any connection can identify itself as any application.

Difficult to diagnose after the event.  Maybe out-of-date statistics, or parameter sniffing.  If you could capture some actual execution plans next time it happens, that would help.  Do you have...

CREATE TABLE #PleasePostConsumableDDLNextTime (
     id int
,    status char(4)
,    crdate date
    );
INSERT INTO #PleasePostConsumableDDLNextTime
VALUES
     (1,'open','01/31/2017')
,    (1,'IP','03/31/2017')
,    (1,'cl','05/31/2017')

SELECT
     p.id
,    p.status

With regard to ignoring rows already processed in subsequent iterations, xp_readerrorlog includes a start date parameter.  You can log in a table each use of your process, and then include...

I think you probably need to look at Change Data Capture or security auditing, although I think the latter capture DDL, not DML, changes.

John

Good spot.  A well-designed table would have a CHECK constraint to stop that from happening.  (Un)fortunately, though, it doesn't change the logic of this query, since we're only need to...

...
WHERE MONTH(startdate) = MONTH(enddate)
AND YEAR(startdate) = YEAR(enddate)
GROUP BY
     YEAR(startdate)
,    MONTH(startdate)

Poornima

Yes, the SQL Server service account needs to have permission to access the files.  I think the problem may be to do with your attempt to create a...

The answer's yes, but only because I think you asked the wrong question.  You can truncate a table that has a foreign key constraint, but you can't truncate a table...

You can still do that without a cursor by using a WHERE clause in your delete statement.  It's difficult if you can't share all your code.

The answer your...

From what I can tell, you're deleting one row at a time from Test_A until the table is empty.  Is that right?  Why use a cursor instead of deleting all...

SELECT
     Day
,    CASE
        WHEN [Semester Week nu] > 26 THEN [Semester Week nu] -26
        ELSE [Semester Week nu]
    END AS SemesterWeekNo
FROM #currentDates

Or this:
DECLARE @date float = 201601;
SELECT DATEFROMPARTS(CAST(@date/100 AS int),CAST(@date AS int)%100,1)

John