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

SSIS Error: Error: ForEach Variable Mapping number n to variable "User:: n1 " cannot be applied.

The exact error I got was
Error: ForEach Variable Mapping number 2 to variable "User::i_HospitalCode" cannot be applied.
I spent almost 2 hours trying to find out what the problem was. It was the problem in the data type. I was declaring the data type of  variable User::i_HospitalCode as string where as the resultset ( returned by the stored procedure) was integer.
I changed the data type of the variable  User::i_HospitalCode  in the variable declaration window and it worked!