November 28, 2008 at 2:04 am
hi,
Actually my production server’s MDF anf NDf increases day by day ; i need to shrink the database but the problem is that whenever I do shrinking,its log files size increases drastically moreover I cannot set recover model to SIMPLE as we have implenented “Database Mirroring between OLTP server and OLAP server.
can this be possible that I can forcibly direct the log increment to secondary log file so that my main log file’s space can be saved .
or you have any alternative to do database shrinking ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 28, 2008 at 4:26 am
bhuvnesh.dogra (11/28/2008)
or you have any alternative to do database shrinking ?
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
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
November 28, 2008 at 4:39 am
yeah i do agree with ur points
But the thing is that the kind of database structure we have ,on every release(on every 4 month) some tables are dropped ,some table's schemas is changed which leaves FREE space
so to utilize that freespace we need to do database shrinking
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 28, 2008 at 5:05 am
Will that space be reused? If so, leave it. There's no harm in free space within a database.
If it will never be reused, do a shrink file (or a series of shrink files) on the data file, then rebuild all of your indexes afterwards. It will heavily impact your logs. No way around that if you have mirroring on.
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
November 28, 2008 at 5:34 am
Sorry i made a mistake i always do shrinking if datafiles
but is the main issue if i shrink the data file the related log file increase heavily ...and if i then create again indexes it will affect the log badly
so can you give me any solution that whenever i do shrinking it will use a dedicated log file ( avoiding main log file)?
that will provide me a good help
bhuvnesh
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 28, 2008 at 6:12 am
bhuvnesh.dogra (11/28/2008)
but is the main issue if i shrink the data file the related log file increase heavily ...and if i then create again indexes it will affect the log badly
Yes, it will. Both shrink and index rebuilds are logged operations.
so can you give me any solution that whenever i do shrinking it will use a dedicated log file ( avoiding main log file)?
Can't be done. In full recovery all operations have to be logged and if there's mirroring all logged operations have to go to the mirror. If they didn't, the mirror would no longer be a mirror of the first database and hence would be worthless for high availability
Either live with the log impact, or don't shrink the data file.
Or completely drop the mirroring, switch to simple, shrink, rebuild, switch back to full and set the mirroring up from scratch. If you do that, then for a time there's not failover server and hence you're not protected if something happens to the principal. Be aware of your availability requirements if you decide to go that way.
What kind of size reduction are you doing?
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
November 28, 2008 at 6:20 am
do a good full backup AND a transaction log backup. Then you will be able to shrink your db files.
November 28, 2008 at 6:29 am
Actually ,,,if i have a database with total size 100gb ,used : 87 gb and free 5 gb
then
i would shrink it upto 80 gb
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 28, 2008 at 7:11 am
bhuvnesh.dogra (11/28/2008)
Actually ,,,if i have a database with total size 100gb ,used : 87 gb and free 5 gbthen
i would shrink it upto 80 gb
If am reading this right, then you will only save 5GB , not really worth shirnking the database as it will more than likely grow back at some stage..
November 28, 2008 at 12:02 pm
bhuvnesh.dogra (11/28/2008)
Actually ,,,if i have a database with total size 100gb ,used : 87 gb and free 5 gbthen
i would shrink it upto 80 gb
You can't shrink that to 80 GB. 87 GB is in use. It's not possible to shrink a database to a size smaller than the data inside it.
Leave that database alone. The space you'll return to the OS is just not worth all of the cost of shrinking, rebuilding and mirroring all of that.
For me, a database has to be around 20-30% empty with 0% chance of reusing that space within 6 months before I'll consider a shrink.
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
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply