Update stats

I had a project where I had to find when the stats were updated last and then had to update the stats of the table which was used heavily. I found the sript on the following blog

http://troubleshootingsql.com/2010/01/22/how-to-check-database-statistics-last-updated-date-time-in-sql-server/


SELECT schemas.name as table_schema,
tbls.name as table_name,
i.name as index_name,
i.id as table_id,
i.indid as index_id,
i.rowmodctr as modifiedRows,
( select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2 ) as rowcnt,
 convert(DECIMAL(18,8),
convert(DECIMAL(18,8),i.rowmodctr) / convert(DECIMAL(18,8),
(select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2))) as ModifiedPercent,
stats_date( i.id, i.indid ) as lastStatsUpdate
FROM sysindexes i
INNER JOIN sysobjects tbls
ON i.id = tbls.id
INNER JOIN sysusers schemas
ON tbls.uid = schemas.uid
INNER JOIN information_schema.tables tl
ON tbls.name = tl.table_name
AND schemas.name = tl.table_schema
AND tl.table_type='BASE TABLE'
WHERE 0 < i.indid
AND  i.indid < 255 and table_schema <> 'sys'
 AND  i.rowmodctr <> 0 and i.status not in (8388704,8388672)
AND  (select max(rowcnt) from sysindexes i2 where i.id = i2.id and i2.indid < 2) > 0
ORDER BY ModifiedPercent
DESC   

Error: [SQLSTATE 42000] (Error 7391) OLE DB provider "SQLNCLI10" for linked server "" returned message "No transaction is active.". [SQLSTATE 01000] (Error 7412). The step failed.

I have a stored procedure in server2 that I was trying to call from server1 using linkedserver so that it gets executed in server2.
Server 1

• Windows Server 2008(x86)
• SQL Server 2008 (x86) Standard Edition SP1

Server 2

• Windows Server 2008 (x86)
• SQL Server 2008 (x86) Standard Edition SP1

Following  is the snippet of my code on Server1

SET  XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION 
declare @RetVal int,

exec @RetVal = linksv1.db1.dbo.usp_update_Referrals 0

COMMIT TRANSACTION

So far I have taken the following steps

1. NETWORK CONNECTIVITY

• Ping server1 from server2  -- success
• Ping server2 from server1  -- success

2. DTC CONFIGURATION

• Open Start->Administrative Tools->Component Services
•  Expand the Component Services Node untill you find Local DTC right click and select properties option
•  In the Local DTC properties click in the security section and set the configuration as shown in the image

   Note that every time you make a change in the DTC Security configuration, the DTC service is restarted automatically

The final step is make shure the DTC service is started and configured to start when windows start.

3. DTC SERVICE IS STARTED

• Open Start - > Open Start->Administrative Tools->Services
• Find the Distributed Transactions Coordinator Service
• Make sure the status is “Started” and the Startup Type is “Automatic”.

Follow the same step in Server2

After the DTC is configured , checked the lnked servers
Linked Server Configuration on Server 1

 OKC is the linked server pointing to my Server2
Highlight the linked Server
Right Click and go to properties
Select the Server Option in the properties window and make sure the following settings are applied
Data Access: True
RPC: True
Enable Promotion of Distributed Transactions: True

All the settings were double checked ...will update in few weeks if we still get the "No transaction is active" error...

Script: View when the tables in a database were accessed last

I had to do a database clean up project, where I had to drop the usused table. I ran the following script to see when were the indexes used last and made the decision based on it. One caution though was the table that did not have indexes did not show the activity.

use [yourdb]
GO

SELECT sobj.[name],iu.last_user_seek,101,iu.last_user_update  FROM sys.dm_db_index_usage_stats iu
INNER JOIN sys.objects sobj
ON iu.object_id = so.object_id
WHERE so.type_desc = 'USER_Table'
ORDER BY  so.[name],last_user_seek
GO

error:[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

I ran into the following error when calling a stored procedure from SSIS. The stored proedure dumps the SELECT query result into a table variable and I was doing a final SELECT from the table variable.

[OLE DB Source [1]] Error: A rowset based on the SQL command was not returned by the OLE DB provider.

The error was eliminated after putting SET NOCOUNT ON in the begining of the stored procedure

Script: To log the space of the database

As a DBA one of my primary job is to keep track of the growth of the database so that I can forecast the space required. I have a static table where I run the following script and collect the data in about in 4 hours and analyze the data collected in the table.
I have scehduled this script as a sql job that runs in every 4 hours. Every Friday I look at the historic data and see by how large our databases grew.


DECLARE
DECLARE
DECLARE @rowcnt INT @iterator INT @dbname VARCHAR(200)DECLARE @exec_sql VARCHAR(500)SET @rowcnt = 0SET

 CREATE TABLE #db_file_info ([Database_Name] SYSNAME NOT NULL,[File_ID] SMALLINT NOT NULL,[File_Type] VARCHAR(10) NOT NULL,[File_Name] SYSNAME NOT NULL,[File_Path] VARCHAR(500) NOT NULL,[File_Size_in_MB] INT NOT NULL,[Space_Used_in_MB] INT NOT NULL,[Space_Left_in_MB] INT NOT NULL,[Time_collected] smalldatetime )
CREATE TABLE #db (dbid INT,name VARCHAR(200))INSERT INTO #dbSELECT dbid,nameFROM MASTER.dbo.sysdatabasesSET @rowcnt = @@ROWCOUNTWHILE@iterator <= @rowcntBEGINSELECT @dbname = '['+ name + ']'



Select db_name(),fileid,case when groupid = 0 then ''log file'' else ''data file'' end,
name,filename,
[file_size] =
convert(int,round((sysfiles.size*1.000)/128.000,0)),
[space_used] =
convert(int,round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,0)),
[space_left] =
convert(int,round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,0)),
getdate()
from
dbo.sysfiles;
'FROM #dbWHERE dbid = @iteratorSET @exec_sql = ' USE ' + @dbname + '; Insert into #DB_FILE_INFO
EXEC( @exec_sql)
SET @iterator = @iterator + 1END
INSERT INTO SQLAdmin_Monitor_db_file_infoSELECT *FROM #db_file_infoDROP TABLE #dbDROP



/*SQLAdmin_Monitor_db_file_info is the static table where I collect the results returned by the above script. */TABLE #db_file_info @iterator = 1

Script: Find the tables and thier sizes in a database

Use the following script to  find out the size of different table in the database. If your database grows big suddenlt the following script comes very handy to find out what is causing the sudden growth.

use [yourdatabase]
GO

SELECT object_name(i.object_id) as objectName,
i.[name] as indexName,
sum(a.total_pages) as totalPages,
sum(a.used_pages) as usedPages,
sum(a.data_pages) as dataPages,
(sum(a.total_pages) * 8 ) / 1024 as totalSpaceMB,
(sum(a.used_pages) * 8 ) / 1024 as usedSpaceMB,
(sum(a.data_pages) * 8 ) / 1024 as dataSpaceMB
FROM sys.indexes i
INNER JOIN sys.partitions p
ON i.object_id = p.object_id
AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a
ON p.partition_id = a.container_id
GROUP BY i.object_id, i.index_id, i.[name]
ORDER BY sum(a.total_pages) DESC, object_name(i.object_id)
GO

SQL Agent: Script to disable and enable SQL agent jobs

The following script disables the job scedules in the agent
BEGIN TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 
BEGIN TRY
exec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 0END TRY


BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 0END TRY

BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH

If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Disable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
 

--select @rcEnd
The following script enables the job scedules in the agent
BEGIN TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 
BEGIN TRY

 
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()END CATCH
BEGIN TRY
exec msdb..sp_update_job @job_name = 'Update Status Closure referrals', @enabled = 1END TRY
BEGIN CATCHSET @errMsg = 'There was an error! ' + ERROR_MESSAGE()
 

If @errMsg <> '' Begin
@profile_name = 'SQLMail@abc.com',
@recipients = 'dataExchange@abc.com',
@subject = 'Job Failure - to Enable jobs on srvr1,
@body = @errMsg,
@body_format ='HTML'
 


--select @rcEnd  exec @rc = msdb.dbo.sp_send_dbmailEND CATCHexec msdb..sp_update_job @job_name = 'BJC_InvalidAddressFix', @enabled = 1END TRYEND CATCHexec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 1END TRYexec @rc = msdb.dbo.sp_send_dbmail
   END CATCHexec msdb..sp_update_job @job_name = 'BJC_CloseAccounts', @enabled = 0END TRY

SQL Agent : Schedule DTS as a SQL Job

Call the DTS from an SQL agent job as

 DTSRun /S Servername /E /N "DTSName"

Select  type as Operating System(cmdExec)  from the drop down


DTSName = Name of the DTS that you have created

Servername= Name of your server where the DTS is created