Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an issue with one database. The issue is as follows: The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode.

In the beginning of this summer the database was about 5GB.

Since then, the only thing we did beyond normal usage is some extensive deletions.

This week, there was a problem with the PC and I was forced to do a backup and restore it on an SQL Server 2008R2 Express and set the database in 2008 mode.

The backup file was about 1GB. When I restored it, the MDF was 9GB of size! I checked the old MDF and it was of the same size.

I checked the size of the tables and they cannot reach the 9GB reported!

I did a shrink but the size did not change.

Any clues or where to check?

Is there a chance that the Full Recovery, can affect the size of the MDF files?

I am thinking of setinng the recovery model to Simple, back-it-up and restore it. Is it going to make a difference? Can I do it on a live database?

Thanx in advance!

UPDATE:The initial size of the database is 1306 MB

UPDATE2 sp_spaceused: Database Size=8646.88 MB Unallocated Space= 0.00 MB

reserved=1336984 KB data=1020376 KB index_size=210408 KB unused=106200 KB

share|improve this question

migrated from stackoverflow.com Aug 5 '13 at 11:49

This question came from our site for professional and enthusiast programmers.

1  
check this: stackoverflow.com/questions/3498148/… –  vijay Aug 5 '13 at 11:43
add comment

3 Answers 3

The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode.

This is fin as long as you are doing regular full and log backups to keep the log file size under control. There are plenty of articles and questions on here regarding log management.

In the beginning of this summer the database was about 5GB. Since then, the only thing we did beyond normal usage is some extensive deletions.

Large deletions are fine but understand that the space that data used was not released back to the operating system, unless you did a shrink of the data file.

This week, there was a problem with the PC and I was forced to do a backup and restore it on an SQL Server 2008R2 Express and set the database in 2008 mode.

I hope you tested your code! T-SQL syntax changed a good bit from SQL 2000 to SQL 2008 R2. I have supported many databases that broke due to some of these changes. Also note that Express has a limitation on how large a database can grow, 10GB with SQL 2008 R2

The backup file was about 1GB. When I restored it, the MDF was 9GB of size! I checked the old MDF and it was of the same size. I checked the size of the tables and they cannot reach the 9GB reported!

When you do a backup of the database, the backup is only going to grab the data itself along with some configuration information of the database. One of those configuration items is the initial size of the data and log file. I would expect if you checked your database properties you might find the initial size of your data file (MDF) is set to 9GB in size.

I did a shrink but the size did not change.

A database shrink is likely what you performed. Although ill-advised, if you need to regain disk space or just bring the data file (MDF) of your database under control you will need to perform a data file shrink. Again there are plenty of articles on MSDN and blog post that provide the way of doing this.

Is there a chance that the Full Recovery, can affect the size of the MDF files?

Nope.

I am thinking of settinng the recovery model to Simple, back-it-up and restore it. Is it going to make a difference?

With the size of your data file, nope.

share|improve this answer
    
Thanx for the reply Shawn. The keep the SQL code compatible with all the version of SQL Server since 2000 version. The initial size of the database is 1306MB.So the 1 to 9 ratio between backup and MDF is a bit strange, isnt it? –  PanosPlat Aug 5 '13 at 13:17
    
So the initial size of the data file or the database is 1306MB? Is this after the restore, or after you did the shrink? If you execute "sp_helpdb 'YourDatabase', what size does it show? –  Shawn Melton Aug 5 '13 at 13:26
    
I do not know if the initial size changed, but 1306MB is the value now.sp_helpdb displays the data file size I stated.But I insist that there are not 9GB of data in that database.We have less than ten big tables, the biggest of them is 150MB and the second is far far behind. Spooky? –  PanosPlat Aug 5 '13 at 15:08
1  
Try executing DBCC UPDATEUSAGE and then checking the numbers again. This command updates the catalog views: technet.microsoft.com/en-us/library/ms188414(v=sql.105).aspx –  Shawn Melton Aug 5 '13 at 23:06
add comment

Try running the following:

EXEC sp_msforeachtable 'EXEC sp_spaceused [?]'

If you notice any excessive values in the unused column for any of the tables then run this:

ALTER INDEX ALL ON <TableName> REBUILD

Or use the rebuild index method of your choice. You want to do a full rebuild though because I don't believe a reorg will clean up the unnecessary unused space.

Fyi by excessive I mean anything in the 10-100 mb range or higher on a table that is actually using less than double the free space. Or any table that has a really large unused space. I wouldn't want to see 1GB of unused space on a 5GB table personally. Although I don't usually check this unless there is an actual space problem.

And by unnecessary unused space I mean that it is possible that one or more of your tables is holding a large amount of space in reserve that it either isn't likely to use, or simply can't. SQL generally holds some space in reserve and this normal and wanted. It's only when it gets excessive that there is a problem.

Once the rebuild(s) is complete try the sp_spaceused and see if that fixed the problem. If it did then you can try your shrink again. Just to point it out this is one of those few cases where a shrink is reasonable. You have a database that was 5 GB before the restore (on 2000) and is now 9 GB after the restore, so hopefully the extra space is unneeded.

If I had to guess the extra space was caused by the upgrade from 2000 to 2008 R2. I've never tried that big a jump before so I can't be certain. Hopefully the above will get rid of any extra space it caused.

share|improve this answer
    
Thank you Kenneth. I will try your suggestion when the customer closes for the day. Question:If I DROP and then CREATE all indeces again will it be better? The excessive data was not due to the "migration".I checked the SQL Server 2000 MDF and it was at 9GB. Something must have happened during the last two months and I am trying to find out. –  PanosPlat Aug 5 '13 at 15:17
    
Ahh, that may make a big difference. You can try the sp_spaceused command immediately (little to no affect on production). That way you can see if this will be of any help at all. –  Kenneth Fisher Aug 5 '13 at 15:33
    
I will update the original post, to give the sp output –  PanosPlat Aug 5 '13 at 16:19
    
Try running sp_spaceused on each table. The sp_msforeachtable command I gave will do that. –  Kenneth Fisher Aug 5 '13 at 16:36
    
Thanx Kenneth..nothing unusual in the output :( Check the UPDATE2 in my original post btw –  PanosPlat Aug 5 '13 at 17:46
add comment

Yes its you recovery model, If you are using a stable and backed up, and not very big database.(Up to 100 GB) its good to use Simple recovery model.

Please refer below link. Its explain recovery models.

http://msdn.microsoft.com/en-us/library/ms189275.aspx

No as such issue in this change.

But the thing is you cannot recover transaction kind of stuff, or intermediate things.

Else perfectly fine.

Regards

Ashutosh Arya

share|improve this answer
    
The OP stated the MDF (data file) was 9GB in size. How does the recovery model affect the data file? Also why would the OP not be able to recovery "transaction kind of stuff, or intermediate things"? –  Shawn Melton Aug 5 '13 at 12:53
    
I'm not sure I understand If you are using a stable and backed up, and not very big database.(Up to 100 GB) its good to use Simple recovery model. Recovery model has nothing to do with the size of a database. It's perfectly reasonable to use simple recovery on ANY size database. It just depends on if your business want's point in time recovery or not. –  Kenneth Fisher Aug 5 '13 at 14:46
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.