Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator

Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator


DBCC CHECKDB('databaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS 


--Resulted In
Msg 8909, Level 16, State 1, Line 5
Table error: Object ID 0, index ID 12341, page ID (1:5880). The PageId in the page header = (9728:16777220).
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object.
 

DBCC results for 'TableName'.
Msg 8928, Level 16, State 1, Line 5
Object ID 871674153, index ID 0: Page (1:5880) could not be processed. See other errors for details.

 
There are 20993 rows in 584 pages for object 'TableName'.
CHECKTABLE found 0 allocation errors and 8 consistency errors in table 'TableName' (object ID 871674153).
Msg 8909, Level 16, State 1, Line 5
Table error: Object ID 1109413712, index ID 24940, page ID (1:5883). The PageId in the page header = (25198:1632843825).
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 1109413712)' (object ID 1109413712).


Although its not an ideal solution, you can use DBCC CHECKTABLE which in our case fixed the issue:

--Put the database into single user mode
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH NO_WAIT
--Check the erors and fix any issues found (that you can)
DBCC CHECKTABLE ('Orders', REPAIR_REBUILD)
--Put the database back into multiuser mode
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT

Reference:

T-SQL code to get the active Connections for each Database.

--Method 1

 SELECT 

    DB_NAME(dbid) as DBName, 

    COUNT(dbid) as NoOfConnections,

    loginame as LoginName

FROM

    sys.sysprocesses

WHERE 

    dbid > 0

GROUP BY 

    dbid, loginame

--Method 2

EXEC sp_who2

--Method 3

SELECT  spid,

        sp.[status],

        loginame [Login],

        hostname, 

        blocked BlkBy,

        sd.name DBName, 

        cmd Command,

        cpu CPUTime,

        physical_io DiskIO,

        last_batch LastBatch,

        [program_name] ProgramName   

FROM master.dbo.sysprocesses sp 

JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid

ORDER BY DBName,spid

Delete all Tables based on creation date

--Delete all Tables based on creation date



 DECLARE @tname VARCHAR(100)
DECLARE @sql VARCHAR(max)

DECLARE db_cursor CURSOR FOR
SELECT name AS tname
FROM sys.objects
WHERE create_date < GETDATE() - 1-- Days old

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tname

WHILE @@FETCH_STATUS = 0
BEGIN
       SET @sql = 'DROP TABLE ' + @tname
       --EXEC (@sql)  -- For Executing
       PRINT @sql --For Printing

       FETCH NEXT FROM db_cursor INTO @tname
END

CLOSE db_cursor
DEALLOCATE db_cursor

Simplest way to Truncate/Delete all tables in a given database.

Simplest way to Truncate all tables in a given database.

Use databasename

EXEC sp_MSForEachTable 'Truncate TABLE ?' --- For Truncating all tables

Simplest way to Truncate all tables in a given database.

Use databasename

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  --- For Deleting all tables

How to Stop SQL Server Agent Job while it is actively running?

How to Stop SQL Server Agent Job while it is actively running?

Try running sp_who2 and find the SPID and kill the process using KILL SPID with status only so that it will show the rollbackup completed as well.

Difference between KILL and KILL WITH STATUSONLY – Estimate Rollback completion time

While I was talking to a customer during a replication scenario, customer asked me a question about understanding the estimated completion time for a rollback.

I was explaining that we have KILL WITH STATUSONLY but it will just report the progress only if the SPID was killed already and it will not kill the SPID also KILL WITH STATUSONLY will not help to track the progress of ROLLBACK issued by ROLLBACK TRAN. This was something new to him that KILL WITH STATUSONLY will not kill the SPID instead it only reports the progress. 

I thought of sharing this in a blog so that it will be useful for others who overlooked the KILL command.

Here is what BOL says:

KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.

Lab:

Step 1: I ran a long running batch from SPID 52 

Step 2: Issued a KILL using the command KILL 52

Step 3: Here is what I observed from SP_WHO2 52:

SPID	Status	Login	HostName	BlkBy	DBName	Command
52	SUSPENDED	abc	def	.	microsoft	KILLED/ROLLBACK

Step 4: Here is what I got when I ran KILL 52 WITH STATUSONLY:

spid 52: Transaction rollback in progress. Estimated rollback completion: 80% Estimated time left: 10 seconds.

Step 5: I tried to run the same statement from a different SPID 53 inside an explicit transaction and issued a ROLLBACK TRAN and this is the status when I ranSP_WHO2 53:

SPID	Status	Login	HostName	BlkBy	DBName	Command
53	ROLLBACK	abc	def	.	microsoft	ROLLBACK TRANSAC

Step 6: Here is the output of KILL 53 WITH STATUSONLY :

Msg 6120, Level 16, State 1, Line 1

Status report cannot be obtained. Rollback operation for Process ID 53 is not in progress.

So KILL WITH STATUS ONLY cannot be used to track the progress of  ROLLBACK issued by ROLLBACK TRAN. Hope this helps!

Reference: Sakthivel Chidambaram

TempDB usage per active session

Introduction

From time to time you find yourself needing to shrink some space out of TempDB. Shrinking database files is never my first choice but sometimes it is the best I have. Many people think that you cannot shrink TempDB in SQL 2005, but I am going to show you how.

Why would I need to shrink TempDB?

Yesterday afternoon my pager started going crazy because an Ad-Hoc query that needed some tuning filled TempDB on a server. Luckily, the user only impacted their own query so it was easy to quickly identify them and work with the right people to get the query rewritten.

Once the immediate problem was resolved there had to be some cleanup. On this server, TempDB has 32 files (1 per processor) all on the same disk. The full database condition caused all kinds of alerts in our monitoring tools, from drive space alerts to too few growths remaining. There were 3 possible solutions to quiet the alerts:

1. Reboot – There is never a good time to reboot a production server

2. Turn off the Alerts – Not really an option. My preference would be for increasing the sensitivity

3. Shrink TempDB – Not a great option, but the best of the 3

Shrinking TempDB

Once we had decided that we would go ahead and shrink the files in TempDB it seemed like the hard part was done, but after running the following command:

USE [tempdb]

GO

DBCC SHRINKFILE (N’tempdev’ , 5000)

GO

I got back the following:

DBCC SHRINKFILE: Page 1:878039 could not be moved because it is a work file page.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

—— ———– ———– ———– ———– ————–

2 1 878040 640000 4672 4672

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

“Page could not be moved because it is a work file page.”…grrr. This is a new thing in SQL 2005 caused by the caching that is done in TempDB. I am not going to try to explain here how objects are cached in TempDB, but Kalen Delaney’s Inside Sql Server Series is a great place to learn about it if you are interested (http://www.insidesqlserver.com/books.html). What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files.

Trying again:

DBCC FREEPROCCACHE

GO

USE [tempdb]

GO

DBCC SHRINKFILE (N’tempdev’ , 5000)

GO

This time it worked:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

—— ———– ———– ———– ———– ————–

2 1 640000 640000 264 264

(1 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I think I got lucky that the shrink worked on the first try. There will certainly be times when you have to try freeing the procedure cache and shrinking multiple times to get a file to shrink, but eventually it will get the job done.

Tempdb is hard to shrink. Some pages cannot be moved because they are actively being used by system processes, so your chances of shrinking tempdb sink.

I see that you already have tried clearing all the caches, which sometimes helps, but is not guaranteed to work.
Try to identify what is using tempdb:

http://www.sqlservercentral.com/scripts/tempdb/72007/

Even if usage is low, a single non movable page is enough to make the shrink process ineffective.

If you can't shrink it, I suggest that you plan a downtime to restart the service and let tempdb restart from its initial size.

Lists the TempDB usage per each active session.
It helps identifying the sessions that use the tempdb heavily with internal objects.

When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom of
 an inefficient plan or a missing index.

Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space.
The only possible alternative to restarting the service, is running DBCC FREESYSTEMCACHE('ALL'), that will clear all cached objects,
 including not only internal objects, but also cached query plans. Use it carefully on a production server.

;WITH task_space_usage AS (
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS alloc_pages,
           SUM(internal_objects_dealloc_page_count) AS dealloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT TSU.session_id,
       TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
       TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
       EST.text,
       -- Extract statement from sql text
       ISNULL(
           NULLIF(
               SUBSTRING(
                   EST.text,
                   ERQ.statement_start_offset / 2,
                   CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset THEN 0 ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
               ), ''
           ), EST.text
       ) AS [statement text],
       EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
    ON  TSU.session_id = ERQ.session_id
    AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC, 5 DESC



Reference: http://adventuresinsql.com/2009/12/how-to-shrink-tempdb-in-sql-2005/

Get SQL Server Physical Cores, Physical and Virtual CPUs, and Processor type information using Transact-SQL (T-SQL) scrip

• Total number of physical CPUs
• Total number of physical cores per CPUs
• Total number of physical cores
• Total number of virtual CPUs
• Processor type (x86 or x64)

As a result of this question, I wrote the following script using sys.dm_os_sys_info and xp_msver, which returns the required information:

exec xp_msver 'ProcessorCount'

Query Starts Here

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

DECLARE @xp_msver TABLE (     [idx] [int] NULL     ,[c_name] [varchar](100) NULL     ,[int_val] [float] NULL     ,[c_val] [varchar](128) NULL     ) INSERT INTO @xp_msver EXEC ('[master]..[xp_msver]');; WITH [ProcessorInfo] AS (     SELECT ([cpu_count] / [hyperthread_ratio]) AS [number_of_physical_cpus]         ,CASE             WHEN hyperthread_ratio = cpu_count                 THEN cpu_count             ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))             END AS [number_of_cores_per_cpu]         ,CASE             WHEN hyperthread_ratio = cpu_count                 THEN cpu_count             ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))             END AS [total_number_of_cores]         ,[cpu_count] AS [number_of_virtual_cpus]         ,(             SELECT [c_val]             FROM @xp_msver             WHERE [c_name] = 'Platform'             ) AS [cpu_category]     FROM [sys].[dm_os_sys_info]     ) SELECT [number_of_physical_cpus]     ,[number_of_cores_per_cpu]     ,[total_number_of_cores]     ,[number_of_virtual_cpus]     ,LTRIM(RIGHT([cpu_category], CHARINDEX('x', [cpu_category]) - 1)) AS [cpu_category] FROM [ProcessorInfo]

The code of this script is tested on SQL Server 2005 and above versions.

Reference: https://basitaalishan.com/2014/01/22/get-sql-server-physical-cores-physical-and-virtual-cpus-and-processor-type-information-using-t-sql-script/