RESTORE DATABASE WITH STOPAT

Hi All,

Today I came across a question on MSDN:

We are using SQL Server 2005. We have a database say "TESTSTOPAT", this database is in Full Recovery Model. Unfortunately we don't have any backup for this database. Today some one deleted records from one of the table.
Now we want to recover from those changes. What we did is took a Full backup of database and then tried to restore it with a new name with STOPAT option but it fails with below errors:

Msg 4338, Level 16, State 1, Line 1
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

To be frank till the time I also didn't look to this option i.e. STOPAT clause with Full Database, as previously this option was coming for LOG files only (in SQL 2000). I did some research and based on that below are my findings.


**************************************************
RESTORE DATABASE WITH STOPAT
**************************************************

Previously in SQL 2000 STOPAT clause was provided for RESTORE LOG wherein you can "halt" the recovery proces at particular point in time. This feature is very useful for recovering from accidental user errors and such.

Now in SQL Server 2005, there is a STOPAT clause provided in the RESTORE DATABASE command also. RESTORE DATABASE WITH STOPAT wow great thing lets test it.

STEP :1
************

--Created a new database.

CREATE DATABASE TESTSTOPAT
GO


--Created a table in this newly created database and inserted values. Time when this query was executed was 14-Oct-2010 20:1043.827.

USE TESTSTOPAT
GO
CREATE TABLE TESTSTOPAT (I INT, II INT)
GO
INSERT INTO TESTSTOPAT VALUES (1,1)
INSERT INTO TESTSTOPAT VALUES (2,2)
INSERT INTO TESTSTOPAT VALUES (3,3)
INSERT INTO TESTSTOPAT VALUES (4,4)
INSERT INTO TESTSTOPAT VALUES (5,5)
INSERT INTO TESTSTOPAT VALUES (6,6)
INSERT INTO TESTSTOPAT VALUES (7,7)
INSERT INTO TESTSTOPAT VALUES (8,8)
INSERT INTO TESTSTOPAT VALUES (9,9)
INSERT INTO TESTSTOPAT VALUES (10,10)
INSERT INTO TESTSTOPAT VALUES (11,11)
GO
SELECT GETDATE()
GO

Time : 2010-10-14 20:10:43.827


STEP :2
***********

--Then we execute a DELETE statement. Time when this query was executed was 14-Oct-2010 20:11:40.437.

DELETE FROM TESTSTOPAT WHERE II>9
GO
SELECT GETDATE()
GO

Time : 2010-10-14 20:11:40.437


STEP :3
***********

--Then we took a full database backup. Time when this backup occurs was 14-Oct-2010 20:13:14.640.

BACKUP DATABASE TESTSTOPAT TO DISK='C:\TESTSTOPATFULL.BAK'
GO
SELECT GETDATE()
GO

Time : 2010-10-14 20:13:14.640

STEP :4
***********

--Now lets try to restore this database, I want to restore it before execution of STEP 2 i.e. Delete statement as I want to recover from that DELETE operation.

RESTORE DATABASE [TESTSTOPAT2]
FROM DISK = N'C:\TESTSTOPATFULL.BAK'
WITH FILE = 1, MOVE N'TESTSTOPAT' TO N'C:\TESTSTOPAT2.mdf', MOVE N'TESTSTOPAT_LOG' TO N'C:\TESTSTOPAT2_LOG.LDF', NOUNLOAD, REPLACE, STATS = 10,STOPAT='OCT 14, 2010 20:11:00 PM'
GO

Output

Msg 4338, Level 16, State 1, Line 1
The STOPAT clause specifies a point too early to allow this backup set to be restored. Choose a different stop point or use RESTORE DATABASE WITH RECOVERY to recover at the current point.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


Looking at the output what we saw is that RESTORE is failing. But why, MS has provided option of RESTORING database with STOPAT then why this is failing.

Here comes the reason
*****************************

As per Books on Line

A full backup (formerly known as a database backup) backs up the entire database, including part of the transaction log (so that the full backup can be recovered). Full backups represent the database at the time the backup completed. The transaction log included in the full backup allows it to be used to recover the database to the point in time at which the backup was completed.

That means Full Database backup can only be restored to the time at which backup was completed not prior to that. So if this the case then


• Without log backups, there is simply no way to achieve a true point-in-time restore.

• What we do when we specify STOPAT in RESTORE DATABASE is to test if the full database backup is already ahead of the point in time you want to stop at. If it is, then the command errors out (like it does above).

• Why it errors out is to provide you a clear signal that even if you restore this full backup and then restore subsequent transaction log backups on top (specifying a STOPAT) then those transaction log
backups would fail with errors since the database is already rolled forward to a point in time ahead of the STOPAT

• In the above case, you would have wasted a lot of time and effort first restoring the database and then the transaction log backup before finding out that you should have started with a earlier full backup.

Happy Learning....................

Convert Hex String to IP Address SQL

Hi,

I came across this code theu MSDN where someone asked how to convert Hex String to IP Address. Its work sharing so here it is.

DECLARE @v1 AS char(8);
SET @v1 = '0A370D16' ;
DECLARE @b1 AS binary;
DECLARE @b2 AS binary;
DECLARE @b3 AS binary;
DECLARE @b4 AS binary;

SELECT @b1 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,1,2));
SELECT @b2 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,3,2));
SELECT @b3 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,5,2));
SELECT @b4 = sys.fn_cdc_hexstrtobin(SUBSTRING(@v1,7,2));
SELECT @b1, @b2, @b3, @b4;

DECLARE @d1 AS int;
DECLARE @d2 AS int;
DECLARE @d3 AS int;
DECLARE @d4 AS int;

SELECT @d1 = CAST (@b1 AS int);
SELECT @d2 = CAST (@b2 AS int);
SELECT @d3 = CAST (@b3 AS int);
SELECT @d4 = CAST (@b4 AS int);
SELECT @d1, @d2, @d3, @d4;
SELECT CAST (@d1 AS char(2)) + '.' + CAST (@d2 AS char(2)) +'.'+ CAST (@d3 AS char(2)) + '.'+ CAST (@d4 AS char(2)) AS IPAddress

Output Will Be
******************


---- ---- ---- ----
0x0A 0x37 0x0D 0x16

(1 row(s) affected)


----------- ----------- ----------- -----------
10 55 13 22

(1 row(s) affected)

IPAddress
-----------
10.55.13.22

(1 row(s) affected)

Agent Job To Script All Agent Job SQL 2008

Hi,

I came across a request on MSDN where one of our colleague was running SQL 2008. He want to create a SQL Agent Job which when executed Script Out All existing SQL Agent Jobs. As per him he don't want to use SMO or VB.

I provided POWERSHELL script to do so.

1) Copy below code in a Text Pad and save it as JOBS.PS1

param($sqlserver)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.JobServer.Jobs foreach {$_.Script()}
2) Create a New Job in SSMS.
3) In Steps add a step of Type as "Powershell".
4) In Command section provide location of file JOBS.PS1 and Server Name from where we want Jobs
Information.

C:\./JOBS.PS1 GURPSETH\SQL2K*
5) Go to Advances section for this Job Step and provide file in OUTPUT FILE section. idea is once this
command will run it should log the output in it.
6) Once done save this job.
7) Starts this job.

Multiline On A Single Record in SQL

Hi,

I came across a SQL post on MSDN where one of our fellow SQL Colleague asked how to Display Single Record in Multiline. Rest of the story is below:

Problem Description
*******************
is it possible to have multi-line in a single record in sql table?? for example

rowid employee address

1 addin adhika jakarta 123456

indonesia

the address column is multiline, is it possible to achieve that?

Resolution Code
*****************

use tempdb
go


Create table #EMP(id int, name varchar(50), address varchar(100))
go

insert into #EMP values(1,'addin adhika','jakarta 123456 indonesia')
go

DECLARE @CrLf CHAR(2);
SET @CrLf = CHAR(13) ;
select id,name,substring(address,1,15)+char(13)+substring(address,15,datalength(address)) from #EMP
drop table #EMP
go

Note: Make Sure We Run This Code In QA And Output Should Be Set To TEXT.

SUM of Hours and Minutes SQL 2005

Hi,

I came across a post where one of our fellow SQL coleague asked a question i.e. In SQL Server 2005 how to do SUM of Hours and Minutes. So here comes rest of story:

Problem Description
***********************
I want to get SUM of Hours and Minutes in SQL Server 2005 in To SELECT Query Like : SELECT SUM(OTHours) FROM EmpInOutRecords.

Example: I Have OverTime Hours Like: 01:25, 02:30, 05:56, 00:50

Now I Want To SUM of This Total Hours and Minutes Like Answer is : 10:41

Resolution Code
****************

DECLARE @Sample TABLE
( data CHAR(5)
)


INSERT @Sample SELECT '01:25' UNION ALL
SELECT '02:30' UNION ALL
SELECT '05:56' UNION ALL
SELECT '00:50'
SELECT
STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM (
SELECT ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 3600 * LEFT(data, CHARINDEX(':', data) - 1) END)) AS theHours,
ABS(SUM(CASE CHARINDEX(':', data) WHEN 0 THEN 0 ELSE 60 * SUBSTRING(data, CHARINDEX(':', data) + 1, 2) END)) AS theMinutes
FROM @Sample
) AS d

SQL 2005 Licencing

SQL Server is licensed in 2 modes - Server and CAL which is Per Seat or Per Proc, it is an anomaly in the world of Licensing, but, that said, gives greater flexibility, see more info on SQL licensing HERE

For SQL 2000 we had an applet in Control Panel which helped to identify which mode you had deployed in and the user was prompted during setup to enter the license type. The resultant data was stored in the registry and could be viewed using the Control Panel applet. We found that this data was not consistently used nor could it be relied upon to be accurate. As a result it was removed in SQL Server 2005. Now this can prove to be a bit of a problem for larger companies who need to keep a track of this for SAM and compliancy, so, as a work around we have the following solution

Tracking License Information in SQL 2005

SQL 2005 no longer tracks licensing (per seat or per processor) via registry entries. SQL 2005 still reads the registry for this information, but the SQL 2005 setup doesn’t put licensing information in the registry during setup as in SQL 2000.

This is by-design. Hence, when ServerProperty(‘LicenseType’) is run on a SQL 2005 installation, ‘DISABLED’ is always returned.


Supported Resolution

Since SQL 2005 still queries the registry for licensing information, add the following key and values and ServerProperty(‘LicenseType’) will return license information.

Note: Licensing has always been server wide and not SQL instance specific. This setting would apply to all instances of SQL Server on the server.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\MSSQLLicenseInfo\MSSQL9.00

If you want to configure SQL Server for Per Processor then add these Registry Values under that Key adjusting for the number of processors you have a license for:

Name                      Type                            Value
Mode                      REG_DWORD           LICENSE_MODE_PERPROC
ConcurrentLimit    REG_DWORD           Number of Processors

If you want to configure SQL Server for Per Seat licensing then add these Registry values under the Key adjusting for the number of seat license you have purchased.

Name                       Type                           Value
Mode                        REG_DWORD           LICENSE_MODE_PERSEAT
ConcurrentLimit       REG_DWORD           No. of client licenses
registered for SQL Server in
Per Seat mode.

Test in SQL Management Studio

You need to stop and restart SQL Server 2005 before the information will be available to ServerProperty() as the registry is read on start-up of SQL Server.

With the above settings you would see the following when you restart SQL Server 2005.


SELECT ServerProperty('LicenseType') as LicenseType, ServerProperty('NumLicenses') as ProcessorCount

Output:
LicenseType ProcessorCount

PER_PROCESSOR 4



We are aware and committed to the need to provide a much more efficient way for centrally tracking SQL Server licenses and will be looking at different options.

The new control features added to SQL Server 2008 R2 are one possible place where we could centralize license metadata management in a future release and this is being assessed.

In terms of understanding what model/edition of SQL Server you have installed the Microsoft Planning and Assessment Toolkit, which is a free download, does an excellent job of creating an inventory of the SQL Server instances on your network.


Hope this helps

Trigger to Get Information Who Updated a Table

Hi,

I came thru a post in MSDN where some one as how he can save details (like SPID, Name) for user who update a specific table.

Below is the code for that

--Create table for storing values

create table idtrack (id int,uname varchar(100),date datetime)

--Create Trigger on table (table1 in this example)

--Trigger will copy SPID, USER NAME & DATE when a update was fired on table1.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create TRIGGER dbo.testtrigger ON dbo.table1
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
insert into idtrack (id,uname,date)select @@SPID,user_name(),getdate()
END
GO


http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/50bc5c3c-797c-4a18-8b9a-4e52d2465b4f

DTA will fail to execute if loginname in trace didn't have SHOWPLAN permissions.

Hi,

I came across a question which is as below:

Problem Description
*********************
We have a user named TEST and it has DB_DATAREADER ; DB_DATAWRITER permissions on a database say it as TEST.

We have a query which take long time to execute. We capture Profiler Trace for
this query (executed by TEST user) with below mentioned events:

Stored Procedure : RPC Started
RPC Completed
SP:Started
SP:Completed
SP:StmtStarted
SP:StmtCompleted
TSQL : SQL:BatchStarted
SQL:BatchCompleted
SQL:StmtStarting
SQL:StmtCompleted

We use this Profiler trace and execute Database Tunning Advisor (DTA) but DTA
fails to execute.

Root Cause
***********

When we run DTA on a Trace file that has LOGINNAME column selected in this, if
that user didn't have SHOWPLAN permission on said database then it will fail to
run DTA tasks and as a result will fail with below type of errors.

TITLE: Database Engine Tuning Advisor
------------------------------
An unexpected error occurred. Click Continue to ignore this error and attempt to continue. Click Quit to shut down the application immediately.

For help, click: http://go.microsoft.com/fwlink?
ProdName=Microsoft+SQL+Server+Database+Engine+Tuning+Advisor&ProdVer=9.00.4035.00
;EvtSrc=Microsoft.SqlServer.ExpressManager.SRMainError&EvtID=UnexpectedError&LinkId
=20476
------------------------------
ADDITIONAL INFORMATION:

Cannot select non-existing cell (0, 0, 0, 0).
Parameter name: SelectionBlocks (Microsoft.SqlServer.GridControl)
------------------------------
BUTTONS: &Continue &Quit
------------------------------

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

Either we should provide SHOWPLAN permission to the user who ran that query and
which is in the trace file.

Don't select LOGINNAME column while capturing profiler trace in this case DTA
In this case, the dta impersonate the loginname as sysadmin or dbowner, and tune
all the events captured.

Maintenance Plan Will Not Execute Thru Command Line

Hi,

I came across a problem where one of newly created Maintenance Plan was not working thru command line. When I did some I research i found that there a propery called as DISABLE for each plan which is by default set to TRUE and it should be set to FALSE in order to get this Maintenance Plan work from command line.

Steps:
1) Open Maintenance Plan in SSMS.
2) Click on VIEW menu then Property Windows.
3) Property Window will appears on the right hand side of the screen.
4) Under section EXECUTIONs we have 2nd option named as DISABLE which is by
default set to TRUE, Change it to FALSE.

Please check below screebshot

SELECT LAST 2 RECORDS IN A TABLE

Hi Friends,

Recently I got a question on MSDN post to find last 2 records in a table. Below is the query which i figure out in order to do that.

create table hh (Id Int, Name varchar(20), salary int)
go
insert into hh values (5,'ankit',233)
insert into hh values (1,'amit',777)
insert into hh values (6,'anuj',666)
go
select identity(int,1,1) as SlNo,* into #temp from hh
select * from (select top 2 * from #temp order by slno
desc) a order by slno
drop table #temp
go

Below is that post : http://social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/4d57e34f-85c6-4105-9a17-6b60dc1b251a

Happy Learning......

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"

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.

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