asked
cpu consumption by sql server objects
In Our Production server 32 GB RAM, Last week In C drive total size is 72 GB and free space 23 GB Size But This week suddenly 10 GB of resource(disk space) occupied by SQL resources So Now total size is 72 GB and free space is 13 GB only. Is any Query to find which SQL server objects(view/table/sp/triggers etc) consumes this 10 GB of Disk space ? How to sort out in future? any method available ? Quick suggestions are highly appreciated.................... Can anyone expert in Sql DBA Can answer this question giants!!!! Note: this week no installation or update/delete/insert had done in cpu and sql server.
Just to be clear, CPU means central processing unit. That's the brain of the computer. Memory is the space used for temporary storage and disk is used for persistant storage. You're talking about the disk, not the CPU. CPU is not measured in GB or in free space.
If you mean disk space, then what do you mean by 'sql resources'? If you can identify the files that have added/grown by 10GB and they relate to a database, then it's more likely to be data (or logs) that has eaten this space - not SPs or code. If the culprit is a data file, then in SSMS, go to that database, right click in object explorer > **Reports** > **Disk Usage by Top Tables** will show you where the space is being used. It won't tell you whats changed/grown, but hopefully you can then apply your system knowledge. If the culprit is a log file, then you need to check that the recovery mode is set correctly and/or log backups are being successfully taken.
You probably have your data files on the C: drive and aren't backing up your transaction logs. If you don't back them up they will grow until the disk is full. When they cannot grow more you will not be able to add data to your database. Either start backing them up regularly (once a day/every 6 hours/etc.) or ascertain if you will ever require point in time recovery and switch your databases to the Simple Recovery mode. Databases get bigger.
11 People are following this question.
