full uninstall of MSSQL 2005 Ent SP1

Hi,

I am facing a serious problem with the unproper uninstall. I have installed MSSQL 2005 Enterprise and created an Active Directory at the server. I have removed the AD and wanted to uninstall SQL 2005.

I was not able to uninstall it fully. I have reinstalled it several times, just to tryying to remove the registry entries.

Is there a solution / tool or a document describing, how I can fully manually remove MS SQL 2005 from a server?

I have already installed SP1.

Dejan

What isn't being uninstalled? Is the uninstall completing, but leaving behind files/registry keys? Or does the uninstall fail somewhere along the way?

Thanks,
Sam Lester (MSFT)

|||

Hi Dejan,

here is an article for removal of SQL Server 2005 which would be helpful to you but in order to get a good answer please re-phrase your question is that only registry which won't removes or some files are their ?

http://msdn2.microsoft.com/en-us/library/aa337087.aspx

Regards

|||

Hi Sam,

I am sorry for my late reply. The uninstall is successful, but the install already reports some errors. It leaves files and registry entries behind.

\Program Files (x86)\Microsoft SQL Server\90\COM

REPLAGNT.dll

REPLDP.dll

snapshot.exe

sqlwep.dll

sqlresld90.dll

\Program Files (x86)\Microsoft SQL Server\90\EULA

\Program Files (x86)\Microsoft SQL Server\90\Shared

D:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn

Here is a portion of the MSSQL 2005 registry entries left behind in the registry after uninstall. Although I delete it manually from the registry (via regedit), it does not help me when I try reinstalling MS SQL 2005 Ent. (64-bit).

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
Class Name: <NO CLASS>
Last Write Time: 12/3/2006 - 12:13 PM

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: CurrentVersion
Type: REG_SZ
Data: 9.00.2047.00

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: ProtocolList
Type: REG_MULTI_SZ
Data: np

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Np
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: PipeName
Type: REG_SZ
Data: \\.\pipe\sql\query

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: TcpPort
Type: REG_SZ
Data: 1433

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: SQLPath
Type: REG_SZ
Data: D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

|||

Hi,

after uninstall, I noticed on many places that registry entries have not been removed (several hundred places). I do not have a complete list (too many entries).

After uninstall, I try to install MSSQL 2005 Ent. (64-bit) once again. System Configuration Check goes fine (all are with status = Success). See the attached report at the end (att 1).

At the selection of components, I check all options (I can not select SQL Server failover cluster).

I select "Default instance". For service account, I choose "Use built-in System account" and select "Local system". By default, the following are selected to start and the end of the setup (SQL server, Analysis Service, Reporting Service). SQL Server agent is unchecked. Also, "Customize for each service account is unchecked".

For the Authentication Model, I select Mixed model (I have entered a password).

SQL Collation is set to "Slovenian dictionary order, case-sensitive, for use with 1250 (Central European).

Report Server is set to default configuration.

Overview of the installing components (see att 2). I click "Install" to start the installation.

I get an error message : An instance with the same name already installed on this computer. To proceed with SQL Server Setup, provide a unique instance name. (Error at SQL Server Database Service). See att 3 for more details from the log file.

I get also an error message with the Reporting services : "There was un unexpected failure during the setup wizard. You may review the setup logs ... Event ID: 50000. I have my content of the 2 installation CDs copied to a hard disk.

__

Att 1:

System Configuration Check

- WMI Service Requirement (Success)

Messages

WMI Service Requirement

Check Passed

- MSXML Requirement (Success)

Messages

MSXML Requirement

Check Passed

- Operating System Minimum Level Requirement (Success)

Messages

Operating System Minimum Level Requirement

Check Passed

- Operating System Service Pack Level Requirement. (Success)

Messages

Operating System Service Pack Level Requirement.

Check Passed

- SQL Server Edition Operating System Compatibility (Success)

Messages

SQL Server Edition Operating System Compatibility

Check Passed

- Minimum Hardware Requirement (Success)

Messages

Minimum Hardware Requirement

Check Passed

- IIS Feature Requirement (Success)

Messages

IIS Feature Requirement

Check Passed

- Pending Reboot Requirement (Success)

Messages

Pending Reboot Requirement

Check Passed

- Performance Monitor Counter Requirement (Success)

Messages

Performance Monitor Counter Requirement

Check Passed

- Default Installation Path Permission Requirement (Success)

Messages

Default Installation Path Permission Requirement

Check Passed

- Internet Explorer Requirement (Success)

Messages

Internet Explorer Requirement

Check Passed

- COM Plus Catalog Requirement (Success)

Messages

COM Plus Catalog Requirement

Check Passed

- ASP.Net Version Registration Requirement (Success)

Messages

ASP.Net Version Registration Requirement

Check Passed

- Minimum MDAC Version Requirement (Success)

Messages

Minimum MDAC Version Requirement

Check Passed

Att 2

The following components will be installed:SQL Server Database Services

(Database Services, Replication, Full-Text Search)Analysis Services

Reporting Services(Reporting Services, Report Manager)Notification Services

Integration Services

Client Components(Connectivity Components, Management Tools, Business Intelligence Development Studio, SQL Server Books Online)

Att 3

<Func Name='ValidateInstanceName'>
Error Code: 0x80076db6 (28086)
Windows Error Text: Source File Name: sqlca\sqlcax.cpp
Compiler Timestamp: Wed Sep 21 01:10:12 2005
Function Name: ValidateInstanceName
Source Line Number: 445

Error Code: 28086
MSI (s) (E8!D4) [12:45:13:054]: Product: Microsoft SQL Server 2005 (64-bit) -- Error 28086. An instance with the same name is already installed on this computer. To proceed with SQL Server Setup, provide a unique instance name.

Error 28086. An instance with the same name is already installed on this computer. To proceed with SQL Server Setup, provide a unique instance name.
<EndFunc Name='LaunchFunction' Return='28086' GetLastError='0'>

|||

Hi,

here are steps to perform Manuall uninstalltion of SQL Express Edition

http://support.microsoft.com/kb/909967

http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx

Hemantgiri S. Goswami

|||

Hi Hemantgiri,

thank you for your reply. Is the uninstall the same for the SQL 2005 64-bit Dev Edition?

Thanks!

Dejan

|||

Hi Dejan,

AFAIK it is the same process to be follow while uninstalling 64-bit edition.

HTH

Hemantgiri S. Goswami

full uninstall of MSSQL 2005 Ent SP1

Hi,

I am facing a serious problem with the unproper uninstall. I have installed MSSQL 2005 Enterprise and created an Active Directory at the server. I have removed the AD and wanted to uninstall SQL 2005.

I was not able to uninstall it fully. I have reinstalled it several times, just to tryying to remove the registry entries.

Is there a solution / tool or a document describing, how I can fully manually remove MS SQL 2005 from a server?

I have already installed SP1.

Dejan

What isn't being uninstalled? Is the uninstall completing, but leaving behind files/registry keys? Or does the uninstall fail somewhere along the way?

Thanks,
Sam Lester (MSFT)

|||

Hi Dejan,

here is an article for removal of SQL Server 2005 which would be helpful to you but in order to get a good answer please re-phrase your question is that only registry which won't removes or some files are their ?

http://msdn2.microsoft.com/en-us/library/aa337087.aspx

Regards

|||

Hi Sam,

I am sorry for my late reply. The uninstall is successful, but the install already reports some errors. It leaves files and registry entries behind.

\Program Files (x86)\Microsoft SQL Server\90\COM

REPLAGNT.dll

REPLDP.dll

snapshot.exe

sqlwep.dll

sqlresld90.dll

\Program Files (x86)\Microsoft SQL Server\90\EULA

\Program Files (x86)\Microsoft SQL Server\90\Shared

D:\Program Files (x86)\Microsoft SQL Server\90\Tools\binn

Here is a portion of the MSSQL 2005 registry entries left behind in the registry after uninstall. Although I delete it manually from the registry (via regedit), it does not help me when I try reinstalling MS SQL 2005 Ent. (64-bit).

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer
Class Name: <NO CLASS>
Last Write Time: 12/3/2006 - 12:13 PM

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\CurrentVersion
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: CurrentVersion
Type: REG_SZ
Data: 9.00.2047.00

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: ProtocolList
Type: REG_MULTI_SZ
Data: np

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Np
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: PipeName
Type: REG_SZ
Data: \\.\pipe\sql\query

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: TcpPort
Type: REG_SZ
Data: 1433

Key Name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Setup
Class Name: <NO CLASS>
Last Write Time: 11/18/2006 - 8:39 AM
Value 0
Name: SQLPath
Type: REG_SZ
Data: D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL

|||

Hi,

after uninstall, I noticed on many places that registry entries have not been removed (several hundred places). I do not have a complete list (too many entries).

After uninstall, I try to install MSSQL 2005 Ent. (64-bit) once again. System Configuration Check goes fine (all are with status = Success). See the attached report at the end (att 1).

At the selection of components, I check all options (I can not select SQL Server failover cluster).

I select "Default instance". For service account, I choose "Use built-in System account" and select "Local system". By default, the following are selected to start and the end of the setup (SQL server, Analysis Service, Reporting Service). SQL Server agent is unchecked. Also, "Customize for each service account is unchecked".

For the Authentication Model, I select Mixed model (I have entered a password).

SQL Collation is set to "Slovenian dictionary order, case-sensitive, for use with 1250 (Central European).

Report Server is set to default configuration.

Overview of the installing components (see att 2). I click "Install" to start the installation.

I get an error message : An instance with the same name already installed on this computer. To proceed with SQL Server Setup, provide a unique instance name. (Error at SQL Server Database Service). See att 3 for more details from the log file.

I get also an error message with the Reporting services : "There was un unexpected failure during the setup wizard. You may review the setup logs ... Event ID: 50000. I have my content of the 2 installation CDs copied to a hard disk.

__

Att 1:

System Configuration Check

- WMI Service Requirement (Success)

Messages

WMI Service Requirement

Check Passed

- MSXML Requirement (Success)

Messages

MSXML Requirement

Check Passed

- Operating System Minimum Level Requirement (Success)

Messages

Operating System Minimum Level Requirement

Check Passed

- Operating System Service Pack Level Requirement. (Success)

Messages

Operating System Service Pack Level Requirement.

Check Passed

- SQL Server Edition Operating System Compatibility (Success)

Messages

SQL Server Edition Operating System Compatibility

Check Passed

- Minimum Hardware Requirement (Success)

Messages

Minimum Hardware Requirement

Check Passed

- IIS Feature Requirement (Success)

Messages

IIS Feature Requirement

Check Passed

- Pending Reboot Requirement (Success)

Messages

Pending Reboot Requirement

Check Passed

- Performance Monitor Counter Requirement (Success)

Messages

Performance Monitor Counter Requirement

Check Passed

- Default Installation Path Permission Requirement (Success)

Messages

Default Installation Path Permission Requirement

Check Passed

- Internet Explorer Requirement (Success)

Messages

Internet Explorer Requirement

Check Passed

- COM Plus Catalog Requirement (Success)

Messages

COM Plus Catalog Requirement

Check Passed

- ASP.Net Version Registration Requirement (Success)

Messages

ASP.Net Version Registration Requirement

Check Passed

- Minimum MDAC Version Requirement (Success)

Messages

Minimum MDAC Version Requirement

Check Passed

Att 2

The following components will be installed:SQL Server Database Services

(Database Services, Replication, Full-Text Search)Analysis Services

Reporting Services(Reporting Services, Report Manager)Notification Services

Integration Services

Client Components(Connectivity Components, Management Tools, Business Intelligence Development Studio, SQL Server Books Online)

Att 3

<Func Name='ValidateInstanceName'>
Error Code: 0x80076db6 (28086)
Windows Error Text: Source File Name: sqlca\sqlcax.cpp
Compiler Timestamp: Wed Sep 21 01:10:12 2005
Function Name: ValidateInstanceName
Source Line Number: 445

Error Code: 28086
MSI (s) (E8!D4) [12:45:13:054]: Product: Microsoft SQL Server 2005 (64-bit) -- Error 28086. An instance with the same name is already installed on this computer. To proceed with SQL Server Setup, provide a unique instance name.

Error 28086. An instance with the same name is already installed on this computer. To proceed with SQL Server Setup, provide a unique instance name.
<EndFunc Name='LaunchFunction' Return='28086' GetLastError='0'>

|||

Hi,

here are steps to perform Manuall uninstalltion of SQL Express Edition

http://support.microsoft.com/kb/909967

http://blogs.msdn.com/astebner/archive/2005/09/13/465401.aspx

Hemantgiri S. Goswami

|||

Hi Hemantgiri,

thank you for your reply. Is the uninstall the same for the SQL 2005 64-bit Dev Edition?

Thanks!

Dejan

|||

Hi Dejan,

AFAIK it is the same process to be follow while uninstalling 64-bit edition.

HTH

Hemantgiri S. Goswami

Full Text Search without any result

Dear all,
I've build a FullText Indexing at Enterprise Manager. However, when I run
the following query, it does not have any result return:
select u_documenttype, u_document, u_filename from doclib where
freetext(u_document, ' "category" or "eis" ')
Does anyone have any idea about my situation?
Thanks
Best Regards,
Eilsa
One more thing, the "u_document" is an image type
thanks
"Eilsa" <asd@.asd.asd> wrote in message
news:OYvWvh1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I've build a FullText Indexing at Enterprise Manager. However, when I run
> the following query, it does not have any result return:
> select u_documenttype, u_document, u_filename from doclib where
> freetext(u_document, ' "category" or "eis" ')
> Does anyone have any idea about my situation?
>
> Thanks
> Best Regards,
> Eilsa
>
|||Eilsa,
Yes, I do... Could you post the full output of the following SQL code?
use <your_database_name_here>
go
SELECT @.@.language
SELECT @.@.version
EXEC sp_help doclib
go
Additionally, what is the language of the text in the documents (MS Word
files) that you are storing in column "u_document" and how did you import or
upload these files to SQL Server? Both questions as well as the datatype and
size of the file extension column in your table doclib will provide clues to
answer your question.
Regards,
John
"Eilsa" <asd@.asd.asd> wrote in message
news:OTAVJx1eEHA.2352@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> One more thing, the "u_document" is an image type
> thanks
> "Eilsa" <asd@.asd.asd> wrote in message
> news:OYvWvh1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
run
>
|||us_english
(1 row(s) affected)
Microsoft SQL Server 2000-8.00.194(IntelX86)
Aug620000:57:48
Copyright(c)1988-2000 Microsoft Corporation
DeveloperEditionon Windows NT5 (Build2195:00:00 Service Pack 4)
(1 row(s) affected)
NameOwnerTypeCreated_datetime
DocFiledbouser table8/9/2004 06:23.5
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
------
File_IDintno4100no(n/a)(n/a)NULL
Fileimageno16yes(n/a)(n/a)NULL
FileTypecharno3yesnoyesSQL_Latin1_General_CP1_CI_AS
IdentitySeedIncrementNot ForReplication
No identity column defined.NULLNULLNULL
RowGuidCol
No rowguidcol column defined.
Data_located_on_filegroup
PRIMARY
index_nameindex_descriptionindex_keys
-----
PK_File clustered, unique,primary key located on PRIMARYFile_ID
constraint_typeconstraint_namedelete_actionupdate_actionstatus_enabledstatus_for_replicationconstraint_keys
------
PRIMARYKEY(clustered)PK_File(n/a)(n/a)(n/a)(n/a)File_ID
No foreign keysreference this table.
Table is referenced by views
"John Kane" <jt-kane@.comcast.net> wrote in message news:<OcV90A2eEHA.3016@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Eilsa,
> Yes, I do... Could you post the full output of the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> SELECT @.@.version
> EXEC sp_help doclib
> go
> Additionally, what is the language of the text in the documents (MS Word
> files) that you are storing in column "u_document" and how did you import or
> upload these files to SQL Server? Both questions as well as the datatype and
> size of the file extension column in your table doclib will provide clues to
> answer your question.
> Regards,
> John
>
> "Eilsa" <asd@.asd.asd> wrote in message
> news:OTAVJx1eEHA.2352@.TK2MSFTNGP09.phx.gbl...
> run
|||Thanks, Bob,
You're using SQL Server 2000 (RTM) Developer Edition on Windows 2000. You're
table DocFile and your file extension column is FileType char(3) and
Nullable. However, I still need additional info, specifically what is the
language of the text in the documents (MS Word files) that you are storing
in column "u_document" and how did you import or upload these files to SQL
Server?
I've attached a SQL script file (Import_FTS_Images.sql) that uses TextCopy
to import documents as well as html pages into a test table FTSTable that
also has a file extension column (ExtCol) defined as char(3) and Nullable
and this configuration as worked for years with US_English documents. Test
your document upload into this configuration and then run a Full Population
and test FTS queries. If you still have a problem getting the expected
results, please post the exact SQL query as well as the exact sample data
contained in the document/html pages.
Thanks,
John
"Bob" <kcsc1@.hotpop.com> wrote in message
news:ef3b388e.0408100822.603f67f2@.posting.google.c om...
> us_english
> (1 row(s) affected)
> Microsoft SQL Server 2000-8.00.194 (Intel X86)
> Aug 6 2000 0:57:48
> Copyright (c) 1988-2000 Microsoft Corporation
> Developer Edition on Windows NT5 (Build 2195:00:00 Service Pack 4)
> (1 row(s) affected)
> Name Owner Type Created_datetime
> DocFile dbo user table 8/9/2004 06:23.5
>
> Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
> ----

> File_ID int no 4 10 0 no (n/a) (n/a) NULL
> File image no 16 yes (n/a) (n/a) NULL
> FileType char no 3 yes no yes SQL_Latin1_General_CP1_CI_AS
>
> Identity Seed Increment Not For Replication
> ----
--
> No identity column defined. NULL NULL NULL
>
> RowGuidCol
> --
> No rowguidcol column defined.
>
> Data_located_on_filegroup
> --
> PRIMARY
>
> index_name index_description index_keys
> ----
--
> PK_File clustered, unique,primary key located on PRIMARY File_ID
>
> constraint_type constraint_name delete_action update_action status_enabled
status_for_replication constraint_keys
> ----

> PRIMARY KEY (clustered) PK_File (n/a) (n/a) (n/a) (n/a) File_ID
>
> No foreign keys reference this table.
> Table is referenced by views
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<OcV90A2eEHA.3016@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
import or[vbcol=seagreen]
and[vbcol=seagreen]
clues to[vbcol=seagreen]
I[vbcol=seagreen]
begin 666 Import_FTS_Images.sql
M#0IU<V4@.<'5B<PT*9V\-"FEF(&5X:7-T<R H<V5L96-T("H@.9G)O;2!S>7-O
M8FIE8W1S('=H97)E(&ED(#T@.;V)J96-T7VED*"=&5%-486)L92<I*0T*("!D
M<F]P('1A8FQE($944U1A8FQE#0IG;PT*#0I#4D5!5$4@.5$%"3$4@.1 E135&%B
M;&4@.* T*("!+97E#;VP):6YT($E$14Y42519("@.Q+#$I($Y/5"!.54Q,( T*
M(" @.($-/3E-44D%)3E0@.1E135&%B;&5?2418(%!224U!4ED@.2T59($-,55-4
M15)%1"P-"B @.5&5X=$-O; ET97AT($Y53$PL#0H@.($EM86=E0V]L"6EM86=E
M($Y53$PL#0H@.($5X=$-O; EC:&%R*#,I($Y53$PL("TM(&-A;B!B92!E:71H
M97(@.<WES;F%M92!O<B!C:&%R*#,I#0H@.(%1I;653=&%M<$-O; ET:6UE<W1A
M;7 @.3E5,3" -"BD@.3TX@.6U!224U!4EE=#0IG;PT*#0H-"BTM($EN<V5R="!D
M871A+BXN("A.;W1E.B!);FET86QI>FEN9R!)34%'12!C;VQU; 6X@.=VET:" P
M>$9&1D9&1D9&(&9O<B!U<V4@.=VET:"!497AT0V]P>2YE>&4I#0I)3E-%4E0@.
M1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W(#$G+" P
M>$9&1D9&1D9&+" G9&]C)RP@.3E5,3"D-"DE.4T525"!&5%-486)L92!V86QU
M97,H)U1E<W0@.5$585"!$871A(&9O<B!R;W<@.,B<L(#!X1D9&1 D9&1D8L("=X
M;',G+"!.54Q,*0T*24Y315)4($944U1A8FQE('9A;'5E<R@.G5 &5S="!415A4
M($1A=&$@.9F]R(')O=R S)RP@.,'A&1D9&1D9&1BP@.)W!P="<L($Y53$PI#0I)
M3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F;W(@.<F]W
M(#0G+" P>$9&1D9&1D9&+" G='AT)RP@.3E5,3"D-"DE.4T525"!&5%-486)L
M92!V86QU97,H)U1E<W0@.5$585"!$871A(&9O<B!R;W<@.-2<L(#!X1D9&1D9&
M1D8L("=H=&TG+"!.54Q,*0T*24Y315)4($944U1A8FQE('9A; '5E<R@.G5&5S
M="!415A4($1A=&$@.9F]R(')O=R V)RP@.,'A&1D9&1D9&1BP@.)W!D9B<L($Y5
M3$PI#0I)3E-%4E0@.1E135&%B;&4@.=F%L=65S*"=497-T(%1%6%0@.1&%T82!F
M;W(@.<F]W(#<G+" P>$9&1D9&1D9&+" G>&UL)RP@.3E5,3"D-"F=O#0H-"@.T*
M+2T@.4V5L96-T(&1A=&$-"E-%3$5#5" J(&9R;VT@.1E135&%B;&4-"F=O#0H-
M"@.T*9&5C;&%R92! <75E<GD@.=F%R8VAA<B@.R,# I#0HM+2!);G-E<G0@.2%1-
M3%]F:6QE+FAT;2!I;G1O(%)O=R U("$A#0HM+2!.3U1%.B!%;G-U<F4@.=&AE
M(&-O<G)E8W0@.<&%T:"!F;W(@.=&5X=&-O<'DN97AE(2$-"G-E="! <75E<GD@.
M/2 G1#I<35-344PX,%Q-4U-13"1344PX,%Q":6YN7'1E>'1C;W!Y("]S("<K
M0$!S97)V97)N86UE*R<@.+W4@.<V$@.+W @.+V0@.<'5B<R O="!&5%-486)L92 O
M8R!);6%G94-O;" O9B!$.EQ344Q&:6QE<UQ3:&EL;VA</$A434Q?9FEL93XN
M:'1M("]I("]K(#4P,# @.+W<@.(G=H97)E($ME>4-O;#TU(B<-"G!R:6YT($!Q
M=65R>0T*97AE8R!M87-T97(N+GAP7V-M9'-H96QL($!Q=65R>0T*9V\-"@.T*
M9&5C;&%R92! <75E<GD@.=F%R8VAA<B@.R,# I#0HM+2!);G-E<G0@.35-?5V]R
M9"YD;V,@.:6YT;R!2;W<@.,2 A(0T*<V5T($!Q=65R>2 ]("=$.EQ-4U-13#@.P
M7$U34U%,)%-13#@.P7$)I;FY<=&5X=&-O<'D@.+W,@.)RM 0'-E<G9E<FYA;64K
M)R O=2!S82 O<" O9"!P=6)S("]T($944U1A8FQE("]C($EM86=E0V]L("]F
M($0Z7$I+86YE7$1O8W-</$U37U=O<F0^+F1O8R O:2 O:R U,# P("]W(")W
M:&5R92!+97E#;VP],2(G#0IP<FEN="! <75E<GD-"F5X96,@.;6%S=&5R+BYX
M<%]C;61S:&5L;"! <75E<GD-"F=O#0H-"BTM(%-E;&5C="!D871A#0I314Q%
M0U0@.*B!F<F]M($944U1A8FQE#0IG;PT*#0H-"BTM($9422 -"G5S92!P=6)S
M#0IG;PT*97AE8R!S<%]F=6QL=&5X=%]D871A8F%S92 G96YA8FQE)R -"F=O
M#0IE>&5C('-P7V9U;&QT97AT7W-E<G9I8V4@.)V-L96%N7W5P)PT*#0HM+2!$
M<F]P($9422P@.:68@.;F5C97-S87)Y+BXN#0IE>&5C('-P7V9U;&QT97AT7W1A
M8FQE("=&5%-486)L92<L)V1R;W G#0IE>&5C('-P7V9U;&QT97AT7T-A=&%L
M;V<@.)T944T-A=&%L;V<G+"=D<F]P)PT*#0H-"F5X96,@.<W!?9G5L;'1E>'1?
M8V%T86QO9R G1E130V%T86QO9R<L)V-R96%T92<-"F5X96,@.<W!?9G5L;'1E
M>'1?=&%B;&4@.)T944U1A8FQE)RPG8W)E871E)RPG1E130V%T8 6QO9R<L)T94
M4U1A8FQE7TE$6"<-"F5X96,@.<W!?9G5L;'1E>'1?8V]L=6UN("=&5%-486)L
M92<L)TEM86=E0V]L)RPG861D)RP@.,'@.P-# Y+" G17AT0V]L)R -"F5X96,@.
M<W!?9G5L;'1E>'1?8V]L=6UN("=&5%-486)L92<L)U1E>'1#;VPG+"=A9&0G
M( T*97AE8R!S<%]F=6QL=&5X=%]T86)L92 G1E135&%B;&4G+" G86-T:79A
M=&4G(" -"F=O#0H-"BTM(%-T87)T($94($EN9&5X:6YG+BXN#0IE>&5C('-P
M7V9U;&QT97AT7V-A=&%L;V<@.)T944T-A=&%L;V<G+"=S=&%R=%]F=6QL)PT*
M9V\-"BTM(%=A:70@.9F]R($94($EN9&5X:6YG('1O(&-O;7!L971E(&%N9"!C
M:&5C:R!.5"]7:6XR2R!!<'!L:6-A=&EO;B!L;V<@.9F]R('-U8V-E<W,O97)R
M;W)S+BX-"@.T*#0IS96QE8W0@.*B!F<F]M($944U1A8FQE#0IG;PT*+2T@.4V5A
M<F-H(&9O<B!S96%R8VA?=V]R9%]H97)E(&EN($A434P@.9FEL92XN#0IS96QE
M8W0@.2V5Y0V]L+"!);6%G94-O;" @.9G)O;2!&5%-486)L92!W:&5R92!C;VYT
M86EN<R@.J+"<\<V5A<F-H7W=O<F1?:&5R93XG*2!O<F1E<B!B>2!+97E#;VP@.
M#0IG;PT*+2T@.4V5A<F-H(&9O<B!S96%R8VA?=V]R9%]H97)E(&EN("Y$3T,@.
M9FEL92XN+@.T*<V5L96-T($ME>4-O;"P@.26UA9V5#;VP@.9G)O;2!&5%-486)L
M92!W:&5R92!C;VYT86EN<R@.J+"<\<V5A<F-H7W=O<F1?:&5R93XG*2!O<F1E
M<B!B>2!+97E#;VP@.#0IG;PT*#0H-"BTM($-O;F9I<FT@.1E0@.4')O<&5R=&EE
M<RXN+@.T*=7-E('!U8G,-"F=O#0IS<%]H96QP7V9U;&QT97AT7V-A=&%L;V=S
M("=&5%-#871A;&]G)R -"F=O#0IS<%]H96QP7V9U;&QT97AT7W1A8FQE<R G
M1E130V%T86QO9R<@.( T*9V\-"G-P7VAE;'!?9G5L;'1E>'1?8V]L=6UN<R G
M1E135&%B;&4G#0IG;PT*4T5,14-4(&9U;&QT97AT8V%T86QO9W!R;W!E<G1Y
M*"=&5%-#871A;&]G)RP@.)U!O<'5L871E4W1A='5S)RD@.#0IG;PT*#0HM+2!2
M96UO=F4@.1E0@.26YD97AE<R F($-A=&%L;V<@.)B!T86)L92XN#0H-"F5X96,@.
M<W!?9G5L;'1E>'1?=&%B;&4@.)T944U1A8FQE)RPG9')O<"<-"@.T*97AE8R!S
M<%]F=6QL=&5X=%]#871A;&]G("=&5%-#871A;&]G)RPG9')O<"<-"@.T*9')O
4<"!T86)L92!&5%-486)L90T*#0H`
`
end

Full Text Indexing Option Disabled

i, like hundreds of other people, am unable to use the Full Text indexing
feature because of the bug in SQL Server Enterprise Manager that disables
the ability to configure it.
See the Microsoft Knowledge Base article, but realize that it doesn't apply.
http://support.microsoft.com/?kbid=270671
It suggests that the problem is due to being logged onto your client
computer using a local account. It implies that because the account is local
to your computer (as opposed to being a domain account), you are not able to
query the state of the MSSearch service on the target SQL Server machine.
When Enterprise Manager is unable to query the state of the MSSearch on the
target SQL Server machine, it assumes the MSSearch service on the target SQL
Server machine is either disabled or not installed - and therefore prevents
you from configuring Full-Text indexing.
Problem is, we are not logged on using a local account; we are logged on
using a domain account. What is more, the domain account we are using is a
domain administrative account - the account has full ability to query state
of services on the remote SQL Server machine.
The we are experiencing has nothing to do with logging onto the client
computer using a local account. So what does it have to do with? What should
the knowledge base article say about this problem? What is the actual
problem?
i know this bug was introduced in Enterprise Manager with 2000sp2. Does
anyone know if this will be fixed in an upcoming service pack?
References:
http://support.microsoft.com/?kbid=270671
http://tinyurl.com/2wseqa
http://tinyurl.com/2mj8aj
http://tinyurl.com/3aqhea
http://tinyurl.com/35gf54
http://tinyurl.com/384wq7
http://tinyurl.com/3bmnab
http://tinyurl.com/2spgfq
http://tinyurl.com/3df22d
Keywords:
SQL Server Enterprise Manager Full-Text Indexing option disabled grayed out
greyed out "Full-Text Indexing..." full text indexing disabled sqlem, cannot
configure full text, fulltext disabled, fulltext indexing feature disabled,
howto configure fulltext indexing menu item disabled
did you try this?
http://www.indexserverfaq.com/TableNotEnabled.htm
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:OQDnYS1RHHA.488@.TK2MSFTNGP06.phx.gbl...
> i, like hundreds of other people, am unable to use the Full Text indexing
> feature because of the bug in SQL Server Enterprise Manager that disables
> the ability to configure it.
> See the Microsoft Knowledge Base article, but realize that it doesn't
> apply.
> http://support.microsoft.com/?kbid=270671
>
> It suggests that the problem is due to being logged onto your client
> computer using a local account. It implies that because the account is
> local to your computer (as opposed to being a domain account), you are not
> able to query the state of the MSSearch service on the target SQL Server
> machine. When Enterprise Manager is unable to query the state of the
> MSSearch on the target SQL Server machine, it assumes the MSSearch service
> on the target SQL Server machine is either disabled or not installed - and
> therefore prevents you from configuring Full-Text indexing.
> Problem is, we are not logged on using a local account; we are logged on
> using a domain account. What is more, the domain account we are using is a
> domain administrative account - the account has full ability to query
> state of services on the remote SQL Server machine.
> The we are experiencing has nothing to do with logging onto the client
> computer using a local account. So what does it have to do with? What
> should the knowledge base article say about this problem? What is the
> actual problem?
> i know this bug was introduced in Enterprise Manager with 2000sp2. Does
> anyone know if this will be fixed in an upcoming service pack?
> References:
> http://support.microsoft.com/?kbid=270671
> http://tinyurl.com/2wseqa
> http://tinyurl.com/2mj8aj
> http://tinyurl.com/3aqhea
> http://tinyurl.com/35gf54
> http://tinyurl.com/384wq7
> http://tinyurl.com/3bmnab
> http://tinyurl.com/2spgfq
> http://tinyurl.com/3df22d
> Keywords:
> SQL Server Enterprise Manager Full-Text Indexing option disabled grayed
> out greyed out "Full-Text Indexing..." full text indexing disabled sqlem,
> cannot configure full text, fulltext disabled, fulltext indexing feature
> disabled, howto configure fulltext indexing menu item disabled
>
>
|||Hello Ian,
Its not just permissions but also firewalls that can get in the way. I have
this and am pretty sure it was looking at my local machine.
Run EM on the server itself should allow you to create your indexes.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons

> i, like hundreds of other people, am unable to use the Full Text
> indexing feature because of the bug in SQL Server Enterprise Manager
> that disables the ability to configure it.
> See the Microsoft Knowledge Base article, but realize that it doesn't
> apply. http://support.microsoft.com/?kbid=270671
> It suggests that the problem is due to being logged onto your client
> computer using a local account. It implies that because the account is
> local to your computer (as opposed to being a domain account), you are
> not able to query the state of the MSSearch service on the target SQL
> Server machine. When Enterprise Manager is unable to query the state
> of the MSSearch on the target SQL Server machine, it assumes the
> MSSearch service on the target SQL Server machine is either disabled
> or not installed - and therefore prevents you from configuring
> Full-Text indexing.
> Problem is, we are not logged on using a local account; we are logged
> on using a domain account. What is more, the domain account we are
> using is a domain administrative account - the account has full
> ability to query state of services on the remote SQL Server machine.
> The we are experiencing has nothing to do with logging onto the client
> computer using a local account. So what does it have to do with? What
> should the knowledge base article say about this problem? What is the
> actual problem?
> i know this bug was introduced in Enterprise Manager with 2000sp2.
> Does anyone know if this will be fixed in an upcoming service pack?
> References:
> http://support.microsoft.com/?kbid=270671
> http://tinyurl.com/2wseqa
> http://tinyurl.com/2mj8aj
> http://tinyurl.com/3aqhea
> http://tinyurl.com/35gf54
> http://tinyurl.com/384wq7
> http://tinyurl.com/3bmnab
> http://tinyurl.com/2spgfq
> http://tinyurl.com/3df22d
> Keywords:
> SQL Server Enterprise Manager Full-Text Indexing option disabled
> grayed out
> greyed out "Full-Text Indexing..." full text indexing disabled sqlem,
> cannot
> configure full text, fulltext disabled, fulltext indexing feature
> disabled,
> howto configure fulltext indexing menu item disabled
|||> did you try this?
> http://www.indexserverfaq.com/TableNotEnabled.htm
Yes.
|||> Its not just permissions but also firewalls that can get in the way. I
> have this and am pretty sure it was looking at my local machine.
The windows firewall is not enabled on my Windows XP SP2 machine. The server
is Windows 2000.

> Run EM on the server itself should allow you to create your indexes.
This, oddly enough, works.
Thank you.
|||Ian,
Make sure your polling interval is turned on in EM. To do this,
select Tools --> Options... and check the box for "Server state
polling".
If polling is not on, EM can't check to see if the MS Search service
is running. This results in the options being grayed out.
This only works on the server where SQL Server is intalled. As a
general rule, I turn this off for client machines, as it generates a
lot of network traffic, especially if you have a bunch of servers
defined in EM.
Hope this works. I spent days following all the instructions out
there from MS and the groups. Finally figured it out after comparing
registry entries for a server that worked and one that didn't.
Rick
On Feb 4, 3:29 pm, Simon Sabin <SimonSa...@.noemail.noemail> wrote:
> Hello Ian,
> Its not just permissions but also firewalls that can get in the way. I have
> this and am pretty sure it was looking at my local machine.
> Run EM on the server itself should allow you to create your indexes.
> Simon Sabin
> SQL Server MVPhttp://sqlblogcasts.com/blogs/simons
>
>
>
>
>
> - Show quoted text -

FULL TEXT CATALOGS CANT BE OPEN

I have number of catalogs on my server. when i am clicking on the full text
catalogs section on the enterprise manager the enterprise manager is stucked
and i cant see any catalogs. anyone knows what is the problem? thanks!
Run sp_who2 to see if there is any deadlocking. Also run profiler to
determine what is going on on your system.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ruby Nadler" <RubyNadler@.discussions.microsoft.com> wrote in message
news:EF872289-8106-4DEA-B7E4-8CC6D16842FF@.microsoft.com...
> I have number of catalogs on my server. when i am clicking on the full
text
> catalogs section on the enterprise manager the enterprise manager is
stucked
> and i cant see any catalogs. anyone knows what is the problem? thanks!

Full recovery backup

Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
Ant
Hello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:

> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>
|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?
|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:

> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>

Full recovery backup

Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how ca
n
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/feat...cle.php/3530616
http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:

> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/feat...cle.php/3530616
> http://www.devx.com/getHelpOn/10Min...32/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...[vbcol=seagreen]
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:

> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>

Full recovery backup

Hi,
I've just discovered the 'Recovery model options' in properties page of a
table in Enterprise manager.
I checked books on line which stated that Full recovery mode ' can recover
from any point in time'. Are they referring to backup/restores? If so how can
this be done at any point in time. Otherwise, what are they referring to?
Thanks for your time in answering this question
AntHello,
Point in time:-
A point in time recovery is restoring a database to a specified date and
time. When you have completed a point in time recovery, your database
will be in the state it was at the specific date and time you identified
when restoring your database. A point in time recovery is a method to
recover
your database to any point in time since the last database backup
Take a look into below URL for datails:-
http://www.databasejournal.com/features/mssql/article.php/3530616
http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how
> can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant|||Thank you for your time,
When I had a look at the restore dialog, the Point in time check box was
disabled. I checked the proertiers to find that it was set to full recovery
mode. Why would the check box not be enabled?
Thanks very much for your assistance
Ant
"Hari Prasad" wrote:
> Hello,
> Point in time:-
> A point in time recovery is restoring a database to a specified date and
> time. When you have completed a point in time recovery, your database
> will be in the state it was at the specific date and time you identified
> when restoring your database. A point in time recovery is a method to
> recover
> your database to any point in time since the last database backup
> Take a look into below URL for datails:-
> http://www.databasejournal.com/features/mssql/article.php/3530616
> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>
> Thanks
> Hari
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how
> > can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
>
>|||Hello,
That will get enabled onky when you restore a transaction log backup over a
non recovered database.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:1A36B0F1-0D04-404F-8E16-2CF68903BE29@.microsoft.com...
> Thank you for your time,
> When I had a look at the restore dialog, the Point in time check box was
> disabled. I checked the proertiers to find that it was set to full
> recovery
> mode. Why would the check box not be enabled?
> Thanks very much for your assistance
> Ant
> "Hari Prasad" wrote:
>> Hello,
>> Point in time:-
>> A point in time recovery is restoring a database to a specified date and
>> time. When you have completed a point in time recovery, your database
>> will be in the state it was at the specific date and time you identified
>> when restoring your database. A point in time recovery is a method to
>> recover
>> your database to any point in time since the last database backup
>> Take a look into below URL for datails:-
>> http://www.databasejournal.com/features/mssql/article.php/3530616
>> http://www.devx.com/getHelpOn/10MinuteSolution/16532/1954?pf=true
>>
>> Thanks
>> Hari
>>
>> "Ant" <Ant@.discussions.microsoft.com> wrote in message
>> news:DBC63D60-27F0-4B94-9E27-39C3D3A3355D@.microsoft.com...
>> > Hi,
>> > I've just discovered the 'Recovery model options' in properties page of
>> > a
>> > table in Enterprise manager.
>> >
>> > I checked books on line which stated that Full recovery mode ' can
>> > recover
>> > from any point in time'. Are they referring to backup/restores? If so
>> > how
>> > can
>> > this be done at any point in time. Otherwise, what are they referring
>> > to?
>> >
>> > Thanks for your time in answering this question
>> > Ant
>>|||On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> Hi,
> I've just discovered the 'Recovery model options' in properties page of a
> table in Enterprise manager.
> I checked books on line which stated that Full recovery mode ' can recover
> from any point in time'. Are they referring to backup/restores? If so how can
> this be done at any point in time. Otherwise, what are they referring to?
> Thanks for your time in answering this question
> Ant
Point-in-time recovery makes use of full backups and transaction log
backups to allow you to restore a database to virtually any point in
time. Consider the following backup scenario:
9:00pm - full backup
10:00pm - log backup
11:00pm - log backup
12:00am - log backup
1:00am - log backup
...
...
...
3:00pm - log backup
You discover that at 2:30pm, a malicious user updated a large portion
of your data with junk, and management has decided it's not safe to
continue with the data as-is, and they ask you to restore the database
to some point prior to the user's update. Without the log backups,
your only recourse would be to restore the 9:00pm full backup, losing
the entire day's activity.
However, with the log backups, you can restore the database literally
up to the minute of the bad update. You first restore the full
backup, specifying WITH NORECOVERY in the restore command. You then
restore the log backups from 10:00pm through 2:00pm, specifying WITH
NORECOVERY for each restore. Finally, you restore the 3:00pm log
backup, this time using the STOPAT clause on the restore command.
With STOPAT, you specify a specific time that you want the restore to
stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
the bad update.
Does that help?|||Brilliant! that makes it clear.
Thank you for your answer.
Ant
"Tracy McKibben" wrote:
> On Feb 5, 9:26 pm, Ant <A...@.discussions.microsoft.com> wrote:
> > Hi,
> > I've just discovered the 'Recovery model options' in properties page of a
> > table in Enterprise manager.
> >
> > I checked books on line which stated that Full recovery mode ' can recover
> > from any point in time'. Are they referring to backup/restores? If so how can
> > this be done at any point in time. Otherwise, what are they referring to?
> >
> > Thanks for your time in answering this question
> > Ant
> Point-in-time recovery makes use of full backups and transaction log
> backups to allow you to restore a database to virtually any point in
> time. Consider the following backup scenario:
> 9:00pm - full backup
> 10:00pm - log backup
> 11:00pm - log backup
> 12:00am - log backup
> 1:00am - log backup
> ...
> ...
> ...
> 3:00pm - log backup
> You discover that at 2:30pm, a malicious user updated a large portion
> of your data with junk, and management has decided it's not safe to
> continue with the data as-is, and they ask you to restore the database
> to some point prior to the user's update. Without the log backups,
> your only recourse would be to restore the 9:00pm full backup, losing
> the entire day's activity.
> However, with the log backups, you can restore the database literally
> up to the minute of the bad update. You first restore the full
> backup, specifying WITH NORECOVERY in the restore command. You then
> restore the log backups from 10:00pm through 2:00pm, specifying WITH
> NORECOVERY for each restore. Finally, you restore the 3:00pm log
> backup, this time using the STOPAT clause on the restore command.
> With STOPAT, you specify a specific time that you want the restore to
> stop at. In this case, you tell it to STOPAT 2:29pm, just prior to
> the bad update.
> Does that help?
>

Full population very slow

I've got SQL2000 Enterprise edition running on windows 2000 advanced server,
we've recently upgraded from SQL sp2 to sp3a, this caused a catalog rebuild
but full population is taking forever. The column with Full text search
enabled is of image type, there are 2.5 million record in the table, about
7GB in size.
I've started full population 2 days ago and it's still showing "Population
in progress", when I view the property page of the catalog, I can see ~
2300000 items, catalog size: 190MB, unique key count: 362586. It appears to
be processing 1000 items per minute.
The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
disks.
mssdmn.exe is the most active process, it's using between 30-40 CPU on one
processor only, disk utilization is minimal and plenty of physical memory
available for use. This process is using 6 threads, 10MB RAM, mssearch.exe is
using 50MB RAM and 16 threads.
Is there any way of speeding up population?
Thanks,
Ad
set sp_fulltext_service 'resource_usage', 5
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
> server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
> rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing "Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
> to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
> is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||Ad,
There are a couple of thing you can do to improve the FT Indexing
performance without stopping & re-locating the FT Catalog. You can set the
MSSearch service's to use one CPU and then use sp_configure to set the
affinity of SQL Server to that cpu to avoid cpu contention during heavy
MSSearch CPU usage via the following method - launch the Tskmgr.exe from the
AT command on the multi-proc server where sql server resides, as follows:
at <current_time+1min> /interactive taskmgr.exe
when it launches, you can then set "cpu affinity" for the MSSearch service
to a cpu or set of cpu's not being used by SQL Server. Then you would use
sp_configure to set SQL Server's cpu affinity to the other cpu's, thus
preventing the cpu usage of mssearch from affecting your sql server
processing. Note, this only works on multi-processor servers and is a
secured method for achieving this feature.
Additionally, as your server has lots of RAM, you can use
sp_fulltext_service 'resource_usage', 5 to set the MSSearch service to 5
(dedicated), and it will use the max amount of RAM (512Mb), but only if the
RAM is not being used by either the OS or by SQL Server.
Finally, I'd highly recommend that you review all the resources "SQL Server
2000 Full-Text Search Resources and Links" at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B94E0F3A-7655-4D8A-8605-EA1BC43E6329@.microsoft.com...
> I've got SQL2000 Enterprise edition running on windows 2000 advanced
server,
> we've recently upgraded from SQL sp2 to sp3a, this caused a catalog
rebuild
> but full population is taking forever. The column with Full text search
> enabled is of image type, there are 2.5 million record in the table, about
> 7GB in size.
> I've started full population 2 days ago and it's still showing
"Population
> in progress", when I view the property page of the catalog, I can see ~
> 2300000 items, catalog size: 190MB, unique key count: 362586. It appears
to
> be processing 1000 items per minute.
> The server has 8 CPUs, 8GB RAM with /PAE and /3GB enabled, heavy duty SAN
> disks.
> mssdmn.exe is the most active process, it's using between 30-40 CPU on one
> processor only, disk utilization is minimal and plenty of physical memory
> available for use. This process is using 6 threads, 10MB RAM, mssearch.exe
is
> using 50MB RAM and 16 threads.
> Is there any way of speeding up population?
> Thanks,
> Ad
>
|||sp_fulltext_service 'resource_usage', 5 , did not make any difference, does
it need stop and re-start of MSSearch service?
Thanks,
Ad
|||Ad,
I didn't think it would make much difference. However, where you get the
biggest bang for your buck, is ensuring that the FT Catalog is on a separate
disk drive from your database files. Can you stop the Full Population, drop
and re-create the FT Catalog on separate disk array or better yet, on a
separate disk controller? See SQL 2000 BOL title "Full-text Search
Recommendations" for more info on this.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ad" <Ad@.discussions.microsoft.com> wrote in message
news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> sp_fulltext_service 'resource_usage', 5 , did not make any difference,
does
> it need stop and re-start of MSSearch service?
> Thanks,
> Ad
>
>
|||The FT Catalog is on a dedicated SAN disk which is almost idle, in fact the
whole disk is cached. So there are plenty of resources on the server that FT
can't utilize, this leads me to believe FT has scalability issues.
Regards,
Ad
"John Kane" wrote:

> Ad,
> I didn't think it would make much difference. However, where you get the
> biggest bang for your buck, is ensuring that the FT Catalog is on a separate
> disk drive from your database files. Can you stop the Full Population, drop
> and re-create the FT Catalog on separate disk array or better yet, on a
> separate disk controller? See SQL 2000 BOL title "Full-text Search
> Recommendations" for more info on this.
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "Ad" <Ad@.discussions.microsoft.com> wrote in message
> news:B72E38AF-4FFF-4F60-9F84-BF56213AC220@.microsoft.com...
> does
>
>

Full backup and Transaction Logs

Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.
Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>
|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl.. .
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>
|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||once i have a full backup of the database why would i need the log files
anymore? backupexec 9 indicates they will be removed during a full backup as
i would expect, but it isn't happening on one of my servers. thx if you have
more clues.
"Tracy McKibben" wrote:

> Kevin A wrote:
> Nope. See the topic "Truncating the Transaction Log" in Books Online.
> If you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>
|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Full backup and Transaction Logs

Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
doing a Full backup isn't truncating or shrinking the log file. The log
file got to be over 10GB and forced the Data drive to fill up.
Is there some reason for this? I thought when full backups were successful,
they should be truncating the log file.
I can backup log with truncate_only and then issue a dbcc shrinkfile to get
it back down, but I'd like to know why full backups aren't working first.
Thanks.Kevin A wrote:
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were successful,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to get
> it back down, but I'd like to know why full backups aren't working first.
> Thanks.
>
Nope. See the topic "Truncating the Transaction Log" in Books Online.
If you're running in Full or Bulk-Logged recovery mode, you MUST do
transaction log backups, or the log will continue to accumulate
transactional data. Even in Simple mode, a single large transaction can
cause the log to grow.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Sorry, I just need a bit more clarification.
Are you saying, after a full backup, I need to issue the truncate command?
That doesn't always seem to shrink the Logfile. I have to manually issue
the dbcc srhinkfile.
What would be the suggested steps to take, after the full backup of the DB?
Thanks.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A3927B.3030202@.realsqlguy.com...
> Kevin A wrote:
>> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed
>> that doing a Full backup isn't truncating or shrinking the log file. The
>> log file got to be over 10GB and forced the Data drive to fill up.
>> Is there some reason for this? I thought when full backups were
>> successful, they should be truncating the log file.
>> I can backup log with truncate_only and then issue a dbcc shrinkfile to
>> get it back down, but I'd like to know why full backups aren't working
>> first.
>> Thanks.
> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
> you're running in Full or Bulk-Logged recovery mode, you MUST do
> transaction log backups, or the log will continue to accumulate
> transactional data. Even in Simple mode, a single large transaction can
> cause the log to grow.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Hello,
FULL database backup will not clear the contents in tranasction log file.
The purpose of FULL recovery model is to perform the POIN IN TIME recovery.
In order to do this
you need to take the tranasction log backup. So when ever you perform a
transaction log backup automatically transaction log will be cleared from
the LDF file and will
leep the LDF file growth under control. Incase if you need to do a recovery
on crash/point in time you can use these log backup files. Just read the
Transaction log backup topic in books online.
Thanks
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:eh2Ecu%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed that
> doing a Full backup isn't truncating or shrinking the log file. The log
> file got to be over 10GB and forced the Data drive to fill up.
> Is there some reason for this? I thought when full backups were
> successful, they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to
> get it back down, but I'd like to know why full backups aren't working
> first.
> Thanks.
>|||Hello,
1. First take a look into the recovery model you use for the database.
2. If it is FULL or BULK_LOGGED then you have schedule a transction log
backup in regular intervals [Say every 15 minutes or so]. This frequency can
be changed based on ur data growth,.
Take a look into the BACKUP LOG command to backup the transaction log or you
could use the Maintenence plan wizard to automate the transaction log
backup.
This will make sure that you LDF will not grow drastically.
To reduce the size of 10 GB currently; you may to need to truncate the Log
first and use DBCC SHRINKFILE to reduce the file size. After that to bring
back the backup chain do:-
1. A full database backup
2. Followed with transaction log backups.
THANKS
Hari
"Kevin A" <kevina@.cqlcorp.com> wrote in message
news:ez%237O%23%23MHHA.4916@.TK2MSFTNGP06.phx.gbl...
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually
> issue the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the
> DB?
> Thanks.
>
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:45A3927B.3030202@.realsqlguy.com...
>> Kevin A wrote:
>> Recently, with a couple of DB's on SQL 2000 Enterprise SP3, I noticed
>> that doing a Full backup isn't truncating or shrinking the log file.
>> The log file got to be over 10GB and forced the Data drive to fill up.
>> Is there some reason for this? I thought when full backups were
>> successful, they should be truncating the log file.
>> I can backup log with truncate_only and then issue a dbcc shrinkfile to
>> get it back down, but I'd like to know why full backups aren't working
>> first.
>> Thanks.
>> Nope. See the topic "Truncating the Transaction Log" in Books Online. If
>> you're running in Full or Bulk-Logged recovery mode, you MUST do
>> transaction log backups, or the log will continue to accumulate
>> transactional data. Even in Simple mode, a single large transaction can
>> cause the log to grow.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>|||Kevin A wrote:
> Sorry, I just need a bit more clarification.
> Are you saying, after a full backup, I need to issue the truncate command?
> That doesn't always seem to shrink the Logfile. I have to manually issue
> the dbcc srhinkfile.
> What would be the suggested steps to take, after the full backup of the DB?
> Thanks.
TRUNCATING is not SHRINKING, they are two different operations.
The log file is a "journal", recording every change that takes place in
your database. If the database is in Simple recovery mode, these
journal entries are automatically removed from the log once they've
completed. In Full or Bulk-Logged mode, they remain in the log until
you perform a log backup. This gives you the ability to restore the
database to any point in time, by "playing back" those journal entries
that you've backed up, an ability that you lose with Simple mode.
For example, suppose your database is in Simple mode, and you are doing
full backups nightly at 9:00pm. You suffer a hard drive crash at
8:00pm, corrupting your database. Your only option for recovery is to
restore the backup from 9:00pm the previous night, losing 23 hours of data.
Now, suppose that same database is in Full recovery mode. You're doing
nightly full backups at 9:00pm, and log backups every 5 minutes. Your
drive fails at 8:00pm, corrupting the database. You now have the
ability to restore the previous night's full backup, followed by the log
backups that have been done since then. At most, you lost 5 minutes of
data.
The space inside the transaction log is "recycled", as committed
transactions are flushed out (via a log backup), the space that they
were using is made available for new transactions. This helps to keep
the log file size under control. If you aren't flushing them out (i.e.
TRUNCATING), the log has to grow larger to hold new transactions.
SHRINKING is a different process - it will remove any free space from
the log, physically reducing the size of the log file. You should only
do this under extreme circumstances, it should NOT be part of your daily
routine. Repeated shrinking/growing of a file (log or database) will
lead to fragmentation, which will ultimately hurt performance. If you
have the disk space, you should allow the log to grow as needed, making
sure you're doing proper backups to keep the committed transactions
flushed out.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you, you put the very well... Regards, John
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A39711.7070204@.realsqlguy.com...
> Kevin A wrote:
>> Sorry, I just need a bit more clarification.
>> Are you saying, after a full backup, I need to issue the truncate
>> command? That doesn't always seem to shrink the Logfile. I have to
>> manually issue the dbcc srhinkfile.
>> What would be the suggested steps to take, after the full backup of the
>> DB?
>> Thanks.
> TRUNCATING is not SHRINKING, they are two different operations.
> The log file is a "journal", recording every change that takes place in
> your database. If the database is in Simple recovery mode, these journal
> entries are automatically removed from the log once they've completed. In
> Full or Bulk-Logged mode, they remain in the log until you perform a log
> backup. This gives you the ability to restore the database to any point
> in time, by "playing back" those journal entries that you've backed up, an
> ability that you lose with Simple mode.
> For example, suppose your database is in Simple mode, and you are doing
> full backups nightly at 9:00pm. You suffer a hard drive crash at 8:00pm,
> corrupting your database. Your only option for recovery is to restore the
> backup from 9:00pm the previous night, losing 23 hours of data.
> Now, suppose that same database is in Full recovery mode. You're doing
> nightly full backups at 9:00pm, and log backups every 5 minutes. Your
> drive fails at 8:00pm, corrupting the database. You now have the ability
> to restore the previous night's full backup, followed by the log backups
> that have been done since then. At most, you lost 5 minutes of data.
> The space inside the transaction log is "recycled", as committed
> transactions are flushed out (via a log backup), the space that they were
> using is made available for new transactions. This helps to keep the log
> file size under control. If you aren't flushing them out (i.e.
> TRUNCATING), the log has to grow larger to hold new transactions.
> SHRINKING is a different process - it will remove any free space from the
> log, physically reducing the size of the log file. You should only do
> this under extreme circumstances, it should NOT be part of your daily
> routine. Repeated shrinking/growing of a file (log or database) will lead
> to fragmentation, which will ultimately hurt performance. If you have the
> disk space, you should allow the log to grow as needed, making sure you're
> doing proper backups to keep the committed transactions flushed out.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||help wrote:
> once i have a full backup of the database why would i need the log files
> anymore? backupexec 9 indicates they will be removed during a full backup as
> i would expect, but it isn't happening on one of my servers. thx if you have
> more clues.
>
Technically, you don't need log files that predate a full backup,
however, it's good insurance to keep as many backups as you can afford
to keep. As the DBA, you're responsible for protecting the company's
data, so keep as many backups as you can.
As for why they're not being deleted by BackupExec, I have no idea. I
don't use third party backup tools like that.
Tracy McKibben
MCDBA
http://www.realsqlguy.com