Post reply

I want to reclaim the space, but I dont want using Shrink Database

  • July 25, 2012 at 9:21 am

    #259376

    Hi,

    I'm a beginner of SQL Server DBA

    I want to reclaim the space, but I dont want using Shrink Database.

    Why I dont want to using Shrink Database?

    Because of Gail Shaw article, http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/ 🙂

    My disk is almost full. So, what is the other way to reclaim the space?

    Really need help

  • July 25, 2012 at 9:34 am

    #1517442

    don't use shrink database, use shrinkfile through TSQL. do you want to shrink the data file or the log file?

    WARNING, after shrinking the file is likely to grow again unless procedures are put in place to prevent it

    shrinking is the only way to get space back from a database and return it to OS.

    After the shrink you will just have to reorganize your indexes. Are you already running index reorganizes on a regular basis? If not this process would reduce space used within the database and slow down future growth.

    you could try dbcc shrinkfile(filename,truncateonly) on the data file, this might get you space back from the end of the file without moving data about

    Whats the growth factor on the files, it might be grabbing more space than it needs.

    run sp_spaceused in the databases, unless reserved space is less than the actual file size the shrink wont get you much.

    ---------------------------------------------------------------------

  • July 25, 2012 at 9:51 am

    #1517462

    Sir,

    I want to shrink log file. Did you have the command?

  • July 25, 2012 at 10:05 am

    #1517472

    it can be done via SSMS (right click database, all tasks, shrink file) but the command is dbcc shrinkfile(logfile logical name, size in MB)

    However, what recovery mode is the database in, if full or bulk logged are you taking log backups and what does command

    select log_reuse_wait_desc from master.sys.databases where name = 'yourdb'

    return

    ---------------------------------------------------------------------

  • July 25, 2012 at 10:11 am

    #1517476

    Oh. Got it sir.

    Let's say, I want to shrink 10 database. Did you have any script to make it this possible?

  • July 25, 2012 at 10:15 am

    #1517480

    I am afraid I don't, I'm sure there will be one in the scripts section of this site.

    Having read Gails blog. you are aware of the pitfalls of what you intend to do?

    ---------------------------------------------------------------------

  • July 25, 2012 at 10:22 am

    #1517487

    Ok sir. I will find the script.

    1. What's the different between shrink data file and shrink log file?

    2. Which one (shrink data / shrink log) will enforce the database to rebuild the index?

    3. Can I perform shrinking when database is in use?

  • July 25, 2012 at 10:29 am

    #1517493

    Little Nick (7/25/2012)

    1. What's the different between shrink data file and shrink log file?

    One shrinks the data file, one shrinks the log file.

    2. Which one (shrink data / shrink log) will enforce the database to rebuild the index?

    None will force the database to rebuild indexes, but you really should rebuild them after shrinking a data file.

    3. Can I perform shrinking when database is in use?

    Yes.

    The T-SQL command is DBCC ShrinkFile. The parameters are described in Books Online.

    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

  • July 25, 2012 at 10:35 am

    #1517499

    Little Nick (7/25/2012)

    Ok sir. I will find the script.

    1. What's the different between shrink data file and shrink log file?

    2. Which one (shrink data / shrink log) will enforce the database to rebuild the index?

    its the same command, it depends which file you specify. Shrinking log files just removes empty virtual log files till it gets to the first used one, there is no data movement, shrinking data files all the data is moved to the front of the file. Having done that you have fragmented the data which is why you then need to rebuild indexes after shrinking data files (not log files)

    Can I perform shrinking when database is in use?

    they will run but I wouldn't. Shrink log you can do safely when activity is low, shrinking data files is very intrusive and best done with users off the system.

    ---------------------------------------------------------------------

  • July 25, 2012 at 10:40 am

    #1517503

    The conclusion is

    1. Shrink log files -- index is not rebuild

    2. Shrink data files -- index is rebuild

    tq sir

  • July 25, 2012 at 10:43 am

    #1517507

    this might just be a language thing, but the conclusion is

    log shring - indexes don't need rebuilding

    data shrink, indexes will need rebuilding (by a seperate job you build and run yourself)

    ---------------------------------------------------------------------

  • July 25, 2012 at 10:46 am

    #1517511

    Sorry sir. 😀

  • July 25, 2012 at 11:58 am

    #1517552

    At the cost of sounding repetitive I am listing the commands that you may execute for shrinking the log file:

    Step 1 : Identify the name of the log file(s) that you may want to shrink as there could be more than one log file. For this you can open a query window in SQL Server Management Studio and first change the context to the database in question by running:

    Use <databasename>

    go

    For e.g.

    use Testlog

    go

    and then execute

    sp_helpfile

    go

    This will provide you with a list of all database files with sizes in KB ( both data and log)

    you can then identify the log file (usage = log)

    Step 2: and then execute the following command to shrink the log file to the size that you want to:

    dbcc shrinkfile (name,size in MB)

    go

    for e.g.

    dbcc shrinkfile (TestLog_log,1024)

    go

    and then execute the sp_helpfile command once again if it see if DBCC shrinkfile command did its job

    One of the reasons ( there are many) that the log file may not get shrinked to the value that you may have specified is because the log file may not be free, therefore to view the current log file usage percent you can execute the following command:

    dbcc sqlperf(logspace)

    go

    In case you want to empty\ truncate the log file you can perform a log backup ( if database is in simple recovery model).

    This will truncate the transactions which have already been committed, i mean not the active transactions.

    you can then attempt shrinking the log file one again.

    Hope this helps.

  • July 25, 2012 at 12:10 pm

    #1517558

    Index is not rebuilt by shrinking the data files. Because of fragmentation caused by shrinking the data files you need rebuild the indexes to degrag them.and you don't need to rebuild all the indexes you should decide based on the index fragmentation level after shrinking the data files

    Pooyan

  • July 25, 2012 at 12:14 pm

    #1517561

    Shrinking is the way to reclaim space. When its said "not to shrink", I think means not to shrink automatically without thinking about it. If you shrink and it just grows back to where it was, its best not to shrink. Maybe best to put on a bigger disk and allocate more space so it is not auto growing so much. As far as the log, if it is being backed up regularly, it should not get out of control.

Viewing 15 posts - 1 through 15 (of 17 total)

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