October 29, 2008 at 8:22 am
FileName FileSize FreeSpace PercentageFree
DEFAULTDB_log120.8 MB111.3 MB 92.14 %
Do I need to change it?
October 29, 2008 at 8:33 am
Based on the couple of questions you have posted, I would suggest to you to go to some SQL Server Administristration classes focused on databases, files and maintenance.
You questions are very general, and accepting an answer on this forum might not give you the correct understanding you need to manage a sql server environment.
God Bless,
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
October 29, 2008 at 8:44 am
If your database has intensive insrt/update highload and transaction log grow up quickly, large and empty transaction log can be useful. In another way you should shrink transaction log file and configure compatibility level and beckup policy properly.
October 29, 2008 at 8:48 am
ThomasLL (10/29/2008)
Based on the couple of questions you have posted, I would suggest to you to go to some SQL Server Administristration classes focused on databases, files and maintenance.You questions are very general, and accepting an answer on this forum might not give you the correct understanding you need to manage a sql server environment.
God Bless,
Thomas
And in response to your actual question: No, I wouldnt worry about changing it though you should ensure that your autogrow settings are sensible (around 10-20%).
100MB-odd is not bad but based on the fact that is has about 10* the data size worth of free space, keep an eye on to ensure some weird query is not forcing growth thru bad coding.
And I agree with Thomas that you should get some basic training. If you do an MS course dont be fooled into doing the transact course first as even though MS says its a pre-req, its well above someone at your level and a waste of time (at this stage).
Or you could just get a good Wrox book 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 29, 2008 at 8:59 am
I'd set the autogrowth to a fixed amount, not a percentage.
October 31, 2008 at 5:41 am
/*
Hi nailosuper,
your question is a bit vague? you would like to tell if your log files are too big? one of the first things that you need to work out is how big is big?
you can run the following code to determine your disk capacities and the amount of free space: (please note that this code uses xp_cmdshell, so you will have to
be sa to run it and have it enabled:
sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
*/
SET NOCOUNT ON
DECLARE
@vcName CHAR(1)
--fsutil volume diskfree
CREATE TABLE #tbl_disks
(
DriveCHAR(1)
,[Space]INT
)
CREATE TABLE ##tbl_Volumes
(
DriveCHAR(1)
,InfoVARCHAR(2000)
)
CREATE TABLE ##tbl_Volumes2
(
InfoVARCHAR(2000)
)
INSERT INTO #tbl_disks
EXEC master.dbo.xp_fixeddrives
DECLARE file_cursor CURSOR FOR
SELECT Drive FROM #tbl_disks
ORDER BY Drive
OPEN file_cursor
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
FETCH NEXT FROM file_cursor
INTO @vcName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('
INSERT INTO ##tbl_Volumes (Drive)
VALUES ('''+@vcName+''')
INSERT INTO ##tbl_Volumes2
EXEC master.dbo.xp_cmdshell ''fsutil volume diskfree '+@vcName+':''
UPDATE ##tbl_Volumes
SET Info = v2.Info
FROM ##tbl_Volumes2 v2
WHERE v2.Info LIKE ''%Total # of bytes%''
AND ##tbl_Volumes.Drive = '''+@vcName+'''
TRUNCATE TABLE ##tbl_Volumes2
')
FETCH NEXT FROM file_cursor
INTO @vcName
END
CLOSE file_cursor
DEALLOCATE file_cursor
--SELECT * FROM #tbl_disks
SELECT
CAST(@@SERVERNAME as VARCHAR(35))
,v.Drive
,(CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1), (LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))/(1024*1024) TotalVolume
,d.[Space] FreeSpace
,(CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1), (LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))/(1024*1024) - (d.[Space]) as SpaceUsed
,CAST((d.[Space] / ((CAST((SUBSTRING(v.Info,(CHARINDEX(':',v.Info,1)+1), (LEN(v.Info)-(CHARINDEX(':',v.Info,1))))) as DECIMAL(38,5)))/(1024*1024) / 100)) AS INT) PercentFreeSpace
FROM ##tbl_Volumes v
INNER JOIN #tbl_disks d ON v.Drive = d.Drive
PRINT 'Results are returned in MB, unless otherwise specified'
DROP TABLE #tbl_disks
DROP TABLE ##tbl_Volumes
DROP TABLE ##tbl_Volumes2
SET NOCOUNT OFF
/*
once you have an idea of the capacity of your drives and how fill they are you can run the following t-sql which will tell you how bi ahh.. screw it, your question is too vague
and it will take too long to address everything that you are asking.
the short answer is yes.
give me some specifics. (do you have databasemail setup? how big is big? do you want to determine the file size by percentage etc.........
*/
October 31, 2008 at 6:56 am
Lynn Pettis (10/29/2008)
I'd set the autogrowth to a fixed amount, not a percentage.
I couldn't agree more. This applies to both log and data files, IMO. A 120 MB seems awful small but without knowing more detail, such as data file size, volume of transactions and such, it's difficult to supply meaningful advice. If you data file is around 200MB, transaction volume is low, maintenace is done infrequently (shame on you if that's the case!), then you're probably OK. See the other suggestions about training/books. Seems like you need to get a better handle on your environment. Most of us will help but our response can only be as good as the information we receive. Give us a better understanding of your environment and any issues you may be facing and we can point you towards a solution.
-- You can't be late until you show up.
October 31, 2008 at 7:42 am
recovery interval (min)03276700
allow updates0100
user connections03276700
locks5000214748364700
open objects0214748364700
fill factor (%)010000
media retention036500
nested triggers0111
remote access0111
two digit year cutoff1753999920492049
default full-text language0214748364710331033
default language0999900
max worker threads3232767255255
remote proc trans0100
network packet size (B)5123276740964096
index create memory (KB)704214748364700
priority boost0100
show advanced options0111
remote login timeout (s)021474836472020
remote query timeout (s)02147483647600600
cursor threshold-12147483647-1-1
min memory per query (KB)512214748364710241024
query wait (s)-12147483647-1-1
set working set size0100
user options03276700
affinity mask-2147483648214748364700
max text repl size (B)021474836476553665536
cost threshold for parallelism03276755
max degree of parallelism03200
min server memory (MB)0214748364700
max server memory (MB)4214748364721474836472147483647
query governor cost limit0214748364700
lightweight pooling0100
scan for startup procs0100
c2 audit mode0100
awe enabled0100
Cross DB Ownership Chaining0111
October 31, 2008 at 7:50 am
yulichka (10/31/2008)
recovery interval (min)03276700allow updates0100
.....
What's the question?
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
October 31, 2008 at 8:06 am
I am new with DB and new at my work and trying to figure out and get some help if my server is setup properly. Thank you
October 31, 2008 at 8:20 am
The only way for anyone to help is to know what issues your are facing. Nobody here knows your environment so if you are having problems, detail them and someone will help. Configuration settings, that you supplied, may be useful after we know what your facing but alone, they're relatively meaningless.
-- You can't be late until you show up.
October 31, 2008 at 8:26 am
For this server I am not facing any problems. I need to know if this server is sat properly. I am trying to see if server has enough memory, to sat integrity job and optimization job.Also need to know if transaction log and data sat properly. Thank you
October 31, 2008 at 8:35 am
It's near impossible to say without knowing a lot more about your environment, your setup, the databases you have, the activity on them, the hardware and a fair bit more.
Is there a senior DBA there that you can ask? Can you ask about some admin-related training?
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
October 31, 2008 at 8:42 am
Just to add a little to Gail's last post, and this is not meant as a putdown - if there is no other "more senior" DBA on staff, seek some quality outside help until you get training. Now, having said that, training is only going to get you so far. Experience and exposure to all the nuances that is SQL is going to be you're real teacher and that comes with time. Good Luck (I have a feeling you may need it).
Lastly, do not hesitate to post when you do have issues. Just keep in mind that the more detail you can provide, the better (and quicker) the reponses will be.
-- You can't be late until you show up.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply