MS Windows Debug

In order to debug windows processes or dumps you can install following file:
http://msdl.microsoft.com/download/symbols/debuggers/dbg_x86_6.7.05.1.exe
How to dump:
http://support.microsoft.com/kb/286350/
How to use Windbg:
http://support.citrix.com/article/CTX106566

SQL Server Standby Recovery

Recover can be done with a similar command as following:

restore log {db_name} from DISK=N'...\{db_name}_tlog_{datetime}.TRN' with standby = '...\UNDO_{db_name}.DAT'

Warning: Do not lost undo file. It is required to apply next log.
When a database or transaction log is restored in standby mode, recovery needs to roll back any uncommitted transactions so that the database can be left in a logically consistent state and used, if necessary, for read-only purposes. Pages in the database affected by the uncommitted, rolled back transactions are modified. This undoes the changes originally performed by the uncommitted transactions. The undo file is used to save the contents of these pages before recovery modifies them to prevent the changes performed by the uncommitted transactions from being lost. Before a subsequent transaction log backup is next applied to the database, the uncommitted transactions that were previously rolled back by recovery must be reapplied first. The saved changes in the undo file are reapplied to the database, and then the next transaction log is applied

If prod-standby database files are at different paths/drives and new datafiles are added to prod database, log shipping will fail with the following error:

Device activation error. The physical file name ...\MSSQL\DATA... may be incorrect.

You should apply these logs manually by moving logical datafiles to valid physical locations at standby server:

sp_resetstatus 'db_name'
restore log db_name from DISK=N'...\{db_name}_tlog_{datetime}.TRN' with standby = '...\UNDO_{db_name}.DAT',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',
MOVE '{Newly_Added_Datafile_Logical_Name}' to '{Valid_Path}\{Physical_Datafile_Name}',
...

How to change service accounts for a clustered SQL Server computer

(SQL Server 2000 and SQL Server 2005)
Although the account used is automatically assigned the appropriate privileges during the installation process, if the account is changed, it (or the Administrator group) must have the following attributes:

• It must be a domain account.
• It must be a member of the local Administrators group (Windows NT 4.0 only).
• It must be granted the following policies:

a. Act as part of the operating system.
b. Logon as a service.
c. Replace a process-level token.
d. Lock Pages in memory

• The service account for the Cluster service must have the right to log in to SQL Server. If you accept the default, the account [NT Authority\System] must have login rights to SQL Server so that the SQL Server resource DLL can run the isAlive query against SQL Server.
• If the service account for SQL Server is not an administrator in a cluster, the administrative shares cannot be deleted on any nodes of the cluster. The administrative shares must be available in a cluster for SQL Server to function.

WARNING: If you need to change the account under which the SQL Server virtual server runs, you must use SQL Server Enterprise Manager. Using this tool to change the service password will make the change on all the nodes and grant the necessary permissions to the selected user account.
If you do not use SQL Server Enterprise Manager to change passwords, full-text search may not function properly and you may be unable to start SQL Server.
If you are using Kerberos Security Support Provider Interface (SSPI) authentication in a SQL Server 2000 and Windows 2000 environment, you must drop your old service principal name (SPN), and then create a new one with the new account information. See the "Security Account Delegation" topic in SQL Server 2000 Books Online for details about using SETSPN to do this.
http://support.microsoft.com/kb/239885

How to carry "linked servers" from one server to another

Following script extracts linked servers info as sql script output

select 'exec sp_addlinkedserver @server=''' + srvname + '''' + isnull(',@srvproduct=''' + nullif(srvproduct, '')+ '''', '') + isnull(', @provider=''' +nullif(providername, '')+ '''', '') + isnull(', @datasrc=''' + nullif(datasource,'')+ '''', '') from master..sysservers

You can run output script at destination database to create linked servers. However you should carry sacurity settings of each linked server manually

Sql Server 2005 TempDB

Script to print object based space usage:

use tempdb
declare @id int
declare @dt smalldatetime
create table #spt_space_all
(
id int,
name varchar(500),
rows varchar(200) null,
reserved varchar(200) null,
data varchar(200) null,
index_size varchar(200)null,
unused varchar(200) null,
create_date smalldatetime null,
)

declare TMP_ITEMS CURSOR LOCAL FAST_FORWARD for
select id from sysobjects
where xtype='U'
open TMP_ITEMS
fetch next from TMP_ITEMS into @id
declare @pages int
WHILE @@FETCH_STATUS = 0
begin
create table #spt_space
(
id int,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
,
create_date smalldatetime null,
)
set nocount on
if @id is not null
set @dt = (select crdate from sysobjects where id=@id)
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* 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
/* 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)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
update #spt_space set create_date=@dt
end
insert into #spt_space_all
select name = @id,object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB'),create_date
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
drop table #spt_space
FETCH NEXT FROM TMP_ITEMS
INTO @id
end
CLOSE TMP_ITEMS
DEALLOCATE TMP_ITEMS
select * from #spt_space_all where [name] not like '%#spt_space_all%'
drop table #spt_space_all
GO

Script to print session based space usage:

SELECT *
FROM sys.dm_db_session_space_usage
ORDER BY (user_objects_alloc_page_count +
internal_objects_alloc_page_count) DESC

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

How to shrink:

sql server 2005 tempdb shrink

http://support.microsoft.com/default.aspx?scid=KB;EN-US;307487

Sql Server 2000 & 2005 sql scripts for space usage

Script to print space usage of tables :

Works on both sql server 2000 & 2005
DBCC UPDATEUSAGE('db_name') --Run this command to update usage statistics. Be careful at very large dbs

create table #space_usage(
OBJ_NAME varchar(255),
OBJ_ROWS varchar(20),
OBJ_RESERVED varchar(20),
OBJ_DATA varchar(20),
OBJ_INDEX_SIZE varchar(20),
OBJ_UNUSED varchar(20))
--collect usage stats
declare @tname varchar(255)
declare c_tables cursor for select name from sysobjects where xtype='U'
open c_tables
fetch next from c_tables into @tname
while @@FETCH_STATUS = 0
BEGIN
insert into #space_usage execute sp_spaceused @tname
fetch next from c_tables into @tname
END
close c_tables
deallocate c_tables
--print usage stats
select sum(CAST(REPLACE(OBJ_RESERVED,'KB','') as int))/1024 Total_Size_in_MB from #space_usage --Log size is not included
select * from #space_usage order by cast(replace(OBJ_RESERVED,' KB','') as decimal(18,0)) desc
drop table #space_usage

Script to print space usage of tables :

Works only on sql server 2005, DBCC UPDATEUSAGE('db_name') is required to update stats.

WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows, fg_name )
AS (SELECT
s.Name
, o.Name
, p.used_page_count * 8
, p.reserved_page_count * 8
, p.row_count
, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end
, ds.name
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects as o ON o.object_id = p.object_id
INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id
INNER JOIN sys.data_spaces as ds on i.data_space_id = ds.data_space_id
WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0)
SELECT t.schema_name
, t.table_name
, t.fg_name
, sum(t.used) as used_in_kb
, sum(t.reserved) as reserved_in_kb
,sum(t.tbl_rows) as rows
FROM table_space_usage as t
GROUP BY t.schema_name , t.table_name,t.fg_name
ORDER BY used_in_kb desc

Script to print space usage of filegroups & data files :

Works on both sql server 2000 and 2005.

declare @cmd nvarchar(1024)
set @cmd='dbcc showfilestats'
create table #tmpstats(
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(1024),
filename varchar(1024)
)
insert into #tmpstats execute (@cmd)
select fg.groupname TS_Name,
sum(s.totalextents)*64/1024 TS_Total_MB,
(sum(s.totalextents)-sum(s.usedextents))*64/1024 TS_Free_MB,
cast(cast(sum(s.usedextents) as real)/cast(sum(s.totalextents) as real)*100 as decimal(5,2))TS_Used_Perc
from #tmpstats s,sysfilegroups fg where s.filegroup = fg.groupid group by fg.groupname order by TS_Used_Perc
select fg.groupname TS_Name,s.name File_Log_Name,s.filename Filename,
s.fileid,s.totalextents*64/1024 File_Total_MB,
(s.totalextents-s.usedextents)*64/1024 File_Free_MB,
cast(cast(s.usedextents as real)/cast(s.totalextents as real)*100 as decimal(5,2))File_Used_Perc
from #tmpstats s,sysfilegroups fg where s.filegroup = fg.groupid order by File_Used_Perc
drop table #tmpstats

Another scriopt

create table #Data(
[Fileid] int NOT NULL,
[FileGroupId] int NOT NULL,
TotalExtents int NOT NULL,
UsedExtents int NOT NULL,
[FileName] sysname NOT NULL,
[FilePath] varchar(max) NOT NULL,
[FileGroup] varchar(MAX) NULL
)
create table #Results(
db sysname NULL ,
FileType varchar(4) NOT NULL,
[FileGroup] sysname not null,
[FileName] sysname NOT NULL,
TotalMB numeric(18,2) NOT NULL,
UsedMB numeric(18,2) NOT NULL,
PctUsed numeric(18,2) NULL,
FilePATH nvarchar(MAX) NULL,
[Fileid] int null
)
create table #Log(
db sysname NOT NULL,
LogSize numeric(18,5) NOT NULL,
LogUsed numeric(18,5) NOT NULL,
Status int NOT NULL,
[FilePath] varchar(max) NULL
)
INSERT into #Data ([Fileid], [FileGroupId], TotalExtents ,
UsedExtents , [FileName] , [FilePath])
EXEC ('DBCC showfilestats')

update #Data
set #data.Filegroup = sysfilegroups.groupname
from #data, sysfilegroups
where #data.FilegroupId = sysfilegroups.groupid

INSERT INTO #Results (db ,[FileGroup], FileType , [FileName], TotalMB ,
UsedMB , PctUsed , FilePATH, [Fileid])
SELECT DB_NAME() db,
[FileGroup],
'Data' FileType,
[FileName],
TotalExtents * 64./1024. TotalMB,
UsedExtents *64./1024 UsedMB,
UsedExtents*100. /TotalExtents UsedPct,
[FilePath],
[Fileid]
FROM #Data
order BY 1,2

insert #Log (db,LogSize,LogUsed,Status )
exec('dbcc sqlperf(logspace)')

insert #Results(db, [FileGroup], FileType, [FileName], TotalMB,UsedMB, PctUsed, FilePath, [Fileid])
select DB_NAME() db,
'Log' [FileGroup],
'Log' FileType,
s.[name] [FileName],
s.Size/128. as LogSize ,
FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,
(s.Size/128. - FILEPROPERTY(s.name,'spaceused')/8.00 /16.00) UsedPct,
s.FileName FilePath,
s.[Fileid] [Fileid]
from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s
where f.dbid = DB_ID()
and (s.status & 0x40) <> 0
and s.[Fileid]=f.[Fileid]
and l.db = DB_NAME()

SELECT r.*,
CASE WHEN s.maxsize = -1 THEN null
else CONVERT(decimal(18,2), s.maxsize /128.)
END MaxSizeMB,
CONVERT(decimal(18,2), s.growth /128.) GrowthMB
FROM #Results r
INNER JOIN dbo.sysfiles s
ON r.[Fileid] = s.[Fileid]
ORDER BY 1,2,3,4,5

DROP TABLE #Data
DROP TABLE #Results
DROP TABLE #Log


Script to print db log space usage :

dbcc sqlperf (logspace)