Tuesday, June 25, 2013

[SQL 2012] FlushCache messages ... the ongoing quest for information

[SQL 2012] FlushCache messages ... the ongoing quest for information

FlushCache messages ... the ongoing quest for information

Posted: 24 Jun 2013 06:26 PM PDT

With some of our SQL2012 instances we get frequent checkpoint messages during data load[quote]2013-06-25 06:14:04.84 spid16s FlushCache: cleaned up 184496 bufs with 11466 writes in 507527 ms (avoided 125257 new dirty bufs) for db 7:02013-06-25 06:14:04.84 spid16s [b]average throughput: 2.84 MB/sec[/b], I/O saturation: 21329, context switches 485042013-06-25 06:14:04.84 spid16s last target outstanding: 2400, [b]avgWriteLatency 990[/b][/quote]as per CSS blog post [url]http://blogs.msdn.com/b/psssql/archive/2012/06/01/how-it-works-when-is-the-flushcache-message-added-to-sql-server-error-log.aspx[/url]these are supposed to be informational and might point you to investigate further.[quote]...the message is indicating that the checkpoint process, for the indicated database, exceeded the configured recovery interval. If this is the case you should review your I/O capabilities as well as the checkpoint and recovery interval targets.[/quote]At SSC forum [url=http://www.sqlservercentral.com/Forums/Topic1363610-2799-1.aspx#bm1435783]system FlushCache happening often[/url] [url=http://www.sqlservercentral.com/Forums/UserInfo498037.aspx]SpringTownDBA[/url] added great information with regards to how to investigate the issue and some tips on how to avoid dirty pages.As I have [u]no windows admin authority[/u] for the concerned instances, I cannot just launch perfmon to investigate the thing.[b]Does someone have a ref that explains the details of it all ?[/b][u]What do these measures mean?[/u]- I/O saturation mean - last target outstandingWhy is there so much variation in the figures of the different FulshCache messages?The only thing going on at that load time is a single database being loaded. No other activity is ongoing at that time span.I think the server raising the posted message is not configured in an optimal way.Mainly based on its value for [i]average throughput [/i]is [b]low[/b] (even 0.42 MB/sec in some messages)and the value for [i]avgWriteLatency[/i] is [b]high[/b] (varying from 65 to 990) for a dedicated RAID1 disk ( DASD 15K ) I think adding RAM to this instance might cause the frequency of these message to lower down, but if the issue is the I/O subsystem, the yield for such operation would be low.Could optimising the NTFS block size to 64K help out ? ( is currently 4K )

SQL 2012 cluster - changing IPs and instance name

Posted: 24 Jun 2013 09:56 AM PDT

We have SQL 2005 cluster (active-passive) in our production environment. Customer wants us to build new cluster with SQL 2012 active-passive configuration and run it in parallel with the existing SQL 2005 cluster for a while (the same network, the same subnet). Later on when all databases get migrated and apps get tested, the old cluster will be removed and the new cluster has to pick up the same IPs, names and instance names as the old had. My concern is will we be able to make these changes (IPs, instance names) in SQL 2012 cluster environment and what problems (if any) we might run into? Great thanks in advance for your opinion

Granting access to remote location to SQL Server 2012

Posted: 24 Jun 2013 11:45 PM PDT

Briefly, what I'm looking to do is set up a job which takes backups locally and then moves them to a server dedicated to holding SQL Server backups.Now, in previous versions of SQLServer, nice and easy, for Server name Wibble using system account for SQL Server services - create directory on target, share, add Wibble$ - set full control permissions to wibble$, works fine.With the different approach to default SQL Server services in 2012 - I can't get this working. This will have an account MSSQL$Wibble (for named instance Wibble) - Assigning Wibble$ full control permissions to its directory on the remote backup server doesn't work - the copyover job fails with an access error. The following suggests it should work as I understand it - but I can't get it to. "Virtual accounts cannot be authenticated to a remote location. All virtual accounts use the permission of machine account. Provision the machine account in the format <domain_name>\<computer_name>$." at http://technet.microsoft.com/en-us/library/ms143504.aspx#Windows Can anyone who's done this point me in the right direction?

SSMS 2012 error when connect to sql server with windows authentication after VS instalation

Posted: 09 Apr 2013 07:33 PM PDT

Hi.After install VS 2012 in my pc i can't connect with SSMS 2012 to my SQL Server 2012 server (i can connect to other server -only SQL Server 2000 server-). I receive the next error message:"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.) (Microsoft SQL Server, Error: 258)".I already execute 'netsh winsock reset' command but the result is the same. I don't know what more doing.Somebody could help me?Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog