Backup Size - Larger than db size

I have a database which I am backing up nightly ( after a log truncate, dbcc
checkdb ) and the .bak size is a lot larger than the actual database.
I am looking at the table space, and I think it might be the reserved space
also being backed up, I thought that only actual data was backed up.
Any pointers would be appreciated. Any more info require pls let me know.Are you perhaps by mistake doing append to the backup device? You can see what backups are on a
backup device using RESTORE HEADERONLY.
Backup will copy all extents which are in use, so even if only one page are used on an extent, the
whole extent will be included in the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Rum" <bundyrum75@.yahoo.com> wrote in message news:eSbUf.14129$814.11725@.newsfe5-win.ntli.net...
>I have a database which I am backing up nightly ( after a log truncate, dbcc checkdb ) and the .bak
>size is a lot larger than the actual database.
> I am looking at the table space, and I think it might be the reserved space also being backed up,
> I thought that only actual data was backed up.
> Any pointers would be appreciated. Any more info require pls let me know.
>
>

BACKUP LOG WITH TRUNCATE_ONLY after DBCC SHRINKDATABASE

Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database where
the recovery model is simple?
Dave
Hi Dave
In SIMPLE mode, SQL Server performs the same operation automatically at
regular frequent intervals, so there would be practically no benefit to
doing it yourself.
But, a related question is, WHY are you shrinking the database? Most people
think they need to shrink in order to reclaim space, but with the NOTRUNCATE
option you're not even going to get that.
Please read this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"DaveF" <dave@.aol.com> wrote in message news:5qIrj.2329$ip3.859@.trnddc07...
> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database where
> the recovery model is simple?
> Dave
>
|||Kalen,
We have a data warehouse load routine written by our software vendor that
actually performs 7 database shrinks over the course of the nightly run.
Believe it or not.
Are you saying these aren't necessary?
FYI. Our dev and test databases are in simple mode but our production
database is in full mode.
Dave
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
> Hi Dave
> In SIMPLE mode, SQL Server performs the same operation automatically at
> regular frequent intervals, so there would be practically no benefit to
> doing it yourself.
> But, a related question is, WHY are you shrinking the database? Most
> people think they need to shrink in order to reclaim space, but with the
> NOTRUNCATE option you're not even going to get that.
> Please read this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "DaveF" <dave@.aol.com> wrote in message
> news:5qIrj.2329$ip3.859@.trnddc07...
>
|||Did you read the article I pointed you to?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"DaveF" <dave@.aol.com> wrote in message news:tYNrj.1262$r03.293@.trnddc08...
> Kalen,
> We have a data warehouse load routine written by our software vendor that
> actually performs 7 database shrinks over the course of the nightly run.
> Believe it or not.
> Are you saying these aren't necessary?
> FYI. Our dev and test databases are in simple mode but our production
> database is in full mode.
> Dave
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
>
|||Time to get a new vendor.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"DaveF" <dave@.aol.com> wrote in message news:tYNrj.1262$r03.293@.trnddc08...
> Kalen,
> We have a data warehouse load routine written by our software vendor that
> actually performs 7 database shrinks over the course of the nightly run.
> Believe it or not.
> Are you saying these aren't necessary?
> FYI. Our dev and test databases are in simple mode but our production
> database is in full mode.
> Dave
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
>

BACKUP LOG WITH TRUNCATE_ONLY after DBCC SHRINKDATABASE

Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database where
the recovery model is simple?
DaveHi Dave
In SIMPLE mode, SQL Server performs the same operation automatically at
regular frequent intervals, so there would be practically no benefit to
doing it yourself.
But, a related question is, WHY are you shrinking the database? Most people
think they need to shrink in order to reclaim space, but with the NOTRUNCATE
option you're not even going to get that.
Please read this article:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"DaveF" <dave@.aol.com> wrote in message news:5qIrj.2329$ip3.859@.trnddc07...
> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database where
> the recovery model is simple?
> Dave
>|||Kalen,
We have a data warehouse load routine written by our software vendor that
actually performs 7 database shrinks over the course of the nightly run.
Believe it or not.
Are you saying these aren't necessary?
FYI. Our dev and test databases are in simple mode but our production
database is in full mode.
Dave
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
> Hi Dave
> In SIMPLE mode, SQL Server performs the same operation automatically at
> regular frequent intervals, so there would be practically no benefit to
> doing it yourself.
> But, a related question is, WHY are you shrinking the database? Most
> people think they need to shrink in order to reclaim space, but with the
> NOTRUNCATE option you're not even going to get that.
> Please read this article:
> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "DaveF" <dave@.aol.com> wrote in message
> news:5qIrj.2329$ip3.859@.trnddc07...
>> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
>> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database
>> where the recovery model is simple?
>> Dave
>|||Did you read the article I pointed you to?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"DaveF" <dave@.aol.com> wrote in message news:tYNrj.1262$r03.293@.trnddc08...
> Kalen,
> We have a data warehouse load routine written by our software vendor that
> actually performs 7 database shrinks over the course of the nightly run.
> Believe it or not.
> Are you saying these aren't necessary?
> FYI. Our dev and test databases are in simple mode but our production
> database is in full mode.
> Dave
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
>> Hi Dave
>> In SIMPLE mode, SQL Server performs the same operation automatically at
>> regular frequent intervals, so there would be practically no benefit to
>> doing it yourself.
>> But, a related question is, WHY are you shrinking the database? Most
>> people think they need to shrink in order to reclaim space, but with the
>> NOTRUNCATE option you're not even going to get that.
>> Please read this article:
>> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "DaveF" <dave@.aol.com> wrote in message
>> news:5qIrj.2329$ip3.859@.trnddc07...
>> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
>> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database
>> where the recovery model is simple?
>> Dave
>>
>|||Time to get a new vendor.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"DaveF" <dave@.aol.com> wrote in message news:tYNrj.1262$r03.293@.trnddc08...
> Kalen,
> We have a data warehouse load routine written by our software vendor that
> actually performs 7 database shrinks over the course of the nightly run.
> Believe it or not.
> Are you saying these aren't necessary?
> FYI. Our dev and test databases are in simple mode but our production
> database is in full mode.
> Dave
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23JPpvkCbIHA.5400@.TK2MSFTNGP03.phx.gbl...
>> Hi Dave
>> In SIMPLE mode, SQL Server performs the same operation automatically at
>> regular frequent intervals, so there would be practically no benefit to
>> doing it yourself.
>> But, a related question is, WHY are you shrinking the database? Most
>> people think they need to shrink in order to reclaim space, but with the
>> NOTRUNCATE option you're not even going to get that.
>> Please read this article:
>> http://www.karaszi.com/sqlserver/info_dont_shrink.asp
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "DaveF" <dave@.aol.com> wrote in message
>> news:5qIrj.2329$ip3.859@.trnddc07...
>> Is there any benefit to performing a BACKUP LOG MyDB WITH TRUNCATE_ONLY
>> after a DBCC SHRINKDATABASE (N'MyDB', 10, NOTRUNCATE) on a database
>> where the recovery model is simple?
>> Dave
>>
>