Developer forum

Thank you; yes, you assumed correctly.... ;-)

So far, I found that executing the following SQL statement cleans up the statistics tables and action log in the correct order.

DELETE FROM Statv2Session
DELETE FROM Statv2Page
DELETE FROM Statv2Object
DELETE FROM Statv2SessionBot
DELETE FROM Statv2UserAgents
DELETE FROM Statv2NotFound
DELETE FROM ActionLog

There might be other tables that can be cleared (such as the Trashbin) but I haven't tested them completely yet.

Imar
 

Old post I know, but from recent experience I would be careful with the first one if the db is very large (+10 GB). I filled up a server with the transaction log....

delete top(2000000) from Statv2Session

and then shrink database between runs.

This has a solution for a script: http://www.virtualobjectives.com.au/sqlserver/deleting_records_from_a_large_table.htm , but I havent testet it..

Hi,

Steen is right: the transaction log may explode if tables have a zillion rows. Consider using TRUNCATE TABLE where possible. Truncate does not log events.

Regards /Snedker

Deleting Records is a good way to go but this will only remove the ammount of data in teh Database, however the Disk Size allocated to the database will not be affected.

Has any one found a way to decrease the database size without affecting its performance? Any good tips on this area would be much aprecciated.

Regards,

Diogo Brito

Hi Diego

Deleting data will not make your database smaller right away, probably because of transaction log. My guess is that you have full logging on the database. Set it to simple and shrink the database, and your database will be way smaller.

BR Nicolai

To shrink immediately:

DBCC SHRINKDATABASE (@DBname, 10);
GO

The second parameter (10) is the amount of free space in percent to leave behind (for future growth) after shrink.

/Snedker

Which will only work if it is not your transaction log that is full of data because of full logging...

This set of statements seem to work quickly, except for the Statv2Object table, which has constraints.
Tried the "delete from Statv2Object" on a database and it took 53 minutes to remove 1870852 rows (!)

truncate table Statv2Session
truncate table Statv2Page
delete from Statv2Object
truncate table Statv2SessionBot
truncate table Statv2UserAgents
truncate table Statv2NotFound
truncate table ActionLog

Hi All,

We do set the databases to "full Recovery", however we truncate the logs daily so every day they are back to 0 KB. We do this so that if necessary we can restore a database to an exact point in time to avoid any data losses in case of any problems.

The big problem with the databases are mainly Statistics and their indexes. Just as an example, we have a solution where the table Statv2Object has 388 MB of Data and 1.8 GB in indexes.

Any idea why statistics create so many indexes?

On another important issue the database shrink has been several times refered in this thread, however in all my research, the database shrink is highly unrecomended due to the Index fragmentation that affects performance in the long run. Any thoughts on this matter?

/Diogo Brito

Just to add a little fun to the thread...

I can trumph your db-sizes... just tried to purge the Sessionsstatistics... prior to this the db size was 953 GB! Now it's 1.3 TB, and the size of the Sessionstatistics didn't move.

Just created a case on this as it's a Hostnordic hosted solution... It's been running for 10 years now :-D

Hi Jacob,

My guess is that the system couldn't complete the statistics deletion and you have end up with a very large log file, but that will have to be confirmed by Hostnordic.

Another guess is that the database size is a plataform miss calculation and you database is probably 13197,19 MB (13GB) (I have seen this scenario in our solutions) again to be confirmed by hostnordic.

BR Diogo Brito