Data Drive Full

Question

Hello,
Currently I am trying to investigate as one of Vendor Application Database having Disk DATA drive almost full.
Right now it's hard to clean up data from the database as it's a vendor application.
I was looking some file growth and size from database side and have some question.

I was looking the File size:
Database FileName CurrentSize in MB FreeSpaceMB
msdb MSDBData 23
msdb MSDBLog 1918
LSSql LSSqlData 338882 15871.125
LSSql LSSqlLog 38191 37977.82031

Why MSDBlog shows more size then MSDBData?
LSSqlData and LSSqlLog is our main database.
All the files are configured in MB for auto Growth.

One of the big table also i was looking:

TableName   No_Of_Rows  ReservedSpace   DataSpace   Index_Size  UnUsed_Space
MSGLog  2640599 56448216 KB 55755552 KB 662008 KB   30656 KB

From Database side any indication with File size level?

TempDB is also currently not showing any size holding:

Database    FileName    CurrentSize in MB   FreeSpaceMB
msdb    MSDBData    23  
msdb    MSDBLog 1918    
LSSql   LSSqlData   338882  15871.125
LSSql   LSSqlLog    38191   37977.82031
tempdb  tempdev 8192    
tempdb  templog 1024    

Answer 1

Cathy,
Thanks for follow up.
Currently moving files to another disk or adding disk space is not feasible so we are thinking to delete the records from tables based certain criteria and will run periodically as a job to clean up data.
Is it any down side running DELETE to remove records over 365 days regularly?
IS it Deleting records from the table will help to gain the disk space?
Any other maintenance you are suggesting in Database to avoid any fragmentation in table after deleting?
I am not in favor to shrink the DB or files as it will cause heavy fragmentation.

If I run to delete records over 365 days like below will be right way?
Table is BIG around 2651000

DELETE FROM SqlTab_1
WHERE CreateDate < DATEADD(dd,-365,GETDATE())

OR following

DELETE FROM SqlTab_1
WHERE CreateDate < GETDATE()) -365

Answer 2

CAthy,

Thanks for Follow up.
Not solved but we will be fine at this point as we might need to add the disk space as right now can't move the files or add any files to another disk.

Answer 3

Cathy,
Thanks for follow up.
Currently moving files to another disk or adding disk space is not feasible so we are thinking to delete the records from tables based certain criteria and will run periodically as a job to clean up data.
Is it any down side running DELETE to remove records over 365 days regularly?
IS it Deleting records from the table will help to gain the disk space?
Any other maintenance you are suggesting in Database to avoid any fragmentation in table after deleting?
I am not in favor to shrink the DB or files as it will cause heavy fragmentation.

If I run to delete records over 365 days like below will be right way?
Table is BIG around 2651000

DELETE FROM SqlTab_1
WHERE CreateDate < DATEADD(dd,-365,GETDATE())

OR following

DELETE FROM SqlTab_1
WHERE CreateDate < GETDATE()) -365

Answer 4

Is it any down side running DELETE to remove records over 365 days regularly?

Well, that depends. If you are required to keep the data for regulatory reasons for 10 years, and the auditors come to visit you, you may end up in jail.

More seriously, this question have to be answered by someone with knowledge about the application. You said this was a vendor application, so you need to talk to the vendor to determine whether this is supported in the first place.

IS it Deleting records from the table will help to gain the disk space?

The amount of free space in the file system will not change, but there will be more free space inside the database.

If I run to delete records over 365 days like below will be right way?
Table is BIG around 2651000

>

DELETE FROM SqlTab_1
WHERE CreateDate < DATEADD(dd,-365,GETDATE())

Keep in mind that the deletions will be logged, so if you do it all in one take, your log file may explode. Delete in batches of 10000 rows at a time, and make sure there is an index so support the filter condition. And make sure that log backups run frequently, or else the log will explode anyway.

Answer 5

Thanks Erland.
I asked "down side running DELETE to remove records over 365 days regularly?" not for Auditing but any database side any other effect like Fragmentations or anything else?
Our main goal is to make the Disk Space FREE as currently almost full.
After running you suggest any other maintenance task to avoid any issue or it can be helpful to cleanup disk space?

I agreed that database will be having Free Space but it will be helpful to free up Disk Drive space?

I will be running in 1000, I might thinking to increase to 5000 batch job iteration.
Creating Index will good Idea.
Actually I have two column in my Where clause so I will create Non Clustered index including both the columns (Status, CreateDate)
We are also running every hour log backup.