Skip to main content

Posts

Showing posts with the label dbcc

SQL Server Tips for Memory usage,replication,CU updates etc

1. To check which user is taking maximum memory usage in SQL 2014 You can try below command select * from sys.sysprocesses sp_who2active 2. How to rename a column in subscription table in replication You have to remove from replication: Rename. and then re-add to replication 3. Clear cache in SQL for TempDb. RUN THIS COMMAND ON TEMPDB  DATABASE ONLY DBCC FREEPROCACHE  4. How to install the Service pack 3 on server,This is my recommendations . Server: SQL server 2012 standard edition having sp1 and you want to install the SP3 and CU 7 at a time ? Then you can install SP3 and check if that covers CU 7 as well, if not then you can install CU 7 after the SP3 Successfully installed. But make sure to take the DB Backups prior to proceed 5. Resolve Authentication Error for Mailbox in SQL Error is Mailbox unavailable. The server response was: Authentication is required for relay in SMTP server If You are providing the server address like Server ip without login

How to Shrink TempDB in Sql Server?

To Shrink TempDB in Sql Server below ideas can help you to handle the issue. If it is data file then Instead of shrinking you should check the open transactions causing tempdb to get full..if it is not critical you can kill the process or else should add secondary data files. Shrinking log whereas is safe witch you can do by dbcc shrinkfile command. 1)If mdf file is full we can shrink that but sometimes we can’t shrink mdf file the reasons are: (i)check if any locks are there through (sys.dm_tran_locks)or sp_locks (ii)check for sys.dm_db_session_space_usage which will allows to check for page allocation and deallocation for each session on that instance (iii)check for temporary objects in tempdb using (select * from sys.all_objects where is_ms_shipped=0)-ser created temp objects. These are called as cached objects which are residing in procedure cache which helps stored procedure to make use of the query plan. But now it is not getting shrinked we have to perform DBCC FREEPROCCACHE wh