Saturday, September 28, 2013

[MS SQL Server] CACHESTORE_SQLCP flush automatically?

[MS SQL Server] CACHESTORE_SQLCP flush automatically?

CACHESTORE_SQLCP flush automatically?

Posted: 27 Sep 2013 11:38 PM PDT

Hi.I have checked my production database for top memory consumer components Database enabled - AdHoc workloads and force parameterzation (for the reason too much single plans created and wasted memory)1. CACHESTORE_SQLCP - using Constantly 2.5GB due to database do not have SP and all.. Application request Ad Hoc query only..2. TokenAndPermUserStore - using 800 MBI cleared the TokenAndPermUserStore by using command (dbcc freesystemcache('TokenAndPermUserStore') and automatically reduce the CACHESTORE_SQLCP memory usage, what could be reason? Thanksananda

Log not available error 9001

Posted: 19 Dec 2010 11:31 PM PST

Came in to work this morning to face a bunch of alerts for severity 21 errors."DESCRIPTION: The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database."Hmm.. The drive the log is on was available and logs for other DB were on it. Plenty of space left. Window Application event log showed no errors other than the one listed above. Windows System log showed no errors. I ran dbcc checkdb on the database and the only error reported was that the log was not available.I took the database offline, then brought it online again and all seems good now. DBCC Checkdb gives no errors. DBCC loginfo(0) gives info, so I'm assuming the log is available. Now just trying to figure out what happened.Took a closer look at the SQL error log and I see this:12/20/2010 02:30:01,spid20s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:30:01,spid20s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:20:29,spid18s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:20:29,spid18s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:00:14,spid217,Unknown,Recovery completed for database NetPerfMon (database ID 18) in 6 second(s) (analysis 1 ms<c/> redo 1212 ms<c/> undo 4437 ms.) This is an informational message only. No user action is required.12/20/2010 02:00:13,spid217,Unknown,1 transactions rolled back in database 'NetPerfMon' (18). This is an informational message only. No user action is required.12/20/2010 02:00:09,spid217,Unknown,21 transactions rolled forward in database 'NetPerfMon' (18). This is an informational message only. No user action is required.12/20/2010 02:00:03,spid19s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:00:03,spid19s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:00:03,spid212,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.12/20/2010 02:00:03,spid212,Unknown,Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.12/20/2010 02:00:03,spid15s,Unknown,Transaction (Process ID 15) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.12/20/2010 02:00:03,spid15s,Unknown,Error: 1205<c/> Severity: 13<c/> State: 51.12/20/2010 02:00:03,spid15s,Unknown,Transaction (Process ID 15) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.12/20/2010 02:00:03,spid15s,Unknown,Error: 1205<c/> Severity: 13<c/> State: 51.12/20/2010 02:00:02,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 02:00:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 01:30:01,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 01:30:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 01:00:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 01:00:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 00:30:01,spid206,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 00:30:01,spid206,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 00:00:23,spid18s,Unknown,This instance of SQL Server has been using a process ID of 1788 since 12/7/2010 9:47:54 PM (local) 12/8/2010 3:47:54 AM (UTC). This is an informational message only; no user action is required.12/20/2010 00:00:01,spid295,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 00:00:01,spid295,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 23:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 23:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 23:00:01,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 23:00:01,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 22:30:02,spid210,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 22:30:01,spid210,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 22:00:01,spid223,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 22:00:01,spid223,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 21:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 21:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 21:00:01,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 21:00:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 20:30:02,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 20:30:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 20:00:01,spid222,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 20:00:01,spid222,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 19:30:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 19:30:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 19:00:03,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 19:00:02,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 18:30:01,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 18:30:01,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 18:00:01,spid218,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 18:00:01,spid218,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 17:30:01,spid196,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 17:30:01,spid196,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 17:00:02,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 17:00:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 16:30:01,spid214,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 16:30:01,spid214,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 16:00:01,spid229,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 16:00:01,spid229,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 15:30:02,spid205,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 15:30:02,spid205,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 15:00:02,spid219,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 15:00:01,spid219,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 14:30:01,spid213,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 14:30:01,spid213,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 14:00:01,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 14:00:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 13:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 13:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 13:00:02,spid223,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 13:00:01,spid223,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 12:30:01,spid222,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 12:30:01,spid222,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 12:00:02,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 12:00:01,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 11:30:01,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 11:30:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 11:00:01,spid220,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 11:00:01,spid220,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 10:30:01,spid206,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 10:30:01,spid206,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 10:00:02,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 10:00:02,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 09:30:02,spid214,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 09:30:01,spid214,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 09:00:03,spid225,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 09:00:01,spid225,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 08:30:01,spid199,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 08:30:01,spid199,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 08:00:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 08:00:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 07:30:02,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 07:30:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 07:00:01,spid216,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 07:00:01,spid216,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 06:30:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 06:30:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 06:00:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 06:00:00,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 05:30:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 05:30:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 05:00:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 05:00:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:30:01,spid207,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:30:01,spid207,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:00:04,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:00:04,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:00:00,spid207,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:00:00,spid207,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 03:30:01,spid198,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 03:30:01,spid198,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 03:00:05,spid234,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 03:00:04,spid234,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 02:52:32,spid78,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 02:52:32,spid78,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 02:52:29,spid71,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 02:52:29,spid71,Unknown,Starting up database 'SpotlightManagementFramework'.2 AM is when my maintenance jobs run. It looks like, for some reason, checkdb got stuck in a loop on this database and had tons of spids working. The first "log uinavailable" error occurs at 12/20/10 2:00:03 with a state value of 5. The subsequent ones all have a state value of 1. I haven't been able to find out what the state value represents. I also noticed that the DBCC CHECKDB messages are appearing at times when DBCC is not scheduled to be run - at roughly half hour intervals through out the day. My transaction log backups run then, but I have verified that job does not perform dbcc.It looks like the clusters of checkdb messages started happening on 12/7/10 at 10 PM, which is just after I restarted the server after applying SQL 2008 SP2.Anyone have any ideas?

Calculating Memory usage

Posted: 27 Sep 2013 03:15 PM PDT

Hi, I wanted to calculate the Total SQL Server Memory usage using DMVs.From sql server 2008, I have below direct query. What I am trying is to get the same value using sys.dm_os_memory_clerks. For memory troubleshooting we normally use DBCC MEMORYSTATUS and sys.dm_os_memory_clerks.I am personally comfortable with tsql and wanted to use sys.dm_os_memory_clerks.Infact, I use sys.dm_os_memory_clerks to see where sql server memory has gone or in other wordswhich component is taking up more memory within sql server. But here, i am interested in only calculating total sql server memory usage which i am not able to get using below query. Notes: I am working on SQL 2008 Developer Edition , 10.0.5500 x64, OS 64bit i.e. win 7 sp1 and LPM is enabled as service account is Local System.-- sql 2008 direct query to find out sql server overall memory usage select virtual_address_space_committed_kb/(1024) as [Total Overall SQL Server Memory usage(MB)]--RAM+pagefile. Does this include AWE and MTL memory as well when we say commit;from sys.dm_os_process_memorygo--output --249 mb-- Reference Link:http://blogs.msdn.com/b/sqljourney/archive/2013/03/16/an-in-depth-look-at-sql-server-memory-part-3.aspxI am trying get the same value using sys.dm_os_memory_clerks. Why am I not getting the same value. Am I missing anything to sum up? Basically, I wanted to use this query for sql 2005 instances. select SUM(single_pages_kb+multi_pages_kb+awe_allocated_kb+shared_memory_committed_kb+virtual_memory_committed_kb)/1024 from sys.dm_os_memory_clerks --output --144 mb I am getting 144 as my output, above sql 2008 query returns 249 value. Why there is a difference? I calculated using perfmon counters i.e Process:Private Bytes(Sqlservr) + SQL Buffer Manager: Total Pages which I am getting value close to 245 which is acceptable.Reference Link :http://blogs.msdn.com/b/joesack/archive/2009/01/08/find-non-buffer-pool-memory-memtoleave-in-private-bytes.aspxBut why using sys.dm_os_memory_clerks I am getting a low value ?Please help.Thank you.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog