SQL Server Was 500 MB, Now is 18 GB... What???

[Rexolio]

Hi everyone,

First, I am not a SQL Server expert. I'm just a web developer. However, much of the time I find myself having to look into problems with our database because our network administrator/web host is limited in his knowledge of SQL Server.

Now, that being said, here's the problem: Today our web site started having Timeout errors on ASP pages. It wasn't on all ASP pages, but the ones that draw heavily from the database, this was occuring. NA rebooted the web server but the same thing kept happening. Meanwhile all other services were running fine. A look at our database revealed that it was a whopping 18 Gigabytes! According to NA, it was only at 489 MB yesterday.

Now, I've looked at several of our more widely used tables (we have LOTS) and can't see anything wrong (like a huge influx of posts/new records.) But something must have happened.

I know we can try and compress the database, but not sure how much it will compress. I need some more things to look for and to have him look into. Please, any suggestions/tips would be GREATLY appreciated.



rexolio@bellsouth.net
"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[ecobb]

Try looking at the transaction log file. If that's the problem, the best way to get rid of it is to do a backup of the transaction log. SQL Server will automatically truncate the log file after it's backed up.

Hope this helps!

 

[forecasting]

Rexolio - I just had a similar problem with a database I am working on. It turned out to be an improperly written trigger that was updating every record in the database each time an insert occurred. You might want to inspect your transaction logs to see if there is some similar out of control procedure.

Also, you can truncate the log file on command. First you need to set up the database.

Use Master
go
sp_dboption myDBname, "trunc. log on chkpt", true
go

then, anytime you want you can manually (or in some schedule action) truncate the log. the manual action is:

Use myDBname
go
checkpoint
go

in a batch you execute just include checkpoint as the last statement.

 

[NoCoolHandle]

<snip>Use Master
go
sp_dboption myDBname, &quot;trunc. log on chkpt&quot;, true
go
</snip>

Be very carefull here. If you use this option, you will not be able to do transaction log backups (which will remove log pages that have been checkpointed). It will force you into a state of always having to backup the entire database every backup. It will also limit your recoverablity to the last full backup as you will no longer be able to backup the log if the database datafiles are lost or corrupted.

If you want to clear the log, you can always perform a backup log DataBaseName with truncate_only, but the above statement holds true again, you will need to do a full backup of the database at this point!

I do agree with the above 2 posts, it is likley that logging is causing the problem.

To find out try running the following query in the database in question

select (size * 8) as SizeInKB, * from dbo.sysfiles

 

[osjohnm]

Have a look at the following to FAQ submitted by Terry and Foxdev.

faq183-1534
&quot;Shrinking Databases and Logs - SQL 7 and Higher&quot;

faq183-345
&quot;Why won't my transaction log shrink?&quot;

Terry's points to a whole lot of links about backing up and shrinking logs.

Foxdev explains the process for backing up logs.

I also suggest the following about backup strategy:
faq183-1784
&quot;How Do I Develop a Backup and Restore Strategy?&quot;

I'm not sure which version of sql server you are using 7/2000 but trunc.log on chkpt will work well for 7 but you'll lose the ability of transaction log backups and point-in-time restores.

For 2000 there are different recovery models Full, Bulk-Logged, Simple.

Simple is the same as trunc.log on chkpt as it only makes the necessary entries (to keep sql server functioning) into the tlog meaning that your log files is relatively small. It is the easiest to maintain but your only &quot;restore&quot; option is your last full backup. Therefore if you backup nightly and your database crashes at 4pm you lose an entires day of data. You cna't backup tlogs with the simple model.

Bulk-Logged supports point-in-time restores as you can perform tlog backups. It doesn't make entries for bulk inserts and select into transactions.

Full records every transaction against the database and allows you to backup the log file and restore to a point-in-time. It is the best model for production databases. The catch here is that only regular tlog backups and full backups shrink the log file. If this doesn't occur then you end up in the scenario that you're in now. It happened to me with a dev database where the log grew to 44GBs!!

Basically you need to decide on a recovery model and setup a backup strategy.

If you are running 2000 and you're recovery model is Full do the following to resolve it at present:

Right click the db > properties > options > change the recovery model to simple.
Then in QA run the following against that database>
dbcc shrinkfile(2,0)
Then change the recovery model back to full and implement a good solid backup/recovery plan.

Hope this helps
John

 

[SQLBill]

Another problem you might be having is: Have you done ANY backups? You should have done at least one FULL backup by now. If you haven't done any backups, you need to start doing them. I suggest:

a full backup immediately. Then begin doing transaction log backups and consider weekly full backups.

-SQLBill