SQL server disk space usage and log cleanup

I. query the usage of disk space and the size and utilization of data files and log files in each database:
1. query the remaining space of each disk partition:
Exec master. DBO. xp_fixeddrives

2. query the data files and log files of the database (including the file group, current file size, maximum file size, file growth settings, file logical name, and file path)
Select * from [database name]. [DBO]. [sysfiles]
The conversion file size is measured in MB:
Select name, convert (float, size) * (8192.0/1024.0)/1024. From [database name]. DBO. sysfiles

3. query the disk usage of the current database:
Exec sp_spaceused

4. query the size and utilization of log files of each database on the database server
DBCC sqlperf (logspace)

 

2. How to clean up logs when we find that our database logs are full.

Method 1:

Code

Declare   @ Dbname   Nvarchar ( 50 )
Set   @ Dbname = ' Dnn50 '
-- 1. Clear logs
Exec ( ' Dump transaction [ ' + @ Dbname + ' ] With no_log ' )
-- 2. truncate transaction logs:
Exec ( ' Backup log [ ' + @ Dbname + ' ] With no_log ' )
-- 3. Compress database files (if not compressed, the database files will not be reduced
Exec ( ' DBCC shrinkdatabase ([ ' + @ Dbname + ' ]) ' )
-- 4. Set automatic contraction
Exec ( ' Exec sp_dboption ''' + @ Dbname + ''' , '' Autoshrink '' , '' True ''' )

method 2
Step 1:
back up the entire database for emergency purposes
Step 2:
after the backup is complete, run the following statement in query Analyzer:
exec sp_detach_db yourdbname, true -- remove the registration information of this dB in MSSQL
Step 3:
Delete the log file or remove the log file from the directory where the log physical file is located
Step 4:
execute the following statement in query Analyzer:
exec sp_attach_single_file_db yourdbname, 'd: \ MSSQL7 \ data \ yourdbname_data.mdf '
-- register the database as a single file. If yes, MSSQL automatically generates a K log file for the database.