January 19, 2013 at 3:26 am
HI my database is keep increasing size very massively. On server i have only 1 database and database have around 30 tables. In which 1 table have around 1 lakh record and rest are very low.
Initially my DB size was around 1 GB, from last few days my server have started consuming 100% CPU utilization. So i checked the size by sp_spaceused command and it show around 100 GB hard disk space occupied, my server have the only 125 GB space.
Please let me know solution if any one is aware.
I am using SQL Server 2012
January 19, 2013 at 4:27 am
I have checked size of .mdf and .ldf file.
.mdf - 3 GB
.ldf - 96 GB
I have checked what all the traces is ON and find only System Trace is ON.
But i am surprised why log file is so big?
January 19, 2013 at 4:28 am
What is the size of the datafile('s), and what is the size of the logfile('s).
Maybe the log file is growing and taking up space. In that case backup the logfile. (Or if you do not need the backups for logfiles, you could use simple mode. But realise what the consequences are from using this mode).
ben brugman
January 19, 2013 at 4:34 am
Hi Ben,
data file size is 3 GB and Log file size is 96 GB, Database we are using from last 1 month. I want to know what is the reason to generate big log file.
January 19, 2013 at 4:37 am
Only saw your second post after anwsering.
Your log file is so big because is logs all datachanges in the database. A write takes about one size of the space of a write (and some overhead) an update can take just over twice the size of what your are updating.
You should design a recoverplan. This involves an backup strategie AND a recoverstrategie. Maybe you can live with a backup of the database a specific times and loose everything since the last backup. Then you can put your database in Simple mode and shrink the log file.
If you need the the log files, you should backup de logfiles. Backing up a log file makes them reusable and they the do not grow as much (or stop growing at all).
When doing maintenance do realise that this might generate a lot of logging. I have done maintenance scripts were the logging was a larger problem than the maintenance itself. So we splitted the maintenance in parts and did a backup of the logging in between.
success,
ben
January 19, 2013 at 4:42 am
My assumption is that the log file has the size it has because of maintenance actions. This could be scripts you have run or for example ONLINE clustering.
First question is, is the database used all the time or do you have a maintenance window where nobody is using the database?
ben
January 19, 2013 at 4:48 am
HI Ben.
Thanks for your promptness,
Only 20 users are using my application, and there is no frequently use of database interaction. And this log file increased up to 96 GB in 1 month, so i a am not able to understand is this a normal behavior of SQL server or some thing is wrong.
January 19, 2013 at 4:49 am
Ben,
Yes we have maintenance window, because have very less user.
January 19, 2013 at 4:53 am
Hello I am going offline, but want to give some help.
So I am going to assume that you have a maintenance window where NOBODY is using the database.
1. Make a full backup of the database.
2. Set your database to simple mode.
3. Shrink the logfile.
4. Set the database in full recover mode.
5. Make a backup of the logfile.
6. Make a full backup of the database.
7. Make a backup of the logfiles.
8. Now the database is available again.
This is a bit cumbersome, Step 1 protects you against dataloss during the total period from 1 to 8.
Step 5/6/7 creates that you have a backup and the unbroken chaing for log backups.
Step 2/3 get's rid of the large logfile.
Step 4 switches back to the save mode.
If you do not need the full recover mode (you should be very sure of that, steps 1/2/3 will be enough).
Please inform us about your progress.
Ben
January 19, 2013 at 5:05 am
purushottam2 (1/19/2013)
HI Ben.Thanks for your promptness,
Only 20 users are using my application, and there is no frequently use of database interaction. And this log file increased up to 96 GB in 1 month, so i a am not able to understand is this a normal behavior of SQL server or some thing is wrong.
If it is not maintenance, somebody is 'probably' doing a large number of updates.
With inserts the database should grow. So only if the are insert / delete 'pairs' this could be the problem, this is unlikely.
Selects do not cause logging.
So if it is not the maintenance which causes the logging, the most likely cause is Update's. So you should look for large updates. There are a number of reports which can help with that. Maybe it is an update where a WHERE clause is missing. This could be an update which is effectively doing nothing, but still generates a lot of logging.
At this moment I exclude that SQLserver is behaving wrongly, this seems very unlikely to me.
ben
January 19, 2013 at 5:08 am
Thanks Ben, I will check all the stored procedures..
January 19, 2013 at 9:53 am
My guess is full recovery model and no log backups
Please read through this - Managing Transaction Logs[/url]
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
January 19, 2013 at 7:06 pm
You can find out the reason why the log grows (why it is not reused, why the VLF's are not cleared)
from the log_reuse_wait_desc in sys.databases:
select name, log_reuse_wait_desc, recovery_model_desc from sys.databases
If it says "NOTHING" then it was a very large operation that finished (e.g. index rebuild) and the log should not grow anymore.
If it is "ACTIVE_TRANSACTION" than you have a very long/old open transaction. Execute "DBCC OPENTRAN" to see what transaction it is and when it started.
If it says "LOG_BACKUP" then your log backup job is not working (job is disabled, failing at every execution or there is no log backup job at all :w00t:).
After fixing the source of the problem (finishing long tran, getting log backup job not to fail etc),
you can shrink the log.
If you do not want to lose point-in-time restore ability (you don't want to break the log backup chain) do this:
1) CHECKPOINT -- not mandatory
2) BACKUP LOG myDbName TO DISK='D:\transactionLog1.trn' -- change the file name on each execution
3) DBCC SHRINKFILE('MyTransactionLogName', 1000) -- 1000 MB, choose anything you like but not too small ! sp_helpfile to find the log name.
Repeat this 3 commands until the log is shrunk (usually 1 or 2 cycles are enough).
Use "DBCC SQLPERF (LOGSPACE)" to see the log size and percentage of active (uncleared, unable to be reused) VLF's. Normally that percentage should be low or close to zero.
January 19, 2013 at 8:27 pm
Thanks GilaMonster and Vedran.
I am supposing to set recover mode as SIMPLE and will check transaction log backup job is working or not.
Thanks to all 🙂
January 20, 2013 at 2:25 am
purushottam2 (1/19/2013)
I am supposing to set recover mode as SIMPLE and will check transaction log backup job is working or not.
One or the other. In simple recovery you can't take log backups (and hence don't have the ability to restore to a point in time)
See the article I posted.
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply