Backup file vs. Database file

Hi,
I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
file that I've restored to a database. That database is now a whopping 185
GB. Can someone explain to me why this database is so large?
Thank you in advance,
DeeI just wanted to clarify that I've restored the backup file to a brand new
database.
"bpdee" wrote:
> Hi,
> I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
> file that I've restored to a database. That database is now a whopping 185
> GB. Can someone explain to me why this database is so large?
> Thank you in advance,
> Dee|||Here is some more information when I ran the sp_spaceused stored procedure.
database_name database_size unallocated space
-- -- --
DecisionStore 181474.19 MB 174091.16 MB
reserved data index_size unused
-- -- -- --
5774368 KB 5540816 KB 158864 KB 74688 KB
"bpdee" wrote:
> Hi,
> I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
> file that I've restored to a database. That database is now a whopping 185
> GB. Can someone explain to me why this database is so large?
> Thank you in advance,
> Dee|||Because you had a lot of free space when you took backup of that database. When you restore, SQL
Server will put back each page in the original location (page address of the database file). Because
of this, each file has to be at least the size it was when you performed the backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:7717DF9A-0730-49D0-8962-7859ED7F0854@.microsoft.com...
> Hi,
> I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
> file that I've restored to a database. That database is now a whopping 185
> GB. Can someone explain to me why this database is so large?
> Thank you in advance,
> Dee|||Thanks, Tibor! I've tried using DBCC SHRINKFILE and DBCC SHRINKDATABASE on
the database and I can't seem to lessen the filesize of the mdf file. Do you
have any suggestion on how to do this?
"Tibor Karaszi" wrote:
> Because you had a lot of free space when you took backup of that database. When you restore, SQL
> Server will put back each page in the original location (page address of the database file). Because
> of this, each file has to be at least the size it was when you performed the backup.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:7717DF9A-0730-49D0-8962-7859ED7F0854@.microsoft.com...
> > Hi,
> >
> > I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
> > file that I've restored to a database. That database is now a whopping 185
> > GB. Can someone explain to me why this database is so large?
> >
> > Thank you in advance,
> > Dee
>|||Fragmentation can be one reason. First step would be to investigate the information that DBCC
SHRINKFILE returns (documented in Books Online).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:2EDC0D8F-2FA6-4FE4-A5FB-0A088F476FBC@.microsoft.com...
> Thanks, Tibor! I've tried using DBCC SHRINKFILE and DBCC SHRINKDATABASE on
> the database and I can't seem to lessen the filesize of the mdf file. Do you
> have any suggestion on how to do this?
> "Tibor Karaszi" wrote:
>> Because you had a lot of free space when you took backup of that database. When you restore, SQL
>> Server will put back each page in the original location (page address of the database file).
>> Because
>> of this, each file has to be at least the size it was when you performed the backup.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:7717DF9A-0730-49D0-8962-7859ED7F0854@.microsoft.com...
>> > Hi,
>> >
>> > I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
>> > file that I've restored to a database. That database is now a whopping 185
>> > GB. Can someone explain to me why this database is so large?
>> >
>> > Thank you in advance,
>> > Dee
>>|||This is the information that I get.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
8 1 23005464 128 719344 719336
"Tibor Karaszi" wrote:
> Fragmentation can be one reason. First step would be to investigate the information that DBCC
> SHRINKFILE returns (documented in Books Online).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:2EDC0D8F-2FA6-4FE4-A5FB-0A088F476FBC@.microsoft.com...
> > Thanks, Tibor! I've tried using DBCC SHRINKFILE and DBCC SHRINKDATABASE on
> > the database and I can't seem to lessen the filesize of the mdf file. Do you
> > have any suggestion on how to do this?
> >
> > "Tibor Karaszi" wrote:
> >
> >> Because you had a lot of free space when you took backup of that database. When you restore, SQL
> >> Server will put back each page in the original location (page address of the database file).
> >> Because
> >> of this, each file has to be at least the size it was when you performed the backup.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> >> news:7717DF9A-0730-49D0-8962-7859ED7F0854@.microsoft.com...
> >> > Hi,
> >> >
> >> > I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
> >> > file that I've restored to a database. That database is now a whopping 185
> >> > GB. Can someone explain to me why this database is so large?
> >> >
> >> > Thank you in advance,
> >> > Dee
> >>
> >>
>|||So you currently have 23005464 pages and according to DBCC SHRINKFILE you should be able to get down
to 719336 pages. I can only assume that there's some page high in the file which can't be moved,
like for instance some page for a service broker table or similar. I suggest you start by Googling
on this issue and see if you can find other with same symptoms (data file, not log). I do recall
vaguely some reasons why shrinkfile might not cut it, like system table pages high, possibly LOB
pages and similar, but I can't recall details, I'm afraid... :-(
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bpdee" <bpdee@.discussions.microsoft.com> wrote in message
news:A16F4176-E110-432E-92F7-680289AA9651@.microsoft.com...
> This is the information that I get.
> DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
> -- -- -- -- -- --
> 8 1 23005464 128 719344 719336
> "Tibor Karaszi" wrote:
>> Fragmentation can be one reason. First step would be to investigate the information that DBCC
>> SHRINKFILE returns (documented in Books Online).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> news:2EDC0D8F-2FA6-4FE4-A5FB-0A088F476FBC@.microsoft.com...
>> > Thanks, Tibor! I've tried using DBCC SHRINKFILE and DBCC SHRINKDATABASE on
>> > the database and I can't seem to lessen the filesize of the mdf file. Do you
>> > have any suggestion on how to do this?
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> Because you had a lot of free space when you took backup of that database. When you restore,
>> >> SQL
>> >> Server will put back each page in the original location (page address of the database file).
>> >> Because
>> >> of this, each file has to be at least the size it was when you performed the backup.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://sqlblog.com/blogs/tibor_karaszi
>> >>
>> >>
>> >> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
>> >> news:7717DF9A-0730-49D0-8962-7859ED7F0854@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
>> >> > file that I've restored to a database. That database is now a whopping 185
>> >> > GB. Can someone explain to me why this database is so large?
>> >> >
>> >> > Thank you in advance,
>> >> > Dee
>> >>
>> >>|||No problem, Tibor. Thank again so much for all of your help!
"Tibor Karaszi" wrote:
> So you currently have 23005464 pages and according to DBCC SHRINKFILE you should be able to get down
> to 719336 pages. I can only assume that there's some page high in the file which can't be moved,
> like for instance some page for a service broker table or similar. I suggest you start by Googling
> on this issue and see if you can find other with same symptoms (data file, not log). I do recall
> vaguely some reasons why shrinkfile might not cut it, like system table pages high, possibly LOB
> pages and similar, but I can't recall details, I'm afraid... :-(
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> news:A16F4176-E110-432E-92F7-680289AA9651@.microsoft.com...
> > This is the information that I get.
> >
> > DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
> > -- -- -- -- -- --
> > 8 1 23005464 128 719344 719336
> >
> > "Tibor Karaszi" wrote:
> >
> >> Fragmentation can be one reason. First step would be to investigate the information that DBCC
> >> SHRINKFILE returns (documented in Books Online).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> >> news:2EDC0D8F-2FA6-4FE4-A5FB-0A088F476FBC@.microsoft.com...
> >> > Thanks, Tibor! I've tried using DBCC SHRINKFILE and DBCC SHRINKDATABASE on
> >> > the database and I can't seem to lessen the filesize of the mdf file. Do you
> >> > have any suggestion on how to do this?
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> Because you had a lot of free space when you took backup of that database. When you restore,
> >> >> SQL
> >> >> Server will put back each page in the original location (page address of the database file).
> >> >> Because
> >> >> of this, each file has to be at least the size it was when you performed the backup.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://sqlblog.com/blogs/tibor_karaszi
> >> >>
> >> >>
> >> >> "bpdee" <bpdee@.discussions.microsoft.com> wrote in message
> >> >> news:7717DF9A-0730-49D0-8962-7859ED7F0854@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I'm running SQL Server 2005 on Windows 2003 Server. I have a 6 GB backup
> >> >> > file that I've restored to a database. That database is now a whopping 185
> >> >> > GB. Can someone explain to me why this database is so large?
> >> >> >
> >> >> > Thank you in advance,
> >> >> > Dee
> >> >>
> >> >>
> >>
>