Devops role

Devops role,

1.work collaboratively with software engineering to deploy and operate our systems.
2.Help automate and streamline our operations and processes.
3.Build and maintain tools for deployment, monitoring and operations. 4.And troubleshoot and resolve issues in our dev, test and production environments.

Sql Server - Find the Size of Database File and Find the Size of Log File

use below script to find the size of the DB

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = '<<your database name>>'
GO

fix: sql server 2008 r2 cpu usage 100%

CPU usage can be caused by a lot of reasons like:

• Bad execution plans caused by bad statistics
• Locks and blocks
• DBCC CHECKDB
• Index rebuild
• etc

I have faced cpu usage touch 90-100%  around 10 hour.  follow action taken but not able to fix it.

1. select * from sys.sysprocesses where blocked>=1 (kill unwanted spid) 

2. restarted sql services

3. Sp_who2 'active'

Referred following articles : 

http://www.sqlservercentral.com/Forums/Topic1411982-391-1.aspx

http://raaviblog.com/how-to-investigate-100-cpu-usage-problem-in-sql-server-2008/


 "after 8 hour search found link and offline dba support to arrive the lack of index. in maintenance activity sp_updatestats missing."

====================================================================

Query execution causing CPU spike:

Query execution  takes long times and spikes CPU commonly because of in-correct carnality estimates caused by outdated statistics, Lack of Index, Server configuration, Distributed queries, etc.

When the server is experiencing this problem run the query in below link to list all the queries which are executing in the server order by CPU time desc along with plan.

{

Get SQL Text and Query Plan for statements which are executing now

}

 It could be one query which is driving the majority CPU time or Multiple queries each driving the CPU. Look at the CPU time of the above query output.

If it is single query/Store procedure which is driving the majority of CPU.

1.        Update the stats of tables and indexes used by the query (If the stats are up to date Estimated rows and estimated execution will  be approximately

same in execution plan .If there is huge difference stats are out dated and requires update

=======.=====================================================

Thanks god found link and read first step and stick-out run the 

Solution: "sp_updatestats"

1) did you update ALL statistics with a FULL SCAN?

2) I would simply use profiler to capture rpc batch completed and tsql batch completed events and find the big hitters. Tune these. I would also consider running a trace to disk and using Qure from DBSophic to do aggregate trace analysis. The thing(s) you need to tune most are not necessarily the ones that run the longest or use the most resources individually...

3) it is possible that someone altered the schema in any way - i.e. dropped some indexes?

4) I also see this routinely at clients these days: did you perchance upgrade to a much better IO subsystem? Getting data into the CPUs faster can cause them to actually start earning their keep.

 

performance: msdb.dbo.sp_readrequest;1 – long running process

When monitoring the new installed version of SQL 2008 R2 SP1, I have encountered that Database mail leaves a hung process few minutes. This doesn’t do any blockages or other trouble to your SQL installation, either that it is anoying to see over and over again this long running process. In order to get rid off it, just check the system parameter value for “DatabaseMailExeMinimumLifeTime” running the following query:

Use msdb
GO
exec sysmail_help_configure_sp 'DatabaseMailExeMinimumLifeTime'
GO

The default setting will be 600. You need to change it to a lower period.

Use msdb
GO
exec sysmail_configure_sp 'DatabaseMailExeMinimumLifeTime', 30
GO

=================================================================

Anyone get a solution to this one?  I'm getting the same hung process today after an upgrade to 2005 SP3

Had a look at the mail logs - the process runs for 10minutes.  Which is coincidentally the same as the setting for 'Database Mail Executable Minimum Lifetime (seconds)'

When I adjusted 'Database Mail Executable Minimum Lifetime (seconds)' down from the 10 minutes default to 10 seconds everything went back to normal.  Much happiness for me. 

I am troubleshooting a similar issue, whereby the Performance Dashboard Reports (the Query Duration Component) show that a stored procedure used by Database Mail is the most costly query. It appears to be a service broker wait request, i.e. the database mail queue is waiting for further requests to process.

Based on your comments this seem like a plausable solutiuon. I have adjusted the very same Database Mail property to see if it clears this issue.

Error: Cannot drop the database because it is being used for replication

Hi,

Try using sp_removedbreplication ‘dbname’

Find text in Stored Procedures View Trigger and Function and Troubleshoot Replication Ambiguous Column

Today I'm faced Challenge in replication when added all tables, Sps,VIEWs and user defined functions.

Error : Its return "Ambiguous Column"

Tried and found below query to identified the root cause of issue.


DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'foobar', @notcontain = 'comments'

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0
or CHARINDEX(@notcontain,syscomments.text)<>0)) 

Transactions waiting to be replicated to Subscription Database (Transactional Replication)

While troubleshooting Replication issues it’s always a question till what point transaction has been replicated to subscription and from which point transaction are in distribution database waiting to be replicated to subscription database.

Replication Architecture

Let’s say distributor agent failed to replicate transaction from distributor to subscriber due to some reason and now after restarting the distributor agent it’s still not replicating the transactions.
There could be n number of reason due to which distributor agent may fail.

In this post we will focus on how to get last transaction replicated to subscriber database and what all transaction are waiting in the distributor database to be replicated.

Every time distributor agent replicates the transaction from distributor to subscriber, it does an entry in a system table dbo.MSreplication_subscriptions which exists in subscriber database.
The MSreplication_subscriptions table contains one row of replication information for each Distribution Agent.

Code: SELECT publisher,publisher_db,publication,transaction_timestamp 
FROM dbo.MSreplication_subscriptions

Transaction_timestamp value is same as xact_seqno and till this sequence number all the transaction are replicated to subscription database.

In case u does not have access to the subscription database and to find out the last replicated xact_seqno to subscriber database from distributor database

sp_MSget_last_transaction Publicationid,Publication_database
sp_MSget_last_transaction 2,TestDB

Connect to the Distributor

Code: select * from MSrepl_commands Where xact_seqno>Transaction_timestamp value

This will give you those records which are in distributor and not been replicated to subscriber database after the last replicated xact_seqno 
in subscriber database (dbo.MSreplication_subscriptions)

sp_browsereplcmds : @xact_seqno_start, @xact_seqno_end

The stored procedure will use to view pending commands in the distributor database.|
It will display the commands in the readable format.

Replication monitor will also give the xact_seqno at which distributor agent failed

You can also check the error on browsing MSrepl_errors table in distribution database.

select
*
From MSrepl_errors

In this case error is :”The row was not found at the Subscriber when applying the replicated command:

To resolve the same I have xact_seqno and command id , I simple deleted the record from the MSrepl_commands at the distributor.Once done distributor agent will start replicating pending transaction to subscriber.

DELETE from MSrepl_commands where command_id=1295 and xact_seqno=0x000260AD000000100021