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

Why MSDBlog shows more size then MSDBData?

Well, why not? Are you backing up the transaction log on msdb regularly? If not, it will keep growing...

But you really have the application as the same drive as the system databases? If that is the case, I would ask the storage administrator to give you a new drive, and move the application database to that drive.

Answer 3

Thanks Erland.
IS it MSDB should be in FULL or normally in SIMPLE Recovery Model?
Should I change it to SIMPLE will be ok any time?
Following sql I can run will be fine, right?

USE master
ALTER DATABASE MSDB SET RECOVERY SIMPLE

checkpoint
go
checkpoint
go

USE MSDB
DBCC SHRINKFILE (MSDB_log)

Answer 4

Thanks Erland.
Application is on difefrent server only we host the database on this server.
So If switch MSDB from FULL to SIMPLE will be fine anytime, right using above code?
I know it won't make much different.

Answer 5

Thank You Erland.
Agreed but moving DB is not easy in Production as we need to consider lots of things like Down time, user affects ...

MSDV doesn't needed in FULL to changed in SIMPLE and shrink the log but nothing much size improvement as you mentioned.