Administration

Question

Post reply

Administration

subbarayudu.net 37258

Mr or Mrs. 500

Points: 540
More actions
March 10, 2010 at 6:33 am

#139282

Hi Every Body,
I take copy-only backup in one of the my Data base. Sparse file is increasing very fast. I don't know what is the resion please help me...
Thanks
Subbarayudu

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Copy only backups have nothing to do with sparse files. Sparse files are used in database snapshots.

You're going to have to give a lot more details as to what you're having a problem with.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

subbarayudu.net 37258 Mr or Mrs. 500 Points: 540 More actions · Answer 2

Sorry Gila, actually that is Snap shot backup.

May I know the at least some 2 or 4 regions regarding sparse file increasing very fast.

Any one idea about snap shot and sparse files, please come to this discussion

Regards

Subbarayudu

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

subbarayudu.net 37258 (3/10/2010)
Any one idea about snap shot and sparse files, please come to this discussion Regards
Subbarayudu

see of it helps you

http://www.sqlservercentral.com/Forums/Topic489120-149-1.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

A database snapshot is not a backup.

I know a fair amount about snapshots and sparse files, but I can't tell what the problem is.

Details please, the more the better.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150467 More actions · Answer 5

GilaMonster (3/10/2010)
A database snapshot is not a backup.

The confusion probably arose because you can RESTORE to a snapshot. You're right though - it isn't a backup.

To the OP:

Database snapshots grow whenever a page in the parent database is modified for the first time since the snapshot was taken. Ultimately, the snapshot will approach the size of the parent database. Snapshots are not part of a database backup strategy.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Sounds to me like an active database with many changes occurring after the snapshot is created.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Paul White (3/10/2010)
GilaMonster (3/10/2010)
A database snapshot is not a backup.
The confusion probably arose because you can RESTORE to a snapshot.

Or a mix-up with SAN snapshots

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

Just a point of clarification. You restore FROM a database snapshot TO the point in time when it was made.

Paul White SSC Guru Points: 150467 More actions · Answer 9

GilaMonster (3/10/2010)
Paul White (3/10/2010)
GilaMonster (3/10/2010)
A database snapshot is not a backup.
The confusion probably arose because you can RESTORE to a snapshot.
Or a mix-up with SAN snapshots

Maybe that too, though the repeated references to sparse files make that less likely. Good luck with this one.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 10

Steve Jones - Editor (3/10/2010)
Just a point of clarification. You restore FROM a database snapshot TO the point in time when it was made.

Yes, that is what I meant to say, of course.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

U J SSCrazy Points: 2423 More actions · Answer 11

Following could be the reasons that sparse file is getting bigger =>

1) lots of insertion/updation/deletion happening

2) not creating snapshots frequently (for eg. if you creating snapshot on weekly basis,then it will grow rapidly)

3) performing index defrag/rebuild operations

so to avoid the big sparse files take the snapshot frequently ( or just after index defrag/rebuild)

Also according to me, its not a good idea to create a snapshot on a database whose whole data is being changed.

GTR SSCrazy Eights Points: 8007 More actions · Answer 12

subbarayudu.net 37258 (3/10/2010)
Hi Every Body,
I take copy-only backup in one of the my Data base. Sparse file is increasing very fast. I don't know what is the resion please help me...
Thanks
Subbarayudu

Is it Production database, if so why do want to take snapshot of OLTP production database, what are you trying to achieve here?

EnjoY!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

Tushar-355587 (3/10/2010)
Also according to me, its not a good idea to create a snapshot on a database whose whole data is being changed.

Depends on the reason for using the snapshot whether it's a good idea or not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass