Post reply

space allocated

  • November 3, 2008 at 11:59 pm

    #126784

    Hi,

    I am having database wherein the space allocated to log file is 30 gb and i want to reduce it to 300 mb can some one please help me i am trying to do it but it gives me an error of space allocated should be more than the current.

  • November 4, 2008 at 7:08 am

    #893598

    well, shrinking the log can be acheived by using DBCC SHRINKFILE(logfilename, size). (depending upon how much space is currently being used, you will be able to shrink it to any size.)

    by executing the DBCC SQLPERF(LOGSPACE) command you can see how much space is filled up and therefore to what extent you are able to shrink it.

    by backing up the t-log you will release any data that has been commited, which will allow you to shrink the file to a smaller size. (if it doesnt release the space, just let me know, and we can see what is preventing it from doing so.)

    you can also use the truncate_only option, though it will affect your capacity to recover the database.

    I would advise regular t-log backups, this will help to keep the size of the log file down, while giving you the capacity to recover to any point in time in case of an emergency.

    that being said, depending upon your recoverability requirements, you may wish to switch to simple recovery mode. (which will immediately free up all commited data in the t-log and only keep data in the log until a transaction completes, then it will be removed) this usually significantly reduces the amount of space that is being utilized by the t-log. (though not the actual file size, you will still need to shrink it.)

  • November 4, 2008 at 9:22 pm

    #894040

    I had shrink the log file but there is no effect

    it is showing the same thing the space which is allocated is 30 gb but actual size of the log in the logfile is 300mb

  • November 4, 2008 at 10:32 pm

    #894052

    First take transactional log backup of your database. It will truncate inactive transactions from log. Then shrink the database and it should be reduced in size.

    DBDigger Microsoft Data Platform Consultancy.

  • November 5, 2008 at 1:48 am

    #894086

    is the database in Simple or full recovery?

  • November 5, 2008 at 2:19 am

    #894091

    There are a couple of things you need to check:

    - Recovery model of the database (if full then you need to make sure you're doing regular transaction log backups, otherwise the log will grow)

    - If your recovery model is set to full but there are large transactions that take place that cause the log to swell substantially (e.g. index rebuilds) then as part of that process you may want to change the recovery model of the database to Bulk Logged (but just for the duration of that transaction and then change it back to full).

    Here's some guidance on choosing the appropriate Recovery Model http://msdn.microsoft.com/en-us/library/ms175987.aspx

    Once you've decided which recovery model you want to use then the log can be cleared by backing it up (if switching to simple mode then it should clear out anyway) and then shrunk using the dbcc shrinkfile command.

  • November 5, 2008 at 4:08 am

    #894128

    yes it is in full recovery mode

  • November 5, 2008 at 4:26 am

    #894130

    In which case you need to establish whether it needs to be in full recovery mode, and if so then set up some transaction log backups on a regular basis.

  • November 5, 2008 at 4:32 am

    #894136

    santosh.lamane (11/3/2008)

    Hi,

    I am having database wherein the space allocated to log file is 30 gb and i want to reduce it to 300 mb can some one please help me i am trying to do it but it gives me an error of space allocated should be more than the current.

    In such case take the backup of your database and then create a new log file

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • November 5, 2008 at 8:14 am

    #894274

    You may want to look into the script posted in this Microsoft support article:

    http://support.microsoft.com/kb/256650

    While the article refers specifically to SQL 7.0, I've used it on SQL 2000 and believe it also works for SQL 2005. I've used this script in the past when I've had problems shrinking the transaction log, due to placement of data in the virtual logs. (See http://msdn.microsoft.com/en-us/library/aa174524(SQL.80).aspx for a full description of how this works.)

    CAUTION: Be sure to run a full backup on your database after you run this script, as you will have invalidated your tran log backups by performing a "truncate_only" backup on them.

    ~~ Everything in moderation, including moderation. ~~

  • November 5, 2008 at 8:46 am

    #894303

    Hi Santosh

    As every one suggested, you cant shrink log if this is in ful recover mode( can but will be of no use), see why you need full recovery mode, if not change to simple recovery mode, take a transcational backup and then shrink, it will shrink,

    Cheers

    🙂

Viewing 11 posts - 1 through 10 (of 10 total)

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