Full Text Search Index build is running too long

I did a DTS insert from a .CSV file and loaded 10 million records into a
table. I am doing a full text index on 3 columns in the table. The full
text index has been populating now for 24 hours continuous. How can I tell
if it's hung, or is it possible that it really takes that long to run? It
took only about 30 minutes to load 10 million records into the database.Brian,
Take a look at Current Activity - Process Info in Enterprise Manager. Might
find some useful insight there.
HTH
Jerry
"Brian Kitt" <BrianKitt@.discussions.microsoft.com> wrote in message
news:13440424-19D0-4E7D-9E23-1BFEB9513303@.microsoft.com...
>I did a DTS insert from a .CSV file and loaded 10 million records into a
> table. I am doing a full text index on 3 columns in the table. The full
> text index has been populating now for 24 hours continuous. How can I
> tell
> if it's hung, or is it possible that it really takes that long to run? It
> took only about 30 minutes to load 10 million records into the database.

Full Text Search Filters What is installed?

Is there a way to query and identify which file extensions have a Full Text
Search filter installed for SQL2005?
Thanks
TomLook up sys.fulltext_document_types catalog view in Books Online.
select *
from sys.fulltext_document_types
ML
http://milambda.blogspot.com/|||that only gives the iFilters which ship with SQL Server 2005. If you choose
to load the ones via
exec sp_fulltext_service 'load_os_resources', 1;
exec sp_fulltext_service 'verify_signature', 0;
go
you need to get a complete list via filtreg
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3388D19A-B26B-470A-9E08-497B93987A26@.microsoft.com...
> Look up sys.fulltext_document_types catalog view in Books Online.
> select *
> from sys.fulltext_document_types
>
> ML
> --
> http://milambda.blogspot.com/|||Good to know, thanks.
ML
http://milambda.blogspot.com/|||Hi ML,
I got the error when run the SQL:
Invalid object name 'sys.fulltext_document_types'
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3A88C201-4D67-4540-A064-23B553D2D932@.microsoft.com...
> Good to know, thanks.
>
> ML
> --
> http://milambda.blogspot.com/|||Are you trying this on SQL 2005?
ML
http://milambda.blogspot.com/

Full Text catalogue best drive to build on

We have a server with 2 drives. Drive 1 has our swap file, operating
system and SQL transaction log writing to it.
Drive 2 has our SQL database and flat file dumps (db backup)
We can't install a 3rd drive pair as there is not physical room in the
server. Which would be the better drive to have the FT catalogues built
on for best effeciency and speed of search?
We have 14GB of RAM on this box and our FT catalogue is only about
0.5GB. Would any of the catalogue stay memory resident or will there be
a lot of disk reads occuring?
Cheers
Julia
*** Sent via Developersdex http://www.codecomments.com ***
Try your second drive which stored the backups.
The caching mechanism used by SQL FTS is the file system cache. Frequently
requested catalog pages will be in the file system cache. Other pages will
be flushed to disk.
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
"Julia" <julia.nicholas@.nospamgmail.com> wrote in message
news:exZZSLStFHA.2212@.TK2MSFTNGP15.phx.gbl...
> We have a server with 2 drives. Drive 1 has our swap file, operating
> system and SQL transaction log writing to it.
> Drive 2 has our SQL database and flat file dumps (db backup)
> We can't install a 3rd drive pair as there is not physical room in the
> server. Which would be the better drive to have the FT catalogues built
> on for best effeciency and speed of search?
> We have 14GB of RAM on this box and our FT catalogue is only about
> 0.5GB. Would any of the catalogue stay memory resident or will there be
> a lot of disk reads occuring?
> Cheers
> Julia
>
> *** Sent via Developersdex http://www.codecomments.com ***

Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zip backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?
Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?
|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:

> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>

Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zi
p backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:5
9pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm.
At 12:30am I zip the file and remove. This zipped file goes with the previo
us days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to
remove inactive entries at each hour backup. Can I shrink the file after ea
ch hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of
the night it is ove 50gigs. Would a simple model work with a differential wi
th init option work better?
After each differential I would zip and remove the the backup to another dri
ve.
In the event of a disaster I would restore from the Full I did at 12:00am an
d use the last differential made in the current day.
"Hari Prasad" wrote:

> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup
.
> This controls the growth of transaction log file(LDF). Probably as you do
by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> zip backup and remove, shrink log file.
> 11:59pm
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> to remove inactive entries at each hour backup. Can I shrink the file afte
r
> each hour backup with out damaging the previous backup?
>
>

Full Model Recovery (Backup)

I run a FULL backup each morning at 12:00am, Steps are, check db, backup, zip backup and remove, shrink log file.
Along with the FULL backup I run Transaction log backups from 7:00am to 11:59pm
once an hour. Append the file. The file has grown to over 33gigs by 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with the previous days Full backup.
My question is, how can I keep the transaction log trimmed? I have it set to remove inactive entries at each hour backup. Can I shrink the file after each hour backup with out damaging the previous backup?Hi,
It seems you have got huge volume of transaction each hour, so rather than
doing the transaction log backup every 1 hour make it every 20 minutes or
so. Inthis case transaction log file will be cleared after each log backup.
This controls the growth of transaction log file(LDF). Probably as you do by
end of day after the full database backup you can shrink the file. The
shrink may not be required if you increase the transaction log backup
frequency.
Note:
I recommend you not to shrink the transaction log file each hour.
Thanks
Hari
MCDBA
"Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
zip backup and remove, shrink log file.
> Along with the FULL backup I run Transaction log backups from 7:00am to
11:59pm
> once an hour. Append the file. The file has grown to over 33gigs by
11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
the previous days Full backup.
> My question is, how can I keep the transaction log trimmed? I have it set
to remove inactive entries at each hour backup. Can I shrink the file after
each hour backup with out damaging the previous backup?|||Thanks, I guess the problem is the actual backup file growth, by the end of the night it is ove 50gigs. Would a simple model work with a differential with init option work better?
After each differential I would zip and remove the the backup to another drive.
In the event of a disaster I would restore from the Full I did at 12:00am and use the last differential made in the current day.
"Hari Prasad" wrote:
> Hi,
> It seems you have got huge volume of transaction each hour, so rather than
> doing the transaction log backup every 1 hour make it every 20 minutes or
> so. Inthis case transaction log file will be cleared after each log backup.
> This controls the growth of transaction log file(LDF). Probably as you do by
> end of day after the full database backup you can shrink the file. The
> shrink may not be required if you increase the transaction log backup
> frequency.
> Note:
> I recommend you not to shrink the transaction log file each hour.
> Thanks
> Hari
> MCDBA
>
> "Robert_at_CBB" <Robert_at_CBB@.discussions.microsoft.com> wrote in message
> news:2C16CE96-BF23-499B-A175-7F0552D66D08@.microsoft.com...
> > I run a FULL backup each morning at 12:00am, Steps are, check db, backup,
> zip backup and remove, shrink log file.
> >
> > Along with the FULL backup I run Transaction log backups from 7:00am to
> 11:59pm
> > once an hour. Append the file. The file has grown to over 33gigs by
> 11:59pm. At 12:30am I zip the file and remove. This zipped file goes with
> the previous days Full backup.
> >
> > My question is, how can I keep the transaction log trimmed? I have it set
> to remove inactive entries at each hour backup. Can I shrink the file after
> each hour backup with out damaging the previous backup?
>
>

full drives full trans log version 7

What does one do?
I tried "backup log mb with no_log" to truncate the file. I've now
started a shrinkdatabase. I can't backup since the drive is full. I
can't disconnect because it's 7.
How does one actually reduce the log file size?
thx
md
PS isn't simple logging supposed to avoid this problem?
PSS is it possible to set a DTS to not log?
*** Sent via Developersdex http://www.codecomments.com ***See if this helps:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/EN-US/
AMB
"M D" wrote:

> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Hi,
Since you dont require the transaction log backup you could truncate the log
first
BACKUP LOG DBNAME with TRUNCATE_ONLY
After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
If the usage is very low than SHRINK the LDF file using DBCC
SHRINKFILE('logical ldf name',size)
Please refer the link Alejandro pointed out.
Thanks
Hari
SQL Server MVP
"M D" <mardukes@.aol.com> wrote in message
news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.codecomments.com ***|||I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:

> Hi,
> Since you dont require the transaction log backup you could truncate the l
og
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
>|||Also run a DBCC OPENTRAN agains the database in question to make sure there
are no pending transactions to be completed.
The trasaction log architecture is different in SS7.0 versus SS2K and can be
quite difficult to shrink, even when using BACKUP LOG ... WITH NO_LOG or
WITH TRUNCATE_ONLY, which are synonomous by the way.
Usually, you will have to kill all processes but your own within the
database you are concerned with.
Execute sp_detach_db.
Delete the Transaction log file(s) for the database in question.
Execute sp_attach_single_file_db.
The new transaction log will be the size of the transaction log for the
model database.
Also know that regardless of Recovery Model, which didn't exist in
SS7.0-there were database options: 'trunc. log on chkpt.' and/or 'select
into/bulk copy'-EVERY TRANSACTION IS WRITTEN TO THE TRANSACTION LOG. There
is no way to stop this. The recovery settings just tell SQL Server what to
do with "COMPLETED" transactions within the transaction log file if they
have not been backed up yet.
If you are using 'trunc. log on chkpt.', then completed transactions are
automatically purged on the next CHECKPOINT process. This allows you to
write over that freed up space with the next transaction. However, your
transaction log will grow to support the largest single active transaction
or concurrent set of transactions.
If transactions are open, not completed or rolled back, then new
transactions can not overwrite those regardless of the recovery settings, or
db options, as the case may be.
Sincerely,
Anthony Thomas
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:8E5D6182-C9B9-4999-BBD7-5074AA5BF78A@.microsoft.com...
I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:

> Hi,
> Since you dont require the transaction log backup you could truncate the
log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
>
>

full drives full trans log version 7

What does one do?
I tried "backup log mb with no_log" to truncate the file. I've now
started a shrinkdatabase. I can't backup since the drive is full. I
can't disconnect because it's 7.
How does one actually reduce the log file size?
thx
md
PS isn't simple logging supposed to avoid this problem?
PSS is it possible to set a DTS to not log?
*** Sent via Developersdex http://www.developersdex.com ***See if this helps:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/kb/256650/EN-US/
AMB
"M D" wrote:
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Hi,
Since you dont require the transaction log backup you could truncate the log
first
BACKUP LOG DBNAME with TRUNCATE_ONLY
After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
If the usage is very low than SHRINK the LDF file using DBCC
SHRINKFILE('logical ldf name',size)
Please refer the link Alejandro pointed out.
Thanks
Hari
SQL Server MVP
"M D" <mardukes@.aol.com> wrote in message
news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> What does one do?
> I tried "backup log mb with no_log" to truncate the file. I've now
> started a shrinkdatabase. I can't backup since the drive is full. I
> can't disconnect because it's 7.
> How does one actually reduce the log file size?
> thx
> md
> PS isn't simple logging supposed to avoid this problem?
> PSS is it possible to set a DTS to not log?
> *** Sent via Developersdex http://www.developersdex.com ***|||I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:
> Hi,
> Since you dont require the transaction log backup you could truncate the log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> > What does one do?
> >
> > I tried "backup log mb with no_log" to truncate the file. I've now
> > started a shrinkdatabase. I can't backup since the drive is full. I
> > can't disconnect because it's 7.
> >
> > How does one actually reduce the log file size?
> >
> > thx
> > md
> >
> > PS isn't simple logging supposed to avoid this problem?
> > PSS is it possible to set a DTS to not log?
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>|||Also run a DBCC OPENTRAN agains the database in question to make sure there
are no pending transactions to be completed.
The trasaction log architecture is different in SS7.0 versus SS2K and can be
quite difficult to shrink, even when using BACKUP LOG ... WITH NO_LOG or
WITH TRUNCATE_ONLY, which are synonomous by the way.
Usually, you will have to kill all processes but your own within the
database you are concerned with.
Execute sp_detach_db.
Delete the Transaction log file(s) for the database in question.
Execute sp_attach_single_file_db.
The new transaction log will be the size of the transaction log for the
model database.
Also know that regardless of Recovery Model, which didn't exist in
SS7.0-there were database options: 'trunc. log on chkpt.' and/or 'select
into/bulk copy'-EVERY TRANSACTION IS WRITTEN TO THE TRANSACTION LOG. There
is no way to stop this. The recovery settings just tell SQL Server what to
do with "COMPLETED" transactions within the transaction log file if they
have not been backed up yet.
If you are using 'trunc. log on chkpt.', then completed transactions are
automatically purged on the next CHECKPOINT process. This allows you to
write over that freed up space with the next transaction. However, your
transaction log will grow to support the largest single active transaction
or concurrent set of transactions.
If transactions are open, not completed or rolled back, then new
transactions can not overwrite those regardless of the recovery settings, or
db options, as the case may be.
Sincerely,
Anthony Thomas
"NetByTelDBA" <NetByTelDBA@.discussions.microsoft.com> wrote in message
news:8E5D6182-C9B9-4999-BBD7-5074AA5BF78A@.microsoft.com...
I am new to this site, but would suggest a DB backup after you truncate the
log file, just to be safe.
"Hari Prasad" wrote:
> Hi,
> Since you dont require the transaction log backup you could truncate the
log
> first
> BACKUP LOG DBNAME with TRUNCATE_ONLY
> After that check the log usage by executing DBCC SQLPERF(LOGSPACE)
> If the usage is very low than SHRINK the LDF file using DBCC
> SHRINKFILE('logical ldf name',size)
> Please refer the link Alejandro pointed out.
> Thanks
> Hari
> SQL Server MVP
> "M D" <mardukes@.aol.com> wrote in message
> news:enQLS9rZFHA.2664@.TK2MSFTNGP15.phx.gbl...
> > What does one do?
> >
> > I tried "backup log mb with no_log" to truncate the file. I've now
> > started a shrinkdatabase. I can't backup since the drive is full. I
> > can't disconnect because it's 7.
> >
> > How does one actually reduce the log file size?
> >
> > thx
> > md
> >
> > PS isn't simple logging supposed to avoid this problem?
> > PSS is it possible to set a DTS to not log?
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>

Full Database backup size.....

When I take the full backup of the database the size of the backup file
varies.
Here is the scenario.
When I took for the first day, the size of the database was 1.5gb
Next day full back up size was 1.6gb(it had today's business data)
Third day full bakup is 1.4gb (it had the third day business data)
None of my application deletes the data from the above database.
Is it not the size of the backup file should increase as the data in the
database grows?
Is there any other factor (like shrinking etc....) is affecting the size of
the backup file.
SQL2K.
Just curious.
Thanks,
AllenThe size of the backup can also fluctuate based on non-data space being
consumed by the database. For example, if the data device is 15gb, 10gb in
use by data and 5gb free and/or the log is 5gb, with 2gb in use and 3gb free,
the overall backup size will be different/larger then if there was no
unused/white-space in the DBs devices.
"AllenHubatka" wrote:
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>
>|||if the Dboption you have chosen Is it Full or Simple and If set to
simple The Log file Will be Shrunk when it Reaches more than 70% on a
checkpoint and also if you have checked autoShrink option both the
data and log file will be shrink ,its a background operation which
happens when u r server isnt busy enough.
Thanks,
Saradhi|||The database backup process goes to the Tlog to capture all of the
transactions since the backup began... which can increase the size...
--
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
"AllenHubatka" <AllenHubtka_67@.hotmail.com> wrote in message
news:OrGCxLZqFHA.2596@.TK2MSFTNGP09.phx.gbl...
> When I take the full backup of the database the size of the backup file
> varies.
> Here is the scenario.
> When I took for the first day, the size of the database was 1.5gb
> Next day full back up size was 1.6gb(it had today's business data)
> Third day full bakup is 1.4gb (it had the third day business data)
> None of my application deletes the data from the above database.
> Is it not the size of the backup file should increase as the data in the
> database grows?
> Is there any other factor (like shrinking etc....) is affecting the size
> of
> the backup file.
> SQL2K.
> Just curious.
> Thanks,
> Allen
>

Full backup to a different file name

I may be missing something here but is it possible to do a full database
backup each night but to a different file name so as not to overwrite the
existing full backup. The database is relatively small (200Meg) and we have
lots of hard drive space available. Of course we will need to manually
delete the old backups occasionally but we are happy to do that.
I know that I can do either a differential backup or a transaction log
backup but this will make the restoration process slower. Often we just
want to quickly restore a a database fron two days ago just to check we did
not screw something up.
Should I be looking towards 3rd party software?
Dave A
Dave
You can call a backup whatever you like. So yes you can call it what you
like. One of the easiest ways to do it, is to give it a base name and add the
date to it. This creates a file something like mydatabase_ddmmyy.bak (or
mydatabase_mmddyy.bak for our American friends)
Hope this helps
John
"Dave A" wrote:

> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>
|||John,
I want to schedule the backup to run everynight automatically. I don't want
to retype the name in each time. Sorry, I should have been more clear.
Regards
Dave A
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the[vbcol=seagreen]
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
> Hope this helps
> John
> "Dave A" wrote:
the[vbcol=seagreen]
have[vbcol=seagreen]
did[vbcol=seagreen]
|||The easiest way to do this is to set up a Maintenance Plan ... The
maintenance plan wizard will create a unique name with the date and time
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
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:OuF4BYgAFHA.1404@.TK2MSFTNGP11.phx.gbl...
> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we
have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we
did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>
|||"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
You can use a maintenance job that handles this or roll your own code.
And here in America I prefer YYYYMMDD :-)
Just use datepart to assemble the file name.
[vbcol=seagreen]
> Hope this helps
> John
> "Dave A" wrote:
the[vbcol=seagreen]
have[vbcol=seagreen]
did[vbcol=seagreen]

Full backup to a different file name

I may be missing something here but is it possible to do a full database
backup each night but to a different file name so as not to overwrite the
existing full backup. The database is relatively small (200Meg) and we have
lots of hard drive space available. Of course we will need to manually
delete the old backups occasionally but we are happy to do that.
I know that I can do either a differential backup or a transaction log
backup but this will make the restoration process slower. Often we just
want to quickly restore a a database fron two days ago just to check we did
not screw something up.
Should I be looking towards 3rd party software?
Dave ADave
You can call a backup whatever you like. So yes you can call it what you
like. One of the easiest ways to do it, is to give it a base name and add th
e
date to it. This creates a file something like mydatabase_ddmmyy.bak (or
mydatabase_mmddyy.bak for our American friends)
Hope this helps
John
"Dave A" wrote:

> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we ha
ve
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we di
d
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>|||John,
I want to schedule the backup to run everynight automatically. I don't want
to retype the name in each time. Sorry, I should have been more clear.
Regards
Dave A
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the[vbcol=seagreen]
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
> Hope this helps
> John
> "Dave A" wrote:
>
the[vbcol=seagreen]
have[vbcol=seagreen]
did[vbcol=seagreen]|||The easiest way to do this is to set up a Maintenance Plan ... The
maintenance plan wizard will create a unique name with the date and time
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
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:OuF4BYgAFHA.1404@.TK2MSFTNGP11.phx.gbl...
> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we
have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we
did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>|||"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
You can use a maintenance job that handles this or roll your own code.
And here in America I prefer YYYYMMDD :-)
Just use datepart to assemble the file name.
[vbcol=seagreen]
> Hope this helps
> John
> "Dave A" wrote:
>
the[vbcol=seagreen]
have[vbcol=seagreen]
did[vbcol=seagreen]

Full backup to a different file name

I may be missing something here but is it possible to do a full database
backup each night but to a different file name so as not to overwrite the
existing full backup. The database is relatively small (200Meg) and we have
lots of hard drive space available. Of course we will need to manually
delete the old backups occasionally but we are happy to do that.
I know that I can do either a differential backup or a transaction log
backup but this will make the restoration process slower. Often we just
want to quickly restore a a database fron two days ago just to check we did
not screw something up.
Should I be looking towards 3rd party software?
Dave ADave
You can call a backup whatever you like. So yes you can call it what you
like. One of the easiest ways to do it, is to give it a base name and add the
date to it. This creates a file something like mydatabase_ddmmyy.bak (or
mydatabase_mmddyy.bak for our American friends)
Hope this helps
John
"Dave A" wrote:
> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>|||John,
I want to schedule the backup to run everynight automatically. I don't want
to retype the name in each time. Sorry, I should have been more clear.
Regards
Dave A
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
> Hope this helps
> John
> "Dave A" wrote:
> > I may be missing something here but is it possible to do a full database
> > backup each night but to a different file name so as not to overwrite
the
> > existing full backup. The database is relatively small (200Meg) and we
have
> > lots of hard drive space available. Of course we will need to manually
> > delete the old backups occasionally but we are happy to do that.
> >
> > I know that I can do either a differential backup or a transaction log
> > backup but this will make the restoration process slower. Often we just
> > want to quickly restore a a database fron two days ago just to check we
did
> > not screw something up.
> >
> > Should I be looking towards 3rd party software?
> >
> > Dave A
> >
> >
> >
> >|||The easiest way to do this is to set up a Maintenance Plan ... The
maintenance plan wizard will create a unique name with the date and time
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
"Dave A" <dave@.sigmasolutionsdonotspamme.com.au> wrote in message
news:OuF4BYgAFHA.1404@.TK2MSFTNGP11.phx.gbl...
> I may be missing something here but is it possible to do a full database
> backup each night but to a different file name so as not to overwrite the
> existing full backup. The database is relatively small (200Meg) and we
have
> lots of hard drive space available. Of course we will need to manually
> delete the old backups occasionally but we are happy to do that.
> I know that I can do either a differential backup or a transaction log
> backup but this will make the restoration process slower. Often we just
> want to quickly restore a a database fron two days ago just to check we
did
> not screw something up.
> Should I be looking towards 3rd party software?
> Dave A
>
>|||"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:BC4DD730-7D65-4866-9652-8AD7D61EB44F@.microsoft.com...
> Dave
> You can call a backup whatever you like. So yes you can call it what you
> like. One of the easiest ways to do it, is to give it a base name and add
the
> date to it. This creates a file something like mydatabase_ddmmyy.bak (or
> mydatabase_mmddyy.bak for our American friends)
You can use a maintenance job that handles this or roll your own code.
And here in America I prefer YYYYMMDD :-)
Just use datepart to assemble the file name.
> Hope this helps
> John
> "Dave A" wrote:
> > I may be missing something here but is it possible to do a full database
> > backup each night but to a different file name so as not to overwrite
the
> > existing full backup. The database is relatively small (200Meg) and we
have
> > lots of hard drive space available. Of course we will need to manually
> > delete the old backups occasionally but we are happy to do that.
> >
> > I know that I can do either a differential backup or a transaction log
> > backup but this will make the restoration process slower. Often we just
> > want to quickly restore a a database fron two days ago just to check we
did
> > not screw something up.
> >
> > Should I be looking towards 3rd party software?
> >
> > Dave A
> >
> >
> >
> >

full backup database files includes transaction log?

when I complete full database backup ,
I want to know this full backup file includes transanction log ?lovexueer@.gmail.com wrote:
> when I complete full database backup ,
> I want to know this full backup file includes transanction log ?
>
Short answer - No.
You'll have to do a seperate log backup in order to get your logfile
backed up - if you need it. If your database is in SIMPLE recovery mode,
you don't need the log backup and SQL server won't let you back it up
either.
When that's said, SQL server do backup a little part of the logfile
during a FULL backup, but it's only so much that it can recreate the
logfile when you restore the backup.
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator

full backup database files includes transaction log?

when I complete full database backup ,
I want to know this full backup file includes transanction log ?lovexueer@.gmail.com wrote:
> when I complete full database backup ,
> I want to know this full backup file includes transanction log ?
No, the contents of the transaction log is not included in a full
backup. Only the current state (of all data, indexes, as well as the
other objects) is included.
However, when restoring a full backup, the transaction log file is
expanded to the size it was when the backup was taken (so this may make
you think the transaction log was actually restored, when it in fact
was not).
Razvan|||This is a multi-part message in MIME format.
--000301030603010101030205
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
lovexueer@.gmail.com wrote:
> when I complete full database backup ,
> I want to know this full backup file includes transanction log ?
>
Short answer - No.
You'll have to do a seperate log backup in order to get your logfile
backed up - if you need it. If your database is in SIMPLE recovery mode,
you don't need the log backup and SQL server won't let you back it up
either.
When that's said, SQL server do backup a little part of the logfile
during a FULL backup, but it's only so much that it can recreate the
logfile when you restore the backup.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--000301030603010101030205
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:lovexueer@.gmail.com">lovexueer@.gmail.com</a> wrote:
<blockquote
cite="mid1152689474.604519.28620@.m79g2000cwm.googlegroups.com"
type="cite">
<pre wrap="">when I complete full database backup ,
I want to know this full backup file includes transanction log ?
</pre>
</blockquote>
<font size="-1"><font face="Arial">Short answer - No. <br>
You'll have to do a seperate log backup in order to get your logfile
backed up - if you need it. If your database is in SIMPLE recovery
mode, you don't need the log backup and SQL server won't let you back
it up either.<br>
<br>
When that's said, SQL server do backup a little part of the logfile
during a FULL backup, but it's only so much that it can recreate the
logfile when you restore the backup.<br>
<br>
<br>
-- <br>
Regards<br>
Steen Schlüter Persson<br>
Databaseadministrator / Systemadministrator<br>
<br>
</font></font>
</body>
</html>
--000301030603010101030205--|||Steen Persson (DK) wrote:
> lovexueer@.gmail.com wrote:
> > when I complete full database backup ,
> >
> > I want to know this full backup file includes transanction log ?
> >
> >
> Short answer - No.
> You'll have to do a seperate log backup in order to get your logfile
> backed up - if you need it. If your database is in SIMPLE recovery mode,
> you don't need the log backup and SQL server won't let you back it up
> either.
> When that's said, SQL server do backup a little part of the logfile
> during a FULL backup, but it's only so much that it can recreate the
> logfile when you restore the backup.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
>
> --000301030603010101030205
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 1148
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:lovexueer@.gmail.com"=>lovexueer@.gmail.com</a> wrote:
> <blockquote
> cite=3D"mid1152689474.604519.28620@.m79g2000cwm.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">when I complete full database backup ,
> I want to know this full backup file includes transanction log ?
> </pre>
> </blockquote>
> <font size=3D"-1"><font face=3D"Arial">Short answer - No. <br>
> You'll have to do a seperate log backup in order to get your logfile
> backed up - if you need it. If your database is in SIMPLE recovery
> mode, you don't need the log backup and SQL server won't let you back
> it up either.<br>
> <br>
> When that's said, SQL server do backup a little part of the logfile
> during a FULL backup, but it's only so much that it can recreate the
> logfile when you restore the backup.<br>
> <br>
> <br>
> -- <br>
> Regards<br>
> Steen Schlüter Persson<br>
> Databaseadministrator / Systemadministrator<br>
> <br>
> </font></font>
> </body>
> </html>
> --000301030603010101030205--
thank you very much ,
but what is a little part of logfile?|||It records the transactions from the log that occured while the backup was
taking place. This ensures the backup is complete and up to date when it
finishes.
"lovexueer@.gmail.com" wrote:
> Steen Persson (DK) wrote:
> > lovexueer@.gmail.com wrote:
> > > when I complete full database backup ,
> > >
> > > I want to know this full backup file includes transanction log ?
> > >
> > >
> > Short answer - No.
> > You'll have to do a seperate log backup in order to get your logfile
> > backed up - if you need it. If your database is in SIMPLE recovery mode,
> > you don't need the log backup and SQL server won't let you back it up
> > either.
> >
> > When that's said, SQL server do backup a little part of the logfile
> > during a FULL backup, but it's only so much that it can recreate the
> > logfile when you restore the backup.
> >
> >
> > --
> > Regards
> > Steen Schlüter Persson
> > Databaseadministrator / Systemadministrator
> >
> >
> > --000301030603010101030205
> > Content-Type: text/html; charset=ISO-8859-1
> > X-Google-AttachSize: 1148
> >
> > <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> > <html>
> > <head>
> > <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
> > </head>
> > <body bgcolor="#ffffff" text="#000000">
> > <a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:lovexueer@.gmail.com">lovexueer@.gmail.com</a> wrote:
> > <blockquote
> > cite="mid1152689474.604519.28620@.m79g2000cwm.googlegroups.com"
> > type="cite">
> > <pre wrap="">when I complete full database backup ,
> >
> > I want to know this full backup file includes transanction log ?
> >
> > </pre>
> > </blockquote>
> > <font size="-1"><font face="Arial">Short answer - No. <br>
> > You'll have to do a seperate log backup in order to get your logfile
> > backed up - if you need it. If your database is in SIMPLE recovery
> > mode, you don't need the log backup and SQL server won't let you back
> > it up either.<br>
> > <br>
> > When that's said, SQL server do backup a little part of the logfile
> > during a FULL backup, but it's only so much that it can recreate the
> > logfile when you restore the backup.<br>
> > <br>
> > <br>
> > -- <br>
> > Regards<br>
> > Steen Schlüter Persson<br>
> > Databaseadministrator / Systemadministrator<br>
> > <br>
> > </font></font>
> > </body>
> > </html>
> >
> > --000301030603010101030205--
>
> thank you very much ,
> but what is a little part of logfile?
>|||Steen Persson (DK) wrote:
> > When that's said, SQL server do backup a little part of the logfile
> > during a FULL backup, but it's only so much that it can recreate the
> > logfile when you restore the backup.
lovexueer@.gmail.com wrote:
> but what is a little part of logfile?
The part which contains the transactions executed between the start of
the backup process and the end of it (usually this is several seconds
or minutes, depending on the size of the database).
Razvan|||Razvan Socol wrote:
> Steen Persson (DK) wrote:
> > > When that's said, SQL server do backup a little part of the logfile
> > > during a FULL backup, but it's only so much that it can recreate the
> > > logfile when you restore the backup.
> lovexueer@.gmail.com wrote:
> > but what is a little part of logfile?
> The part which contains the transactions executed between the start of
> the backup process and the end of it (usually this is several seconds
> or minutes, depending on the size of the database).
> Razvan
I meant that the part contains transaction log is drity pages?
the dirty pages is uncommited for datafile.|||lovexueer@.gmail.com wrote:
> Razvan Socol wrote:
> > Steen Persson (DK) wrote:
> > > > When that's said, SQL server do backup a little part of the logfile
> > > > during a FULL backup, but it's only so much that it can recreate the
> > > > logfile when you restore the backup.
> >
> > lovexueer@.gmail.com wrote:
> > > but what is a little part of logfile?
> >
> > The part which contains the transactions executed between the start of
> > the backup process and the end of it (usually this is several seconds
> > or minutes, depending on the size of the database).
> >
> > Razvan
>
> I meant that the part contains transaction log is drity pages?
> the dirty pages is uncommited for datafile.
The transaction log pages that are contained in a full backup, may or
may not be committed in the actual backup file. In other words, they
were not committed to the data file when the backup was started, but
they were committed to the data file (or should have been committed,
i.e. they were dirty pages) when the backup was finished. Since it's
not clear if the those data pages were copied in the backup file before
or after they were modified, the log pages are stored in the backup
file too, so when the backup is restored, the database can be recovered
in a consistent state.
Razvan|||Razvan Socol wrote:
> lovexueer@.gmail.com wrote:
> > Razvan Socol wrote:
> > > Steen Persson (DK) wrote:
> > > > > When that's said, SQL server do backup a little part of the logfile
> > > > > during a FULL backup, but it's only so much that it can recreate the
> > > > > logfile when you restore the backup.
> > >
> > > lovexueer@.gmail.com wrote:
> > > > but what is a little part of logfile?
> > >
> > > The part which contains the transactions executed between the start of
> > > the backup process and the end of it (usually this is several seconds
> > > or minutes, depending on the size of the database).
> > >
> > > Razvan
> >
> >
> >
> > I meant that the part contains transaction log is drity pages?
> > the dirty pages is uncommited for datafile.
> The transaction log pages that are contained in a full backup, may or
> may not be committed in the actual backup file. In other words, they
> were not committed to the data file when the backup was started, but
> they were committed to the data file (or should have been committed,
> i.e. they were dirty pages) when the backup was finished. Since it's
> not clear if the those data pages were copied in the backup file before
> or after they were modified, the log pages are stored in the backup
> file too, so when the backup is restored, the database can be recovered
> in a consistent state.
> Razvan
thanks great!

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

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 successfu
l,
> they should be truncating the log file.
> I can backup log with truncate_only and then issue a dbcc shrinkfile to ge
t
> 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 issu
e
> 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 a
s
i would expect, but it isn't happening on one of my servers. thx if you hav
e
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 h
ave
> 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 and Diff Backups

If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1 hour while another 5 Gig is added in transactions to the log file, The Backup should complete leaving only the new 5 Gig of transactions while the previous 10 Gig were applied to t
he backup. Basically backing up everything upto and including the Backup statement.
Does the same action take place on the transaction log during a differential backup? Does a differential backup include every transaction on the log file upto and including the backup statement?
>> Basically backing up everything upto and including the Backup statement.
Not exactly. From Inside SQL Server 2000:
'To maintain consistency for either a full or a differential backup, SQL
Server records the current log sequence number (LSN) at the time the backup
starts and then again at the time the backup ends. This allows the backup to
also capture the relevant parts of the log. The relevant part starts with
the oldest open transaction at the time of the first recorded LSN and ends
with the second recorded LSN.'
Thus, the 5 Gig of 'new' trxs will also be present in the log, but will only
be applied upon restore if they were committed at the time the backup ended,
assuming you restore with the RECOVERY option.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:2A52A0B3-23D5-4771-8170-3BE8CFF8330C@.microsoft.com...
> If I run a full Bkp when the Log file is 10 Gig, and the backup runs for 1
hour while another 5 Gig is added in transactions to the log file, The
Backup should complete leaving only the new 5 Gig of transactions while the
previous 10 Gig were applied to the backup. Basically backing up everything
upto and including the Backup statement.
> Does the same action take place on the transaction log during a
differential backup? Does a differential backup include every transaction on
the log file upto and including the backup statement?
>