Thursday, June 28, 2012

How to take SharePoint Site into Offline Mode

How do you quiesce the farm?
                1. From Central Administration, Operations, select "Quiesce Farm."
                2. Enter the number of minutes in which you want the farm to be fully quiesced and click "Start Quiescing."

How do you "un-quiesce" (reset) a farm:
                1. From Central Administration, Operations, select "Quiesce Farm."
                2. Click "Close Quiescing."

Turn off SharePoint Services
This will stop the connections to some of the databases. If you try to detach any of the databases you will find that some will have connections active.
In case of the content databases you will probably find that the connections to the have already been taken care of
Either way you will need to stop some of the SharePoint services in order to get the search databases and config database totally disconnected.
You can either stop the services through the Central Administration, or you can do it at the Services Console.
To stop the connections to the existing databases stop the following services:
                1. Office SharePoint Search Service
                2. Windows SharePoint Services Administration
                3. Windows SharePoint Services Search
                4. Windows SharePoint Services Timer
                5. Windows SharePoint Services Tracing
                6. Windows SharePoint Services VSS Writer
                7. World Wide Web Publishing Service
After the services are stopped, you should be able to detach the databases that you are looking to move.
After detaching the databases navigate to where the data and log files are being stored and copy them to your destination.
Reattach your databases.
Restart your services.
Un-quiesce the farm

Wednesday, June 27, 2012

How to change Primary Key From Int to BigInt

ALTER TABLE [dbo].[TableName]
Drop CONSTRAINT PrimaryKeyConstraintName

--change data type
ALTER TABLE [dbo].[TableName]
ALTER COLUMN ColumnName BigInt

--add primary key
ALTER TABLE [dbo].[TableName]
ADD CONSTRAINT PrimaryKeyConstraintName PRIMARY KEY (ColumnName)

Thursday, June 21, 2012

How to see PC details

Run command SystemInfo at command prompt


Start --> ControlPanel -->Administrative Tools --> Event Viewer --> Windows Log --> System

Code 6005: Tells about when system got restarted
Code 6006: Tells about when system got shut down

Tuesday, June 12, 2012

How to see transaction log space usage statistics for all databases

dbcc sqlperf(logspace)

How to See SQL Server Databases Health

Use master

 IF  EXISTS (SELECT * FROM tempdb..sysobjects with (nolock) WHERE name like '%##drives%' AND type in (N'U'))
 DROP TABLE ##drives
 IF  EXISTS (SELECT * FROM tempdb..sysobjects with (nolock) WHERE name like '%##database%' AND type in (N'U'))
 DROP TABLE ##database
 IF  EXISTS (SELECT * FROM tempdb..sysobjects with (nolock) WHERE name like '%##db_space_info%' AND type in (N'U'))
 DROP TABLE ##db_space_info

 DECLARE @hr int
 declare @exec varchar(500)
 DECLARE @fso int
 DECLARE @drive nchar(1)
 DECLARE @odrive int
 DECLARE @TotalSize nvarchar(20)
 DECLARE @MB numeric(20,2) ; SET @MB = 1048576.00

 CREATE TABLE ##drives (
 drive nchar(1) collate database_default PRIMARY KEY,
 FreeSpace numeric(20,2) NULL,
 TotalSize numeric(20,2) NULL

 INSERT ##drives(drive,FreeSpace)
 EXEC master.dbo.xp_fixeddrives

 CREATE TABLE ##database (dbname nvarchar(128) collate database_default)

 INSERT ##database(dbname)
 SELECT name FROM sysdatabases --with (nolock)
 where (512 & status) <> 512
 and (32 & status) <> 32
 and cmptlevel > 70

 DECLARE @dbname nvarchar(128)
 DECLARE @sql nvarchar(4000)
 CREATE TABLE ##db_space_info(
 dbname nvarchar(128) collate database_default,
 fgname nvarchar(50) collate database_default null,
 lname nvarchar(50) collate database_default,
 phname nvarchar(70) collate database_default,
 used_mb numeric(20,2),
 percent_used nvarchar(10) collate database_default,
 autogrow bit,growth_check bit

 DECLARE dbinfocur CURSOR LOCAL FAST_FORWARD FOR SELECT dbname from ##database ORDER by dbname

 OPEN dbinfocur

 FETCH NEXT FROM dbinfocur
 INTO @dbname

 set @sql = 'use [' + @dbname +']'
 set @sql = @sql + ' insert into ##db_space_info
 select @dbname, sfg.groupname as fgname,lname=rtrim(left([name],50)), phname=rtrim(left(filename,70)),
 case when convert(numeric(20,2),convert(numeric(20,2),FILEPROPERTY([name],''SpaceUsed''))/128) != 0
 then convert(nvarchar(10),convert(numeric(20,2),convert(numeric(20,2),convert(numeric(20,2),FILEPROPERTY([name],''SpaceUsed''))/128) / convert(numeric(20,2),convert(numeric(20,2),[size])/128) * 100))
 else ''0'' end percent_used,
 case when growth = 0 then 0 else 1 end IsAutogrow,
 CASE WHEN (sf.status&0x100000) > 0 and [maxsize] > 0 and convert(numeric (20,2),convert(numeric(20,2),convert(numeric(20,2),[size])/128) * (str(growth))/100) + convert(numeric(20,2),convert(numeric(20,2),[size])/128) < [maxsize]/128.0 and convert(numeric (20,2),convert(numeric(20,2),convert(numeric(20,2),[size])/128) * (str(growth))/100) < FreeSpace
 WHEN (sf.status&0x100000) > 0 and [maxsize] = -1 and convert(numeric (20,2),convert(numeric(20,2),convert(numeric(20,2),[size])/128) * (str(growth))/100) < FreeSpace
 WHEN (sf.status&0x100000) < 1 and [maxsize] > 0 and convert(numeric(20,2),growth/128.0) + convert(numeric(20,2),convert(numeric(20,2),[size])/128) < [maxsize]/128.0 and convert(numeric(20,2),growth/128.0) < FreeSpace
 WHEN (sf.status&0x100000) < 1 and [maxsize] = -1 and convert(numeric(20,2),growth/128.0) < FreeSpace
 ELSE 0 end growth_check
 from sysfiles sf with (nolock)
 left outer join sysfilegroups sfg with (nolock) on sf.groupid=sfg.groupid,##drives
 where (drive  = upper(left(filename,1)) COLLATE DATABASE_DEFAULT
 or drive  = lower(left(filename,1)) COLLATE DATABASE_DEFAULT) order by 1'

 exec sp_executesql @sql,N'@dbname nvarchar(128) output', @dbname output

 FETCH NEXT FROM dbinfocur
 INTO @dbname

 CLOSE dbinfocur
 DEALLOCATE dbinfocur

 create table #db_Files1
(name varchar(400),
fileid int,
filename varchar(4000),
Filegroup varchar(255),
Size varchar(300),
MaxSize varchar(100),
Growth varchar(100),
usage varchar(100))
create table #db_Files2
(Database_Name varchar(255),
Filegroup varchar(255),
Logical_Name varchar(400),
filename varchar(4000),
Size varchar(300),
MaxSize varchar(100),
Growth varchar(100))

declare dbcursor CURSOR for SELECT name FROM master..sysdatabases where (512 & status) <> 512 and (32 & status) <> 32
Open dbcursor

Fetch next from dbcursor
into @dbname

set @exec = 'Use ['+@dbname+'] exec sp_helpfile'
insert into #db_Files1 exec (@exec)

insert into #db_Files2 select rtrim(@dbname), Filegroup, rtrim(name), filename,Size, MaxSize, Growth from #db_Files1
truncate table #db_Files1
Fetch next from dbcursor
into @dbname

CLOSE dbcursor

 update #db_Files2 set Filegroup = 'Log' where Filegroup is null
 update ##db_space_info set fgname ='Log' where fgname is null

 select b.Database_Name,b.Filegroup,b.Logical_Name,b.Size,b.MaxSize,a.used_mb,a.percent_used,b.Growth,a.autogrow,a.growth_check,b.filename from ##db_space_info a, #db_Files2 b
 where b.Database_Name = a.dbname and b.Filegroup = a.fgname and b.Logical_Name = a.lname
 order by 1,2,3

 --select * from ##db_space_info with (nolock) order by dbname, fgname,lname
 drop table ##drives drop table ##database drop table ##db_space_info
 drop table #db_Files2
 drop table #db_Files1

SSRS - Change the Interactive Sort default to Descending

I believe as an SSRS developer you must be aware of Interactive sort option. This feature of the SSRS helps the end user to sort their report field/Column the way they want to sort. The default nature of this sort is an ascending and it is obvious that all users do not want their data to be sorted in an ascending order.

So if they want to sort their data in any of the way (ascending or descending) they need to click on up/down arrow button on the report column. In the first click data sorted in an ascending and on second click data gets sorted in descending order.

But here is question if user wants in reverse order I mean on first click data should be sorted in descending order and on second click in ascending order. This feature is not available in SSRS so we have to do some magic with code to get this done. This is possible on Numeric/Integer types not on character or date type fields. It may be possible but need to use cast or Convert.

So if you have any Numerical field then you have to multiply (*) that field with -1 like this