manually expand log space

Question

Post reply

manually expand log space

sqlfriend

SSC Guru

Points: 52588
More actions
March 27, 2011 at 12:55 am

#247911

Currently we set up log file to auto growth.

Is there a way to manually add space to log file?

Can it be done in both SQL and Management studio?

How can I do it in management studio too?

I do this is because our transaction log is full, after I did a transaction log backup, it frees up the size, and I think I may also need to add some space manually to transaction log, is this a good practice?

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

it is a good practice to pre-size your database files.

With ssms you can expand your file(s) using the object browser, right click on the database name, click properties, click Files and modify the size of the file of your choice.

If you want to see the TSQL equivalent, just click on the "script" button and SSMS will generate the statement for you.

(keep in mind to cancel the ssms window or it will already execute the statement)

Another point of interest is the autogrowth size. By default the may still be in percentages (10%). Growth in percentages may be tricky for larger files, so many of us alter that to growth in a controlable number of MB. e.g 100 or 500MB. Chances are high your system can extend that size with the os timeout time.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Anna, it may be better to stick to one thread on a problem at a time, otherwise you're likely to get conflicting advice if people haven't read the whole story.

http://qa.sqlservercentral.com/Forums/Topic1084539-391-1.aspx

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

sqlfriend SSC Guru Points: 52588 More actions · Answer 3

Thank you all.

Sorry for the confusion, in this case, I do mean for generic question about transaction log, not related with my other thread, before doing that, I thought this should be separate from my other case, so I made another one.

So to change the file size, do you mean I should change from the initial size field, because I cannnot find other field to change in database property screen. I kind of confused about the meaning of word of Initial, does that mean I can change any time in that field after database file already created and existed for a while?

Also, I can see log file and space used by using dbcc sqlperf (logspace), is there something equivalent to find out data file used space?

in management studio, when at database property screen- general - it shows database size, and space available, does that mean data file size ?

Thank you

Shabba SSCertifiable Points: 5007 More actions · Answer 4

Yes Anna, you would use the 'initial size' boxes to grow the data & log files accordingly. If you right-click on the database and go to Tasks, Shrink, Files you can see the 'Currently allocated space' & 'Available free space' of both data & log files.

There may be a tsql equivalent of dbcc sqlperf (logspace) but i'm not aware of one.

As far as i'm aware the size shown on the database properties page shows the combined size of data & log files. Space available seems to indicate the free space within the data file only...

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 5

You can find that info using:



use yourdb

          SELECT    db_name() DatabaseName

                  , (

                      SELECT    SUM(CAST(df.size as float))

                      FROM      sys.database_files AS df

                      WHERE     df.type in ( 0, 2, 4 )

                    ) AS [DbSize(pages)]

                  , SUM(a.total_pages) AS [SpaceUsed(pages)]

                  , (

                      SELECT    SUM(CAST(df.size as float))

                      FROM      sys.database_files AS df

                      WHERE     df.type in ( 1, 3 )

                    ) AS [LogSize]

                  , SUM(CASE WHEN a.type <> 1 THEN a.used_pages

                             WHEN p.index_id < 2 THEN a.data_pages

                             ELSE 0

                        END) AS [DataSpaceUsage(pages)]

                  , SUM(a.used_pages) AS [IndexSpaceTotal(pages)]

          FROM      sys.allocation_units AS a

          INNER JOIN sys.partitions AS p

                    ON ( a.type = 2

                         AND p.partition_id = a.container_id

                       )

                       OR ( a.type IN ( 1, 3 )

                            AND p.hobt_id = a.container_id

                          )