.BAK file double in size

Very strange. My .bak file for a SQL2000 databae has doubled in size. The
database .mdf file has not increased in size. My .bak file is .25g and my
.mdb file is 15g.
The only change I can determine is that a week ago we started a replication
publication on this database. But it is really strange the the .bak file has
grown an not the .mdf file.
Do make sure the file was not corrupt, I did restore it and it restored
okay. I actually restored to a 2005 server (because that is where I had some
room). I restored fine as I said. So then I backed up the database there and
the .bak file is back down to normal size around 11g.
Any ideas. Since we save 4 copies of the .bak on the server it is taking up
a lot of space, but I mainly concerned with the strangeness of it all.
Thanks for your help
DebDo not worry about it. The backup file may also have information from the
transaction log. So, if you were able to restore it then it is fine.
Hope this helps,
Ben Nevarez
"DebHerman" wrote:
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a replication
> publication on this database. But it is really strange the the .bak file has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had some
> room). I restored fine as I said. So then I backed up the database there and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb|||Did you back up to the same file without using INIT? This will append a
second backup to the original backup file. As Erland posted in another
thread today, you can check how many backups are in the BAK file by RESTORE
... HEADERONLY and simply counting the rows.
"DebHerman" <DebHerman@.discussions.microsoft.com> wrote in message
news:C84C48BD-E6DE-4736-B12B-0F5E2DCC12B3@.microsoft.com...
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a
> replication
> publication on this database. But it is really strange the the .bak file
> has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had
> some
> room). I restored fine as I said. So then I backed up the database there
> and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking
> up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb|||Can you tell me the scenarion where it would have that much information from
the transaction log?
"Ben Nevarez" wrote:
> Do not worry about it. The backup file may also have information from the
> transaction log. So, if you were able to restore it then it is fine.
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:
> > Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> > database .mdf file has not increased in size. My .bak file is .25g and my
> > .mdb file is 15g.
> >
> > The only change I can determine is that a week ago we started a replication
> > publication on this database. But it is really strange the the .bak file has
> > grown an not the .mdf file.
> > Do make sure the file was not corrupt, I did restore it and it restored
> > okay. I actually restored to a 2005 server (because that is where I had some
> > room). I restored fine as I said. So then I backed up the database there and
> > the .bak file is back down to normal size around 11g.
> > Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> > a lot of space, but I mainly concerned with the strangeness of it all.
> > Thanks for your help
> > Deb|||"DebHerman" <DebHerman@.discussions.microsoft.com> wrote in message
news:C84C48BD-E6DE-4736-B12B-0F5E2DCC12B3@.microsoft.com...
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a
> replication
> publication on this database. But it is really strange the the .bak file
> has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had
> some
> room). I restored fine as I said. So then I backed up the database there
> and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking
> up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb
Your backup should be fine. Maybe you have some long running transactions or
heavy concurrent activity during the backup and that makes your log bigger
than usual. Check your log file size.
--
Rubén Garrigós
Solid Quality Mentors|||From 'Full Database Backups' on BOL.
"A full database backup backs up the whole database. This includes part of
the transaction log so that the full database backup can be recovered. Full
database backups represent the database at the time the backup finished."
"Enough of the transaction log is included in the backup to let you recover
the database to the time when the backup finished."
Hope this helps,
Ben Nevarez
"DebHerman" wrote:
> Can you tell me the scenarion where it would have that much information from
> the transaction log?
> "Ben Nevarez" wrote:
> >
> > Do not worry about it. The backup file may also have information from the
> > transaction log. So, if you were able to restore it then it is fine.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> >
> > "DebHerman" wrote:
> >
> > > Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> > > database .mdf file has not increased in size. My .bak file is .25g and my
> > > .mdb file is 15g.
> > >
> > > The only change I can determine is that a week ago we started a replication
> > > publication on this database. But it is really strange the the .bak file has
> > > grown an not the .mdf file.
> > > Do make sure the file was not corrupt, I did restore it and it restored
> > > okay. I actually restored to a 2005 server (because that is where I had some
> > > room). I restored fine as I said. So then I backed up the database there and
> > > the .bak file is back down to normal size around 11g.
> > > Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> > > a lot of space, but I mainly concerned with the strangeness of it all.
> > > Thanks for your help
> > > Deb|||I understand that transactions that happen during the back up are stored in
the back up file, but nothing "should" be happening at the time of the
backup. The transaction log file is actually backed up just prior to the full
database back up and it is really small.
But my suspicion is that a snapshot is running at the same time as the back
up since we just started replication. Would a snapshot create transactions.
If so this is probably my problem. I adjusted the timing last night but not
quite enough. The snapshot didn't quite finish before the backup up of the
transaction log. But it did finish before the backup of the database.
Confusing. I'm not sure a full snapshot should be running each night but
that is a whole other discussion.
I need to determine what is causing this. We store 4 days worth .bak on the
server and these are backed up to tape each night, so 4 12g is 48g a space
that makes the network backup people cranky.<g>
Thanks for any help?
"Ben Nevarez" wrote:
> From 'Full Database Backups' on BOL.
> "A full database backup backs up the whole database. This includes part of
> the transaction log so that the full database backup can be recovered. Full
> database backups represent the database at the time the backup finished."
> "Enough of the transaction log is included in the backup to let you recover
> the database to the time when the backup finished."
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:
> > Can you tell me the scenarion where it would have that much information from
> > the transaction log?
> >
> > "Ben Nevarez" wrote:
> >
> > >
> > > Do not worry about it. The backup file may also have information from the
> > > transaction log. So, if you were able to restore it then it is fine.
> > >
> > > Hope this helps,
> > >
> > > Ben Nevarez
> > >
> > >
> > >
> > >
> > > "DebHerman" wrote:
> > >
> > > > Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> > > > database .mdf file has not increased in size. My .bak file is .25g and my
> > > > .mdb file is 15g.
> > > >
> > > > The only change I can determine is that a week ago we started a replication
> > > > publication on this database. But it is really strange the the .bak file has
> > > > grown an not the .mdf file.
> > > > Do make sure the file was not corrupt, I did restore it and it restored
> > > > okay. I actually restored to a 2005 server (because that is where I had some
> > > > room). I restored fine as I said. So then I backed up the database there and
> > > > the .bak file is back down to normal size around 11g.
> > > > Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> > > > a lot of space, but I mainly concerned with the strangeness of it all.
> > > > Thanks for your help
> > > > Deb

.BAK file double in size

Very strange. My .bak file for a SQL2000 databae has doubled in size. The
database .mdf file has not increased in size. My .bak file is .25g and my
..mdb file is 15g.
The only change I can determine is that a week ago we started a replication
publication on this database. But it is really strange the the .bak file has
grown an not the .mdf file.
Do make sure the file was not corrupt, I did restore it and it restored
okay. I actually restored to a 2005 server (because that is where I had some
room). I restored fine as I said. So then I backed up the database there and
the .bak file is back down to normal size around 11g.
Any ideas. Since we save 4 copies of the .bak on the server it is taking up
a lot of space, but I mainly concerned with the strangeness of it all.
Thanks for your help
Deb
Do not worry about it. The backup file may also have information from the
transaction log. So, if you were able to restore it then it is fine.
Hope this helps,
Ben Nevarez
"DebHerman" wrote:

> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a replication
> publication on this database. But it is really strange the the .bak file has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had some
> room). I restored fine as I said. So then I backed up the database there and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb
|||Did you back up to the same file without using INIT? This will append a
second backup to the original backup file. As Erland posted in another
thread today, you can check how many backups are in the BAK file by RESTORE
... HEADERONLY and simply counting the rows.
"DebHerman" <DebHerman@.discussions.microsoft.com> wrote in message
news:C84C48BD-E6DE-4736-B12B-0F5E2DCC12B3@.microsoft.com...
> Very strange. My .bak file for a SQL2000 databae has doubled in size. The
> database .mdf file has not increased in size. My .bak file is .25g and my
> .mdb file is 15g.
> The only change I can determine is that a week ago we started a
> replication
> publication on this database. But it is really strange the the .bak file
> has
> grown an not the .mdf file.
> Do make sure the file was not corrupt, I did restore it and it restored
> okay. I actually restored to a 2005 server (because that is where I had
> some
> room). I restored fine as I said. So then I backed up the database there
> and
> the .bak file is back down to normal size around 11g.
> Any ideas. Since we save 4 copies of the .bak on the server it is taking
> up
> a lot of space, but I mainly concerned with the strangeness of it all.
> Thanks for your help
> Deb
|||Can you tell me the scenarion where it would have that much information from
the transaction log?
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Do not worry about it. The backup file may also have information from the
> transaction log. So, if you were able to restore it then it is fine.
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:
|||From 'Full Database Backups' on BOL.
"A full database backup backs up the whole database. This includes part of
the transaction log so that the full database backup can be recovered. Full
database backups represent the database at the time the backup finished."
"Enough of the transaction log is included in the backup to let you recover
the database to the time when the backup finished."
Hope this helps,
Ben Nevarez
"DebHerman" wrote:
[vbcol=seagreen]
> Can you tell me the scenarion where it would have that much information from
> the transaction log?
> "Ben Nevarez" wrote:
|||I understand that transactions that happen during the back up are stored in
the back up file, but nothing "should" be happening at the time of the
backup. The transaction log file is actually backed up just prior to the full
database back up and it is really small.
But my suspicion is that a snapshot is running at the same time as the back
up since we just started replication. Would a snapshot create transactions.
If so this is probably my problem. I adjusted the timing last night but not
quite enough. The snapshot didn't quite finish before the backup up of the
transaction log. But it did finish before the backup of the database.
Confusing. I'm not sure a full snapshot should be running each night but
that is a whole other discussion.
I need to determine what is causing this. We store 4 days worth .bak on the
server and these are backed up to tape each night, so 4 12g is 48g a space
that makes the network backup people cranky.<g>
Thanks for any help?
"Ben Nevarez" wrote:
[vbcol=seagreen]
> From 'Full Database Backups' on BOL.
> "A full database backup backs up the whole database. This includes part of
> the transaction log so that the full database backup can be recovered. Full
> database backups represent the database at the time the backup finished."
> "Enough of the transaction log is included in the backup to let you recover
> the database to the time when the backup finished."
> Hope this helps,
> Ben Nevarez
>
>
> "DebHerman" wrote:

*** Can anyone explain me this ***

Hi:
I'm having a very strange and problematic behavior of SQL Server 2000 in my
program.

Program execution:
--------------
1.BEGIN TRANSACTION
2.EXECute for the first time a SP called sp_GAupdARMAZ_MOV, which inserts into a table ARMAZ_MOV a new record.
3.Til this point everything is ok!
4.EXECute for the second time sp_GAupdARMAZ_MOV, which I don't why rollbacks my transaction, aborting obviously the previously inserted record. But still continues execution without any error and inserts another record in my table ARMAZ_MOV.
5.COMMIT TRANSACTION

I make a "select * from armaz_mov" and there's only one record !!!!!!! What the hell? ... Can anyone explain me this???

I attached the SQL profiler trace of this problem, and an image of the exact line where my transaction is rolled back.

I analysed very well my SP and the only reason I encountered to explain this is because of an string concatenation with an integer in a SP line of code that's not even executed !!! Maybe a compilation error is causing this...
If I include a "convert(varchar,id)" in my string concatenation everything
works fine !!!!!!!!

If anyone could help me with this, maybe already had a similar problem, or maybe this is a known issue of SQL Server...

I would appreciate a lot if someone could analise for some minutes the
attached SQL Profiler trace and give me some feedback, opinion ...

Thanks a lot.
Monica
------------------------Can you post your stored procedure ? You may want to repost the profiler in zip format rather than rar.|||Originally posted by rnealejr
Can you post your stored procedure ? You may want to repost the profiler in zip format rather than rar.

Ok I reposted the Profiler trace in zip format and included my SP.|||What is the schema for the table ARMAZ_MOV ? Also, where are you executing the transaction (in query analyzer) ? Have you tried it in query analyzer to see if any errors are reported ? What are the exact statements that you are using to execute this transaction (including the parameters) ? You have to cast your int value to varchar anyway - but I am curious as well.|||Also, which version of sql server are you using - including service pack ?|||Originally posted by rnealejr
Also, which version of sql server are you using - including service pack ?

I execute my program in Delphi 6, W2K, with SQL Server 2000 and SP2 installed.
I'm connecting to my database and executing the SP using ADO.

The schema for my ARMAZ_MOV table is:
--
create table DBO.ARMAZ_MOV
(
ID_ARMAZ_MOV integer not null,
ID_REG_MOV integer not null,
REF_TIPO_MOV char(3) not null,
COD_FABRICA char(2) not null,
REF_TP_ARMAZ char(2) not null,
COD_ARTIGO varchar(10) not null,
DATA_MOV datetime not null,
QTD_MOV decimal(18,3) not null,
QTD_EXIST decimal(18,3) null ,
UNIDADE_MOV varchar(10) not null,
OBS_MOV varchar(200) null ,
INS_BY varchar(10) not null,
INS_DATE datetime not null,
UPD_BY varchar(10) not null,
UPD_DATE datetime not null,
STATUS_EXPORT varchar(15) null
constraint CKC_STATUS_EXPORT_ARMAZ_MO check (STATUS_EXPORT in ('EXPORTADO','NAO_EXPORTADO')),
ERRO_EXPORT varchar(200) null ,
TIPO_REG varchar(15) not null
constraint CKC_TIPO_REG_ARMAZ_MO check (TIPO_REG in ('NORMAL','CORRECCAO')),
ID_ORIGINAL integer null ,
constraint PK_ARMAZ_MOV primary key (ID_ARMAZ_MOV)
)|||I executed the 2 iterations of my program in qry analyser and everything worked fine.

So my conclusion is that problem is not from the DBMS but from the client side (ADO/Delphi).

I have to see if this is a known problem of ADO or Delphi, or not.

Rui Ferreira

(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column.... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

Seems to me that the data being imported is being imported as an int and hence the values which are not integer (row 62 and beyond) do not get imported into the table. However, this is just my guess. You might get better answer from Integration Services forum and I am moving this thread to the forum for you to get better assistance.

HTH,

|||

Yes that is correct. SSIS will look at the first few rows of Excel data to determine what the data type of the column is. As far as I know there is no easy way around that. Your best option is to ensure that one of the "R" values is in the first few rows.

Otherwise, you could do an import from .csv isntead of from Excel. That would give you more control.

Dylan.

|||

I struggle with the strange behaviour of excel imports for days now..

what helped a lot was this articel:

http://blog.lab49.com/?p=196

I hope it helps you too...

Mybe someone has a solution to my urgent excel import problem too... please look here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2129460&SiteID=1

if you have a minute left...

Thanks

Jens

|||

Thank you every one for your input... I solved the problem by changing the cell format as text and then copied all the information back.. to the excel spread sheet as text and everything works fine...

Regards

Karen

(urgent)strange behaviour in excel file when previewed in SqlServer (importData)

Hi,

I am trying to import a excel file in to my database... The improt works fine.. But some of the data is missing though it is present in the excel spread sheet.

I have some data for cusip which are 9 characters and they be a combination of numbers wiht a letter.. for eg.. 123456789 or 12345R789. And in the my spread sheet there are around 73 rows.. until the 62 row it has numbers like 123456789 and from the 62 to 73 it has 12345R789 this is an just example but the data is in that format.

I went to sqlServer 2005 and imported the data using Tasks -ImportData and selected my excel spreadsheet, the user name and password for the database and i selected the sheet i want to import.. and when i preview the data ... Until the 62 row i can see the numbers and after that i cannot see any data in that column... and when i import the data from 62 row the value is NULL...

So can some please tell me what going on why isnt that data been recognised by the importer in sql server.

Any help will be appreciated.

Regards

Karen

A short answer(work around): import throgh an Access table will address this issue.

Or change the numbers in this column to text by adding an ' in front of these numbers before your import.

I hope someone can provide more elegant solution(s) to this issue.