(imported topic written by gage)
Our database continues to grow at an alarming rate. For approximately 4700 clients we have an 18GB DB. We use SQL. The actual maintenance of the server is handled by our Systems Services staff. Systems staff says they have tuned the server to BF documentation but the DB continues to grow. I delete expired actions, stop actions that are left open unnecessarily, remove clients that have not checked in over 30 days, etc. Is there database tuning we are missing?
(imported comment written by jessewk)
The size is almost certainly related to the transaction log mode being set to full. You can set this to simple mode and then shrink the database.
(imported comment written by gage)
No, I checked that. We are at Simple mode on both BES DB and Reporting DB. I am currently running the BESDeletedCompRemover_6_2 tool that was posted in this forum. We are also using Deep Freeze – I am hoping this will make a considerable difference.
Thanks.
(imported comment written by BenKus)
Hey gage,
Deep Freeze will cause quite a bit of extra database size because it constantly resets the BES Clients as they “thaw” and “freeze”. This means that you will have many deleted computers growing in the database.
Try this:
Do you have many deleted computers? If so, the space can be reclaimed…
Run this: http://support.bigfix.com/bes/install/downloadutility.html#besdelcompremover and see if it helps the database size. Note that if you run this tool and a deleted computer comes “back to life”, it will appear as all “” entries for the properties.
Ben
(imported comment written by gage)
Ben:
Thanks for the suggestion. We have been trying to run that utility but keep getting errors because of space. I ran the query this morning and sure enough there are 304,994 records that need to be deleted. First time I ran the utility this is the error: DBD::ODBC::st execute failed:
Microsoft
[ODBC SQL Server Driv
log file for database ‘BFEnterprise’ is full. Back up the tra
he database to free up some log space. (SQL-42000)(DBD: st_exe
r=-1) at BESDeletedCompRemover_6_2.pl line 344, line 1
Second time: DBD::ODBC::st execute failed:
Microsoft
ODBC SQL Server Driver
SQL Server
The
log file for database ‘BFEnterprise’ is full. Back up the transaction log for t
he database to free up some log space. (SQL-42000)(DBD: st_execute/SQLExecute er
r=-1) at BESDeletedCompRemover_6_2.pl line 344, line 1.
Deleted -1 Question results records.
Deleting Long Question results. This may take a while…
Deleted 0 Long Question results records.
Deleting Fixlet results. This may take a while…
DBD::ODBC::st execute failed:
Microsoft
ODBC SQL Server Driver
SQL Server
Cou
ld not allocate space for object ‘(SYSTEM table id: -558665830)’ in database 'TE
MPDB’ because the ‘DEFAULT’ filegroup is full. (SQL-42000)(DBD: st_execute/SQLEx
ecute err=-1) at BESDeletedCompRemover_6_2.pl line 344, line 1.
Deleted -1 Fixlet results records.
Computer removal completed. A total of 96 result rows were deleted from the dat
abase.
Third Time: DBD::ODBC::st execute failed:
Microsoft
ODBC SQL Server Driver
SQL Server
The
log file for database ‘BFEnterprise’ is full. Back up the transaction log for t
he database to free up some log space. (SQL-42000)(DBD: st_execute/SQLExecute er
r=-1) at BESDeletedCompRemover_6_2.pl line 344, line 1.
Deleted -1 Question results records.
Deleting Long Question results. This may take a while…
Deleted 0 Long Question results records.
Deleting Fixlet results. This may take a while…
DBD::ODBC::st execute failed:
Microsoft
ODBC SQL Server Driver
SQL Server
Cou
ld not allocate space for object ‘(SYSTEM table id: -833114455)’ in database 'TE
MPDB’ because the ‘DEFAULT’ filegroup is full. (SQL-42000)(DBD: st_execute/SQLEx
ecute err=-1) at BESDeletedCompRemover_6_2.pl line 344, line 1.
Deleted -1 Fixlet results records.
Computer removal completed. A total of 1066 result rows were deleted from the d
atabase.
Any suggestions?
Thanks,
Alida Gage
(imported comment written by SystemAdmin)
Alida is running out of disk space for the database. When the tool runs to delete rows it causes the transaction log to grow until the disk if full. You’ll need to shrink the database to free up disk space, re-run the tool, shrink the database, etc… until the tool runs to completion.
(imported comment written by BenKus)
Hey Alida,
In addition to Tyler’s suggestion, consider removing any caps you have on the transaction log temporarily to allow the transaction to complete. When you are done, shrink the log and turn back on the transaction log limit.
Ben
(imported comment written by rdamours91)
I’m sure the Deepfreeze issue is the root cause of my database size problems as well. I’ve increased the size of my transaction logfiles as well. The utility is running again right now to deleted all those extraneous records
(imported comment written by gage)
Thanks to all. Talked with Weylan today at BF and he checked settings on server, which looked okay. We have run the delete utility several times and hopefully will continue to make progress. DeepFreeze has a white paper on working with BigFix but I have been unsuccessful in getting the department that uses DF to try them. Have you tried the steps in the white paper, and, if so, did they help? The utility ran for almost four hours this afternoon but here we are again with almost no space free. Not a SQL person, how much should I increase the size on the transaction log?
Alida
(imported comment written by rdamours91)
We have been trying to avoid using the suggestions in the white paper but may have no choice. The paper basically stops the Bigfix service at login. We will be using Deepfreeze scheduled maintenance midnight - 5 AM thawed which we restart the service when the pc’s reboot at midnight.
I have about 40GB to play with for the transaction file…hopefully it doesn’t use that much. I’m deleting pc’s that haven’t reported in 60 days…then eventually will progress to 45, 30, etc. I do regular backs which seem to get rid of the transaction logs…
I will also do regular database shrinks as well as reindexing. Hopefully I can get the database size back down as well as a performance increase.
(imported comment written by ckcheong)
The support site only show How do change the transaction logging behavior for SQL Server 2000, does anyone know how do change it in SQL Server 2005?
(imported comment written by rdamours91)
I had to re-run the job with deleteing computers not seen in 365 days to keep the transaction logfile size down. I will then slowly decrement the number of days deleting smaller batches of records. I did a Bes_enterprise database backup and then progressed to a right click on the database name with SQL Server Enterprise Manager and clicked all tasks, shrink database, clicked on files to shrink at the file level. On the pulldown I selected the transaction log file to compress pages and truncate…that got my log file down to 50MB.