Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Database shrink

The nightly database maintenance job performs a "shrink" of the database. This is causing me problems as my database is so big it takes too long too "grow" again. The database is 25Gb. Does anyone know how to turn off the DB shrink part of the nightly maintenance job?

Find more posts tagged with

Accepted answers

All comments

Network_Guru

25Gb is HUGE!

See the following topic;

www2.solarwinds.net/.../topic.asp

"Just an update regarding database size and maintenance.
I highly recommend compacting the database at least once a month,
and checking the status of the daily roll-up/statistics tables.
You can find this in the Web admin page > Admin > Database details > historical statistics.

When nodes are added or removed, the indexes on the tables start
to grow, and need to be re-indexed (compacted)."

-=Cheers=-
NG

Rob

I am aware that the database is huge. I'm keeping a lot of history. Hence my problem that the DB maintenance is not very good at maintaining my DB and I would like to turn off the bit which performs the shrink. Does anyone know if this is possible?

pancamo

We also have a 30GB mdf file database and a 25GB ldf file... And we are also having nightly database problems where we loose 1-2 hours worth of pollings..

If the nightly job and/or "shrink" portion can be run once a week, I also would like to know how...

Web Server USHOUSOLWNDS01
Operating System Windows 2000 Advanced Server
OS Version 5.0.2195
Service Pack 4.0

Database Server ushousolwnds01
Catalog Solarwinds1
Database Engine Microsoft SQL Server
SQL Version Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Security Model User Name \ Password
User Name SolarWindsNPM
Connection Timeout 20 seconds

Network Elements 8249 Elements
Nodes 1477 Nodes
Interfaces 4559 Interfaces
Volumes 2213 Volumes

Alerts 33 Alerts
Events 109661 Events
Pollers 13771 Pollers
Polling Engines 1 Polling Engines

Oldest Event 14-May-04 12:00 AM
Oldest Daily Traffic Stat 07-Jan-04 12:00 AM
Oldest Hourly Traffic Stat 14-May-04 12:00 AM
Oldest Detailed Traffic Stat 13-Jul-04 12:00 AM

Last Database Archive 10-Nov-04 02:15 AM
Next Scheduled Database Archive 11-Nov-04 02:15 AM
Database Archive Time 02:15 AM

Retain Detail Stats 120 Days
Retain Hourly Stats 180 Days
Retain Daily Stats 5000 Days
Retain Events 180 Days

ralf.hessberger

Dear Rob and pancamo,

we're operating a SL2000 and keep data up to a year. In former times we had 5-6 GB of mdb file, then I created a maintenance job on the SQL-Server, since then no data loss, no lost time of polling and a maximum size of 1GB even if all elements are in use now.

I can recommend by experience to use the Microsoft SQL-Enterprise Manager to have the maintenance done and not only Solarwinds own solution.

Good luck,
Ralf

Ralf Hessberger

Administrator Network Monitoring
IT Shared Services Northern Europe

Phone: +49 5721 702 740
mailto: rhessberger@stadthagen.faurecia.com

Rob

My problem with the DB shrink is this :
The Solarwinds DB maintenance performs a shrink on the database if there is excessive space in the DB. It will then perform a grow if there is not enough space in the DB. The DB grow fails because it takes too long for the DB grow to complete because of the size of the DB (25GB). The length of time the DB grow takes is outside of the Solarwinds DB timeout value so the no more entries are logged to the DB. I can perform maintenance on my SQL Enterprise server however i need to stop Solarwinds initiating the shrink. Any ideas ?

pancamo

can you post the contents/schedule of the job(s) here, and how do you turn off the nightly maintenance?

quote:Originally posted by ralf.hessberger

Dear Rob and pancamo,

we're operating a SL2000 and keep data up to a year. In former times we had 5-6 GB of mdb file, then I created a maintenance job on the SQL-Server, since then no data loss, no lost time of polling and a maximum size of 1GB even if all elements are in use now.

I can recommend by experience to use the Microsoft SQL-Enterprise Manager to have the maintenance done and not only Solarwinds own solution.

Good luck,
Ralf

Ralf Hessberger

Administrator Network Monitoring
IT Shared Services Northern Europe

Phone: +49 5721 702 740
mailto: rhessberger@stadthagen.faurecia.com

Network_Guru

Ive been able to manually compress the database from over 5Gb to 1.7Gb using the Orion Database Manager app, re-indexing one table at a time. However, something must have changed in this util in V7.5, as pulling up the node details web page takes 10-15 seconds now, rather than the 2-3 seconds before the last compress.
It appears the DB files were shrunk, not just the whitespace removed, and now must constantly perform a grow as data is written to the DB. The SQL.exe process now runs on and off at 25%, instead of the normal 3-5%. I'm not sure how to fix this...?

-=Cheers=-
NG

BryanBecker

pan...

Retain Detail Stats 120 Days
Retain Hourly Stats 180 Days
Retain Daily Stats 5000 Days
Retain Events 180 Days

That seems like a lot of data. I'm using the following :

Retain Detail Stats 14 Days
Retain Hourly Stats 30 Days
Retain Daily Stats 415 Days
Retain Events 30 Days

I also only have about 2500 elements where you have over 8000. Do other people tweak the roll-up schedule? What are you all using?

filipv

quote:I can recommend by experience to use the Microsoft SQL-Enterprise Manager to have the maintenance done and not only Solarwinds own solution.

Is it faster this way?

Nightly maintenance takes too much time on our test-setup. WAY too much time. I can't really understand why, since both CPU and memory usage are no where near 100%. In fact, NightlyMaintenance.exe takes only 1-2 % CPU during the process, and SQL process just over 10%.

It's a big problem!

Once again, is Enterprise Manager FASTER executing the maintenance job?

patwill66

I was having a similar issue when my database had reached about 60Gb. The database would pretty much freeze up and the server would crash. The Orion jobs wouldnt run and the SQL jobs would freeze up too.

The problem was with the automatic grow feature in SQL. It was trying to grow by 10% when it stared getting full. The database was filling up so fast it couldnt grow fast enough and it would basiclaly get full and crash.

My DBA ended up setting the grow to 512MB's instead of by 10%. This way with a large database size, the database
doesnt have to consume as much resources to grow.

Now the nightly jobs complete as they should and the server has stopped crashing on me.

NasaGeek

We have had many issues with the polling engine and the database. One of them was very similar with our DB maintenance running all night (12 hours), and during this time losing essentially all data polled. We were able to get it down to .5-1 hours, and we lose minimal data.

First let me complain about SW support. Most of the time support is friendly and clueless. I had heard “turn off all MS SQL maintenance” and from another “it’s OK to run MS SQL maintenance.” We have also been forced to fix an issue that support was unable to resolve. We had to do our own research, and after a few months we were able to pin down a memory leak in the engine from polling a particular device. Once the device was removed, the polling engine became stable. Their final solution was for us to separate the engine from the database; we did, and it made no difference. I would take anything from them with 2 grains of salt. I also wish they would support MySQL.

We currently have about 6000 elements with this schedule:
Retain Detail Stats 46 Days
Retain Hourly Stats 92 Days
Retain Daily Stats 365 Days
Retain Events 10 Days

Our DB is around 18GB, and after many trial and error changes, it’s stable. Engine and DB are on separate 3-4 year old Dell 2650’s w/ dual Xeons and 4 GB of Ram running server 2003, we use the /3GB option for the DB server.

We poll devices every 2 minutes, interfaces every 5, and collect on both every 5.

Here are the settings, this is on SQL 2000; these settings may or may not be the best for your configuration:

Automatically grow data and transaction by 10%, with unrestricted growth.
Simple recovery model, Torn page detection, auto shrink, and compatibility 80.

Here are the maintenance jobs:

Complete backup to disk nightly.
Optimizations – Reorganize data and index pages – Change free space per page percentage to 25%, ran weekly.

We run the mandatory Solarwinds nightly maintenance.

God Speed.

filipv

quote:We have had many issues with the polling engine and the database. One of them was very similar with our DB maintenance running all night (12 hours), and during this time losing essentially all data polled. We were able to get it down to .5-1 hours, and we lose minimal data.

I would appreciate it very much if you tell me how exactly were you able to get it down to .5-1 hours.