Recently I have deleted unwanted records from my SQL server database table, what i realise is even after deleting records, space used by database is not reducing. After browsing help available on Internet, I found out
2) Even after deleting table , sql server doesn't reduce size of database.
3) Instead of Freeing space for deleted records, sql server marks pages containing deleted records as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages.
So In order to claim database space after deleting records in Table, go through following steps:
1) Check what is Size of your Database using following command?EXEC SP_SPACEUSED
2) Delete Records from table, If you have already did that skip this step.
3) Run below command to claim unused database space.
DBCC SHRINKDATABASE(0)
DBCC SHRINKDATABASE command - Shrinks the size of the data and log files in the specified database.
Best Practise to use this command1.
1.A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
1.A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
2. Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
3. A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
3. A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.
http://msdn.microsoft.com/en-us/library/ms190488.aspx
No comments:
Post a Comment