Recovery Models

In an attempt to manage the size of the transaction log , I would like to use
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Regards
Ed.
No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>
|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>
|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas

"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

Recovery Models

In an attempt to manage the size of the transaction log , I would like to us
e
the
Simple verses the Full Recovery model. Other than the loss of transaction lo
g
backup and recovery only to the last full or differential backup, are
there any
other “landmines” I should consider? Thanks for being there to help. Reg
ards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

Recovery Models

In an attempt to manage the size of the transaction log , I would like to use
the
Simple verses the Full Recovery model. Other than the loss of transaction log
backup and recovery only to the last full or differential backup, are
there any
other â'landminesâ' I should consider? Thanks for being there to help. Regards
Ed.No landmines, but remember the tlog will still grow to accept the largest
single transaction you ever do, so the log might still get quite large, and
it does not shrink automatically.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
> use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
> log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
> Regards
> Ed.
>|||I thing that features that come with transaction log are many time worthier
than the time you lose learning how to deal with transaction log. So put
some effort on learning and you will have great benefit.
Regards,
Daniel
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
> In an attempt to manage the size of the transaction log , I would like to
use
> the
> Simple verses the Full Recovery model. Other than the loss of transaction
log
> backup and recovery only to the last full or differential backup, are
> there any
> other "landmines" I should consider? Thanks for being there to help.
Regards
> Ed.
>|||Dealing with the size of the transaction log is about as difficult as
dealing with any of the other maitenance plans. You are doing the other
maitenance options aren't you? Because if you are, the transaction log will
grow to the size of the single largest transaction, which is typically the
Database Reorganization plan, REINDEX.
Now, also consider what the transaction log really is. It is akin to the
general ledger of an accounting system. It records every action take
against a database the takes the database image from one snapshot to
another. The FULL and DIFFERENTIALS are only the snapshots. The
transaction logs are the actual work modifications that get you between the
two.
Sincerely,
Anthony Thomas
"coenzyme" <coenzyme@.discussions.microsoft.com> wrote in message
news:BB2640FA-431E-485B-8B30-B5D3B72DE5EA@.microsoft.com...
In an attempt to manage the size of the transaction log , I would like to
use
the
Simple verses the Full Recovery model. Other than the loss of transaction
log
backup and recovery only to the last full or differential backup, are
there any
other "landmines" I should consider? Thanks for being there to help.
Regards
Ed.

recovery mode and log size

hi friends,
after setting merge replication my log file is incresing continuouly.its going in gbs.what should be the optimum log file size for a database.what are the microsoft recommendations regarding this
what should be the best recovery mode to implement.it is simple,full,bulklogback.
which should be the efficient .
what is the microsoft recommendations for this
please help
thanks
reddy
Reddy,
the size of the transaction log depends on too many factors to recommend a fixed value. As you're using merge replication, this shouldn't be related to transactions not read from the log.
The frequency of the backup of the database depends on the normal factors - personally I backup the database each evening and the log every 30 mins.
If it is particularly large then you might increase the frequency of your log backups (if you're doing things this way). Generally, to remove committed transactions - backup the log, truncate the log or use simple recovery mode. To reduce the log size, ru
n DBCC SHRINGFILE.
HTH,
Paul Ibison
|||paul,
thanks for your solution
regards
reddy
"Paul Ibison" wrote:

> Reddy,
> the size of the transaction log depends on too many factors to recommend a fixed value. As you're using merge replication, this shouldn't be related to transactions not read from the log.
> The frequency of the backup of the database depends on the normal factors - personally I backup the database each evening and the log every 30 mins.
> If it is particularly large then you might increase the frequency of your log backups (if you're doing things this way). Generally, to remove committed transactions - backup the log, truncate the log or use simple recovery mode. To reduce the log size,
run DBCC SHRINGFILE.
> HTH,
> Paul Ibison

Recovering MDF file! Need Assistance!!

Somehow, our MS SQL 2000 Server database mdf file size is 0,
The database is in suspected state. and the backup file related to that database shown 0 size.
Its totaly weird as there are many databases in server group but just one database's mdf file and backup file size is 0.

Any idea how this can possibly happen and is there any way to recover it from this state

Thank you very muchTry running sp_resetstatus 'DBNAME' .
For a Suspect database the query "Select Status from master..Sysdatabases' would return 256.
The above sp should set it back to 0.

Also restoring the database file will turn off the suspect mode.|||reseting status did not help cause mdf files' physical size on the HD is zero bytes. the backup files size is as same as its

IS there a way to see a log file in sql to see what has happened?|||The only tool I have heard of that does this is Lumigent Log Explorer. It is fairly expensive though...

http://www.lumigent.com/products/le_sql/le_sql.htm|||My guess is that there is something wrong with your backup procedure (has it ever been tested) and that the zero length file has been restored.

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.
See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
AMB
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>
|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/d...br />
8dbn.asp
AMB
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combin
ed
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combin
ed
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
AMB
"Absolutely" wrote:
> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:
> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size more than 8060B

Hi,

In MS SQL Server, while creating the table, I am getting a warning
message saying like "maximum row size can exceed allowed maximum size
of 8060 bytes".

Is there any way in SQL Server, to increase this allowed maximum row
size?

The setting like "set ANSI_WARNINGS OFF" is not suitable in my case. I
need creation of table with around 11000 bytes in the record (Summation
of precision of all the columns). And more over, I do not want to loose
any data (truncation) in inert operation to keep on the whole record
size to 8060B.

Thanks a lot in advance.
Ramakrishna.Hi,

SQL Server is limited to 8060 bytes of data stored in row. Depending on
your SQL Server version some data types can be stored in row or outta
row, but for SQL Server if you want to have more than the mentioned
limit you either have to do a 1:1 relation with pulling out some data
to another table, or use data types (But I wouldnt prefer that as a
solution) that are not stored in row, than stored as pointers (like
text/ntext)

HTH, jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Am 6 Jun 2006 10:46:41 -0700 schrieb RamaKrishna Narla:

> In MS SQL Server, while creating the table, I am getting a warning
> message saying like "maximum row size can exceed allowed maximum size
> of 8060 bytes".
> Is there any way in SQL Server, to increase this allowed maximum row
> size?

The perfect solution would be changing to SQL Server 2005 (or SQLExpress).
There is a new datatype called VARCHAR(MAX) which can grow up till 2 Gb!
I would avoid TEXT for strings in any case. Read BOL to see which functions
do not work with TEXT, how to change TEXT (you need pointers!), ...

bye, Helmut|||On 6 Jun 2006 10:46:41 -0700, RamaKrishna Narla wrote:

>Hi,
>In MS SQL Server, while creating the table, I am getting a warning
>message saying like "maximum row size can exceed allowed maximum size
>of 8060 bytes".
>Is there any way in SQL Server, to increase this allowed maximum row
>size?

Hi Ramakrishna,

Helmut's suggestion of upgrading to SQL Server 2005 is spot on. Not only
because of the new VARCHAR(max) datatype, but also because SQL Server
2005 will automatically store part of the data on overflow pages if your
row size exceeds the 8060 byte limit.

If you're stuck on SQL Server 2000, you'll have to work around the
limitation. For instance by creating a second table that holds some of
the data. E.g.

CREATE TABLE Reports
(ReportID int NOT NULL,
FirstBigColumn varchar(4000) NOT NULL,
SecondBigColumn varchar(4000) NOT NULL,
PRIMARY KEY (ReportID)
)
CREATE TABLE ReportExtensions
(ReportID int NOT NULL,
ThirdBigColumn varchar(4000) NOT NULL,
FourthBigColumn varchar(4000) NOT NULL,
PRIMARY KEY (ReportID),
FOREIGN KEY (ReportID) REFERENCES Reports(ReportID)
)

--
Hugo Kornelis, SQL Server MVP