|
Shrinking
Databases
Foreword
In SQL Server 2000 and in SQL Server 2005, each database contains at least one data file and one transaction log file. SQL Server stores the data physically in the data file. The transaction log file stores the details of all the modifications that you perform on your SQL Server database and the details of the transactions that performed each modification. Because the transactional integrity is considered a fundamental and intrinsic characteristic of SQL Server, logging the details of the transactions cannot be turned off in SQL Server.
Database sizes
EXEC master.dbo.sp_databases
See a list of all the databases along with their sizes (in Kb):
Database_Name Database Size
AccView 1258176
BoonSoft 4773120
master 6592
model 7936
msdb 12608
ReportServer 4096
ReportServerTempDB 3072
SMI_CustomerMMC 1150080
SMI_SupplierMMC 1662656
smiffySQL 18950464
SOLO 4236168
SOLO_Idea 2032000
SOLO_Liverpool 1463040
SOLO_Tunbridgewells 1475328
SOLOTempDB 1752512
tempdb 8704
As you can see from this list, SOLO has 4Gb of data, liverpool 1Gb of data and SmiffySQL which has a staggering 19Gb of data.
Not bad no?
I know you had your chance with Access and the compacting tools but now is SQL time.
To begin, select a database that you think occupies too much of your precious hard drive space.
Note
The transaction logs are very important to maintain the transactional integrity of the database. Therefore, you must not delete the transaction log files even after you make a backup of your database and the transaction logs.
View Databases
SQL Server 2000: SELECT *
FROM master.dbo.sysdatabases
SQL Server 2005: SELECT *
FROM sys.databases
You will obtain a list of databases defined in the system (includes the name, owner, creation date etc)
View your desired database files
SQL Server 2000: SELECT *
FROM dbo.sysfiles
SQL Server 2005: SELECT *
FROM sys.database_files;
Do not forget to select your desired database prior to running this command. The data file (the mdf) has the type_desc field ROWS and the log file has the
type_desc field LOG. Please take a note of the name of the log, the name of the database and their respective file paths.
USE SOLO;
SELECT * FROM sys.database_files;
file_id file_guid type type_desc data_space_id name physical_name state state_desc size max_size growth is_media_read_only is_read_only is_sparse is_percent_growth is_name_reserved create_lsn drop_lsn read_only_lsn read_write_lsn differential_base_lsn differential_base_guid differential_base_time redo_start_lsn redo_start_fork_guid redo_target_lsn redo_target_fork_guid backup_lsn
----------- ------------------------------------ ---- ------------------------- ------------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------ ----------- ----------- ----------- ------------------ ------------ --------- ----------------- ---------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------------------------ ----------------------- --------------------------------------- ------------------------------------ --------------------------------------- ------------------------------------ ---------------------------------------
1 3DFD01D6-FEC5-4EE8-A40D-078C800F124E 0 ROWS 1 SSC_Test E:\SQLData\MSSQL.1\MSSQL\Data\SOLO.mdf 0 ONLINE 528256 -1 128 0 0 0 0 0 NULL NULL NULL NULL 33237000000204300136 46F9735D-9875-4EC5-B9C5-8DCFE82D2CFA 2009-04-10 00:00:16.900 NULL NULL NULL NULL NULL
2 C94366D0-DD3E-4C70-B39D-3AFF4A223836 1 LOG 0 SSC_Test_log E:\SQLData\MSSQL.1\MSSQL\Data\SOLO_log.ldf 0 ONLINE 140888 268435456 10 0 0 0 1 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
65537 B1AA0721-3947-4EE1-96BD-DC62978F3167 4 FULLTEXT 1 sysft_products E:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\SOLOproducts 0 ONLINE 0 0 0 0 0 0 0 0 385000000973100001 NULL NULL NULL 33237000000204300136 46F9735D-9875-4EC5-B9C5-8DCFE82D2CFA 2009-04-10 00:00:16.900 NULL NULL NULL NULL NULL
65538 E48519AB-CAB9-4FDC-AC9E-27DEE79DB8C7 4 FULLTEXT 1 sysft_products_full E:\SQLData\MSSQL.1\MSSQL\FTData\products_full 0 ONLINE 0 0 0 0 0 0 0 0 12871000000756600001 NULL NULL NULL 33237000000204300136 46F9735D-9875-4EC5-B9C5-8DCFE82D2CFA 2009-04-10 00:00:16.900 NULL NULL NULL NULL NULL
Shrink Database
USE DatabaseName
GO
DBCC SHRINKFILE('TransactionLogName', 1)
BACKUP LOG 'DatabaseName' WITH TRUNCATE_ONLY
DBCC SHRINKFILE('TransactionLogName', 1)
While attempting to run DBCC SHRINKFILE(SSC_Test_log,1) I received the following error message:
Cannot shrink log file 2 (SSC_Test_log) because all logical log files are in use.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
13 2 1265 1265 1264 1264
So I attempted to change the percentage to 10 :DBCC SHRINKFILE(SSC_Test_log,10)
and received this error:
Cannot shrink file '2' in database 'SOLO' to 1280 pages as it only contains 1265 pages.
Cannot shrink log file 2 (SSC_Test_log) because total number of logical log files cannot
be fewer than 2.
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
------ ----------- ----------- ----------- ----------- --------------
13 2 1265 1265 1264 1264
I left this command as is because it seems that the system is already optimised in this area!
Next Command I ran was:
BACKUP LOG SOLO WITH TRUNCATE_ONLY
which executed without any issues.
And now for the interesting fact:
DBCC SHRINKFILE(SSC_Test_log, 1)
Prevent the transaction log files from growing unexpectedly
To prevent the transaction log files from growing unexpectedly, consider using one of the following methods:
Set the size of the transaction log files to a large value to avoid the automatic expansion of the transaction log files.
Configure the automatic expansion of transaction log files by using memory units instead of a percentage after you thoroughly evaluate the optimum memory size.
For additional information about the issues to consider when you configure the autogrow option, click the following article number to view the article in the Microsoft Knowledge Base:
315512 (http://support.microsoft.com/kb/315512/ ) Considerations for autogrow and autoshrink configuration
Change the recovery model. If a disaster or data corruption occurs, you must recover your database so that the data consistency and the transactional integrity of the database are maintained. Based on how critical the data in your database is, you can use one of the following recovery models to determine how your data is backed up and what your exposure to the data loss is:
- Simple recovery model
- Full recovery model
- Bulk-logged recovery model
By using the simple recovery model, you can recover your database to the most recent backup of your database. By using the full recovery model or the bulk-logged recovery model, you can recover your database to the point when the failure occurred by restoring your database with the transaction log file backups.
By default, in SQL Server 2000 and in SQL Server 2005, the recovery model for a SQL Server database is set to the Full recovery model. With the full recovery model, regular backups of the transaction log are used to prevent the transaction log file size from growing out of proportion to the database size. However, if the regular backups of the transaction log are not performed, the transaction log file grows to fill the disk, and you may not be able to perform any data modification operations on the SQL Server database.
You can change the recovery model from full to simple if you do not want to use the transaction log files during a disaster recovery operation.
Back up the transaction log files regularly to delete the inactive transactions in your transaction log.
Design the transactions to be small.
Make sure that no uncommitted transactions continue to run for an indefinite time.
Schedule the Update Statistics option to occur daily.
To defragment the indexes to benefit the workload performance in your production environment, use the DBCC INDEXDEFRAG Transact-SQL statement instead of the DBCC DBREINDEX Transact-SQL statement. If you run the DBCC DBREINDEX statement, the transaction log may expand significantly when your SQL Server database is in Full recovery mode. Additionally, the DBCC INDEXDEGRAG
statement does not hold the locks for a long time, unlike the DBCC DBREINDEX statement.
|
|