We are getting many alerts for one of the drive (it has user and System db files as well ---the MDF and LDF files shoudn't beon same drive how ever it was built by some one(will take actions on this) and so my team met was planning to shrink the tempdb files, but it was not allowing him to shrink,he checked all the ways i,e checked
any Open transactions,any SPID is using tempdb,any Jobs are running etc.... nothing worked, even I also tried it
it was not allowing , so I had 2 options in My mind 1.To restart the SQL server services 2.clear the procedure cache.
1st option I can use but due to weekend there were no business people on duty and since ours is financila account it is an Lenghty processes , so at present to resolve I went with the Danger option of clearing the Procedure cache.
Note ->IThe DBCC FREEPROCCACHE command clears the procedure cache for all the procedures on the server. hence we need to use very care fully.
Why carefully?
"DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval".
Since I have checked that all user db's are not in big size & also there were no user's activity during that point of time hence I have used DBCC FREEPROCCACHE then did the shrink operation ->So then it allowed the shrink operation.
any Open transactions,any SPID is using tempdb,any Jobs are running etc.... nothing worked, even I also tried it
it was not allowing , so I had 2 options in My mind 1.To restart the SQL server services 2.clear the procedure cache.
1st option I can use but due to weekend there were no business people on duty and since ours is financila account it is an Lenghty processes , so at present to resolve I went with the Danger option of clearing the Procedure cache.
Note ->IThe DBCC FREEPROCCACHE command clears the procedure cache for all the procedures on the server. hence we need to use very care fully.
Why carefully?
"DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled instead of reused from the cache. This can cause a sudden, temporary decrease in query performance. In SQL Server 2005 Service Pack 2, for each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval".
Since I have checked that all user db's are not in big size & also there were no user's activity during that point of time hence I have used DBCC FREEPROCCACHE then did the shrink operation ->So then it allowed the shrink operation.
No comments:
Post a Comment