Archive for April, 2008

Steps to install DNS Server on Windows 2003 Server.

1. Click on Start

2. Click

  1. Before installation of SQL Server take the backup of existing Datafiles and Transaction logs.
  2. Rename the datafiles and transaction logs for all system databases master,model, msdb and pubs to file_name_OLD.mdf and file_name_OLD.ldf.
  3. Install the SQL Server.
  4. Stop the services for SQL Server.
  5. Rename the New datafiles and Transaction logs for all system databases to file_name_NEW.mdf and file_name_NEW.ldf.
  6. Rename the Old datafiles and transaction logs for all system databases to Original name file_name.mdf and file_name.ldf.
  7. Start the services for SQL Server.
  8. Check all user and system Databases exists on the server and nothing is offline.
  9. Verify the sql server and windows logins.

Configuring MS DTC

Posted: April 1, 2008 in Sqlserver

Configuring MS DTC

 

MS DTC should be configured on a new server to allow distributed transactions.

 

1) This should be done through selecting Programs-Administrative Tools – Component Settings.

 

2) This will open the Component Services.

 

3) You will find Console.  If you expand this, you will find Component Services.

 

4) Under component Services, you will find Computers directory.

 

5) Under Computers directory, we can find My Computer.

 

6) Right click on My Computer and select Properties.

 

7) In the Properties, we need to select the MSDTC Tab.

 

8) At the bottom of the screen, we will find Security Button.

 

9) Click on this button to go the Security Screen.  There you will find the settings that should be made so that the remote transactions can be allowed.

Identifying and Correcting the Problem for Transaction Log Full w.r.t Regular DB’s

Error:

From:                SQLExec

Sent:                Sunday, April 30, 2006 2:00 AM

To:                    #IS CompTechDBA

Subject:            SQL Server Alert System: ‘Full Log’ occurred on \\HAS605

DATE/TIME:      4/30/2006 2:00:04 AM

DESCRIPTION:  Error: 9002, Severity: 17, State: 6

The log file for database ‘AGQuantum’ is full. Back up the transaction log for the database to free up some log space.

COMMENT:       (None)

JOB RUN:         (None)

 Solution:

If the Regular database Transaction logs runs out of space, this is indicated in the SQL ERRORLOG files, use the following process:

1. Free up (unallocated) the space used by the LOG portion of the database with the following command called from the master database:

USE <database>

GO

BACKUP LOG <database> WITH TRUNCATE_ONLY
GO

Notes:

1. After you truncate a database LOG file, the SQL server documentation recommends that you back up your database. In case of a physical failure (for example a power down or hard disk error), the SQL server cannot recover from the transaction log, as it was just truncated.

2. After running this command, the LDF file has been reorganized to have a lot of unallocated space, but the database must be shrunk to release that space to the file system. (It still looks like a large file if you view it from a command prompt directory listing). See next example for how to shrink the database.

Shrinking a Database

You can shrink a database to release the unallocated or unused space (or both) to the file system with the following command:

USE <database>

GO

DBCC SHRINKDATABASE (database)

GO

You can also use the SQL Enterprise Manager to shrink a database by selecting the following menu items: Right click on the Database -> All Tasks -> Shrink Database.

 

 

Identifying and Correcting the Problem for Transaction Log Full w.r.t Tempdb

Error:

From: SQLExec

Sent: Monday, February 13, 2006 5:05 PM

To: #IS CompTech DBA

Subject: SQL Server Alert System: ‘Full log’ occurred on \\HAS644

DATE/TIME: 2/13/2006 5:04:33 PM

DESCRIPTION: Error: 9002, Severity: 17, State: 6

The log file for database ‘tempdb’ is full. Back up the transaction log for the database to free up some log space.

COMMENT: (None)

JOB RUN: (None)

Solution:

  • Check whether for the sufficient Disk Space.
  • When this error is raised, in the Enterprise Manager
    Right click on the Tempdb Database ->click on Properties -> Data Files -> Look for the Space allocated Column (Size)
  • Now, Increase the Size of the datafile or you can also add a new Data file.
  • Do Not try to Shrink “Tempdb” database.

sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
sp_configure ‘xp_cmdshell’, 1
GO
RECONFIGURE
GO

Monitor Database Growth For SQLSERVER2005

__________________________________________________________________________________________________________________________________________________

–PART 1

If exists (Select name from sys.objects where name = ‘DBGrowthRate’ and Type = ‘U’)
Drop Table dbo.DBGrowthRate

Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Drop table #TempDBSize

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize_In_MB, CurSize_In_MB, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)

–Drop table #TempDBSize

Select *
from DBGrowthRate
–Above creates initial table and checks initial data

–PART 2
–Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate
from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))
– dgr.CurSize)) + ‘ MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
End

–Select *
–from DBGrowthRate
—-Verifies values were entered

Drop table #TempDBSize2

_____________________________________________________________________________________________________________

Monitor Database Growth FOR SQLSERVER2000

_____________________________________________________________________________________________________________

–PART 1

If exists (Select name from sys.objects where name = ‘DBGrowthRate’ and Type = ‘U’)
Drop Table dbo.DBGrowthRate

Create Table dbo.DBGrowthRate (DBGrowthID int identity(1,1), DBName varchar(100), DBID int,
NumPages int, OrigSize_In_MB decimal(10,2), CurSize_In_MB decimal(10,2), GrowthAmt varchar(100),
MetricDate datetime)

Drop table #TempDBSize

Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize
from sysdatabase sd
join sysaltfiles mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize_In_MB, CurSize_In_MB, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)

–Drop table #TempDBSize

Select *
from DBGrowthRate
–Above creates initial table and checks initial data

–PART 2
–Below is the code run weekly to check the growth.
Select sd.name as DBName, mf.name as FileName, mf.database_id, file_id, size
into #TempDBSize2
from sys.databases sd
join sys.master_files mf
on sd.database_ID = mf.database_ID
Order by mf.database_id, sd.name

If Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
and Convert(varchar(10),GetDate(),101) > (Select Distinct Convert(varchar(10),Max(MetricDate),101) as MetricDate
from DBGrowthRate)
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
dgr.CurSize as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
Convert(varchar(100),(Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024))
– dgr.CurSize)) + ‘ MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
join DBGrowthRate dgr
on tds.database_ID = dgr.DBID
Where DBGrowthID = (Select Distinct Max(DBGrowthID) from DBGrowthRate
where DBID = dgr.DBID)
Group by tds.database_ID, tds.DBName, dgr.CurSize)
End
Else
IF Not Exists (Select Distinct DBName from #TempDBSize2
where DBName in (Select Distinct DBName from DBGrowthRate))
Begin
Insert into dbo.DBGrowthRate (DBName, DBID, NumPages, OrigSize, CurSize, GrowthAmt, MetricDate)
(Select tds.DBName, tds.database_ID, Sum(tds.Size) as NumPages,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as OrigSize,
Convert(decimal(10,2),(((Sum(Convert(decimal(10,2),tds.Size)) * 8000)/1024)/1024)) as CurSize,
‘0.00 MB’ as GrowthAmt, GetDate() as MetricDate
from #TempDBSize2 tds
where tds.database_ID not in (Select Distinct DBID from DBGrowthRate
where DBName = tds.database_ID)
Group by tds.database_ID, tds.DBName)
End

–Select *
–from DBGrowthRate
—-Verifies values were entered

Drop table #TempDBSize2

bry4+u

/*****************************************************************************************************
First Create Procedure and execute on Sqlserver2000 server
*****************************************************************************************************/

Use Northwind
go
create procedure SpaceInfo @dbname varchar(100)
as
DECLARE @SQLCode varchar(5000)
SET @SQLCode =
‘USE [‘ + @DBName +’]
DECLARE @SpaceSeeker TABLE ([Group_Name] varchar(50), [File_Name] varchar(100), Logical_Disk char(3),[File_Location] varchar(100),
[Allocated_Space] decimal(15,4), [Actual_Size] decimal(15,4), [Space_Available] decimal(15,4),
[Total_FREE_Drive_Space] decimal(15,2), [Max_Growth] char(16),[File_Growth] varchar(15))
— insert calculations into table

INSERT INTO @SpaceSeeker ([Group_Name] , [File_Name] ,Logical_Disk,[File_Location], [Allocated_Space] , [Actual_Size], [Total_FREE_Drive_Space] , [Max_Growth] ,[File_Growth])
SELECT
Group_Name = sfg.groupname,
[File_Name] = sf.[name],
Logical_Disk=LEFT([filename],3),
File_Location = [filename],
Allocated_space = sf.[size] * 8 /1024,
Actual_Size  = FILEPROPERTY(sf.[name], ”SPACEUSED”) * 8 /1024,
Total_Disk_Space = 0.0,
MAX_GROWTH = CASE
WHEN sf.[maxsize] = 0 THEN ”NO GROWTH SET”
WHEN sf.[maxsize] = -1 THEN ”NO MAX LIMIT SET”
ELSE STR((sf.[maxsize] * 8) /1024)+ ” MB”
END,
File_Growth=CASE
WHEN (sf.status&0x100000) > 0 THEN STR(sf.growth)+” %”
ELSE STR((sf.growth * 8) /1024)+” MB”
END
— join sysfilegroup just to get filegroup name
FROM sysfiles sf (NOLOCK) LEFT OUTER JOIN sysfilegroups sfg (NOLOCK)
ON sf.groupid = sfg.groupid
ORDER BY 1
— make a second pass to update table
— this will store the actual space available in the file
UPDATE @SpaceSeeker
SET Space_Available = ((Allocated_Space) – (Actual_Size ))
–*****************************************************
— this is to update total drive space. it uses xp_fixeddrives and temp table
— reason for temp table is that exec is not support when doing an insert into a table variable
— this statement is a little different in the job
Create TABLE  #DRIVESPACE  ([Logical_Disk] char(1), [MB_Free] varchar(15))
INSERT #DriveSpace
exec (”master..xp_fixeddrives”)
update @SpaceSeeker
SET Total_FREE_Drive_Space = (MB_FREE)/1024
from #DriveSpace a JOIN @SpaceSeeker b ON a.Logical_Disk =LEFT(b.Logical_Disk,1)
drop table #DriveSpace
–*******************************************************
IF NOT EXISTS (SELECT 1 FROM Northwind.DBO.sysobjects WHERE NAME = ”DBA_DBSize_Growth” AND type = ”U”)
BEGIN
CREATE TABLE Northwind.DBO.DBA_DBSize_Growth
(
[Database_Name] varchar(50), [Group_Name] varchar(50), [File_Name] varchar(50), Logical_Disk char(3),[File_Location] varchar(100),
[Allocated_Space] decimal(15,4), [Actual_Size] decimal(15,4), [Space_Available] decimal(15,4),
[Total_FREE_Drive_Space_GB] decimal(15,4), [Max_Growth] char(16),[File_Growth] varchar(15),
[Size_Date] smalldatetime DEFAULT (GETDATE())
)
END
–INSERT INTO Northwind
INSERT INTO Northwind.DBO.DBA_DBSize_Growth
SELECT ”’ + @DBName + ”’,
Group_Name ,
File_Name  ,
Logical_Disk,
File_Location,
Total_Allocated_Space_MB = Allocated_Space ,
Actual_File_Size_MB = Actual_Size,
Space_Available_MB = Space_Available ,
Total_FREE_Drive_Space,
MAX_GROWTH_MB = MAX_GROWTH ,
File_Growth,
Size_Date= GETDATE()
FROM @SpaceSeeker
GO’

–print @SQLCode
EXEC (@SQLCode)
–***************************************************************************************
GO

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

Place in job to do a nightly dump into table for monitoring purposes.

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

USE Northwind
GO

DECLARE @DBName varchar(50), @SQL nvarchar(250)

DECLARE  tmpDBRetrieval CURSOR FOR
SELECT [name] from master.dbo.sysdatabases
WHERE DBID NOT IN (1,2,3,4)–5,7,8,9,10,11,12)  –AND status NOT IN (16,32,64,128,256,512,520)
ORDER BY [name]
OPEN tmpDBRetrieval
FETCH NEXT FROM tmpDBRetrieval
INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N’exec Northwind.dbo.SpaceInfo ”’ + @DBName + ””
EXEC (@SQL)
–print @sql
IF @@ERROR <> 0
BEGIN
GOTO IFERROR
END
IFERROR:
FETCH NEXT FROM tmpDBRetrieval
INTO @DBName

END
CLOSE tmpDBRetrieval
DEALLOCATE tmpDBRetrieval

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

To get result

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

SELECT     * FROM         DBA_DBSize_Growth

for %1 in (d:\TestPackages\t1*.dtsx) do dtexec /F “%1”

Change the ‘SA’ password without having old password

Use OSQL with the –E option (for authenticated user). If you are an administrator of the OS, you will get in without being prompted for the sa password. Once you are at the OSQL interactive prompt, issue the following command:

—————————————————————————————————
EXEC sp_password NULL, ‘newpassword’, ‘username’ GO