MS SQL server developer 2019 occupied 300G on my C drive
-
I installed MS SQL server developer 2019 edition last year. but now I find it occupied almost 300G on my C drive. the total size of my C drive is 500G, so I have to uninstall this SQL server 2019 instance to get enough space to install new Windows patches. what's wrong with my SQL sever developer 2019 installation?
diligent hands rule....
-
I installed MS SQL server developer 2019 edition last year. but now I find it occupied almost 300G on my C drive. the total size of my C drive is 500G, so I have to uninstall this SQL server 2019 instance to get enough space to install new Windows patches. what's wrong with my SQL sever developer 2019 installation?
diligent hands rule....
-
I installed MS SQL server developer 2019 edition last year. but now I find it occupied almost 300G on my C drive. the total size of my C drive is 500G, so I have to uninstall this SQL server 2019 instance to get enough space to install new Windows patches. what's wrong with my SQL sever developer 2019 installation?
diligent hands rule....
As Jacquers said, it's probably your database files. Specifically, if you have the "recovery model" set to "full", and you're not taking regular backups of the transaction logs, they will just keep growing. You can check the
log_reuse_wait_desccolumn in thesys.databasesmanagement view to check for other reasons why the transaction logs might not be shrinking.SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases ORDER BY name;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
As Jacquers said, it's probably your database files. Specifically, if you have the "recovery model" set to "full", and you're not taking regular backups of the transaction logs, they will just keep growing. You can check the
log_reuse_wait_desccolumn in thesys.databasesmanagement view to check for other reasons why the transaction logs might not be shrinking.SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases ORDER BY name;
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
I run this query and here is my result:
Name recovery_model_desc log_reuse_desc
AdventureWorks SIMPLE NOTHING
master SIMPLE NOTHING
model FULL NOTHING
msdb SIMPLE NOTHING
tempdb SIMPLE NOTHINGDo I have any issue with these tables?
diligent hands rule....
-
Check the database sizes. One or more of them may have grown. Maybe time to shrink some log files.
I used a free tool to check the file size and find the SQL log file folder is with the biggest size:
5.| PATH: Program Files\Microsoft SQL Server\MSSQL15.Peaker\MSSQL\Log | SIZE: 346.30 Gb | DEPTH: 5
how to shrink these log folder size?
diligent hands rule....
-
I used a free tool to check the file size and find the SQL log file folder is with the biggest size:
5.| PATH: Program Files\Microsoft SQL Server\MSSQL15.Peaker\MSSQL\Log | SIZE: 346.30 Gb | DEPTH: 5
how to shrink these log folder size?
diligent hands rule....
Checking table sizes in a database: DECLARE @str VARCHAR(500) SET @str = 'exec sp_spaceused ''?''' EXEC sp_msforeachtable @command1=@str Shrinking the database and logs: ALTER DATABASE [database name] SET RECOVERY SIMPLE WITH NO_WAIT; DBCC SHRINKFILE('database name', 1); ALTER DATABASE [database name] SET RECOVERY FULL WITH NO_WAIT;
-
Checking table sizes in a database: DECLARE @str VARCHAR(500) SET @str = 'exec sp_spaceused ''?''' EXEC sp_msforeachtable @command1=@str Shrinking the database and logs: ALTER DATABASE [database name] SET RECOVERY SIMPLE WITH NO_WAIT; DBCC SHRINKFILE('database name', 1); ALTER DATABASE [database name] SET RECOVERY FULL WITH NO_WAIT;
thank you for your tips! I learned new things from you. I think I find the reason: my PolyBase feature is not installed correctly, so it caused SQL server keep dumping big files. Now I fixed this problem.
diligent hands rule....
