Update the statistics to all tables in a database WITH FULLSCAN


DECLARE @SQL NVARCHAR (MAX)
DECLARE C1 CURSOR FOR
SELECT DISTINCT ‘UPDATE STATISTICS [dbo].[‘+o.name +’] WITH FULLSCAN ‘

FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id]
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = ‘U’ and ISNULL (STATS_DATE(i.[object_id], i.index_id), ’01/011/2014′) < '01/31/2014'
OPEN C1
FETCH NEXT FROM C1
INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR (@sql, 10, 1) 
EXEC (@SQL)
FETCH NEXT FROM C1
INTO @SQL
END
CLOSE C1
DEALLOCATE C1

Posted in Uncategorized | Leave a comment

Disable All SQL Server Agent Jobs


USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE Enabled = 1;
GO

Posted in Uncategorized | Leave a comment

SQL SERVER 2014 CTP 1 Installation


Today Microsoft announced the release of SQL SERVER 2014 CTP 1

http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/25/sql-server-2014-public-ctp-now-available.aspx

Below is the installation of SQL Server 2014 CTP 1 on windows 2012 standard edition 

Image

 

Image

ImageImageImageImageImageImageImageImageImageImageImageImage

 

 

 

Note : The SQL 2014 CTP version number : 11.0.9120Image

Posted in SQL SERVER 2014 | Leave a comment

Performance Reads ‘Not Enabled’


When looking at the performance monitor under the column ‘Performance’, the message ‘Not Enabled’ is indicated instead of the typical message of ‘Excellent’, ‘Poor’, or ‘Critical’.  This simply means that the threshold for this agent is not enabled and needs to have the record inserted via the threshold table.

To correct this error, run the following statements located in the below script

— Get the Id of the Publication

DECLARE @PublicationID AS INT

SET @PublicationID = (SELECT [publication_id]

FROM [distribution].[dbo].[MSpublications]

WHERE publication = ‘Publication Name’

)

 

— Add the proper missing thresholds

INSERT INTO [distribution].[dbo].[MSpublicationthresholds]

([publication_id]

,[metric_id]

,[value]

,[shouldalert]

,[isenabled])

VALUES

(@PublicationID   — [publication_id]

,1

,80

,0

,1)

GO

 

INSERT INTO [distribution].[dbo].[MSpublicationthresholds]

([publication_id]

,[metric_id]

,[value]

,[shouldalert]

,[isenabled])

VALUES

(@PublicationID   — [publication_id]

,2

,30

,0
,1)
GO

Posted in Replication | Leave a comment

The process could not connect to Subscriber ‘ServerName’


When the message displays that it cannot connect to the subscriber, it is commonly because of the following reasons:

The subscribing server is busy

The subscribing server is down

The authentication is failing

Before correcting the problem, verify if the error message is occurring for one agent on a specific server or if it is the majority of the agents connecting to the subscriber with this problem.

If it is one subscriber, verify that the agent is not pushing a large amount of transactions.  Also, verify the both the CPU and disk space as well as the database size to rule out any of these as an issue.

Next, try to ping the subscribing server to verify that a connection can be established.

If it is only one agent that is having the issue, stop and start the agent and wait for it to connect.  Sometimes this needs to be done multiple times to establish a connection.

This issue may also occur if the agent has lost permissions to the destination subscriber.  This seldom is the case unless it is a new subscriber.

Posted in Replication | Leave a comment

Transaction was deadlocked on lock resources : Transaction (Process ID #) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction


It is uncertain how the deadlocks occur, but it may be application related or due to too many processes running concurrently.  To avoid this, all the lookup tables are scheduled to execute every hour.  The specific deadlock error message that can occur is as follows:

 

“Transaction (Process ID #) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. “

 

When an agent becomes chosen as a deadlock victim, it will go into retry mode.  Once it is in this state, it normally does not start again on its own.   To correct this error, follow the following steps:

 

  1. Stop all agents that have been chosen as a deadlock victim.
  2. Start each agent one by one.  Be sure the agent has started before moving on to the next stopped agent.
Posted in Replication | 1 Comment

Inactive Subscription: The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated


When looking at the replication monitor, a message may appear under the ‘status’ column of the subscription watch tab that displays the message ‘not running/ inactive subscription’

“The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated“

When this message appears, there are two approaches to correcting the error:

Right click on the subscription and re-initialize with the option checked to ‘generate a new snapshot’

Update the status flag on the publication and distribution database to change the status from inactive to active.  This can be accomplished by running the stored procedure Usp_ForceInactiveSubscriberActive.

Posted in Replication | Leave a comment