Search This Blog

Thursday, August 26, 2010

Virtual Memory Of SQL Server

Hi Friends,

I got a request from one of my friend stating that he needs a alert that once Vitual Memory of SQL Server Process goes less than 256 MB he can get an alert.

He wished and I did it.

VirtualMemoryLess256MB.vbs
******************************

set events = GetObject("winmgmts:{impersonationLevel=impersonate}").ExecNotificationQuery ("select * from __instancemodificationevent within 5 where targetinstance isa 'Win32_Process' and targetinstance.Name='SQLSERVR.exe' and targetinstance.VirtualSize<268435456")
' Note this next call will wait indefinitely - a timeout can be specified
'WScript.Echo "Waiting for Virtual Memory to be less than 256 MB event..."
'WScript.Echo ""
do
set NTEvent = events.nextevent
if err <> 0 then
WScript.Echo Err.ProcessID, Err.Name, Err.VirtualSize
Exit Do
else
WScript.Echo NTEvent.TargetInstance.ProcessID
WScript.Echo NTEvent.TargetInstance.Name
WScript.Echo NTEvent.TargetInstance.VirtualSize
end if
loop
WScript.Echo "finished"

Wednesday, August 25, 2010

Query to Linked Server Fails With Error "An error occured during decryption"

Hi,

Recently someone asked me about this and I think i should post it on my blog.

Problem Description
**********************

Our development server crashed. It was a VM and the Virtual Disks disapeared. The server was rebuilt, SQL Server installed and i restored the master and msdb databases from a backup.

Now whenever i try and query a linked server i get the message "An error occured during decryption".

Resolution
************

Please check if we have a entry in our registry (below one)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\Security

Key Name is : Entropy

Value for this key will be a big characters (not readable).

If this entry is missing then run below command and it will recreate this entry and Linked Server should work.

USE MASTER

ALTER SERVICE MASTER KEY FROCE REGENERATE


NOTE: It might be that this registry key will be there, in that case I will still
suggest to Re-Generate the MASTER KEY.

Saturday, August 14, 2010

Missing Tables in Master Database

Hi All,

I met with a strange incident where some tables from MASTER database were deleted. Below are the steps which I take in order to correct this problem.


Problem -

--Tables from MASTER database are dropped (4-5 tables).

--User databases are not working fine.

--When they do SP_SPACEUSE it gives error about a table

SPT_VALUE table is not there

--Customer don't have database backups.

--Customer believes they have recently moved to production.

Environment -


SQL Server 2005 Enterprise Edition x32 SP2 3054
Windows 2003 Enterprise Server x32 SP2

Root Cause (if known) -

--System tables from master databases were deleted.

Resolution -

--We have below tables missing from Master Database.

MSreplication_options
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
spt_values

--As system tables from master databases were deleted so we were left with 2 options.

1) Try to rebuild system database.
2) Reinstall new instance of SQL and move user database.

--We select 1st option.

--As we have couple of logins in the databases so we script out those logins using KB article. http://support.microsoft.com/kb/918992

--We script or a job as well although that was disabled.

--We used below command to rebuild master databases but it was failing with errors.

setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1
SAPWD=sql.server SKUUPGRADE=1 SQLINSTALLDIR="E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA"

Last Action : InstallFinalize
Error String : An installation package for the product Microsoft SQL Server 2005
cannot be found. Try the installation again using a valid copy of the
installation package 'SqlRun_SQL.msi'.
Error Number : 1706

--We checked into the registry keys and changed LastUsedSource key to new location (from where we were staring the installation).

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\\SourceList\LastUsedSource

--Again start installation but it again fails with same error.

--Further analyzed and found that we didn’t have a key named NET under above mentioned registry key so we created this new key and add a key in it with the name 1 having
type Expandable String. This points to new location of setup files as was for LasteUsedSource.

--Now we have below keys in registry.

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\\SourceList – With keys
LastUsedSource
PackageName
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\\SourceList\Media – With keys
1
DiskPrompt
MediaPackage
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Installer\Products\\SourceList\Net – With keys
1

--Start rebuilding task and it completes successfully.

--We attached user databases and recreated logins (from the script created) & recreated job.

--We installed 3054 hot fix successfully.

Regards
Gursethi

How to Configure Replication & Database Mirroring on Principal Database

Hi All,

I was working on a customer requirement where they want to enable Database Mirroring and Replication on the same database. I thoughts lets discuss it with you guys:

ISSUE:
========

We want to setup replication and mirroring on the Principal server.
We also like to understand what would be the behaviour when the principal/mirror goes into disconnected state, Principal server becomes unavailable etc.
What will be the effect on Replication?

Research:
============

--Was able to setup the replication and mirroring by following the MSDN article
{
http://msdn.microsoft.com/en-us/library/ms151799.aspx
Replication and Database Mirroring

--Please make sure that the we are going thru this section in order to perform this setup.


--Configuring replication and database mirroring involves five steps.

1.>Configure the Publisher.
2.>Configure database mirroring.
3.>Configure the mirror to use the same Distributor as the principal.
4.>Configure replication agents for failover.
5.>Add the principal and mirror to Replication Monitor.

--Refer to the above article for more details on these steps
}

Setup:
----------
Server1: Publisher/Initial Principal: SQL 2005 (9.00.3310)
Server2: Mirror :SQL 2005 (9.00.3310)
Server3: Distributor and Subscriber: SQL 2005 (9.00.3310)

--We set the PublisherFailoverPartner switch only for as per the article Snapshot Agent (for all publications) Log Reader Agent (for all transactional publications)

--The initial setup looked good. Server1 was mirroring the data to Server2. Also replicating the data to Serevr3


Initiating the failures:
--------------------------

Scenario 1:
=================

Initial Principal goes down and you make the Initial mirror server the new Principal
-----------------------------------------------------------------------------------------------
Taking the Principal Server down:
---------------------------------
1.>Stopped Server1
2.>We see that both Server1 and Server2 go into disconnected state.
3.>We did not have a witness and hence no automatic failover occurred.
4.>The Initial principal is down so has to transfer the role of to using

alter database database_name set partner FORCE_SERVICE_ALLOW_DATA_LOSS

5.>Now the Status of the New Principal is

At this stage even if you get the Initial principal up, we will still see them in the disconnected state.
At this stage, the replication will not work (The data is not pushed to the subscriber) And you would see the following in the verbose logs

{

2009-04-14 23:08:37.328 Connecting to OLE DB T-GURSETHI\GURSETHI2 at datasource: 'T-GURSETHI\GURSETHI2', location: '', catalog: 'sqlnexus', providerstring: '' using provider 'SQLNCLI'
2009-04-14 23:08:37.328 Connection is using failover partner name 't-gursethi\gursethi1' to connect to database'sqlnexus'
2009-04-14 23:08:39.677 OLE DB T-GURSETHI\GURSETHI2: T-GURSETHI\GURSETHI2
...
...
...
2009-04-14 23:09:31.492 Publisher: {call sp_replcmds (500, 0, 0, , 5023, 500000)}
2009-04-14 23:09:36.515 Publisher: {call sp_replcmds (500, 0, 0, , 5024, 500000)}
2009-04-14 23:09:41.530 Publisher: {call sp_replcmds (500, 0, 0, , 5007, 500000)}
2009-04-14 23:09:46.554 Publisher: {call sp_replcmds (500, 0, 0, , 5039, 500000)}
2009-04-14 23:09:46.570 OLE DB DISTOLE GURSETHI2008': sp_MSget_last_transaction @publisher_id = 3, @publisher_db = N'sqlnexus', @for_truncate = 0x1
2009-04-14 23:09:46.571 Publisher: exec sp_replcounters N'sqlnexus'
2009-04-14 23:09:46.572 OLE DB Publisher 'T-GURSETHI\GURSETHI2': exec sp_replcounters N'sqlnexus'
2009-04-14 23:09:46.573 Status: 16384, code: 22522, text: 'Replicated transactions are waiting for next Log backup or for mirroring partner to catch up.'.
2009-04-14 23:09:51.580 Publisher: {call sp_replcmds (500, 0, 0, , 5023, 500000)}
2009-04-14 23:09:56.607 Publisher: {call sp_replcmds (500, 0, 0, , 5024, 500000)}
2009-04-14 23:10:01.622 Publisher: {call sp_replcmds (500, 0, 0, , 5007, 500000)}

}

The Error with which the log reader agent would fail with is:



Indicating that the Principal and Mirror are not in sync and hence the data is not replicated to the subscriber.
In fact the data is not even pushed to the distributor as the agent that’s failing is log reader agent

If we execute on the publisher during this time, we can see the count of increasing
Also ran on the distribution database and confirmed that the new statements/transactions are not yet in there


==>This is as per the expected behaviour. Here are the behaviours observed.

Operating Mode
------------------------
High-safety mode with automatic failover
Transaction Safety is FULL

Mirroring and Replication Behaviour
-------------------------------------------

All the committed transactions are guaranteed to be hardened to disk on the mirror server.
Log reader will only replicate transactions which are hardened on the mirror.

Loss of a partner has the following effect:

1.>If the mirror is unavailable, the Principal continues to serve the database. The Log Reader Agent propagates commands to the distribution database.
But the commands are not yet pushed to the subscriber until the mirror server comes in sync with the principal. Also the principal cannot failover
to the mirror until the mirror is back online and has all transactions from the principal.

2.>If the principal is unavailable, automatic failover occurs. The mirror server switches to the role of principal, and it offers its database as the
principal database. The same points mentioned above in case of still apply here for replication



Operating Mode
------------------------
High-performance mode
Transaction Safety is OFF

Mirroring and Replication Behaviour
--------------------------------------------
Log reader will only replicate transactions which are hardened on the mirror.

Loss of a partner has the following effect:

1.>If the mirror is unavailable, the principal database is running exposed, however replication always runs in SAFE mode i.e., only replicates those
transactions that are mirrored. The Log reader agent doesn’t even push the commands to the distribution database. Be aware that replication latency
will increase if the mirror falls behind the principal.

2.>When the principal fails, the database owner has several choices, as follows:

a.>Leave the database unavailable until the principal becomes available again.

If the principal database and its transaction log are intact, this choice preserves all of the committed transactions at the expense of availability.

b.>Stop the database mirroring session, and then begin a new database mirroring session.

If the principal database is lost but the principal server is still running, immediately attempt to back up the tail of the log on the principal database.

If the tail-log backup succeeds, stopping the session may be your best alternative. After stopping the mirroring session, you can restore the log onto
the former mirror database, which preserves all of the data.

c.>Force service (with possible data loss) on the mirror server.

Forcing service and causes the mirror server to assume the role of principal and serve its copy of the database for clients.
When service is forced, whatever transaction logs the principal has not yet sent to the mirror server are lost.
Therefore, you should limit forced service to situations where possible data loss is acceptable and immediate database availability is critical.

When service is forced and the mirror server assumes the role of the principal, the log reader will work against the mirror and start picking up the new transactions.
But they are not propagated to the subscribers until the previous principal/Current mirror gets in sync with the new principal.
Hence the replication latency will increase if the mirror falls behind the principal.


Operating Mode
------------------------
High-safety mode without automatic failover
Transaction Safety is FULL

Mirroring and Replication Behaviour
-------------------------------------------
All the committed transactions are guaranteed to be hardened to disk on the mirror server.
Log reader will only replicate transactions which are hardened on the mirror.

Loss of a partner has the following effect:

If the mirror is unavailable, the principal disallows further activity in the database; therefore the Log Reader Agent has no transactions to replicate.


Regards
Gursethi

Finding the last date when Full / Diff / Transaction Log / File Level Backups Taken For All Databases

Select Distinct convert(varchar(25),@@Servername) as Servername,
convert(varchar(30),e.database_name) as DBname,
convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery'))) as Recovery_Model,
(Select convert(varchar(25),Max(backup_finish_date), 100) +' File Location ' + bmf.physical_device_name
From msdb.dbo.backupset a,msdb.dbo.backupmediafamily bmf
Where a.database_name=e.database_name
and a.media_set_id=bmf.media_set_id
and bmf.media_set_id=e.media_set_id
and a.server_name = @@servername
and type='D'
Group by a.database_name,bmf.physical_device_name) as 'Full Backup Date & File Location',

(Select convert(varchar(25),Max(backup_finish_date) , 100) +' File Location '+ bmf.physical_device_name
From msdb.dbo.backupset b,msdb.dbo.backupmediafamily bmf
Where b.database_name=e.database_name
and b.media_set_id=bmf.media_set_id
and bmf.media_set_id=e.media_set_id
and b.server_name = @@servername
and type='L' Group by b.database_name,bmf.physical_device_name) 'Transaction Log Backup Date & File Location',

(Select convert(varchar(25),Max(backup_finish_date) , 100) +' File Location '+ bmf.physical_device_name
From msdb.dbo.backupset c,msdb.dbo.backupmediafamily bmf
Where c.database_name=e.database_name
and c.media_set_id=bmf.media_set_id
and bmf.media_set_id=e.media_set_id
and c.server_name = @@servername
and type='I' Group by c.database_name,bmf.physical_device_name) as 'Differential Backup Date & File Location',

(Select convert(varchar(25),Max(backup_finish_date) , 100) +' File Location '+ bmf.physical_device_name
From msdb.dbo.backupset d,msdb.dbo.backupmediafamily bmf
Where d.database_name=e.database_name
and d.media_set_id=bmf.media_set_id
and bmf.media_set_id=e.media_set_id
and d.server_name = @@servername
and type='F' Group by d.database_name,bmf.physical_device_name) as 'File Level Backup Date & File Location'
From msdb.dbo.backupset e
Where e.database_name Not in ('tempdb','pubs','northwind','model')
and e.server_name = @@Servername
and e.database_name in (Select Distinct name from master..sysdatabases)

-- NEVER BACKED UP

Union all
select Distinct convert(varchar(25),@@Servername) as Servername,
convert(varchar(30),name) as DBname,
convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),NULL, NULL , NULL, NULL
from master..sysdatabases as record
where name not in (select distinct database_name from msdb.dbo.backupset)
and name not in ('tempdb','pubs','northwind','model')
order by 1,2

Error 2601, Severity 14, State 1, Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'

Hi Team,

Here by I would like to share a problem which I faced in once of my account,

Environment
****************

SQL Server 2008 Enterprise Edition x64 10.00.2531 (Service Pack 1)
Windows 2003 Enterprise Edition x64 SP2

Problem Description
*************************

* We have CHANGE TRACKING enabled on one of our database.
* Because of some problem we restarted SQL Server Services and after that we start getting below error messages:

2010-03-30 09:47:57.42 spid13s Error: 2601, Severity: 14, State: 1.
2010-03-30 09:47:57.42 spid13s Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'.
2010-03-30 09:47:57.42 spid13s Error: 3999, Severity: 17, State: 1.
2010-03-30 09:47:57.42 spid13s Failed to flush the commit table to disk in dbid 6 due to error 2601. Check the errorlog for more information.

* Manual CHECKPOINT operation & Backup of database also got failed with the same error.

Resolution As Per KB Article
*****************************

* Microsoft Identified this problem as a bug and as per resolution one need to apply SQL Server 2008 Service Pack 1 Cummulative Update 7.
* Below is the KB artcile.

A backup Operation On A SQL Server 2008 Database Fails If You Enable Change Tracking On This Database
http://support.microsoft.com/kb/978839

Twist In The Tail
*****************

* This KB has a WORK AROUND section which talk about:

Restarting SQL Server Services
Disable Change Tracking
Re-enable Change Tracking

* We did worked as per this method (below) but it didn't resolved our problem :

Restart SQL Server Services.
Disable Change Tracking On All The Tables Involved.
Disable Change Tracking at Database Level.
Re-enabled Change Tracking at Database Level,
Re-enabled Change Tracking on All the Tables Involved.

* We applied Cummulative Update 7 of Service Pack 1 of SQL Server 2008 but still problem persists.

* The reason was that still somehow some system was trying to insert Duplicate Values in table "SYS.SYSCOMMITTAB".

* We checked with Microsoft and came to know that we have to first delete DUPLICATE rows from this "SYS.SYSCOMMITTAB"
table.

* We put Database into SINGLE user mode.

* We started DAC Connection (As we have to modify SYSTEM Table).

* After connecting to DAC we ran below query to identify DUPLICATE Rows and then DELETE it.
In this below script we first created a new database and then moved all rows into this newly
created database and then deleted duplicate rows from SOURCE database.


-- Script to extract contents of SYSCOMMITTABLE
-- SYSCOMMITTABLE is the in-memory buffer that stores syscommittab rows before they are
-- flushed to sys.syscommittab (on disk)
--
-- Script must be run with server in single user mode and from a DAC connection:
-- sqlcmd -S -A -i c:\debuggingtools\ChangeTracking\Extract_SYSCOMMITTABLE_contents.sql

if exists(select 1 from sys.databases where name = 'dbChangeTrackingMetadata')
begin
drop database dbChangeTrackingMetadata
end
go

create database dbChangeTrackingMetadata
go

use dbChangeTrackingMetadata
go

-- Will store contents of SYSCOMMITTABLE
--

create table dbo.t_SYSCOMMITTABLE (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
)
go

-- Will store a backup of DUP rows to be removed from sys.syscommittab
--

create table dbo.t_syscommittab (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
,dbfragid int
)
go

-- Enables the below OpenRowset query against SYSCOMMITTABLE to work
--

exec sys.sp_setbuildresource 1
go


-- Here Database Name should be the the one in which we have enabled CHANGE TRACKING and
we are facing issue.

USE
go

declare @rowcount bigint
SET @rowcount = 0

-- Copy contents of SYSCOMMITTABLE
--

insert into dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE
SELECT commit_ts, xdes_id, commit_lbn, commit_csn, commit_time
FROM OpenRowset (table SYSCOMMITTABLE, db_id (), 0, 0)

-- Backup the rows to be deleted
--

insert into dbChangeTrackingMetadata.dbo.t_syscommittab
select ondisk_ct.* from sys.syscommittab as ondisk_ct
join dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE as inmem_ct
on ondisk_ct.xdes_id = inmem_ct.xdes_id

delete from sys.syscommittab
where xdes_id in ( select xdes_id from dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE )

set @rowcount = @@rowcount

if (@rowcount > 0)
begin
print ''
print 'DELETED '+CAST(@rowcount as NVARCHAR(10))+' rows from sys.syscommittab that were also stored in SYSCOMMITTABLE'
print ''
end
else
begin
print ''
print 'Failed to DELETE DUP rows from sys.syscommittab'
print ''
end

exec sys.sp_setbuildresource 0
go

* Once we deleted duplicate rows with above script after that we ran CHECKPOINT on this user database
and it completes succesfully.

* We started SQL Server in NORMAL Mode (Without putting in SINGLE user mode).

* We didn't got any further errors with regard to CHECKPOINT or Cannot Insert Duplicate Row in SYS.SYSCOMMITTAB table.

* We succesfully took bcakup of that user database.

Conclustion
************

* If we landed into such problem where we encounter above mentioned error messages while taking backup
then we have to first delete duplicate rows from this SYSTEM table and then apply CU7 of SP1 of SQL Server 2008.

* Before doing this deletion make sure that APPLICATION Team process the CHANGE RECORDS as there are chances
that while doing DELETION it can remove data which is not yet processed by the APPLICATION.

Regards
Gursethi

Capacity Management Script

Hi All,
As most of our fellow DBA's suffers lesser disk space issues and when they ask there Business Unites to add more space they ask to provide a previous utilisation pattern. In order to do this here by I am posting a script which will run on a specific server and capture database space information and later we can check the summary.

This script has 2 main procedures.
1) DBGrowthTableProcedure
2) DBGROWTHANALYSIS

DBGrowthTableProcedure -- This procedure creates table and a job which is scheduled to run every day (we can schedule it as per our need) and stored database space related information in a table.

DBGROWTHANALYSIS -- We need to pass Database Name, From Date & To Date in order to run this procedure. Once executed this procedure will show us space utilisation for said database between said dates.

/* Step 1: creating the table to capture the Event information */
USE Master
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[MASTER].[DBO].[DBGrowthTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE [dbo].[DBGrowthTable]

GO

Create Table MASTER.DBO.DBGrowthTable(TableEntryID int identity(1,1) primary key, EntryDate smalldatetime not null default getdate(), Servername Varchar(100), DBNAME Varchar(100), DBsizeMB bigint, unallocMB bigint, reservedMB bigint, dataMB bigint, indexMB bigint, unusedDB bigint)

GO

CREATE INDEX [DBGrowthTable_IDX01] ON [dbo].[DBGrowthTable]([EntryDate]) WITH FILLFACTOR = 100

GO

/*Step 2 : Creating the Procedure that will capture Database Space Utilisation*/

USE [Master]

GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id=OBJECT_ID(N'[MASTER].[DBO].[DBGrowthTableProcedure]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE DBO.DBGrowthTableProcedure

GO

Create Procedure DBO.DBGrowthTableProcedure as

DECLARE DBCURSOR CURSOR FOR SELECT NAME FROM SYS.DATABASESOPEN DBCURSOR;
DECLARE @dbnames varchar(100), @sql nvarchar(4000), @sql1 nvarchar(4000), @sql2 nvarchar(4000), @sql3 nvarchar(4000);
FETCH NEXT FROM DBCURSOR into @dbnamesWHILE (@@FETCH_STATUS<>-1)BEGINset @sql='USE '+@dbnames+';
DECLARE @dbsize bigint, @dbname varchar(100), @logsize bigint, @reservedpages bigint, @usedpages bigint, @pages bigint; set @dbname=DB_NAME();
select @dbsize = sum(convert(bigint, case when status & 64 = 0 then size else 0 end)), @logsize = sum(convert(bigint, case when status & 64 <> 0 then size else 0 end)) from dbo.sysfiles; select @reservedpages = sum(a.total_pages), @usedpages =sum(a.used_pages), @pages = sum( case when it.internal_type IN (202, 204) then 0 when a.type <> 1 then a.used_pages when p.index_id < 2 then a.data_pages else 0 end ) from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id;
Insert into MASTER.DBO.DBGrowthTable( ServerName, DBNAME, DBsizeMB, unallocMB, reservedMB, dataMB, indexMB, unusedDB)select @@servername,@dbname, cast(((@dbsize + @logsize) * 8192/1048576.) as decimal(15, 2)) "DB Size(MB)", (case when @dbsize >= @reservedpages then cast(((@dbsize - @reservedpages) * 8192/1048567.) as decimal(15, 2)) else 0 end) "Unalloc. Space(MB)", cast((@reservedpages * 8192/1048576.) as decimal(15, 2)) "Reserved(MB)", cast((@pages * 8192/1048576.) as decimal(15, 2)) "Data Used(MB)", cast(((@usedpages - @pages) * 8192/1048576.) as decimal(15, 2)) "Index Used(MB)", cast(((@reservedpages - @usedpages) * 8192/1048576.) as decimal(15, 2)) "Unused(MB)"'
Exec (@sql)
FETCH NEXT FROM DBCURSOR INTO @dbnames;
END;
CLOSE DBCURSOR;
DEALLOCATE DBCURSOR;
Go

/*Step 3: Create SQL Agent Job Which Will run every day and collect SQL Databases Information */

--DECLARE @ServiceAccount varchar(128)
--SET @ServiceAccount = N''
--DECLARE @SQLOperator varchar(128)
--SET @SQLOperator = N''
BEGIN TRANSACTIONDECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END
DECLARE @jobId BINARY(16)EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Capture Database Space Utilization Daily', @enabled=1, @notify_level_eventlog=2, @notify_level_email=3, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Job for capturing Database Space utilization daily',@category_name=N'[Uncategorized (Local)]', --@owner_login_name=@ServiceAccount, --@notify_email_operator_name=@SQLOperator, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/*Step 4: Adding Steps To The Job */

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute Procedure to capture Database Growth',@step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC DBGrowthTableProcedure', @database_name=N'master', @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollbackEXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO
EndSave
QuitWithRollback:IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTIONEndSave:GO

/*Step 5: Create procedure to print space information for a said database */

CREATE PROCEDURE DBGROWTHANALYSIS (@DATABASENAME VARCHAR(100),@MONTH_FROM SMALLDATETIME,@MONTH_TO SMALLDATETIME) AS
--DECLARE @DATABASENAME VARCHAR(100)
DECLARE @DBID INT
--DECLARE @MONTH_FROM SMALLDATETIME--DECLARE @MONTH_TO SMALLDATETIME
DECLARE @MAX_MONTH SMALLDATETIME
DECLARE @MIN_MONTH SMALLDATETIME
DECLARE @GROWTH INT
DECLARE @RowCtr INT
DECLARE @NoOfRows INT
DECLARE @GROWTH1 INT
--SET @DATABASENAME='SQLNEXUS'
--SET @MONTH_FROM='03-JAN-2009'
--SET @MONTH_TO='06-May-2010'
SET @DBID = DB_ID(@DATABASENAME)
IF @DBID IS NULL
BEGIN PRINT 'Database ' +@DATABASENAME +' Does not exists in current server'
END
ELSE
IF @MONTH_FROM IS NULL
BEGIN
SET @MONTH_FROM=(SELECT MIN(ENTRYDATE) FROM DBGROWTHTABLE WHERE DBNAME=@DATABASENAME);
END
ELSE
SELECT @MONTH_FROM
IF @MONTH_TO IS NULL
BEGIN SET @MONTH_TO=(SELECT MAX(ENTRYDATE) FROM DBGROWTHTABLE WHERE DBNAME=@DATABASENAME);
END
ELSE
SELECT @MONTH_TOIF @MONTH_FROM >@MONTH_TO
BEGIN
PRINT 'Start Date '+CAST(@MONTH_FROM as varchar(50))+' Cannot be more then End Month '+CAST(@MONTH_TO as varchar(50))
END
ELSE
SELECT @MIN_MONTH=MIN(EntryDate), @MAX_MONTH=MAX(EntryDate2) FROM DBO.DBGrowthTableWHERE DBNAME=@DATABASENAME
IF @MIN_MONTH>@MONTH_TO BEGIN PRINT 'Provided Range For Backup is invalid. Actual Backup For '+@DATABASENAME +' is between '+CAST(@MIN_MONTH as varchar(50)) +' '+CAST(@MAX_MONTH as varchar(50))
END
IF @MAX_MONTH<@MONTH_TOBEGIN SET @MONTH_TO=@MAX_MONTHEND
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DBO].[#DBROWS]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE DBO.#DBROWS ( RowID Int Identity, DBGrowthSize Int, DBUNALLOCSIZE Int, DBName Varchar(200), MonthYear Varchar(100), DBGrowth Int, DBUAGrowth Int )
Insert Into DBO.#DbRows (DBGrowthSize, DBUNALLOCSIZE,DBName, MonthYear) SELECT SUM(DBSIZEMB), SUM(UNALLOCMB), DBNAME, cast(DATEPART(mm,ENTRYDATE) as varchar(4))+'-'+cast(DATEPART(yy,ENTRYDATE)as varchar(4)) FROM DBO.DBGrowthTable WHERE DBNAME=@DATABASENAME AND ENTRYDATE >= @MONTH_FROM AND ENTRYDATE <=@MONTH_TO GROUP BY DBNAME,cast(DATEPART(mm,ENTRYDATE)as varchar(4))+'-'+cast(DATEPART(yy,ENTRYDATE)as varchar(4)) ORDER BY cast(DATEPART(mm,ENTRYDATE) as varchar(4))+'-'+cast(DATEPART(yy,ENTRYDATE)as varchar(4))

Select @NoOfRows = count(*) from DBO.#DBROWS
SET @RowCtr = 1
SET @GROWTH1 = 0
SET @GROWTH=0
WHILE @RowCtr <= @NoOfRows
BEGIN
UPDATE DBO.#DBROWS SET DBGrowth = CASE WHEN @RowCtr = 1 THEN 0 ELSE DBGrowthSize - @GROWTH1 END WHERE RowID = @RowCtr

UPDATE DBO.#DBROWS SET DBUAGrowth = CASE WHEN @RowCtr = 1 THEN 0 ELSE DBUNALLOCSize - @GROWTH END WHERE RowID = @RowCtr

SELECT @GROWTH1 = DBGrowthSize FROM DBO.#DBROWS WHERE RowID = @RowCtr
SELECT @GROWTH = DBUNALLOCSize FROM DBO.#DBROWS WHERE RowID = @RowCtr
SET @RowCtr = @RowCtr + 1 END
SELECT ROWID,DBGrowthSize as "Database Size (in MB)",DBNAME as "Database Name", MonthYear as "Month-Year", DBGrowth as "Database Growth In MB",DBUAGROWTH as "Database Unallocated Space In MB"
FROM DBO.#DBROWS
DROP TABLE DBO.#DBROWS

Regards
Gursethi

Capture SQL Inventory Details

Hi Friends,
Find enclosed here with a script which will capture most of the SQL Server Inventory Information. This script can work on SQL 2000, 2005 & 2008.

/*Script : Inventory_baseline.sqlAuthor : Gurpreet Singh Sethi & Abhay Chaudhary, 15 OCT, 2009Purpose : Collecting SQL Server 2000/2005/2008 baseline inventory: Requirements: SQL Server 2008 Express Edition (Please go through Inventory_baseline.txt)Suggestions : gur.sethi@in.ibm.comVersion: 1.0*/

--Step 1: Setting NULLs and quoted identifiers to ON and checking the version of SQL Server

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'prodver') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table prodvercreate table prodver ([index] int, Name nvarchar(50),Internal_value int, Charcater_Value nvarchar(50))insert into prodver exec xp_msver 'ProductVersion'
if (select substring(Charcater_Value,1,1)from prodver)!=8 begin

-- Step 2: This code will be used if the instance is Not SQL Server 2000
Declare @image_path nvarchar(100)
Declare @startup_type int
Declare @startuptype nvarchar(100)
Declare @start_username nvarchar(100)
Declare @instance_name nvarchar(100)
Declare @system_instance_name nvarchar(100)
Declare @log_directory nvarchar(100)
Declare @key nvarchar(1000)
Declare @registry_key nvarchar(100)
Declare @registry_key1 nvarchar(300)
Declare @registry_key2 nvarchar(300)
Declare @IpAddress nvarchar(20)
Declare @domain nvarchar(50)
Declare @cluster int
Declare @instance_name1 nvarchar(100)
Declare @ClusterName nvarchar(100)
Declare @ClusterResourceName nvarchar(100)
Declare @Registry_key4 nvarchar(100)
Declare @Registry_key5 nvarchar(100)
Declare @DependsOn nvarchar(100)
Declare @Address nvarchar(100)
Declare @OSProductName nvarchar(100)
Declare @OSVersion nvarchar(100)
Declare @Registry_key6 nvarchar(100)

-- Step 3: Reading registry keys for IP,Binaries,Startup type ,startup username, errorlogs location and domain.

SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');

If @instance_name!='MSSQLSERVER'

Set @instance_name=@instance_name

Set @instance_name1= coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');


If @instance_name1!='MSSQLSERVER'

Set @instance_name1='MSSQL$'+@instance_name1

EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;

Set @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name1;

SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';

If @registry_key is NULL

set @instance_name=coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');

EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instance_name, @system_instance_name output;

SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';

SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\supersocketnetlib\TCP\IP1';

SET @registry_key2 = N'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',N'Cluster',@value_name='ClusterName',@value=@ClusterName OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',N'Cluster',@value_name='ClusterNameResource',@value=@ClusterResourceName OUTPUT
SET @Registry_key4=N'Cluster\Resources\'+@ClusterResourceName; Print @ClusterResourceName

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key4,@value_name='DependsOn',@value=@DependsOn OUTPUT

SET @Registry_key5=N'Cluster\Resources\'+@DependsOn+'\Parameters';

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key5,@value_name='Address',@value=@Address OUTPUT SET @Registry_key6=N'Software\Microsoft\Windows NT\CurrentVersion';

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key6,@value_name='ProductName',@value=@OSProductName OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key6,@value_name='CSDVersion',@value=@OSVersion OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ImagePath',@value=@image_path OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT

Set @startuptype= (select 'Start Up Mode' = CASE WHEN @startup_type=2 then 'AUTOMATIC' WHEN @startup_type=3 then WHEN @startup_type=4 then 'Disabled' END)

--Step 4: Getting the cluster node names if the server is on cluster .else this value will be NULL.

Declare @ClusterNodeName nvarchar(400)
SELECT @ClusterNodeName = COALESCE(@ClusterNodeName+'' ,'') + Nodename from
sys.dm_os_cluster_nodes

--Step 5: Getting Drives names if the server is on cluster else this value will be NULL.
Declare @ClusterDriveName nvarchar(400)
SELECT @ClusterDriveName = COALESCE(@ClusterDriveName+'' ,'') + DriveName from sys.dm_io_cluster_shared_drives

-- Step 6: Printing Server details

SELECT @domain as 'Domain', serverproperty('ComputerNamePhysicalNetBIOS') as 'MachineName', @OSProductName + ' '+ @OSVersion 'OS Edn. Ver', CPU_COUNT as 'CPUCount', (physical_memory_in_bytes/1048576) as 'PhysicalMemoryMB', @Ipaddress as 'IP_Address', @instance_name1 as 'SQL InstanceName', @image_path as 'BinariesPath', @log_directory as 'ErrorLogsLocation', @start_username as 'StartupUser', @Startuptype as 'StartupType', (select CASE WHEN SUBSTRING(Charcater_Value,1,1)=9 THEN 'SQL SERVER 2005'+' '+cast(serverproperty('edition') as varchar(50))+' '+cast(serverproperty('Productlevel') as varchar(10)) WHEN SUBSTRING(Charcater_Value,1,2)=10 THEN 'SQL SERVER 2008'+' '+cast(serverproperty('edition') as varchar(50))+' '+cast(serverproperty('Productlevel') as varchar(10)) END FROM PRODVER ) as 'SQL Edn. Ver.', serverproperty('productversion') as 'SQL Version', serverproperty('collation') as 'Collation', serverproperty('Isclustered') as 'ISClustered', @ClusterName as 'Cluster Name', @Address as 'Cluster Address', @ClusterNodeName as 'ClusterNodes', @ClusterDriveName as 'Cluster Drive Names', serverproperty('IsFullTextInstalled') as 'ISFullText' From sys.dm_os_sys_info

-- Step 7: Printing database details

SELECT serverproperty ('ComputerNamePhysicalNetBIOS') as 'Machine' ,@instance_name1 as InstanceName, (SELECT 'file_type' = CASE WHEN s.groupid <> 0 THEN 'data' WHEN s.groupid = 0 THEN 'log' END) AS 'fileType' , d.dbid as 'DBID' , d.name AS 'DBName' , s.name AS 'LogicalFileName' , s.filename AS 'PhysicalFileName' , (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB , d.cmptlevel as 'CompatibilityLevel' , DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel' , DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' , --, d.is_published as 'Publisher' --, d.is_subscribed as 'Subscriber' --, d.is_distributor as 'Distributor' (SELECT 'is_replication' = CASE WHEN d.category = 1 THEN 'Published' WHEN d.category = 2 THEN 'subscribed' WHEN d.category = 4 THEN 'Merge published' WHEN d.category = 8 THEN 'merge subscribed' Else 'NO replication' END) AS 'Is_replication' , m.mirroring_state as 'MirroringState' --INTO master.[dbo].[databasedetails] FROM sys.sysdatabases d INNER JOIN sys.sysaltfiles s ON d.dbid=s.dbid INNER JOIN sys.database_mirroring m ON d.dbid=m.database_id ORDER BY d.name

--Step 8 :printing Backup details

Select Distinct convert(varchar(25),@@Servername) as Servername, convert(varchar(30),e.database_name) as DBname, convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery'))), (Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset a Where a.database_name=e.database_name and a.server_name = @@servername and type='D' Group by a.database_name) Database_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset b Where b.database_name=e.database_name and b.server_name = @@servername and type='L' Group by b.database_name) Log_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset c Where c.database_name=e.database_name and c.server_name = @@servername and type='I' Group by c.database_name) Diff_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset d Where d.database_name=e.database_name and d.server_name = @@servername and type='F' Group by d.database_name) File_Bk
From msdb.dbo.backupset e Where e.database_name Not in ('tempdb','pubs','northwind','model') and e.server_name = @@Servername and e.database_name in (Select Distinct name from master..sysdatabases)
-- DATABASES NEVER BACKED UP
Union all select Distinct convert(varchar(25),@@Servername) as Servername, convert(varchar(30),name) as DBname, convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),NULL, NULL , NULL, NULL from master..sysdatabases as record where name not in (select distinct database_name from msdb.dbo.backupset) and name not in ('tempdb','pubs','northwind','model') order by 1,2
END
ELSE
BEGIN

--Step 9: If the instance is 2000 this code will be used.
-- declare @registry_key4 nvarchar(100)
declare @Host_Name varchar(100)
declare @CPU varchar(3)
declare @nodes nvarchar(400) set @nodes =null /* We are not able to trap the node names for SQL Server 2000 so far*/
declare @mirroring varchar(15) set @mirroring ='NOT APPLICABLE' /*Mirroring does not exist in SQL Server 2000*/
Declare @reg_node1 varchar(100)
Declare @reg_node2 varchar(100)
Declare @reg_node3 varchar(100)
Declare @reg_node4 varchar(100)
SET @reg_node1 = N'Cluster\Nodes\1'
SET @reg_node2 = N'Cluster\Nodes\2'
SET @reg_node3 = N'Cluster\Nodes\3'
SET @reg_node4 = N'Cluster\Nodes\4'
Declare @image_path1 varchar(100)
Declare @image_path2 varchar(100)
Declare @image_path3 varchar(100)
Declare @image_path4 varchar(100)
Declare @OSProductName2 nvarchar(100)
Declare @OSVersion2 nvarchar(100)
Declare @Registry_key66 nvarchar(100)
Set @image_path1=null
Set @image_path2=null
Set @image_path3=null
Set @image_path4=null
Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node1, @value_name='NodeName',@value=@image_path1 OUTPUT

Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node2, @value_name='NodeName',@value=@image_path2 OUTPUT

Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node3, @value_name='NodeName',@value=@image_path3 OUTPUT

Exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@reg_node4, @value_name='NodeName',@value=@image_path4 OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',N'Cluster',@value_name='ClusterName',@value=@ClusterName OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',N'Cluster',@value_name='ClusterResourceName',@value=@ClusterResourceName OUTPUT SET @Registry_key4=N'Cluster\Resources\'+@ClusterResourceName;

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key4,@value_name='DependsOn',@value=@DependsOn OUTPUT SET @Registry_key5=N'Cluster\Resources\'+@DependsOn+'\Parameter';

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key5,@value_name='Address',@value=@Address OUTPUT

SET @Registry_key66=N'Software\Microsoft\Windows NT\CurrentVersion';

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key66,@value_name='ProductName',@value=@OSProductName2 OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@Registry_key66,@value_name='CSDVersion',@value=@OSVersion2 OUTPUT

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'nodes') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table nodes
Create table nodes (name varchar (20))
insert into nodes values (@image_path1)
insert into nodes values (@image_path2)
insert into nodes values (@image_path3)
insert into nodes values (@image_path4)

declare @Out nvarchar(400)
declare @value nvarchar (20)

SELECT @Out = COALESCE(@Out+'/' ,'') + name from nodes where name is not null

-- Step 10: Reading registry keys for Number of CPUs,Binaries,Startup type ,startup username, errorlogs location and domain.

SET @instance_name = coalesce(convert(nvarchar(100), serverproperty('InstanceName')),'MSSQLSERVER');

IF @instance_name!='MSSQLSERVER'
BEGIN set @system_instance_name=@instance_name set @instance_name='MSSQL$'+@instance_name
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\MSSQLServer\Parameters';
SET @registry_key1 = N'Software\Microsoft\Microsoft SQL Server\' + @system_instance_name + '\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT
END
IF @instance_name='MSSQLSERVER' BEGIN
SET @key=N'SYSTEM\CurrentControlSet\Services\' +@instance_name;
SET @registry_key = N'Software\Microsoft\MSSQLSERVER\MSSQLServer\Parameters'; SET @registry_key1 = N'Software\Microsoft\MSSQLSERVER\Setup';
SET @registry_key2 = N'SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\';
SET @registry_key4 = N'SYSTEM\CurrentControlSet\Control\Session Manager\Environment'

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='SQLPath',@value=@image_path OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='Start',@value=@startup_type OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@key,@value_name='ObjectName',@value=@start_username OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key,@value_name='SQLArg1',@value=@log_directory OUTPUT

--EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key1,@value_name='IpAddress',@value=@IpAddress OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key2,@value_name='Domain',@value=@domain OUTPUT

EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',@registry_key4,@value_name='NUMBER_OF_PROCESSORS',@value=@CPU OUTPUT
END

set @startuptype= (select 'Start Up Mode' = CASE WHEN @startup_type=2 then 'AUTOMATIC' WHEN @startup_type=3 then 'MANUAL' WHEN @startup_type=4 then 'Disabled' END)

--Step 11 : Using ipconfig and xp_msver to get physical memory and IP

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'tmp') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

DROP TABLE tmp
create table tmp (server varchar(100)default cast( serverproperty ('Machinename') as varchar),[index] int, name sysname,internal_value int,character_value varchar(30))

insert into tmp([index],name,internal_value,character_value)

exec xp_msver PhysicalMemory

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'ipadd') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table ipadd
create table ipadd (server varchar(100)default cast( serverproperty ('Machinename') as varchar),IP varchar (100))
insert into ipadd (IP)
exec xp_cmdshell 'ipconfig' delete from ipadd where ip not like '%IP Address.%' or IP is null

-- Step 12 : Getting the Server details

SELECT top 1 @domain as 'Domain', serverproperty('Machinename') as 'MachineName', @OSProductName2 + ' '+ @OSVersion2 'OS Edn. Ver', @CPU as 'CPUCount', cast (t.internal_value as bigint) as PhysicalMemoryMB, cast(substring ( I.IP , 40,45) as nvarchar(20))as IP_Address, serverproperty('Instancename') as 'InstanceName', @image_path as 'BinariesPath', @log_directory as 'ErrorLogsLocation', @start_username as 'StartupUser', @Startuptype as 'StartupType', (select CASE WHEN SUBSTRING(Charcater_Value,1,1)=8 THEN 'SQL SERVER 2000'+' '+cast(serverproperty('edition') as varchar(50))+' '+cast(serverproperty('Productlevel') as varchar(10)) END FROM PRODVER ) as 'SQL Edn. Ver.', serverproperty('productversion') as 'Version', serverproperty('collation') as 'Collation', @ClusterName as 'Cluster Name', @Address as 'Cluster Address', serverproperty('Isclustered') as 'ISClustered', @Out as 'ClustreNodes', serverproperty('IsFullTextInstalled') as 'ISFullText' From tmp t inner join IPAdd I on t.server = I.server

-- Step 13 : Getting the instance details

SELECT serverproperty ('Machinename') as 'Machine', serverproperty ('Instancename') as 'InstanceName', (SELECT 'file_type' = CASE WHEN s.groupid <> 0 THEN 'data' WHEN s.groupid = 0 THEN 'log' END) AS 'fileType' , d.dbid as 'DBID' , d.name AS 'DBName' , s.name AS 'LogicalFileName' , s.filename AS 'PhysicalFileName' , (s.size * 8 / 1024) AS 'FileSizeMB' -- file size in MB ,d.cmptlevel as 'CompatibilityLevel' , DATABASEPROPERTYEX (d.name,'Recovery') as 'RecoveryModel' , DATABASEPROPERTYEX (d.name,'Status') as 'DatabaseStatus' , (SELECT 'is_replication' = CASE WHEN d.category = 1 THEN 'Published' WHEN d.category = 2 THEN 'subscribed' WHEN d.category = 4 THEN 'Merge published' WHEN d.category = 8 THEN 'merge subscribed' Else 'NO replication' END) AS 'Is_replication', @Mirroring as 'MirroringState' FROM sysdatabases d INNER JOIN sysaltfiles s ON d.dbid=s.dbid ORDER BY d.name

-- Step 14 : Getting backup details

Select Distinct convert(varchar(25),@@Servername) as Servername, convert(varchar(30),e.database_name) as DBname, convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery'))), (Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset a Where a.database_name=e.database_name and a.server_name = @@servername and type='D' Group by a.database_name) Database_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset b Where b.database_name=e.database_name and b.server_name = @@servername and type='L' Group by b.database_name) Log_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset c Where c.database_name=e.database_name and c.server_name = @@servername and type='I' Group by c.database_name) Diff_Bk,
(Select convert(varchar(25),Max(backup_finish_date) , 100) From msdb.dbo.backupset d Where d.database_name=e.database_name and d.server_name = @@servername and type='F' Group by d.database_name) File_Bk
From msdb.dbo.backupset e Where e.database_name Not in ('tempdb','pubs','northwind','model') and e.server_name = @@Servername and e.database_name in (Select Distinct name from master..sysdatabases)

-- NEVER BACKED UP

Union allselect Distinct convert(varchar(25),@@Servername) as Servername, convert(varchar(30),name) as DBname, convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),NULL, NULL , NULL, NULLfrom master..sysdatabases as recordwhere name not in (select distinct database_name from msdb.dbo.backupset)and name not in ('tempdb','pubs','northwind','model')order by 1,2


-- Step 15: Dropping the table we created for IP and Physical memory

Drop Table TMP
Drop Table IPADD
Drop Table Nodes
Drop Table Prodver
end
go

-- Step 16 : Setting Nulls and Quoted identifier back to Off

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO