SQL Sever Data FAQ

One, the execution of stored procedure memory continues to not degrade, the solution.

ALTER procedure [dbo]. [Clearmemory]
As
Begin
--Clear all caches
DBCC Freeproccache
DBCC Freesessioncache
DBCC Freesystemcache (' all ')
DBCC dropcleanbuffers
--Open Advanced Configuration
exec sp_configure ' show advanced options ', 1
--Set the maximum memory value to clear the existing cache space
exec sp_configure ' max server memory ', 512
EXEC (' RECONFIGURE ')
--Set wait time
WAITFOR DELAY ' 00:00:01 '
--Reset the maximum memory value
EXEC sp_configure ' max server memory ', 6000
EXEC (' RECONFIGURE ')
--Turn off Advanced configuration
exec sp_configure ' show advanced options ', 0
End

Ii.SQL Server blocked the STATEMENT ' OpenRowset for component ' Ad Hoc distributed Queries ' ...

Error: msg 15281, Level 16, State 1, line 1th SQL Server blocked access to the statement ' Openrowset/opendatasource ' of component ' Ad Hoc distributed Queries ' because this component was shut down as part of this server's security configuration. System administrators can enable ' Ad Hoc distributed Queries ' by using sp_configure. For more information about enabling ' Ad Hoc distributed Queries ', see "surface Area Configurator" in SQL Server Books Online.

WORKAROUND:--Enable AD Hoc distributed Queries: New query, input

exec sp_configure ' show advanced options ', 1

Reconfigure

exec sp_configure ' Ad Hoc distributed Queries ', 1

Reconfigure

--Close ad Hoc distributed Queries after use is complete:

exec sp_configure ' Ad Hoc distributedqueries ', 0

Reconfigure

exec sp_configure ' show advanced options ', 0
Reconfigure

　

　　

SQL Sever Data FAQ