Database size keep increasing size massively..

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Database size keep increasing size...

16 posts, Page 1 of 2

1 2 »»

Database size keep increasing size massively..

Rate Topic

Display Mode

Topic Options

Author

Message

purushottam2

Posted Saturday, January 19, 2013 3:26 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101

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

Post #1409171

purushottam2

Posted Saturday, January 19, 2013 4:27 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101

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?

Post #1409177

ben.brugman

Posted Saturday, January 19, 2013 4:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 152, Visits: 751

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

Post #1409178

purushottam2

Posted Saturday, January 19, 2013 4:34 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101

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.

Post #1409180

ben.brugman

Posted Saturday, January 19, 2013 4:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 152, Visits: 751

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

Post #1409181

ben.brugman

Posted Saturday, January 19, 2013 4:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 152, Visits: 751

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

Post #1409182

purushottam2

Posted Saturday, January 19, 2013 4:48 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101

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.

Post #1409183

purushottam2

Posted Saturday, January 19, 2013 4:49 AM

Valued Member

Group: General Forum Members
Last Login: Monday, April 29, 2013 1:59 AM
Points: 53, Visits: 101

Ben,

Yes we have maintenance window, because have very less user.

Post #1409184

ben.brugman

Posted Saturday, January 19, 2013 4:53 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 152, Visits: 751

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

Post #1409186

ben.brugman

Posted Saturday, January 19, 2013 5:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 152, Visits: 751

purushottam2 (1/19/2013)

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

Post #1409188

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions