July 24, 2008 at 9:58 am
Hello
I have noticed that a live database has suddenly started to grow at a scaring rate. Is there any way or method to use what and what is causing it to grow and know what i can do to ensure disk space?
Thanks
July 24, 2008 at 11:32 am
In SSMS right click your DB and select Reports->Standart Reports->Disk Usage by Top Table.
This should get you started.
July 24, 2008 at 11:42 am
can you do a sp_helpdb [database name]
Check your translog Growth.
Check your Growth % if changed.
Maybe it is the transaction Log.
Describe your Recovery mode.(Full/SIMPLE/BULK)??
how many users are connecting to the Database.
Was it upgraded recently from 2000 to 2005.
Can u highlight more info about the Db pl.
Maninder
www.dbanation.com
July 24, 2008 at 4:31 pm
Its a live database been used by an e-commerce application, in other words there is only one user logged in at a time.
The recovery model is set to full at the moment, i used profiler but couldn't really see any transaction, that was actually addling bulk data, will it be the transaction log file growing fast and if so, any ideas on how to handle it?
July 24, 2008 at 7:28 pm
b_boy (7/24/2008)
Its a live database been used by an e-commerce application, in other words there is only one user logged in at a time.The recovery model is set to full at the moment, i used profiler but couldn't really see any transaction, that was actually addling bulk data, will it be the transaction log file growing fast and if so, any ideas on how to handle it?
back it up as often as needed.
* Noel
July 24, 2008 at 10:39 pm
you can monitor transactionlog growth with dbcc sqlperf(logspace)
you can see if there is a long running transaction in the particular database with dbcc opentran ( your-database-name)
If dbcc opentran reports "No active open transactions" or only very recent transactions for your database but you are seeing the 'Log Space Used (%)' from dbcc sqlperf increase rapidly, then increase the frequency of your transactionlog backups.
July 24, 2008 at 10:42 pm
Is it the database or the log that's growing?
You need to schedule backups for both of those regularly.
July 25, 2008 at 1:38 am
Will setting the autogrowth of the 'file growth' to 1%, and leaving the maximum file size to unlimited do the trick?
July 25, 2008 at 7:27 am
Personally, I never do a percent growth (and 1% can kill performance if it's growing fast!). Always a fixed amount, depending on growth patterns. My best advice is to monitor growth and try and manually adjust it whenever possible, typically during low activity time, and growing it large enough each time so that this is an infrequent process by you. But, still continue to allow autogrow in case it needs to increase and you're not around...As far as ensuring disk space, plan ahead. If you cannot archive data, get more disk or move a file(s) to different physical partitions.
And, as Steve asked, is it the data or the log that's growing? The log can be managed, to a certain degree, by simple backups on a regular basis. Data will depend on your business needs.
-- You can't be late until you show up.
July 25, 2008 at 11:11 am
To add to this:
Like he said, growing the data file hurts performance since the database must be totally locked to grow. It may also give you fragmentation problems.:crying:
July 25, 2008 at 11:15 am
You DO NOT want to grow on demand. You want to set a size that will be used over the next 3-6 months, as Tom mentioned. Do it during periods of low demand.
Autogrow is for emergencies an unexpected data growth.
July 25, 2008 at 11:16 am
sam (7/25/2008)
To add to this:the database must be totally locked to grow
That's not true, I have users in when an autogrow is initiated....
sam (7/25/2008)
To add to this:Like he said, growing the data file hurts performance It may also give you fragmentation problems.
But this is most definitely true.
-- You can't be late until you show up.
July 27, 2008 at 4:18 am
tosscrosby (7/25/2008)
sam (7/25/2008)
To add to this:the database must be totally locked to grow
That's not true, I have users in when an autogrow is initiated....
It can seem that the database gets locked. Only one process is kicked off to grow the database, this process is the spid which tried to insert but found no free space. Other processes that require space must wait for the auto-grow to complete. Additionally resources locked by the process that kicked off the autogrow continue to be held and may block other processes.
If there is a connection timeout set on the client for the spid that has triggered the autogrowth and that timeout is exceeded then you can get in real trouble if under load.
If the connection timeout is reached when autogrowth is occuring then the spid is killed by the client, this causes the autogrow to fail and so no space has been added. The next process that was waiting for datafile space then kicks off the autogrow and if this doesn't complete within the client's connection timeout it too will be aborted and the autogrow will 'rollback'. And on and on it will go, until the autogrowth completes within the connection timeout for a particular connection.
When this happens you will see lots of blocking but with the spid at the head of the blocking chain changing, and messages in your event log about connections running out of space in the data file. If you connect to the sql server and expand the file manually it will resolve this issue.
This is why it's important to set the autogrowth to a fixed size that you know is able to be completed quickly even when under load, and you need to monitor all database files so you can pre-empt any autogrowth with a reasonably sized manual increase.
July 27, 2008 at 4:37 am
it looks like he is talking about the growth of the log files (ldf). specifically it needs to be analysed why the log file growth? r u performing any transaformation using SSIS(DTS)? is your tables are properly indexed? r u droping and recreating the table within the SSIS? If u could list out these queries, easily u could sort out the issue. in the mean time u cud try shrinking the database.
July 27, 2008 at 4:42 am
BAD-DBA-DAB (7/27/2008)
it looks like he is talking about the growth of the log files (ldf). specifically it needs to be analysed why the log file growth? r u performing any transaformation using SSIS(DTS)? is your tables are properly indexed? r u droping and recreating the table within the SSIS? If u could list out these queries, easily u could sort out the issue. in the mean time u cud try shrinking the database.
true, it could be that a job that trucates and shrinks the log file, has failed, due to which the log size has considerably increased??
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply