How to check Service Broker is Enabled or Disabled in SQL2005

Service Broker - Service Broker will be disabled when a backup is restored
You may be surprised to see your service broker application does not work after you restore a backup. The reason is that, when you restore a backup, the new Database will be restored with service broker in disabled mode. You can run the following query to check this.

SELECT is_broker_enabled FROM sys.databases WHERE database_id = DB_ID()

Run the above query in the newly restored database and it will return 0. The reason for this behavior is the following.

Each database contains a service broker identifier that uniquely identifies the service broker instance within the network. This unique identifier is used to identify the correct instance of the service broker and deliver messages. When you restore a database, the newly restored database will have the same service broker instance ID as the database from which the backup is taken. This can cause conflicts and existing service broker applications can be disturbed. To avoid this, when a database is restored, service broker is disabled.

To enable service broker you should run the following.
ALTER DATABASE dbname SET NEW_BROKER

This will reinitialize sevice broker, will assign a new service broker instance ID and clear the messages in the queues. You can run the query we saw previously to make sure that service broker is enabled. If you check the service_broker_guid column of sys.databases, you could see that a new GUID is assigned to the service broker instance of the new database.
Ref: http://www.sqlserverandxml.com/2008/08/service-broker-service-broker-will-be.html

-- Enable Service Broker:
ALTER DATABASE [Database Name] SET ENABLE_BROKER;

-- Enable Service Broker with new service_broker_guid, used when you restored any database:
ALTER DATABASE dbname SET NEW_BROKER

-- Disable Service Broker:
ALTER DATABASE [Database Name] SET DISABLE_BROKER;
Node: By default service broker is enabled for TEMPDB & MSDB databases.
For More Information: http://msdn.microsoft.com/en-us/library/ms166057.aspx

Automated Monitoring Database Size Using sp_spaceused

Use this:
exec master.dbo.sp_msforeachdb "exec [?].dbo.sp_spaceused"

For more information please go to the below link:
http://www.sqlservercentral.com/articles/Administration/2771/

Determing SQL Server Table Size

Here is the Scripts: Using this you can findout the spaceused by the tables in a database.

Solution 1: Using this script you can only check the size of user tables not system tables.
DECLARE @SQL VARCHAR(255)
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC(@SQL)
CREATE TABLE #foo
(
name VARCHAR(255),
rows INT ,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255)
)
INSERT into #foo
EXEC sp_MSForEachtable 'sp_spaceused ''?'''
SELECT * FROM #foo
DROP TABLE #foo

Solution 2:
The SYSINDEXES file keeps tracks of all information.
The size are the number of pages, and each page is 8Kb, so they need to be converted.

USE sql911;
go

SELECT CAST(OBJECT_NAME(id) AS VARCHAR(12)) AS 'Table',
CAST(rowcnt AS VARCHAR(6)) AS '#Rows',
reserved * 8 AS 'Disk - Kb',
dpages * 8 AS 'Data - Kb',
(SUM(used) * 8) - (dpages * 8) AS 'Index - Kb'
FROM sysindexes
WHERE indid IN (0,1)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
GROUP BY id, rowcnt, reserved, dpages
ORDER BY 'Table';
go
Note:
You must first select the database.
SYSINDEXES store the space allocated in pages.
The standard page size is 8kb, unless you created the database with a different page size, then adjust the query.

------------
For more information please go to below link:
http://www.eggheadcafe.com/software/aspnet/30215209/database-table-size.aspx
http://www.sqlhacks.com/index.php/Administration/RecordsForAllTables
http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx

Collation Setting Change In SQL 2005.

The collation of the server is taken as the default collation for all the system databases and the user databases that are created thereafter. Hence by changing the Collation setting for the Server we can create a default collation which would automatically be applied to the new user databases that are created.

Follow the below steps to change the collation for SQL Server 2005:

Take the backup of all the databases, jobs, logins etc.
1. Detach all the user databases.
2. To rebuild the master databse:
* Copy the SQL Bits
* From the command prompt you need to run the setup file. For this you have to change the directory in the command window to the directory where the SQL setup.exe file is located
egs:
C:\> cd d:\SQLBITS\SQL\Enterprise\X86\Tools


* Here you need to run the following command mentioning the new collation that you want to apply:

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SQLCOLLATION=
Note: Here the command assumes that we are using CD for sql server setup or Install.
Running the above command would lead you to the GUI Screen, similar to the one seen during SQL Installation. Following the instructions on this GUI screen would complete the rebuilding of master database
You may then re-attach the user databases.
Once this is done, you can check the server collation under properties window for a server in Management studio.

Here is the link for reference:
http://msdn.microsoft.com/en-us/library/ms144259.aspx

If we are using dumps for installation :
So open command prompt and browse to the location where sqlserver install dumps are located say D:\X86\Servers\ Now run the below command.

\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD= SQLCOLLATION=

1. SAPWD can be ignored if the sql server authentication is Windows only.
2. Incase if there are user databases and sql server agent jobs configured already then take backup of user database (attach/detach the .mdb and .ldf files) and sysjobs (script the jobs), User logins.

3. Backup the original resource database and system databases, Once the new databases are created with new collation then you can attach back the user databases and script the jobs.

Is there a way to determine node name in SQL using QA?

Customer Question:
I am running a 2-node SQL 2000 cluster in Active / Passive mode. Is there a way in SQL to determine what the node name would be, that runs the instance of SQL at any point in time?

Method :1
For SQL Server 2005 clustered instances, we can use a new serverproperty
select serverproperty('ComputerNamePhysicalNetBios')

->This will return the NetBIOS name of the local computer on which the instance of SQL Server is currently running
->Also, now in SQL 2005, the errorlogs tells us the actual node name too.

Method :2
DECLARE @NodeName sysname
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\',
@value_name='ComputerName', @value=@NodeName OUTPUT
PRINT @NodeName