Monday, July 29, 2013

[MS SQL Server] Guidance with shrink and rebuild/reorganise indexes

[MS SQL Server] Guidance with shrink and rebuild/reorganise indexes

Guidance with shrink and rebuild/reorganise indexes

Posted: 28 Jul 2013 06:56 PM PDT

Hi,I'm not really very offay with database shrinking per se. I know how to do it but there are some things that I'm not understanding so I'm after some explanation as to what's going on behind the scenes as to why I'm seing what I'm seeing.The server and DBs I'm working on are all development. Shrink is evil and the code was written by Satan himself, I know, and I would never attempt any of this in a production environment.First of all a bit of background information: The server I'm working on hosts several dev databases, one of them was huge, 75GB data file with one table containing half a billion rows. This was causing the server to run out of disc space so some action was required. I've managed to get this down to about 100 million by keeping only the last 14 days worth of records. All the DBs are also using Simple recovery.After reducing the size of this particular DB I shrunk it to about 15GB and set up a job to delete any records older than 14 days to prevent it from getting rediculously large again. Obviously this has caused massive fragmentation (not that it matters particularly on this DB) so as an opportunity to learn I decided to rebuild the indexes.From what I've read online, rebuilding should compact the pages and reclaim disc space. However, as I was running the rebuild (using a task in SSIS) I noticed the data file was actually growing in size.So my question (after going round the houses a bit) is, what's goin on here? Why is the DB growing while the indexes are rebuilding? Will the DB grow in size permanently or is this just a temporary growth state while indexes are dropped and recreated?I've read a number of articles about it but none of them seem to acknowledge this so I'm left a little confused.Thanks in advance.

Alert-tempdb freesapce?

Posted: 28 Jul 2013 04:54 PM PDT

Hi,I had received alert for Tempdb free sapce and reach the below threshold values.I want to know.. Tempdb location and drive having enough free space available. Tempdb reached 200 MB it will be automatically extent & allocating sapce if database autogrowth enable. so we don't want take action if more free sapce available in Disk.If it datafile size reached Full accoupied in disk and no free sapce available then we ill consider the take action.DATE/TIME: 7/29/2013 11:11:47 AMDESCRIPTION: The SQL Server performance counter 'Free Space in tempdb (KB)' (instance 'N/A') of object 'SQLServer:Transactions' is now below the threshold of 200.00 (the current value is 192.00).Thanksananda
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog