Share via

Data Drive Full

Pds 46 Reputation points
2021-11-08T21:20:49.207+00:00

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,264 questions
Accepted answer
  1. origae 76 Reputation points
    2021-11-27T06:41:24.273+00:00

    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

    0 comments

32 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Pds 46 Reputation points
    2021-11-18T23:16:54.357+00:00

    Erland,
    That's why I asked to get expert advice as I thought by the time it should delete the records having Index.
    Not sure why it's not using or efficiently using? any Clue?
    I have created Index before running the delete.
    This table should delete around 630000 Records

  2. Pds 46 Reputation points
    2021-11-20T00:07:31.66+00:00

    Thanks Erland.
    IT worked perfectly, only in 1 hour, deleted almost 1750000 records.
    Thanks for help!
    For Disk space, Now I have to SHRINK the Database and almost DB size is 350 GB so what you recommend?

  3. Pds 46 Reputation points
    2021-11-20T15:03:30.617+00:00

    Erland,
    I am completely agreed with your suggestion as it's most valuable it but currently it's not feasible as it's running 24X7 and need to coordinate with Vendor, Business people and other dept.
    Although, it will be in road map to move DB to another drive

  4. Pds 46 Reputation points
    2021-11-20T16:27:02.317+00:00

    Thanks Erland.
    You are absolutely right, Let see hoe I can coordinate earliest.
    But meanwhile I would like to run DBCC SHRINK for Data File so at least I can free up some space and that's why I was trying to take recommendation.
    I can run in small chunk to shrink it instead of running in bulk.