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

Thanks Erland.
Agreed Shrinking database will be free up more space but I don't prefer to run DBCC SHRINK database operation as it will be more fragmentation although we are running weekly job for Index Maintenance.
Log file doesn't occupying much space as it's more Data File so DBSS SHRINK for Log file will not much helpful.
Please correct me if I misunderstood anything.

Answer 3

Thanks Erland.
Moving Database will be tuff right now so I might need to rethink about DBCC SHRINK as anyway we are running Index Maintenance job.

Answer 4

Hi @Pds ,

SQL server log will record every deleted rows, if you delete many rows, then the size of the log file will growth large.

A shrink operation is most effective after an operation that creates a large amount of unused space, such as a truncate table or a drop table operation. A shrink operation doesn't preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This fragmentation is another reason not to repeatedly shrink the database.

Answer 5

Thanks Cathy.
I will be running in batch but looking into disk space and log will fill up so need to watch and is it any way I can stop when near by filling up the space or certain batch size I can stop it?