Find Table Size In a Database

/* This Script will List Table size in a database

SQL Server 2005 

SQL Server 2000*/

declare @id        int                                          

declare @type  character(2)                      

declare @pages                int                                          

declare @dbname sysname

declare @dbsize dec(15,0)

declare @bytesperpage               dec(15,0)

declare @pagesperMB                  dec(15,0)

create table #spt_space

(

                objid                      int null,

                rows                      int null,

                reserved              dec(15) null,

                data                       dec(15) null,

                indexp                  dec(15) null,

                unused                 dec(15) null

)

set nocount on

-- Create a cursor to loop through the user tables

declare c_tables cursor for

select    id

from      sysobjects

where   xtype = 'U'

open c_tables

fetch next from c_tables

into @id

while @@fetch_status = 0

begin

                /* Code from sp_spaceused */

                insert into #spt_space (objid, reserved)

                                select objid = @id, sum(reserved)

                                                from sysindexes

                                                                where indid in (0, 1, 255)

                                                                                and id = @id

                select @pages = sum(dpages)

                                                from sysindexes

                                                                where indid <>

                                                                                and id = @id

                select @pages = @pages + isnull(sum(used), 0)

                                from sysindexes

                                                where indid = 255

                                                                and id = @id

                update #spt_space

                                set data = @pages

                where objid = @id

                /* index: sum(used) where indid in (0, 1, 255) - data */

                update #spt_space

                                set indexp = (select sum(used)

                                                                from sysindexes

                                                                where indid in (0, 1, 255)

                                                                and id = @id)

                                                    - data

                                where objid = @id

                /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */

                update #spt_space

                                set unused = reserved

                                                                - (select sum(used)

                                                                                from sysindexes

                                                                                                where indid in (0, 1, 255)

                                                                                                and id = @id)

                                where objid = @id

                update #spt_space

                                set rows = i.rows

                                                from sysindexes i

                                                                where i.indid <>

                                                                and i.id = @id

                                                                and objid = @id

                fetch next from c_tables

                into @id

end

select    TableName = (select left(name,60) from sysobjects where id = objid),

                Rows = convert(char(11), rows),

                ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),

                DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),

                IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),

                UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')

                               

from      #spt_space, master.dbo.spt_values d

where   d.number = 1

and        d.type = 'E'

order by reserved desc

drop table #spt_space

close c_tables

deallocate c_tables

Script To Monitor Status Of Update Statistics Operation

I tried to write a script to monitor the progress of update statistics operation. .

This script receives the SPID of the connection running this operation. It has two versions for SQL 2000 & 2005. In my box I found script for SQL 2000 runs fine for SQL 2005.

/*************************************************

*                INSTRUCTION                     *

**************************************************

1. Please open a new query analyzer window to run update statistics with full scan.

      sp_MSForEachTable 'Update Statistics ? WITH FULLSCAN'

2. Please note the associated spid with that query analyzer window from the bottom right. This spid will

      appear along with your log in name.

3. Now, please open another Query analyzer window and make sure you are connected to the SAME DATABASE that

      you are going to update the statistics.

4. Paste the script given below and create the stored procedure Stat_Prog_2000 or Stat_Prog_2005 as per the

      SQL SERVER Version.

5. Now you can pass the spid (from the query window opened in step 1) to the stored procedure to monitor the

      progress. This stored procedure will print the progress (in %) at that point of time.

*/

--------------------------------------------------

/* SQL SERVER 2005 */

use database_name

go

if(object_id('stat_prog_2005') is not null) drop procedure stat_prog_2005

go

create procedure stat_prog_2005

@spid int

as

declare @starttime as datetime;

--declare @spid as int;

--set @spid=54;

set @starttime= (select top 1 last_batch from master..sysprocesses where spid=@spid);

if(@starttime is null)

begin

      print 'Invalid spid. please check at the bottom right side of the query analyzer window running update statistics statement for correct spid.';

      return;

end

print 'SQL SERVER 2005: This script will show the progress of the update statistics with full scan for this database only.';

declare @kpid as bigint;

set @kpid = (select top 1 kpid from master..sysprocesses where spid=@spid)

if(@kpid =0)

begin

      print 'Session completed or not running.';

      return;

end

declare @blocked int;

set @blocked = (select count(*) from sys.sysprocesses where spid=@spid and blocked <>0 and blocked<>@spid)

if(@blocked <>0)

begin

      print replicate('*',60)

      print 'This supplied spid (' + convert(varchar(10),@spid) + ') is blocked.'

      select 'Blocked by spid = ' + convert(varchar(10),spid) + ', Login Time: ' + convert(varchar(30),login_time) +

      ', Program Name: ' + rtrim(program_name) + ', Login Name: ' + rtrim(loginame) from sys.sysprocesses  where spid

      in (select blocked from sys.sysprocesses where spid=@spid and blocked <>0 and blocked<>@spid)

      print replicate('*',60)

      return;

end

declare @totalstat as int

set @totalstat = (

select

      count(*)

from

      sys.stats, sys.objects

where

      stats_date(sys.stats.object_id,sys.stats.stats_id) is not null

      and sys.stats.object_id=sys.objects.object_id

      and sys.objects.type='u' );

declare @completecount as bigint;

declare @prevval as bigint;

set @completecount=(

      select count(*)

      from

            sys.stats, sys.objects

      where

            stats_date(sys.stats.object_id,sys.stats.stats_id) is not null

            and stats_date(sys.stats.object_id,sys.stats.stats_id) >= @starttime

            and sys.stats.object_id=sys.objects.object_id

            and sys.objects.type='u' );

print convert(varchar(10), (@completecount * 100)/@totalstat) + ' % statistics update complete.';

go

--EXEC stat_prog_2005 52

--------------------------------------------------------------------

--------------------------------------------------------------------

/* SQL SERVER 2000 */

use pubs

go

if(object_id('stat_prog_2000') is not null) drop procedure stat_prog_2000

go

create procedure stat_prog_2000 

@spid int

as

declare @starttime as datetime;

set @starttime= (select top 1 last_batch from master..sysprocesses where spid=@spid);

if(@starttime is null)

begin

      print 'Invalid spid. please check at the bottom right side of the query analyzer window along with your login name running update statistics statement for correct spid.';

      return;

end

print 'SQL SERVER 2000: This script will show the progress of the update statistics with full scan for this database only.';

declare @kpid as bigint;

set @kpid = (select top 1 kpid from master..sysprocesses where spid=@spid)

if(@kpid =0)

begin

      print 'Session completed or not running.';

      return;

end

declare @blocked int;

set @blocked = (select count(*) from master..sysprocesses where spid=@spid and blocked <>0 and blocked<>@spid)

if(@blocked <>0)

begin

      print replicate('*',60)

      print 'This supplied spid (' + convert(varchar(10),@spid) + ') is blocked.'

      select 'Blocked by spid = ' + convert(varchar(10),spid) + ', Login Time: ' + convert(varchar(30),login_time) +

      ', Program Name: ' + rtrim(program_name) + ', Login Name: ' + rtrim(loginame) from master..sysprocesses  where spid

      in (select blocked from sys.sysprocesses where spid=@spid and blocked <>0 and blocked<>@spid)

      print replicate('*',60)

      return;

end

declare @totalstat as int

set @totalstat = (

select count(*) from sysindexes, sysobjects

where stats_date(sysindexes.id, sysindexes.indid ) is not null

and sysindexes.id=sysobjects.id

and sysobjects.type='u');

declare @completecount as bigint;

declare @prevval as bigint;

set @completecount=(

      select count(*) from sysindexes, sysobjects

where stats_date(sysindexes.id, sysindexes.indid ) is not null

and stats_date(sysindexes.id, sysindexes.indid ) >= @starttime

and sysindexes.id=sysobjects.id

and sysobjects.type='u');

print convert(varchar(10), (@completecount * 100)/@totalstat) + ' % statistics update complete.';

go

--exec stat_prog_2000 54 

SQL Sever 2005 Cluster Setup Checklist

Beloe are the documents that are good and one should go thru before installing SQL Server 2005 on a cluster .
Server Clusters: Storage Area Networks http://www.microsoft.com/downloads/details.aspx?FamilyID=ea283d46-125f-4f94-9059-87681c0ab587&DisplayLang=en
Guide to Creating and Configuring a Server Cluster under Windows Server 2003 http://www.microsoft.com/downloads/details.aspx?FamilyID=a5bbb021-0760-48f3-a53b-0351fc3337a1&DisplayLang=en
SQL Clustering White paper: .  http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&DisplayLang=en
Below is the list of some important thing that we should keep handy before stating cluster installation. These will be required through out the process of installing windows cluster and then SQL cluster.

Parameter	Example
Domain Name	Mydomain.com
Node1 Name	ClusterNode1
Node2 Name	ClusterNode2
Node 1 Public Network IP Address/Mask	192.168.1.1/255.255.255.0
Node 2 Public Network IP Address/Mask	192.168.1.2/255.255.255.0
Private Network IP Address on Node1	10.10.10.1/255.0.0.0
Private Network IP Address on Node2	10.10.10.2/255.0.0.0
Admin Account Name & Password	Administrator/Password
Windows Cluster Virtual Name	WindowsCluster
Windows Cluster IP Address	192.168.1.3/255.255.255.0
MSDTC IP Address	192.168.1.4/255.255.255.0
MSDTC Network Name	MSDTC
Virtual SQL Server Name (Default or Named(	SQLCLUSTER\MyInstance
Virtual SQL IP Address	192.168.1.5/255.255.255.0
Cluster Service Account Name & Password	ClusterSVC/Password
SQL Service Account Name & Password	SQL2K5SVC/Password
SQL Server Domain	SQL Server Admin
MSDTC Disk Letter	M:
Quorum Disk Letter	Q:
Drive Letter For SQL Server Database Files	P:, O:, L: