I am looking for an API to flush all data in memory held by SQL Server
to disk. Also, is there a tool for SQL Server like eseutil for
Exchange that lets you correct a SQL database?
Hi,
A long time ago I posted this: http://blogs.conchango.com/jamiethomson/archive/2005/06/09/1583.aspx explaining all the different buffer types in the pipeline.
I have to admit I'm still not clear on the difference between a private buffer and a flat buffer though.
Are flat buffers a subset of private buffers.
If so, if a private buffer is not a flat buffer - what is it?
If not, can a buffer be a private buffer AND a flat buffer?
Some descriptions from BOL are:
Private buffers: A private buffer is a buffer that a transformation uses for temporary work only
Flat buffers: Flat buffers are blocks of memory that a component uses to store data
That sounds like two ways of saying the same thing to me! It certainly doesn't distinguish them anyway!
Just seeking some clarification here. If you could whip of a demo package that explains the difference between the two (with reference to the Performance Counters) then that would be great.
Thanks
Jamie
While waiting for the fax of instructions to contact MS Support, I thought I would post here (tried several times and no fax...)
We get this message in the log file and then all hell breaks loose until it resets memory. The SQL Service continues working but nobody can connect for about 5 minutes and then is seems to reset itself. This has happened three times over the past two weeks. Only one time it did create the SQLDUMP files but all three occurences have practically the same entries.
We are running SQL Server 2005 x64 SP2 under Windows 2003 x64 SP1. We have 4GB RAM and SQL is configured to use 2GB of it. We have a large number of databases (about 400) on this one instance that experiences this problem. The server itself is not under a tremendous load. All of the databases were recently upgraded from SQL 2000 SP4 32 bit instance. The first occurence happened just days after the migration.
----- Log Entries -----
LazyWriter: warning, no free buffers found.
2007-06-14 14:15:56.18 spid3s
Memory Manager
VM Reserved = 4415288 KB
VM Committed = 4398048 KB
AWE Allocated = 0 KB
Reserved Memory = 1024 KB
Reserved Memory In Use = 0 KB
2007-06-14 14:15:56.18 spid3s
Memory node Id = 0
VM Reserved = 4409528 KB
VM Committed = 4392376 KB
AWE Allocated = 0 KB
SinglePage Allocator = 1951840 KB
MultiPage Allocator = 81680 KB
2007-06-14 14:15:56.18 spid3s
MEMORYCLERK_SQLGENERAL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 39264 KB
MultiPage Allocator = 21944 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLBUFFERPOOL (Total)
VM Reserved = 4214784 KB
VM Committed = 4198400 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 8 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLQUERYEXEC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 264 KB
MultiPage Allocator = 104 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLOPTIMIZER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 26816 KB
MultiPage Allocator = 96 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLUTILITIES (Total)
VM Reserved = 120 KB
VM Committed = 120 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4728 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLSTORENG (Total)
VM Reserved = 96768 KB
VM Committed = 96768 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 62192 KB
MultiPage Allocator = 27624 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLCONNECTIONPOOL (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4392 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLCLR (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLSERVICEBROKER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 624 KB
MultiPage Allocator = 320 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLHTTP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SNI (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 568 KB
MultiPage Allocator = 16 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_FULLTEXT (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLXP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_BHF (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 336 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLQERESERVATIONS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8192 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_HOST (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 24 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SOSNODE (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 42376 KB
MultiPage Allocator = 27392 KB
2007-06-14 14:15:56.20 spid3s
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 48 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_OBJCP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 352016 KB
MultiPage Allocator = 432 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_SQLCP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 953192 KB
MultiPage Allocator = 3088 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_PHDR (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 308728 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_XPROC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 56 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_TEMPTABLES (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 128 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_NOTIF (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_VIEWDEFINITIONS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_XMLDBTYPE (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_XMLDBELEMENT (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_XMLDBATTRIBUTE (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_STACKFRAMES (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 0 KB
MultiPage Allocator = 8 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_BROKERTBLACS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 96 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_BROKERKEK (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_BROKERDSH (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_BROKERUSERCERTLOOKUP (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_BROKERRSB (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_BROKERREADONLY (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 32 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_BROKERTO (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 8 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_EVENTS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 16 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
CACHESTORE_SYSTEMROWSET (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 43744 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
USERSTORE_SCHEMAMGR (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 30632 KB
MultiPage Allocator = 16 KB
2007-06-14 14:15:56.20 spid3s
USERSTORE_DBMETADATA (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 39856 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
USERSTORE_TOKENPERM (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 14536 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
USERSTORE_OBJPERM (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 14904 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
USERSTORE_SXC (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 936 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
OBJECTSTORE_LBSS (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 64 KB
MultiPage Allocator = 400 KB
2007-06-14 14:15:56.20 spid3s
OBJECTSTORE_SNI_PACKET (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 6584 KB
MultiPage Allocator = 48 KB
2007-06-14 14:15:56.20 spid3s
OBJECTSTORE_SERVICE_BROKER (Total)
VM Reserved = 0 KB
VM Committed = 0 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 272 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.20 spid3s
OBJECTSTORE_LOCK_MANAGER (Total)
VM Reserved = 8192 KB
VM Committed = 8192 KB
AWE Allocated = 0 KB
SM Reserved = 0 KB
SM Committed = 0 KB
SinglePage Allocator = 4232 KB
MultiPage Allocator = 0 KB
2007-06-14 14:15:56.21 spid3s Buffer Distribution: Stolen=22764 Free=0 Cached=221235
Database (clean)=268686 Database (dirty)=3406
I/O=0, Latched=5
2007-06-14 14:15:56.21 spid3s Buffer Counts: Committed=516096 Target=523990 Hashed=272097
Internal Reservation=104798 External Reservation=1022
Stolen Potential=252769
Min Free=128 Visible=523990
Available Paging File=4634734592
2007-06-14 14:15:56.21 spid3s Procedure Cache: TotalProcs=11768 TotalPages=202189 InUsePages=950
2007-06-14 14:15:56.21 spid3s Process physical/virtual memory pressure: 0/0 System physical memory pressure: 0
2007-06-14 14:15:56.23 spid3s Global Memory Objects:
Resource= 2880
Locks= 532
SE Schema Mgr= 3653
SQLCache= 1622
Replication= 3
ServerGlobal= 49
XPGlobal= 2
Xact= 165
SETLS= 4
DatasetMemObjs= 8
SubpDescPmos= 4
SortTables= 2
2007-06-14 14:15:56.23 spid3s Query Memory Objects: Grants=8 Waiting=0 Maximum=226034 Available=225010 Limit=226048
2007-06-14 14:15:56.23 spid3s Query Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=237945
2007-06-14 14:15:56.23 spid3s Small Query Memory Objects: Grants=0 Waiting=0 Maximum=11895 Available=11895 Limit=11895
2007-06-14 14:15:56.24 spid3s
Optimization Queue
Overall Memory = 3440517120 Target Memory = 1485357056 Last Notification = GROW Timeout = 6 Early Termination Factor = 5
2007-06-14 14:15:56.24 spid3s
Small Gateway
Configured Units = 16 Available Units = 16 Acquires = 0
Waiters = 0 Threshold Factor = 380000 Threshold = 380000
2007-06-14 14:15:56.24 spid3s
Medium Gateway
Configured Units = 4 Available Units = 4 Acquires = 0
Waiters = 0 Threshold Factor = 12 Threshold = -1
2007-06-14 14:15:56.24 spid3s
Big Gateway
Configured Units = 1 Available Units = 1 Acquires = 0
Waiters = 0 Threshold Factor = 8 Threshold = -1
2007-06-14 14:15:56.24 spid3s
MEMORYBROKER_FOR_CACHE
Allocations = 221239
Rate = 0
Target Allocations = 379816
Future Allocations = 0
Last Notification = GROW
2007-06-14 14:15:56.24 spid3s
MEMORYBROKER_FOR_STEAL
Allocations = 22741
Rate = 0
Target Allocations = 181318
Future Allocations = 0
Last Notification = GROW
2007-06-14 14:15:56.24 spid3s
MEMORYBROKER_FOR_RESERVE
Allocations = 1024
Rate = 0
Target Allocations = 253810
Future Allocations = 95233
Last Notification = GROW
2007-06-14 14:39:56.82 Server Resource Monitor (0x1180) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 148160 KB. Approx CPU Used: kernel 125 ms, user 62 ms, Interval: 65000.
2007-06-14 14:40:56.84 Server Resource Monitor (0x1180) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 218536 KB. Approx CPU Used: kernel 328 ms, user 93 ms, Interval: 125046.
2007-06-14 14:41:56.84 Server Resource Monitor (0x1180) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 288960 KB. Approx CPU Used: kernel 515 ms, user 125 ms, Interval: 185046.
2007-06-14 14:42:56.84 Server Resource Monitor (0x1180) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 366008 KB. Approx CPU Used: kernel 718 ms, user 171 ms, Interval: 245046.
2007-06-14 14:43:56.84 Server Resource Monitor (0x1180) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 435992 KB. Approx CPU Used: kernel 968 ms, user 296 ms, Interval: 305046.
2007-06-14 14:44:56.84 Server Resource Monitor (0x1180) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 505160 KB. Approx CPU Used: kernel 1203 ms, user 390 ms, Interval: 365046.
2007-06-14 14:45:56.84 Server Resource Monitor (0x1180) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 572488 KB. Approx CPU Used: kernel 1468 ms, user 468 ms, Interval: 425046.
2007-06-14 14:46:56.84 Server Resource Monitor (0x1180) Worker 0x000000008000C1C0 appears to be non-yielding on Node 0. Memory freed: 639056 KB. Approx CPU Used: kernel 1703 ms, user 500 ms, Interval: 485046.
hello,all
I am new to Sql 2000,I installed sql 2000 database in C disk,but Now I found my C disk space is smaller than before,So I want to move my databse(include data and structure) from C Disk to D Disk(its space is very large) .
is it possible to do it ?
if its can be done ,do I need to change my asp.net program source code (exp: chaneg my crystal report connectstring ) ?
thanks in advanced!
Hi all,
Ok here goes,
I have a three tier system using SQL server 2000, we are currently experiencing IO bottle necks on our SCSI Raid 10 array, which holds the Data and the logs in separate partitions.
So my options as I understand it are:
Get Enterprise edition
or
Get another physical raid 10 array and separate the logs and data i.e. data on one array and logs on the other array.
I would like to try the latter but I am totally unsure how much difference this will make or whether it will make any difference at all.
Does anyone know how much performance increase I will get from using two arrays as opposed to one?
Any other advice on this scenario would be greatly appreciated.
Thanks
Someone told me they closed there window and lost their query and wanted to know if there was some way to get it back from a buffer or something. Does anyone know if that is possible? I was not aware of anything like that, but the way that SQL caches and buffers things I thought it might be possible extract a query from a buffer or something...?
Cheers,
-Ryan
Occasionally on my SQL 2K SP3 Standard servers. The servers lock up withthe error.LazyWriter: warning, no free buffers found.After that I have to restart the SQLServer service to get things up andrunning. How can I prevent this from happening?Thanks,Ray
View 1 Replies View RelatedI have a long running (48 hour+) stored proc. I've added PRINT statements that print "1%", "2%", etc to provide progress so I can get a ballpark idea of how far along the process is. I ran the stored proc and realized that this won't work. I don't see the output of the print statements until the stored proc is completed. I'm running this from Query Analyzer (SQL Server 2000 SP3a).
For example, the following will wait ten seconds and then print both statements; it doesn't print one then wait, then print the other. Is there any flush command to make the print statements take effect immediately?
PRINT 'before'
WAITFOR DELAY '000:00:10'
PRINT 'after'
Quick question. Is it possible to Flush the tables in SQL Server, I know you can do it in MySQL, but not sure on SQL Server?
Thanks
Dear All,
i'm trying increase the performance of one select statement.
after trying first time, at the second time it is giving results fastly.because the data is there already in the buffers.
how can i clean the buffers everytime after run the query?
i'm using
--dbcc dropcleanbuffers
--dbcc freeproccache
are these enough or need some more....
please guide me
Vinod
Even you learn 1%, Learn it with 100% confidence.
I got the following error. What is it?
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Canada DBA
Is there a clean way to flush the query cache so I can simulate the first execution of an SQL statement any time I want to?
View 3 Replies View RelatedI want to trace the user logins by using a stored procedure. This script (sp_login_trace) is created by the SQL Profiler tool. (Once this procedure works well, I will use sp_procoption to run it automatically everytime the SQL Server startup.)
After I successfully created sp_login_trace, I run it (exec sp_login_trace). The trace process is started and TraceID is 1. (I use select * from ::fn_trace_getinfo(default) to verify it). However the file size of login_trace.trc is always 0 even after I use Query Ananlysis or Eneterprise manager to let some users to login into the SQL Server instance. (when I use SQL Profiler to start a trace, the trace file size will increase along with users continaully login in). At that time if I use SQL Profiler to open the login_trace.trc file, the system will give me an error message: No data since Empty File.
After I stop and delete the trace process, I find that the file size of login_trace.trc becomes 128K and I can see the login records caught by sp_login_trace if I use SQL Profiler to open this file again.
How can I flush the buffer to trc file frequently without need of stopping trace process?
Thanks for helps in advance.
Leon
Could anybody help me on how to do buffer flush before program exit?
I am using OLE DB to insert record to SQL CE database, but cannot control data commit, each time if I exit too quick, then all of inserted record cannot stored into database .sdf file. Thanks a lot!
If I return the Average, Minimum, and Maximum values for the counter Physical Disk: Avg. Disk Queue Length, and those values are 10, 0, 87 respectively, which value do I use to compute the Avg. Disk Queue Length for a 4 disk array(RAID 10): Average, Minimum, or Maximum? The disk(lun) is on a SAN.
Hi all
Using SQL 2000 MSDE
I'm bulk inserting about 3.200.000 records into a table
unfortunately all memory dissapears and never returns the dirty buffers count goes up to 48000 approx.
any ideas on how to rectify this ..... ?
I have detached a SQL Server 2005 database from one server and attached it to another SQL Server 2005 and I now get the following in the error Log and in the event viewer evry 10 - 20 minutes or so.
SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Object Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-03-16 12:37:14.64 spid17s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'SQL Plans' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
2007-03-16 12:37:14.64 spid17s SQL Server has encountered 1 occurrence(s) of cachestore flush for the 'Bound Trees' cachestore (part of plan cache) due to some database maintenance or reconfigure operations.
Starting up database 'DBName'
It appears under different SPID's 18, 20, 24 ...... and consistently has the 4 errors in a row
Full Text indexing is running for both servers but I dont know if this is the cause of the error.
I would greatly appreciate any help to get rid of this as I have trawled the net and not found anything of use.
Thank You.
-- Initialize Control Mechanism
DECLARE@Drive TINYINT,
@SQL VARCHAR(100)
SET@Drive = 97
-- Setup Staging Area
DECLARE@Drives TABLE
(
Drive CHAR(1),
Info VARCHAR(80)
)
WHILE @Drive <= 122
BEGIN
SET@SQL = 'EXEC XP_CMDSHELL ''fsutil volume diskfree ' + CHAR(@Drive) + ':'''
INSERT@Drives
(
Info
)
EXEC(@SQL)
UPDATE@Drives
SETDrive = CHAR(@Drive)
WHEREDrive IS NULL
SET@Drive = @Drive + 1
END
-- Show the expected output
SELECTDrive,
SUM(CASE WHEN Info LIKE 'Total # of bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS TotalBytes,
SUM(CASE WHEN Info LIKE 'Total # of free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS FreeBytes,
SUM(CASE WHEN Info LIKE 'Total # of avail free bytes : %' THEN CAST(REPLACE(SUBSTRING(Info, 32, 48), CHAR(13), '') AS BIGINT) ELSE CAST(0 AS BIGINT) END) AS AvailFreeBytes
FROM(
SELECTDrive,
Info
FROM@Drives
WHEREInfo LIKE 'Total # of %'
) AS d
GROUP BYDrive
ORDER BYDrive
E 12°55'05.25"
N 56°04'39.16"
Hello,
I am trying to setup a test cluster and am having an issue. When I try to create the resource of a physical disk it takes both the drive e: and drive q: and doesn't seperate them into two physical disks as resources. This means when I try to associate the quorum disk it links the to physcial disk resource of drive e and q. Then when I try to install SQL2k5 I get the warning about installing SQL on the quorum disk. Am I missing something? Is there a way to seperate e and q onto two physical disk resources so I can specifically associate the quorum to q and the sql to e or should I be setting the quorum disk to a majority node set? Thanks in advance.
John
I have corrected the problem with named pipes versus TCP/IP but I have found a reference to lrustats stating that page flushes should be less than 100 and free avg less than 10 but my numbers are extremely high. One of the things I noticed upon assuming this job was that many settings were off a bit bit LogLRU buffers were never set.This setting was introduced with service pack 2 with a readme.txt on how to configure. All my references and the ones that I have skimmed through in bookstores do not contain this configuration setting.
Microsoft no longer offers SP2 for download so that I could get the readme.txt. If any of you have this file or service pack laying around, please email it to me so that I can configure. It is suppose to take care of some bufwait errors. I even download the French version to see if it was there and maybe I could find someone with a french background but it did not contain the file. It was in last years Technet CD but my company tossed it when they got the new one. Can anyone help?
I'm working on a stored procedure, and I'm nearing completion. The procedure takes a while to run (it's a very large dataset), but that's not my issue. The problem is that I include several print statements, but I don't get to see any of them until the entire procedure has finished. Is there anything I can do to be able to see them as they are printed? Using sql server 2000 with management studio for 2005.
View 2 Replies View Relatedhi
We have use SQL Server 2005 on 64bit windows 2003 server Cluster. SQL instance stop responding for some leaving application interrupted.
In the Sql Err log I noticed the following information memory errors just before cluster issued stop command to SQlsrvres. Lazywriter gave warning that no free buffers found .
6/20/08 11:02 AMWarning[098] SQLServerAgent terminated (forcefully)
6/20/08 11:02 AMError[382] Logon to server '(local)' failed (DisableAgentXPs)
6/20/08 11:02 AMError[298] SQLServer Error: 258<c/> Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]
6/20/08 11:02 AMError[165] ODBC Error: 0<c/> Login timeout expired [SQLSTATE HYT00]
6/20/08 11:02 AMError[298] SQLServer Error: 258<c/> TCP Provider: Timeout error [258]. [SQLSTATE 08001]
6/20/08 11:02 AMError[311] Thread 'AlertEngine' (ID 3812) is still running
6/20/08 11:02 AMError[311] Thread 'JobInvocationEngine' (ID 860) is still running
6/20/08 11:02 AMError[240] 2 engine thread(s) failed to stop after 30 seconds of waiting
6/20/08 11:01 AMInformation[131] SQLSERVERAGENT service stopping due to a stop request from a user<c/> process<c/> or the OS...
6/20/08 10:58 AMspid2sUnknownMEMORYBROKER_FOR_RESERVE <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYBROKER_FOR_STEAL <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYBROKER_FOR_CACHE <nl/>
6/20/08 10:58 AMspid2sUnknownBig Gateway <nl/>
6/20/08 10:58 AMspid2sUnknownMedium Gateway <nl/>
6/20/08 10:58 AMspid2sUnknownSmall Gateway <nl/>
6/20/08 10:58 AMspid2sUnknownOptimization Queue <nl/>
6/20/08 10:58 AMspid2sUnknownSmall Query Memory Objects: Grants=0 Waiting=0 Maximum=12800 Available=12800 Limit=12800
6/20/08 10:58 AMspid2sUnknownQuery Memory Objects: Next Request=0 Waiting For=0 Cost=0 Timeout=0 Wait Time=0 Last Target=787875
6/20/08 10:58 AMspid2sUnknownQuery Memory Objects: Grants=3 Waiting=0 Maximum=775075 Available=774691 Limit=775075
6/20/08 10:58 AMspid2sUnknownGlobal Memory Objects: <nl/>
6/20/08 10:58 AMspid2sUnknownProcess physical/virtual memory pressure: 0/0 System physical memory pressure: 0
6/20/08 10:58 AMspid2sUnknownProcedure Cache: TotalProcs=10446 TotalPages=117316 InUsePages=6442
6/20/08 10:58 AMspid2sUnknownBuffer Counts: Committed=1032192 Target=1048458 Hashed=895536<nl/>
6/20/08 10:58 AMspid2sUnknownBuffer Distribution: Stolen=10949 Free=0 Cached=125707<nl/>
6/20/08 10:58 AMspid2sUnknownOBJECTSTORE_LOCK_MANAGER (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownOBJECTSTORE_SERVICE_BROKER (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownOBJECTSTORE_SNI_PACKET (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownOBJECTSTORE_LBSS (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownUSERSTORE_SXC (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownUSERSTORE_OBJPERM (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownUSERSTORE_TOKENPERM (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownUSERSTORE_DBMETADATA (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownUSERSTORE_SCHEMAMGR (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_SYSTEMROWSET (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_EVENTS (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_BROKERTO (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_BROKERREADONLY (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_BROKERRSB (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_BROKERUSERCERTLOOKUP (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_BROKERDSH (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_BROKERKEK (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_BROKERTBLACS (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_STACKFRAMES (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_XMLDBATTRIBUTE (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_XMLDBELEMENT (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_XMLDBTYPE (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_VIEWDEFINITIONS (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_NOTIF (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_TEMPTABLES (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_XPROC (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_PHDR (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_SQLCP (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownCACHESTORE_OBJCP (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SOSNODE (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_HOST (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLQERESERVATIONS (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_BHF (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLXP (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_FULLTEXT (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SNI (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLHTTP (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLSERVICEBROKER (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLCLR (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLCONNECTIONPOOL (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLSTORENG (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLUTILITIES (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLOPTIMIZER (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLQUERYEXEC (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLBUFFERPOOL (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMEMORYCLERK_SQLGENERAL (Total) <nl/>
6/20/08 10:58 AMspid2sUnknownMemory node Id = 0 <nl/>
6/20/08 10:58 AMspid2sUnknownMemory Manager <nl/>
6/20/08 10:58 AMspid2sUnknownLazyWriter: warning<c/> no free buffers found.
Anyone seen this SSIS error when importing data? I have a 64bit quad processor with 8gb and am importing from Oracle 9 using 32bit DTExec.exe from the command line.
OnInformation,Myserver,MyDomainSQLAdmin,J001OracleDimExtract,{CEB7F874-7488-4DB2-87B9-28FC26E1EF9F},{1221B6EB-D90A-466E-9444-BA05DBC6AFD8},6/29/2006 10:58:08 AM,6/29/2006 10:58:08 AM,1074036748,0x,The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Thanks
Hi,
I am working on my own data flow source component. Here is a fragment of this component code:
public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers) { PipelineBuffer selectedBuffer = buffers[0]; string message; while ((message = GetMessage()) != null) { selectedBuffer.AddRow(); selectedBuffer.SetString(0, message); // how to flush data here? } selectedBuffer.SetEndOfRowset(); } private string GetMessage() { // we are retrieving some message here, this is a long-term process }
When a new row is added by this component to the buffer then this row is not immediately available to the next component in data flow. It is possible to configure SSIS in that way that each row is immediately sent to the next data flow component? If no also please inform me about that.
Thanks,
Rafal
Hello,
this is my configuration :
1) 3 disks in RAID5 that hold the SQL data
2) 1 disk in RAID0 that holds the only paging file.
What will happen to the SQL data (DB) when the disk that holds the paging file crashes?
Kindest regards,
Luc.
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 12 buffers were considered and 12 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
View 12 Replies View RelatedWe are using sql server 2005 Enterprise Edition with service pack1
I got the following error messages in the SQL log
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000090000 in file '....mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
fcb::close-flush: Operating system error 21(The device is not ready.) encountered.
I got these errors for about 2 hrs and after that I see these messages in the sql log
Starting up database ' '
1 transactions rolled forward in database '' (). This is an informational message only. No user action is required.
0 transactions rolled back in database ' ' (). This is an informational message only. No user action is required.
Recovery is writing a checkpoint in database ' ' ( ). This is an informational message only. No user action is required.
CHECKDB for database '' finished without errors on (local time). This is an informational message only; no user action is required.
Can anyone please help me in troubleshooting this issue. Why this migh have happened.
any help would be appreciated.
Thanks
I have been tasked with moving our SQL server estate onto new 64bit SQL 2008 Virtual servers on a VM base. Each Virtual server will be attached to our SAN that i will have no control over. Do i ask for multiple LUNs pretending that there is a COS), Etemp), FData) and Glog) disk structure or do I just present a very big space as a single C: drive and let it go.We are consolidating lots of old physical servers onto fewer (more powerful) virtual servers (according to the VM and SAN administrators)
1. Configure your SAN properly
2. Don't overallocate shared resource
Hi,
I have a 250 GB database and not much space left on the disk drive. I want to run SQLMAINT to do optimization and integrity checks on this db. My question is : How much work space does SQLMAINT need to perform these tasks?.
Thanks in advance for your help.
F.
I try to find some feedback regarding setting the TempDB files on a RAM disk.Specifically I am looking for "production results" that could show the difference/benefit of such an usage.The tests on physical server and VM I already made have shown a boost in overall SQL Server 2012 performance on SQL Server instances housing data for SharePoint 2013 and Dynamics AX 2012 R2.Graphic below show differences between 5 different configuration on the same physical server:
- Physical HD: Server with local HD
- Physical SANEX1PRD: Server with TempDB files stored on a low-end SAN
- Physical SAN1: Server with TempDB files stored on a high-end SAN (around 100000 IOps)
- Physical SAN1 Jumbo: same setup with Jumbo Frame activated on NIC and DB engine
- Physical RAMdrive: with TempDB files stored on a 16 GB soft RAM drive within OS memory
Results were really impressive for the DB engine housing Dynamics AX data. My colleagues from the SharePoint team told me it also boosted a bit overall SharePoint performances but they did not have any baseline comparison to show.If you have some feedback, results, links, whatever I am interested.Indeed before setting this to all our SQL Server 2012 instances I y rather collect some *real world* feedback.
Hello,
I have the following problem:
I'am running SQL Server 2005 Express Advanced Services on a Windows 2003 Server in a hosted Environment. Some times SQL Server is beginning writing entries into C:ProgrammeMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG until the disk is full.. After that, I have to delete the error log file (some GB of size), restart the server and everything is running fine until the log file runs amok again.
I have installed SQL Server Management Studio.
With SQL server 2005 Standard I can configure or disable Error logging in the Management Studio. But with the Express Edition it seems that is not possible.
What I want to do is (maybe with system stored procedures)
limit number of error log files by cycling it, e.g. 5 files and delete the old ones
limit the size of one log file. e.g. 100 MB
Is there an option to configure this in the Express edition of SQL Server 2005?
Thanks in advance
Regards
Rolf
I have a question concerning where to put certain database files for the followinig RAID configurations. The server has 2 RAID configs: 2 hds in a RAID 1 and 4 hds in a RAID 10. The server will host 4 database instances: A replicated db, a Reporting Services db (which technically constitutes 2 db instances) and an application db.
In order to get the best performance, should I put the OS, SQL binary and log files on the RAID 1 config with the data and tempdb on the RAID 10? If not, please explain the best solution. Thank you!
How to increase disk-space of database in sql server?
View 4 Replies View Related