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!

Restore database

use master
GO

Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE


GO RESTORE DATABASE INACTIVECLIENTS2009FROM DISK = 'E:\DatabaseArchivesNotRestored\INACTIVECLIENTS2009_20110112_1053am.bak'WITH REPLACEGO

Alter Database INACTIVECLIENTS2009SET MULTI_USER With ROLLBACK IMMEDIATEGO USE INACTIVECLIENTS2009;GO
EXEC sp_updatestats

I was getting error "database cannot be restored because it is in use" this was resolved when I set the database to single_user mode

Alter Database INACTIVECLIENTS2009SET SINGLE_USER With ROLLBACK IMMEDIATE




Then after successfully restoring it I changed it to the multi user mode.
GO

Guidelines for index-fragmetnation

Some starting points to keep in mind for index fragmentation

•  If an index has less than 1000 pages and is in memory, don't bother removing fragmentation
• if the index has:
  • less than 5% logical fragmentation, don't do anything
  • between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX ... REORGANIZE)
  • more than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX ... REBUILD)

 The guidelines are taken from 
http://sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx

Error: SSIS package : A rowset based on the SQL command was not returned by the OLE DB provider

I am trying to use a stored procedure in the OLE DB source, I am using the SQL Command for the Data Access mode.  It returns values when using the preview but when I test the SSIS package I receive the error, "A rowset based on the SQL command was not returned by the OLE DB provider


Solution

I just had to add SET NOCOUNT ON in the begining of the stored procedure that I was using.