Thursday, March 8, 2012

backup causes file size to grow

I backed up my database using the sql command Backup database xxx to disk =
'yyy'
When it completed the file size of my database approximately tripled. Did I
just corrupt some data? The application appears to continue to run ok. I
have experienced performance problems in the past and assumed it was hardwar
e
related. Maybe my database was just sized too small'?Which file tripled? The database is made up of by default one data file and
one log file. Was it the data or log file that grew? If it was the log
file then you need to know that log backups can not finish during a full
backup. So if your log file was small to begin with and someone did a large
(or lots of ) transactions during the full backup the log file may have had
to grow since it could not be truncated during the full backup.
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=
> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?|||It was the data file (.mdb) that grew. My log file (.ldf) is basically the
same size it was. I went from a 6gb database file to a 20gb database file.
Thanks in advance for any advice.
"Andrew J. Kelly" wrote:

> Which file tripled? The database is made up of by default one data file a
nd
> one log file. Was it the data or log file that grew? If it was the log
> file then you need to know that log backups can not finish during a full
> backup. So if your log file was small to begin with and someone did a lar
ge
> (or lots of ) transactions during the full backup the log file may have ha
d
> to grow since it could not be truncated during the full backup.
> --
> Andrew J. Kelly SQL MVP
>
> "doug" <doug@.discussions.microsoft.com> wrote in message
> news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>
>|||Are you positive that no one issued an ALTER DATABASE command that may have
grown the file? I have never heard of a db file growing from a backup
before. If it was due to autogrow there should have been lots of log
entries as well.
Andrew J. Kelly SQL MVP
"doug" <doug@.discussions.microsoft.com> wrote in message
news:68FFC5B6-9B4B-4194-A360-64FE8B2003AB@.microsoft.com...[vbcol=seagreen]
> It was the data file (.mdb) that grew. My log file (.ldf) is basically
> the
> same size it was. I went from a 6gb database file to a 20gb database
> file.
> Thanks in advance for any advice.
> "Andrew J. Kelly" wrote:
>|||is this sql 2005, and do you have full-text indexing? If so, expect large
backup files.
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
"doug" <doug@.discussions.microsoft.com> wrote in message
news:D9566F87-EC93-4950-9B59-0CF936AF0D74@.microsoft.com...
>I backed up my database using the sql command Backup database xxx to disk
>=
> 'yyy'
> When it completed the file size of my database approximately tripled. Did
> I
> just corrupt some data? The application appears to continue to run ok. I
> have experienced performance problems in the past and assumed it was
> hardware
> related. Maybe my database was just sized too small'?

No comments:

Post a Comment