Skip to main content

Shrink data and log files - MSSQL

Shrink data and log files

To shrink data and log files for the database, use below query.
use <database>;
DECLARE @mdfName varchar(60);
DECLARE @ldfName varchar(60);
SELECT @mdfName = file_name(1) FROM sys.master_files WHERE database_id = db_id() AND type = 1;
SELECT @ldfName = name FROM sys.master_files WHERE database_id = db_id() AND type = 1;
ALTER DATABASE <database> SET RECOVERY SIMPLE;
    DBCC SHRINKFILE (@mdfName, 0);
    DBCC SHRINKFILE (@ldfName, 0);
    DBCC SHRINKFILE (@mdfName, TRUNCATEONLY);
    DBCC SHRINKFILE (@ldfName, TRUNCATEONLY);

To set database recovery as FULL, use below query.
 ALTER DATABASE <database> SET RECOVERY FULL;

Labels:

Comments

Post a Comment

Popular posts from this blog

Identify the duplicates in MySQL

To identify the duplicate entries in MySQL, use below query.                      SELECT COUNT(*) as repetitions, group_concat(id, ' (', startTime, ', ', endTime, ') ' SEPARATOR ' | ') as row_data FROM GROUP BY startTime, endTime HAVING repetitions > 1 FYI, id, startTime and endTime columns should be changed based on your table schema. you can change the output format here like alias and the separator.
Post a Comment
Read more

Exclude or Include table(s) during creating backup from MySQL

 To exclude tables when you are creating database backup from MySQL, execute below query. mysqldump -u root -p<Password> DATABASENAME --ignore-table= DATABASENAME.TABLENAME  > BACKUPFILENAME.sql  To include  tables when you are creating database backup from MySQL, execute below query. mysqldump -u root -p<Password> DATABASENAME TABLENAME1 TABLENAME2  > BACKUPFILENAME.sql Take note that there is no whitespace between -p and the password..
Post a Comment
Read more