backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larger than the original working database (approx 4GB). This doesn't seems logical to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
Bernard Goh
I would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||I would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh
|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh

backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larg
er than the original working database (approx 4GB). This doesn't seems logic
al to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
--
Bernard GohI would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
----
--
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Trun
cateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Trun
cateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh

backup file much larger than the actual working database file

Hi All,
I am having a problem with the backup file (approx 70+ GB) that is much larger than the original working database (approx 4GB). This doesn't seems logical to me, anyone knows what causes this strange situations ?
following is my database backup setting
- Full Database Backup
- Overwrite Media
- Backup to physical disc on the same server
Many thanks in advance.
--
Bernard GohI would guess there is more than 1 backup set in that file. If you were to
run the RESTORE HEADERONLY command on the backup file, how many entries do
you see?
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Goh|||Hi,
Can you execute the below command to check the number of backup sets you
have in your backup file:-
RESTORE headeronly from disk='c:\FILENAME.BAK'
This will show the number of backup files in the backfile. Since you
selected the "Overwrite media", there should be only one file.
To identify the exact backup file size, Execute the below command from Query
Analyzer:-
BACKUP DATABASE <dbname> to disk='d:\backup\dbname.bak' with
,stats=10 -- INIT will overwrite the backup file
Give the drive letter and folder based on ur availability , after the
execution check the file size.
----
--
Can you check the transaction log size as well. How to check the log size
DBCC SQLPERF(LOGSPACE)
If you have a big log file then backup the transaction log file and shrink
the trasnaction log file.
How to shrink the transaction log file if your database is FULL recovery
model.
1. Backup the Transaction Log using (Backup log command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the transaction log file if your database is Simple recovery
model.
1. Truncate the Transaction Log using (Backup log dbname with truncate_only
command)
2. Use DBCC SHRINKFILE('logical_tran_log_name','Truncateonly')
How to shrink the data files
1. Check any open transactions or there using DBCC OPENTRAN(DBname)
2. If not, Run DBCC SHRINKFILE('logical_datafile_name',size)
--
Thanks
Hari
MCDBA
"SummerCoke" <SummerCoke@.discussions.microsoft.com> wrote in message
news:A150B725-F523-4152-9E53-D1DC71815A94@.microsoft.com...
> Hi All,
> I am having a problem with the backup file (approx 70+ GB) that is much
larger than the original working database (approx 4GB). This doesn't seems
logical to me, anyone knows what causes this strange situations ?
> following is my database backup setting
> - Full Database Backup
> - Overwrite Media
> - Backup to physical disc on the same server
> Many thanks in advance.
> --
> Bernard Gohsql

Backup and Restore issue

I have a strange case. I do a full backup this morning. The size of
the backup is about 300MB which is 4 times larger than the actual
database file. Now when I restore the backup, some of the objects (eg
some tables) are not there!!.
Now I just do another backup, the size of this backup is now about
80MB, which is normal and when I restore this backup, I got all the
tables.
What am I missing? Did someone has similar experience?
Probably earlier you would have appended to a older backup file (WITH NOINIT
which is default).
That caused the backup file to show a bigger size. While restoring you
restore the first backup file
which was taken some days back and that do not have some new tables.
This is just my assumption. If you have the backup file which you has issues
just try
RESTORE HEADERONLY FROM DISK='Backupfilename.BAK'
The above command will give you all the backup sets in the backup file.
Thanks
Hari
"akkha1234@.gmail.com" wrote:

> I have a strange case. I do a full backup this morning. The size of
> the backup is about 300MB which is 4 times larger than the actual
> database file. Now when I restore the backup, some of the objects (eg
> some tables) are not there!!.
> Now I just do another backup, the size of this backup is now about
> 80MB, which is normal and when I restore this backup, I got all the
> tables.
> What am I missing? Did someone has similar experience?
>
|||Dear Hari,
You are absolutely correct. Learn one more thing on MSSQL again.

Backup and Restore issue

I have a strange case. I do a full backup this morning. The size of
the backup is about 300MB which is 4 times larger than the actual
database file. Now when I restore the backup, some of the objects (eg
some tables) are not there!!.
Now I just do another backup, the size of this backup is now about
80MB, which is normal and when I restore this backup, I got all the
tables.
What am I missing? Did someone has similar experience?Probably earlier you would have appended to a older backup file (WITH NOINIT
which is default).
That caused the backup file to show a bigger size. While restoring you
restore the first backup file
which was taken some days back and that do not have some new tables.
This is just my assumption. If you have the backup file which you has issues
just try
RESTORE HEADERONLY FROM DISK='Backupfilename.BAK'
The above command will give you all the backup sets in the backup file.
Thanks
Hari
"akkha1234@.gmail.com" wrote:
> I have a strange case. I do a full backup this morning. The size of
> the backup is about 300MB which is 4 times larger than the actual
> database file. Now when I restore the backup, some of the objects (eg
> some tables) are not there!!.
> Now I just do another backup, the size of this backup is now about
> 80MB, which is normal and when I restore this backup, I got all the
> tables.
> What am I missing? Did someone has similar experience?
>|||Dear Hari,
You are absolutely correct. Learn one more thing on MSSQL again.|||Thats good to know...
Thanks
Hari
<akkha1234@.gmail.com> wrote in message
news:1170369045.337711.234660@.l53g2000cwa.googlegroups.com...
> Dear Hari,
> You are absolutely correct. Learn one more thing on MSSQL again.
>

Backup and Restore issue

I have a strange case. I do a full backup this morning. The size of
the backup is about 300MB which is 4 times larger than the actual
database file. Now when I restore the backup, some of the objects (eg
some tables) are not there!!.
Now I just do another backup, the size of this backup is now about
80MB, which is normal and when I restore this backup, I got all the
tables.
What am I missing? Did someone has similar experience?Probably earlier you would have appended to a older backup file (WITH NOINIT
which is default).
That caused the backup file to show a bigger size. While restoring you
restore the first backup file
which was taken some days back and that do not have some new tables.
This is just my assumption. If you have the backup file which you has issues
just try
RESTORE HEADERONLY FROM DISK='Backupfilename.BAK'
The above command will give you all the backup sets in the backup file.
Thanks
Hari
"akkha1234@.gmail.com" wrote:

> I have a strange case. I do a full backup this morning. The size of
> the backup is about 300MB which is 4 times larger than the actual
> database file. Now when I restore the backup, some of the objects (eg
> some tables) are not there!!.
> Now I just do another backup, the size of this backup is now about
> 80MB, which is normal and when I restore this backup, I got all the
> tables.
> What am I missing? Did someone has similar experience?
>|||Dear Hari,
You are absolutely correct. Learn one more thing on MSSQL again.|||Thats good to know...
Thanks
Hari
<akkha1234@.gmail.com> wrote in message
news:1170369045.337711.234660@.l53g2000cwa.googlegroups.com...
> Dear Hari,
> You are absolutely correct. Learn one more thing on MSSQL again.
>

backup / trn file size.

I noticed recently that my data fike was quite large, and that the Log file
is even larger.
I have a 1.1 Gb Data file and a 12.8 Gb log file.
I noticed the grand sizes because of my backups, the bak file (done every 12
hours, grows by about 200mb every day. I am having a hard time believing my
400 odd users are pumping that much data in.
The Log and Data files are set to grow unrestricted, by 10% a go.
I have tried DBCC ShrinkFile and DBCC ShrinkDatabase, neither of which had
any effect on the DB size. My most recent attempt : DBCC SHRINKFile
(vmclean_log,20) gave me:
CurrentSize: 1634992
MinimumSize:128
UsedPages:1634992
Est Pages:128
I have 2 questions:
1) Recovery is set to full. Could I change this to Simple and be satisfied
that in event of failure I can recover my 12 hour backup and apply my 3 hour
Log backups to it to get back to the point of failure?
2) How can I find out what may be causing this?
Thanks
---
Duncan LoxtonDuncan
Have you tried to BACKUP LOG file before you issued DBCC SHRINKFILE.?
Look at DBCC LOGINFO(dbid) command to make sure you don't have transactions
with status=2 at the end of the list. This is causing to the LOG file not to
be shrinking.
"Duncan Loxton" <me@.privacy.com.au> wrote in message
news:2h31nmF7t7caU1@.uni-berlin.de...
> I noticed recently that my data fike was quite large, and that the Log
file
> is even larger.
> I have a 1.1 Gb Data file and a 12.8 Gb log file.
> I noticed the grand sizes because of my backups, the bak file (done every
12
> hours, grows by about 200mb every day. I am having a hard time believing
my
> 400 odd users are pumping that much data in.
> The Log and Data files are set to grow unrestricted, by 10% a go.
> I have tried DBCC ShrinkFile and DBCC ShrinkDatabase, neither of which had
> any effect on the DB size. My most recent attempt : DBCC SHRINKFile
> (vmclean_log,20) gave me:
> CurrentSize: 1634992
> MinimumSize:128
> UsedPages:1634992
> Est Pages:128
> I have 2 questions:
> 1) Recovery is set to full. Could I change this to Simple and be satisfied
> that in event of failure I can recover my 12 hour backup and apply my 3
hour
> Log backups to it to get back to the point of failure?
> 2) How can I find out what may be causing this?
> Thanks
> --
> ---
> Duncan Loxton
>|||Uri,
I checked Loginfo, and there are about 491 with status =2. I synched the
publication, and it still had the exact same number after the synch.
I just came across in BOLa reference to the Log Reader Agent. It looks like
this does the job of changing the status of pages that have been replicated.
I am running Merge Rep, but dont have a Log Reader Agent - should I have
one?
Surely these transactions with status =2 should be removed once the synch
has happened - but they arent..... What can I do?
Thanks!
duncan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtCFGfkPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Duncan
> Have you tried to BACKUP LOG file before you issued DBCC SHRINKFILE.?
> Look at DBCC LOGINFO(dbid) command to make sure you don't have
transactions
> with status=2 at the end of the list. This is causing to the LOG file not
to
> be shrinking.
>
> "Duncan Loxton" <me@.privacy.com.au> wrote in message
> news:2h31nmF7t7caU1@.uni-berlin.de...
> file
every[vbcol=seagreen]
> 12
believing[vbcol=seagreen]
> my
had[vbcol=seagreen]
satisfied[vbcol=seagreen]
> hour
>|||Duncan
In order to move these transaction at the top of the list you have to
perform dummy insertion.
Create a table and with simple while...loop perfom an INSERT statement into
the table and then you will see that transction with status=2 is moved to
the top of the list.
"Duncan" <me@.privacy.com.au> wrote in message
news:2h4vn8F8v2ouU1@.uni-berlin.de...
> Uri,
> I checked Loginfo, and there are about 491 with status =2. I synched the
> publication, and it still had the exact same number after the synch.
> I just came across in BOLa reference to the Log Reader Agent. It looks
like
> this does the job of changing the status of pages that have been
replicated.
> I am running Merge Rep, but dont have a Log Reader Agent - should I have
> one?
> Surely these transactions with status =2 should be removed once the synch
> has happened - but they arent..... What can I do?
> Thanks!
> duncan
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtCFGfkPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> transactions
not[vbcol=seagreen]
> to
> every
> believing
> had
> satisfied
3[vbcol=seagreen]
>

backup / trn file size.

I noticed recently that my data fike was quite large, and that the Log file
is even larger.
I have a 1.1 Gb Data file and a 12.8 Gb log file.
I noticed the grand sizes because of my backups, the bak file (done every 12
hours, grows by about 200mb every day. I am having a hard time believing my
400 odd users are pumping that much data in.
The Log and Data files are set to grow unrestricted, by 10% a go.
I have tried DBCC ShrinkFile and DBCC ShrinkDatabase, neither of which had
any effect on the DB size. My most recent attempt : DBCC SHRINKFile
(vmclean_log,20) gave me:
CurrentSize: 1634992
MinimumSize:128
UsedPages:1634992
Est Pages:128
I have 2 questions:
1) Recovery is set to full. Could I change this to Simple and be satisfied
that in event of failure I can recover my 12 hour backup and apply my 3 hour
Log backups to it to get back to the point of failure?
2) How can I find out what may be causing this?
Thanks
--
---
Duncan LoxtonDuncan
Have you tried to BACKUP LOG file before you issued DBCC SHRINKFILE.?
Look at DBCC LOGINFO(dbid) command to make sure you don't have transactions
with status=2 at the end of the list. This is causing to the LOG file not to
be shrinking.
"Duncan Loxton" <me@.privacy.com.au> wrote in message
news:2h31nmF7t7caU1@.uni-berlin.de...
> I noticed recently that my data fike was quite large, and that the Log
file
> is even larger.
> I have a 1.1 Gb Data file and a 12.8 Gb log file.
> I noticed the grand sizes because of my backups, the bak file (done every
12
> hours, grows by about 200mb every day. I am having a hard time believing
my
> 400 odd users are pumping that much data in.
> The Log and Data files are set to grow unrestricted, by 10% a go.
> I have tried DBCC ShrinkFile and DBCC ShrinkDatabase, neither of which had
> any effect on the DB size. My most recent attempt : DBCC SHRINKFile
> (vmclean_log,20) gave me:
> CurrentSize: 1634992
> MinimumSize:128
> UsedPages:1634992
> Est Pages:128
> I have 2 questions:
> 1) Recovery is set to full. Could I change this to Simple and be satisfied
> that in event of failure I can recover my 12 hour backup and apply my 3
hour
> Log backups to it to get back to the point of failure?
> 2) How can I find out what may be causing this?
> Thanks
> --
> ---
> Duncan Loxton
>|||Uri,
I checked Loginfo, and there are about 491 with status =2. I synched the
publication, and it still had the exact same number after the synch.
I just came across in BOLa reference to the Log Reader Agent. It looks like
this does the job of changing the status of pages that have been replicated.
I am running Merge Rep, but dont have a Log Reader Agent - should I have
one?
Surely these transactions with status =2 should be removed once the synch
has happened - but they arent..... What can I do?
Thanks!
duncan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtCFGfkPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Duncan
> Have you tried to BACKUP LOG file before you issued DBCC SHRINKFILE.?
> Look at DBCC LOGINFO(dbid) command to make sure you don't have
transactions
> with status=2 at the end of the list. This is causing to the LOG file not
to
> be shrinking.
>
> "Duncan Loxton" <me@.privacy.com.au> wrote in message
> news:2h31nmF7t7caU1@.uni-berlin.de...
> > I noticed recently that my data fike was quite large, and that the Log
> file
> > is even larger.
> >
> > I have a 1.1 Gb Data file and a 12.8 Gb log file.
> >
> > I noticed the grand sizes because of my backups, the bak file (done
every
> 12
> > hours, grows by about 200mb every day. I am having a hard time
believing
> my
> > 400 odd users are pumping that much data in.
> >
> > The Log and Data files are set to grow unrestricted, by 10% a go.
> >
> > I have tried DBCC ShrinkFile and DBCC ShrinkDatabase, neither of which
had
> > any effect on the DB size. My most recent attempt : DBCC SHRINKFile
> > (vmclean_log,20) gave me:
> >
> > CurrentSize: 1634992
> > MinimumSize:128
> > UsedPages:1634992
> > Est Pages:128
> >
> > I have 2 questions:
> >
> > 1) Recovery is set to full. Could I change this to Simple and be
satisfied
> > that in event of failure I can recover my 12 hour backup and apply my 3
> hour
> > Log backups to it to get back to the point of failure?
> >
> > 2) How can I find out what may be causing this?
> >
> > Thanks
> >
> > --
> > ---
> > Duncan Loxton
> >
> >
>|||Duncan
In order to move these transaction at the top of the list you have to
perform dummy insertion.
Create a table and with simple while...loop perfom an INSERT statement into
the table and then you will see that transction with status=2 is moved to
the top of the list.
"Duncan" <me@.privacy.com.au> wrote in message
news:2h4vn8F8v2ouU1@.uni-berlin.de...
> Uri,
> I checked Loginfo, and there are about 491 with status =2. I synched the
> publication, and it still had the exact same number after the synch.
> I just came across in BOLa reference to the Log Reader Agent. It looks
like
> this does the job of changing the status of pages that have been
replicated.
> I am running Merge Rep, but dont have a Log Reader Agent - should I have
> one?
> Surely these transactions with status =2 should be removed once the synch
> has happened - but they arent..... What can I do?
> Thanks!
> duncan
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtCFGfkPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> > Duncan
> > Have you tried to BACKUP LOG file before you issued DBCC SHRINKFILE.?
> > Look at DBCC LOGINFO(dbid) command to make sure you don't have
> transactions
> > with status=2 at the end of the list. This is causing to the LOG file
not
> to
> > be shrinking.
> >
> >
> > "Duncan Loxton" <me@.privacy.com.au> wrote in message
> > news:2h31nmF7t7caU1@.uni-berlin.de...
> > > I noticed recently that my data fike was quite large, and that the Log
> > file
> > > is even larger.
> > >
> > > I have a 1.1 Gb Data file and a 12.8 Gb log file.
> > >
> > > I noticed the grand sizes because of my backups, the bak file (done
> every
> > 12
> > > hours, grows by about 200mb every day. I am having a hard time
> believing
> > my
> > > 400 odd users are pumping that much data in.
> > >
> > > The Log and Data files are set to grow unrestricted, by 10% a go.
> > >
> > > I have tried DBCC ShrinkFile and DBCC ShrinkDatabase, neither of which
> had
> > > any effect on the DB size. My most recent attempt : DBCC SHRINKFile
> > > (vmclean_log,20) gave me:
> > >
> > > CurrentSize: 1634992
> > > MinimumSize:128
> > > UsedPages:1634992
> > > Est Pages:128
> > >
> > > I have 2 questions:
> > >
> > > 1) Recovery is set to full. Could I change this to Simple and be
> satisfied
> > > that in event of failure I can recover my 12 hour backup and apply my
3
> > hour
> > > Log backups to it to get back to the point of failure?
> > >
> > > 2) How can I find out what may be causing this?
> > >
> > > Thanks
> > >
> > > --
> > > ---
> > > Duncan Loxton
> > >
> > >
> >
> >
>

backup / trn file size.

I noticed recently that my data fike was quite large, and that the Log file
is even larger.
I have a 1.1 Gb Data file and a 12.8 Gb log file.
I noticed the grand sizes because of my backups, the bak file (done every 12
hours, grows by about 200mb every day. I am having a hard time believing my
400 odd users are pumping that much data in.
The Log and Data files are set to grow unrestricted, by 10% a go.
I have tried DBCC ShrinkFile and DBCC ShrinkDatabase, neither of which had
any effect on the DB size. My most recent attempt : DBCC SHRINKFile
(vmclean_log,20) gave me:
CurrentSize: 1634992
MinimumSize:128
UsedPages:1634992
Est Pages:128
I have 2 questions:
1) Recovery is set to full. Could I change this to Simple and be satisfied
that in event of failure I can recover my 12 hour backup and apply my 3 hour
Log backups to it to get back to the point of failure?
2) How can I find out what may be causing this?
Thanks
Duncan Loxton
Duncan
Have you tried to BACKUP LOG file before you issued DBCC SHRINKFILE.?
Look at DBCC LOGINFO(dbid) command to make sure you don't have transactions
with status=2 at the end of the list. This is causing to the LOG file not to
be shrinking.
"Duncan Loxton" <me@.privacy.com.au> wrote in message
news:2h31nmF7t7caU1@.uni-berlin.de...
> I noticed recently that my data fike was quite large, and that the Log
file
> is even larger.
> I have a 1.1 Gb Data file and a 12.8 Gb log file.
> I noticed the grand sizes because of my backups, the bak file (done every
12
> hours, grows by about 200mb every day. I am having a hard time believing
my
> 400 odd users are pumping that much data in.
> The Log and Data files are set to grow unrestricted, by 10% a go.
> I have tried DBCC ShrinkFile and DBCC ShrinkDatabase, neither of which had
> any effect on the DB size. My most recent attempt : DBCC SHRINKFile
> (vmclean_log,20) gave me:
> CurrentSize: 1634992
> MinimumSize:128
> UsedPages:1634992
> Est Pages:128
> I have 2 questions:
> 1) Recovery is set to full. Could I change this to Simple and be satisfied
> that in event of failure I can recover my 12 hour backup and apply my 3
hour
> Log backups to it to get back to the point of failure?
> 2) How can I find out what may be causing this?
> Thanks
> --
> Duncan Loxton
>
|||Uri,
I checked Loginfo, and there are about 491 with status =2. I synched the
publication, and it still had the exact same number after the synch.
I just came across in BOLa reference to the Log Reader Agent. It looks like
this does the job of changing the status of pages that have been replicated.
I am running Merge Rep, but dont have a Log Reader Agent - should I have
one?
Surely these transactions with status =2 should be removed once the synch
has happened - but they arent..... What can I do?
Thanks!
duncan
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtCFGfkPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> Duncan
> Have you tried to BACKUP LOG file before you issued DBCC SHRINKFILE.?
> Look at DBCC LOGINFO(dbid) command to make sure you don't have
transactions
> with status=2 at the end of the list. This is causing to the LOG file not
to[vbcol=seagreen]
> be shrinking.
>
> "Duncan Loxton" <me@.privacy.com.au> wrote in message
> news:2h31nmF7t7caU1@.uni-berlin.de...
> file
every[vbcol=seagreen]
> 12
believing[vbcol=seagreen]
> my
had[vbcol=seagreen]
satisfied
> hour
>
|||Duncan
In order to move these transaction at the top of the list you have to
perform dummy insertion.
Create a table and with simple while...loop perfom an INSERT statement into
the table and then you will see that transction with status=2 is moved to
the top of the list.
"Duncan" <me@.privacy.com.au> wrote in message
news:2h4vn8F8v2ouU1@.uni-berlin.de...
> Uri,
> I checked Loginfo, and there are about 491 with status =2. I synched the
> publication, and it still had the exact same number after the synch.
> I just came across in BOLa reference to the Log Reader Agent. It looks
like
> this does the job of changing the status of pages that have been
replicated.[vbcol=seagreen]
> I am running Merge Rep, but dont have a Log Reader Agent - should I have
> one?
> Surely these transactions with status =2 should be removed once the synch
> has happened - but they arent..... What can I do?
> Thanks!
> duncan
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtCFGfkPEHA.3300@.TK2MSFTNGP09.phx.gbl...
> transactions
not[vbcol=seagreen]
> to
> every
> believing
> had
> satisfied
3
>