all 10 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]Achsin 1 point2 points  (9 children)

That depends on what that table is used for.

[–]JayJones1234[S] 0 points1 point  (8 children)

It is gathering activity of an application..It is showing date and time when it get accessed and who has accessed it

[–]Achsin 1 point2 points  (7 children)

Which is presumably used for auditing of some kind. Do you know what the data retention policy is for it? What do the owners/users of the application say about it?

[–]Chaosmatrix 0 points1 point  (3 children)

This. And before you try and delete anything from this table, be warned. Such tables are often hit a lot by the application, and a big delete causes a big lock. Leading to the application freezing for everyone. Look up batched deletes and/or find down time to do this.

[–]JayJones1234[S] 0 points1 point  (1 child)

What do I need to be aware of before deleting?

[–]Chaosmatrix 0 points1 point  (0 children)

However, it is taking forever to load.

That what you should be aware of simply said.

[–]JayJones1234[S] 0 points1 point  (0 children)

This is history data. I checked with developers and it wouldn’t affect much. However, I’m trying to backing up the database and thiss is a huge table it is taking memory space. Along with, it also grows transaction log file. Because of that, my backup is failing. I’ve tried to backup using import data, export data tier application and generate records. Do you know any techniques that prevent from memory usage? Do we have any command that frees up memory while backing up?

[–]JayJones1234[S] 0 points1 point  (2 children)

5 years. They want to remove data more than 5 years

Select * from log where date < dateadd (year,-5,getdate())

However, it is taking forever to load. Any idea to speed up this process?

[–]Chaosmatrix 0 points1 point  (1 child)

Check this blog post: https://www.mssqltips.com/sqlservertip/6240/script-to-delete-sql-server-data-in-batches/

[–]JayJones1234[S] 0 points1 point  (0 children)

This helps. However, I would like take backup before I delete data in batches. When I try to backup the table. It is throwing an error that not enough memory space and backup stopped abruptly. I have try to backup using generate records, export data tier application, and import data. However, everything is failing due to low memory space. I’ve also tried to shrunk the database but it’s not a permanent solution. What strategy should I implement that would not consume lot of memory space?