March 27, 2011 at 12:55 am
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
March 27, 2011 at 2:59 am
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
March 27, 2011 at 3:41 am
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://www.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
March 27, 2011 at 10:32 pm
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
March 28, 2011 at 4:13 am
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...
March 28, 2011 at 5:05 am
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
)
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
March 28, 2011 at 5:06 am
Nice one Alz!
March 29, 2011 at 9:33 am
Thank you, both of your replies are very helpful.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply