How to flush cache in SQL Server2k database?

  • venkat_dba

    Old Hand

    Points: 316

    Hi,

    There are two Windows2k systems running SQL Server2K.

    The database on the first system is in read/write mode with very frequent writes.

    and is replicated on the second system using a block level replication engine.

    The second system runs SQL Server2K on the replicated Database in read only mode.

    (It is not using SQL Server Replication)

    Clients are doing transactions and updates to the database on the first SQL

    server. Different set of clients are doing transcations (reads only and noupdates

    or new transactions) on the 2nd server. While this is happening, in parallel, the

    block transfer engine is shipping blocks of new (updated) raw data from the 1st

    server to the 2nd server.

    Since the SQL server on the 2nd system has no way of knowing that the raw data

    on the disk is changing, he always give stale data which is in its buffers. Is there

    a way we can flush all the pages and buffers of the database and reread everything

    from the cache, without the database going offline, that is while the connections

    are active?

    Thanks in advance.

  • Andy Warren

    SSC Guru

    Points: 119694

    Not that I know of. Why would you not use replication? Replication solves all of this at very little cost.

    Andy

  • venkat_dba

    Old Hand

    Points: 316

    We already tried the SQL Replicator but it didn't work under heavy transactions on the 1st SQL Server2k eventhough the 2nd SQL Server2k database is read only.

    Even Microsoft is not recommending SQL Replicator for high OLTP system.

    Thanks

  • Andy Warren

    SSC Guru

    Points: 119694

    How many transactions are we talking? Replication usually only generates a load of 5-10% on the machine, less if multi processor. What hardware configuration, SQL version?

    Andy

  • venkat_dba

    Old Hand

    Points: 316

    The hardware is Dell PowerEdge 6400 using Dell storage and running over 40 databases.

    8GB of RAM....

    Online transactions half a million.

    thanks

  • Andy Warren

    SSC Guru

    Points: 119694

    Thats what, 4 processors? Half a million transactions...per minute? Day?

  • venkat_dba

    Old Hand

    Points: 316

    yes it is 4processors and half a million transactions perday.

    thanks

  • Andy Warren

    SSC Guru

    Points: 119694

    Guess it depends on what you call a transaction, but that doesnt seem like too much for replication. Figuring it all happens in 8 hours, that breaks down to an average of about 20 transactions per second.

    Did you try replication at all, or just decide not to use it?

    Andy

  • Andy Warren

    SSC Guru

    Points: 119694

    Another option would be to split the load across multiple servers, using distributed partitions maybe. Then all data would be live.

    Andy

  • venkat_dba

    Old Hand

    Points: 316

    please can brief how to split the load across multiple servers, using distributed partitions.

    thanks

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720940

    Look them up in BOL, but basically you split your table by the PK into 2 parts. If your table is orders using an OrderID (int) as a PK, you place all orders that are even on one server, and all orders that are odd on another server.

    Then you define a view on each server (same view name) that unions all data from both servers. No matter which server the users connect to, they will be able to insert/update/delete using the view.

    Check "Federated servers" in BOL.

    Steve Jones

    steve@dkranch.net

  • gregtm

    SSC Eights!

    Points: 836

    Yes see the BOL DBCC commands below called

    DBCC DROPCLEANBUFFERS and DBCC FREEPROCCACHE.

    hope this helps...

    DBCC FREEPROCCACHE

    Removes all elements from the procedure cache.

    Syntax

    DBCC FREEPROCCACHE

    Remarks

    Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.

    Result Sets

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

    Permissions

    DBCC FREEPROCACHE permissions default to members of the sysadmin and serveradmin fixed server role only, and are not transferable.

    ©1988-2000 Microsoft Corporation. All Rights Reserved.

    DBCC DROPCLEANBUFFERS

    Removes all clean buffers from the buffer pool.

    Syntax

    DBCC DROPCLEANBUFFERS

    Remarks

    Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.

    Result Sets

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

    Permissions

    DBCC DROPCLEANBUFFERS permissions default to members of the sysadmin fixed server role only, and are not transferable.

    ©1988-2000 Microsoft Corporation. All Rights Reserved.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply