Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - Recently I was contacted by one of my friends operating a large database that he was running out of

  • Recently I was contacted by one of my friends operating a large database that he was running out of disk space because of some table with many indexes unnecessarily created. Even after truncating the table the space was not given back to the OS. When does SQL Server give the space back once data is deleted?

    Posted on 01-28-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

6  Answers  

Subscribe to Notifications
  • Score
    10

    Under certain circumstances, SQL Server does not automatically reclaim space once used. If a table definition is altered to drop one or more variable length columns, the space consumed by those columns is not immediately made available for reuse by SQL Server. But that's to say that the space is forever lost. We can employee a DBCC utility reclaim the newly-freed space.

    To Calculate space

    (1) sp_spaceused
    Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

    Syntax

    sp_spaceused [[ @objname = ] 'objname' ] 
    [,[ @updateusage = ] 'updateusage' ]
    

    (2) using DMV sys.dmdbindexphysicalstats

    --check the size of the table 
    SELECT
            alloc_unit_type_desc
           ,page_count
           ,avg_page_space_used_in_percent
           ,record_count
    FROM
            sys.dm_db_index_physical_stats(
                DB_ID()
                ,OBJECT_ID(N'TableName')
                ,NULL
                ,NULL
                ,'Detailed') ;
    

    Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE
    Reclaims space from dropped variable-length columns in tables or indexed views.

    Syntax

    DBCC CLEANTABLE
    (
        { database_name | database_id | 0 }
        , { table_name | table_id | view_name | view_id }
        [ , batch_size ]
    )
    [ WITH NO_INFOMSGS ]
    

    All DBA and Developers must have observed when any variable length column is dropped from table, it does not reduce the size of table. Table size stays the same till Indexes are reorganized or rebuild. There is also DBCC command DBCC CLEANTABLE, which can be used to reclaim any space previously occupied with variable length columns. Variable length columns include varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Space can be reclaimed when variable length column is also modified to lesser length.

    DBCC command for reclaiming space is very simple. Following example is for AdventureWorks database and Person.Contact table.

    DBCC CLEANTABLE ('AdventureWorks','Person.Contact', 0)

    The result of DBCC is displayed below. DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC is fully logged operation. It also does not affect temp tables and system tables.

    http://blog.sqlauthority.com/2008/01/11/sql-server-reclaim-space-after-dropping-variable-length-columns-using-dbcc-cleantable

    • For huge tables it might use a lot of transaction log space, so you may want to divide this into smaller batches.
    • DBCC CLEANTABLE is not supported on system tables or temporary tables.

    Example

    http://weblogs.sqlteam.com/joew/archive/2008/01/14/60456.aspx

    Reclaim space after dropping fixed length column

    fixed length columns are stored at a fixed offset in each record (as opposed to variable length columns, for which the offset can vary and it is stored itself in each record). You can find the offset for each fixed length column by looking at the xoffset column in syscolumns. Since this offset is stored in only one place for all the records, it means that new records should use the same structure as the old records, and that's why the space is also wasted for new rows.

    The solution (besides recreating the table) is indeed to create and then drop (or drop and then recreate) a clustered index on that table, thus rebuilding the structure of the entire table.Creating/dropping Cluster index on a table to reclaim space from dropped column only for SQL Server 2005. On SQL Server 2000, you will have to recreate the table.

    Reclaim space in SQL Server 2005 database when dropping tables permanently

    DBCC Shrinkdatabase(0) -- Currently selected database or

    DBCC Shrinkdatabase() -- Named database

    However, shrinking files will likely fragment your tables, particularly larger onces, as contents of tables get moved about within the file, so once shrunk it's a good idea to defragment your tables. This, of course, will make your files grow again, but probably not so large as they were before you dropped your old tables. (Err, that assumes that the dropped tables contained large quantities of data...)

    Reclaim Transaction Log Space

    USE [AdventureWorks]
    GO
    DBCC SHRINKFILE (N'AdventureWorks_log' , 0, TRUNCATEONLY)
    GO
    

    This command will break the chains of LSN but it should not matter as in simple recovery model.

    Release space used after sql server delete in rows with BLOB varbinary(max) using TEXTIMAGE ON separate filegroup

    1. Rebuilt the clustered indexes of the table.
    2. Execute the below DBCC Command

      DBCC UPDATEUSAGE (0) GO

    http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f80ad8c7-e6c9-4538-a89b-8d6727050b9c

    DBCC UPDATEUSAGE

    Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

    Reclaiming freed space Example

    A SQL Server database has this table:

       CREATE TABLE MyTable
       (Column1 int NOT NULL,
        Column2 char(200) NOT NULL,
        Column3 varchar(40) NOT NULL,
        PRIMARY KEY NONCLUSTERED(Column1));
    

    No constraints or indexes other then those indicated by the CREATE TABLE statement exist for this table.

    The table holds a million rows. Since Column2 is no longer used, you decide to free up some space by dropping it:

    ALTER TABLE MyTable
    DROP COLUMN Column2;
    

    However, running sp_spaceused shows that no space is freed at all. Which of the following strategies can you use to reclaim the space freed up by removing Column2?

    Correct answer:
    Copy the existing data to a new table. Drop MyTable, create it again (without Column2), than copy the data back in., Copy the existing data to a new table. Drop MyTable, than rename the new table to MyTable., Create a clustered index on Column1 of MyTable, then drop this index again.

    Explanation:
    Dropping a column is a metadata-only operation. That means that the pages are not rebuilt. This makes the operation very fast, but also means the space is not reclaimed. And since metadata is the same for all pages, the same amount of space will also be lost on pages that are later allocated to the table. SQL Server will only free up this space if it has to rebuild all the pages of the table. For most tables, that is the case when you rebuild indexes (using ALTER INDEX .. REBUILD, or using the deprecated DBCC DBREINDEX option). But not on this table, since it is a heap. DBCC DBREINDEX will rebuild the nonclustered index on Column1, but not the actual data stored in the heap.

    Creating a clustered index does involve a rebuild of all the data pages, so this will also change the metadata and free up the space previously taken by Column2.

    Even though TRUNCATE TABLE does remove all the data pages and could theoretically be a great time to change the metadata, it doesn't work this way (as can easily be verified by testing).

    Creating a new table does of course free up the space that was required to store Column2 (though it temporarily uses extra space to store the duplicated copy).

    http://www.sqlservercentral.com/questions/Administration/67277

    Reduce the Virtual Log Files (VLFs) from LDF file
    If you have lots of VLFs, you can reduce them right away using the following method:

    USE AdventureWorks
    GO
    BACKUP LOG AdventureWorks TO DISK='d:\adtlog.bak'
    GO
    -- Get Logical file name of the log file
    sp_helpfile
    GO
    DBCC SHRINKFILE(AdventureWorks_Log,TRUNCATEONLY)
    GO
    ALTER DATABASE AdventureWorks
    MODIFY FILE
    (NAME = AdventureWorks_Log,SIZE = 1GB)
    GO
    DBCC LOGINFO
    GO
    

    http://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file

    Good Article Changing Table Schema – what goes behind the scenes – Part II http://www.sqlservercentral.com/articles/Design+and+Theory/67553

    Replied on Jan 28 2012 1:29AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    10

    First we will discuss about Problem..

    When an extent is allocated to a table (or index, etc) it belongs to that table until you

       o drop the table
       o truncate the table
    

    When you delete the data from the table -- the blocks will go onto the freelist for that table (assuming the amount of space on a block that was freed fell below the pctused).

    These blocks will be used for subsequent inserts and updates into this table.

    When you delete data from the index -- if the block the index entry was on is now "empty" -- that block will go back onto the freelist to be used anywhere in the index struct.
    Else that block stays where it is and data that would naturally go onto that block (because of where it is in the b*tree) will go there.

    Space is effectively reused when you delete. Your database will not show any new free space in sp_spaceused -- it will have more blocks on freelists and more empty holes in index structures.

    There are two issues:

    • Space is not reused by SQL Server during a transaction. The locking scheme that the engine currently uses prevents the reuse of records that are deleted in a transaction until that transaction is committed.

    If you do not manually start transactions, and if you put several records in a database, and then if you delete those records, the space remains unused.

    If you add more records, that space is used again. If you compact the database, you eliminate unused space.

    • SQL Server does not make databases smaller unless you compact the database. You must have two times the database size to compact the database because the compact action creates a new database. You cannot compact in place in SQL Server. To reduce the size of your database, you must run a compact process. When new records are added to the database, the database grows. If you delete those records, the space is then empty in your database but the database does not become smaller. When you add more records, the empty space in your database is used until the database must grow again. SQL Server databases do not become smaller during typical operation. You must use a compact process regularly to make sure that the database is not taking up extra space and that the database does not have internal errors.


    What are the diffrent ways to solve this Problem.? Plz see following Answer....

    AUTOMATIC DATABASE SHRINKING

    When the AUTOSHRINK database option has been set to ON, the Database Engine automatically shrinks databases that have free space. This option is set using the ALTER DATABASE statement. By default, it is set to OFF. The Database Engine periodically examines the space usage in each database. If a database has the AUTOSHRINK option set to ON, the Database Engine reduces the size of the files in the database. This activity occurs in the background and does not affect any user activity within the database.


    MANUAL DATABASE SHRINKING

    You can manually shrink a database or files within a database using the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statement. If a DBCC SHRINKDATABASE or DBCC SHRINKFILE statement cannot reclaim all the specified space in a log file, the statement will issue an informational message that indicates what action you must perform to make more space eligible to be freed.

    DBCC SHRINKDATABASE and DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.

    When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. Therefore, if a database was created with a size of 10 MB and grew to 100 MB, the smallest the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

    However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.


    SHRINKING THE TRANSACTION LOG

    If you know that a transaction log file contains unused space that you will not be needing, you can reclaim the excess space by reducing the size of the transaction log. This process is known as shrinking the log file.

    Shrinking database and log files can be set to occur automatically. However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default. If autoshrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused. The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger.

    How Does Shrinking the Log File Work plz click .....


    ROW VERSIONING-BASED ISOLATION LEVELS AND SHRINK OPERATIONS

    It is possible for shrink operations to be blocked by a transaction that is running under a row versioning-based isolation level. For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation is executed, the shrink operation will wait for the delete operation to complete before shrinking the files. When this happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log every five minutes in the first hour and then every hour after that.


    The below script will work for SQL 2008

    Use <database Name>
    GO
    
    Alter Database <database Name> Set Recovery Simple
    GO
    
    Alter Database <database Name> Set Recovery Full
    GO
    
    DBCC SHRINKFILE (‘<log file name>’, 1)
    GO
    
    DBCC SHRINKDATABASE(<DB_NAME>, 40)
    GO
    
    BACKUP Log <DB_NAME> with no_log
    EXEC sp_updatestats
    GO
    

    spcleandbfilefree_space

    Removes residual information left on database pages because of data modification routines in SQL Server. spcleandbfilefree_space cleans all pages in only one file of a database.

    spcleandbfreespace

    Removes residual information left on database pages because of data modification routines in SQL Server. spcleandbfreespace cleans all pages in all files of the database.


    MAINTENANCE PLAN WIZARD

    The Maintenance Plan Wizard helps you set up the core maintenance tasks to make sure that your database performs well, is regularly backed up, and is free of inconsistencies. The Maintenance Plan Wizard creates one or more SQL Server Agent jobs that perform these tasks on local servers or on target servers in a multiserver environment. Execution can be at scheduled intervals or on demand.

    Maintenance plans can be created to perform the following tasks:

    1) Reorganize the data on the data and index pages by rebuilding indexes with a new fill factor. Rebuilding indexes with a new fill factor makes sure that database pages contain an equally distributed amount of data and free space. It also enables faster growth in the future. For more information, see Fill Factor.

    2) Compress data files by removing empty database pages.

    3) Update index statistics to make sure the query optimizer has current information about the distribution of data values in the tables. This enables the query optimizer to make better judgments about the best way to access data, because it has more information about the data stored in the database. Although index statistics are automatically updated by SQL Server periodically, this option can force the statistics to update immediately.

    4) Perform internal consistency checks of the data and data pages within the database to make sure that a system or software problem has not damaged data.

    5) Back up the database and transaction log files. Database and log backups can be retained for a specified period. This lets you create a history of backups to be used if you have to restore the database to a time earlier than the last database backup. You can also perform differential backups.

    6) Run SQL Server Agent jobs. This can be used to create jobs that perform a variety of actions, and also the maintenance plans to run the jobs.

    How to: Shrink a File using SQL Server Management Studio plz click..

    The best article on shrinking database plz click....

    Thanks

    Yogesh

    Replied on Jan 28 2012 7:21AM  . 
    Yogesh Kamble
    142 · 1% · 349
    • Score
      9

      Question: "Recently I was contacted by one of my friends operating a large database that he was running out of disk space because of some table with many indexes unnecessarily created. "

      Okei, here I assume that we have no enough space.

      When does SQL Server give the space back once data is deleted?

      A shrink(DBCC SHRINKFILE) operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

      However, as per the first statement in the question, with running out of disk space already,If insufficient free space is available, the shrink operation cannot reduce the file size any further.

      verify that the file has adequate free space to remove by performing one of the following operations:

      Run the following query.

       SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
      FROM sys.database_files;
      

      Run the DBCC SQLPERF command to return the space used in the transaction log.

      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.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(thinking the same scenario as question stated) not to repeatedly shrink the database.

      Typically it is the log file that appears not to shrink. This is usually the result of a log file that has not be truncated. You can truncate the log by setting the database recovery model to SIMPLE, or by backing up the log and then running the DBCC SHRINKFILE operation again.

      Note: Do not shrink the data files for such case , because that would again blot the size and end up with error as the question mentioned they have less space.

      Okei, now coming to general scenario,

      DBCC CLEANTABLE - Reclaims space from dropped variable-length columns in tables or indexed views. This is a great technique to reclaim the space. However, for heaps, though it reclaims the space, it will not free the forward pointers.

      Replied on Jan 30 2012 5:08AM  . 
      Latheesh NK
      51 · 4% · 1178
    • Score
      0

      Test

      Replied on Feb 4 2012 11:11AM  . 
      Mitesh Modi
      18 · 10% · 3080
    • Score
      9

      When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits. Deferred drop operations do not release allocated space immediately. Therefore, the values returned by sys.database_files immediately after dropping or truncating a large object may not reflect the actual disk space available.

      To find the space currently used by these allocation units, use the sys.allocation_units catalog view

      http://msdn.microsoft.com/en-us/library/ms177495(d=printer).aspx

      Reclaim Space After Dropping Variable-Length Columns

      DBCC CLEANTABLE Reclaim Space After Dropping Variable-Length Columns. Variable length columns include varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Space can be reclaimed when variable length column is also modified to lesser length.

      Following example is for AdventureWorks database and Person.Contact table.

      DBCC CLEANTABLE ('AdventureWorks','Person.Contact', 0)
      

      Reclaim space in SQL Server 2005 database when dropping tables permanently

      DBCC Shrinkdatabase(0) -- Currently selected database
      

      Truncate the file
      You can truncate the log file by setting the database recovery model to SIMPLE, or by backing up the log and then running the DBCC SHRINKFILE operation again.

      BACKUP LOG <DatabaseName> TO DISK = '<BackupFile>'
      
      DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
      

      http://technet.microsoft.com/en-us/library/ms189493.aspx

      Unable to Shrink/FreeUp TempDB Space

      USE TempDB
      GO
      DBCC FREEPROCCACHE 
      GO 
      DBCC DROPCLEANBUFFERS 
      go 
      DBCC FREESYSTEMCACHE ('ALL') 
      GO 
      DBCC FREESESSIONCACHE 
      GO 
      DBCC SHRINKFILE (<TempDBLogicalFileName>,SizeInMB) 
      GO
      

      Just dropping the table and recreating it.

      Restart SQL Server service instance running the database.

      Another good way to reclaim space
      http://www.sqlskills.com/BLOGS/PAUL/post/Why-you-should-not-shrink-your-data-files.aspx

      To Calculate space

      To get accurate result you need to run DBCC UPDATEUSAGE then exec sp_spaceused

      exec sp_spaceused @updateusage = true
      

      http://www.mssqltips.com/sqlservertip/1805/different-ways-to-determine-free-space-for-sql-server-databases-and-database-files

      Replied on Feb 6 2012 4:16AM  . 
      Mitesh Modi
      18 · 10% · 3080
    • Score
      6

      When Table Column is dropped, we can reclain space usning DBCC CLEANTABLE;

      DBCC CLEANTABLE ('DBNAME','TABLENAME')
      

      TABLENAME is the table whose column is deleted.

      DBCC SHRINKDATABASE or DBCC SHRINKFILE can also be the options but shrinking DATA file is not a GOOD option. It creates fregmentation and to overcome that, you need to rebuild all the indexes. And SQL Server might reclaim all the space back in this process.

      Shrinking Log files can be another option. You can also make a better Maintenance plan to keep your transaction log truncated and short.

      But it also depends upon the Recovery Model selected against the database.

      Replied on Feb 9 2012 6:08AM  . 
      ATif-ullah Sheikh
      132 · 1% · 391

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.