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.

Replacing multiple occurences of space with one space in a string

Given string :  THIS                IS                A                 BAD                 STRING
Out put string : THIS IS A BAD STRING

The Logic
-----------------

• In the given string replcae the space with a token (e,g. ~ in this case) and a space  e.g. '~ '
• Repace the space and token in the string result from step 1 with  an empty string.' ~'
• Replace the result of the above atring with a token and the space

DECLARE
@str AS VARCHAR(1000) = 'THIS IS A BAD STRING',@Token AS VARCHAR(1) = '~'

SELECT REPLACE(REPLACE(REPLACE(@str,' ','~ '),' ~',''),'~',' ')

Counting the occurence of a substring within a string

Today I learnt a special techinque from SQL Server magazine to find the total number of substrings within a string. The logic is so simple that I am surprised why I never used it.
E.g. abcdballmnopballqrstball
We need to find the total number of occurences of the substring "ball" in the given string.
 Logic
--------------
Find the substring ball and replace it with ''   e.g.abcdmnopqrst
Find the length of the new string                   12
Subtract the new length from the lenth of the original string  24-12 = 12
Divide the new number with the length of the substring   12/4 = 3

Here is the script - taken form the November issue of SQL server magazine

DECLARE
@str       AS VARCHAR(1000) = ' abcdballmnopballqrstball ';
@substr  AS  VARCHAR(1000) = 'ball';


SELECT  LEN(@str) - LEN(REPLACE(@str,(@substr,''>/LEN(@substr)

This is so awesome!

COALESCE - Selecting each record with all not nul values

I had three rows of data that had the same acount number but differnt variation in the three columns. I had to select each account with not null column . This was achieved by the following...

My data
create table #tmpcol(c219 int, c220 int,c221 int)
INSERT INTO #tmpcol
SELECT null,220,null
INSERT INTO #tmpcol
SELECT 219,null,null
INSERT INTO #tmpcol
SELECT null,null,221


select top 1 * from #tmpcolorderby coalesce(100*c219,10*c220,c221) desc

Shrink the transaction log file

The steps needed to shrink the transaction log file
Scenario: The database is in FULL RECOVERY mode and the transaction log file was growing to more than 2GB. We were taking the transaction logbackups in every 1 hour inteval. However the transaction log file still grew because at one point someone just set the recovery mode to FULL and there was no transaction log backups set up. The following steps were taken to reduce the siz of the log file.

--check the log file name
select * from sys.database_files

--Truncate the log by changing the database recovery model to SIMPLE. ( checkpoint occurs-frees up VLFs)  

ALTER DATABASE ARCHIVEhcfsdatabase
 SET RECOVERY SIMPLE;
 GO

-- Shrink the truncated log file the smalles size possible
DBCC SHRINKFILE (ARCHIVEhcfsdatabase_Log,0, TRUNCATEONLY);
GO

--reset the log file size to 64MB

ALTER DATABASE archivehcfsdatabase
Modify FIle(Name = 'ARCHIVEhcfsdatabase_Log',
,Size = 1024
)
 -- Reset the database recovery model.
ALTER  DATABASE ARCHIVEhcfsdatabase
SET RECOVERY FULL;
GO

--Do a full Back up - Until a full back up is done the database is not set to full recovery mode--