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


OR


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)
GO



How to See SQL Server Databases Health

Use master
GO


 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


 WHILE @@FETCH_STATUS=0
 BEGIN
 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)),
 used_mb=convert(numeric(20,2),convert(numeric(20,2),FILEPROPERTY([name],''SpaceUsed''))/128),
 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
 THEN 1
 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
 THEN 1
 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
 THEN 1
 WHEN (sf.status&0x100000) < 1 and [maxsize] = -1 and convert(numeric(20,2),growth/128.0) < FreeSpace
 THEN 1
 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
 END


 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


WHILE @@FETCH_STATUS = 0
BEGIN
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
END


CLOSE dbcursor
DEALLOCATE 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
GO

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

=Fields!Salary.Value*-1