How to Shrink a Sql Database
Open topic with navigation
TASK
Shrink and Re-Index a SQL Database
The transaction log file grows every time a transaction from Cartegraph completes. For example, select, insert, update, or delete. The log file continues to growing until the hard drive on the SQL gets full or when the file size reaches the limit set in SQL by the administrator. Large log files cause considerable performance issues with Cartegraph and may stop some calculations from running. A shrink and re-index should be run at least every two weeks to help with performance issues.
Before you begin: backup the database and verify no one is using the database.
NOTE: These instructions apply to both SQL Express and SQL Server databases.
SQL 2005
Shrink
To shrink the database and transaction log:
1. From the SQL Server Management Studio, copy this script into the Enterprise Manager or Management Studio window:
use DatabaseName
Checkpoint
dbcc checkdb
dbcc shrinkdatabase (DatabaseName,TRUNCATEONLY)
BACKUP LOG DatabaseName
WITH TRUNCATE_ONLY
dbcc shrinkfile ([Log File Logical Name or ID],1)
2. Replace the DatabaseName with the name of the database when you expand the left–hand Databases folder.
View the file name by right-clicking on the database and select Properties. The file page displays the location and name of both the database.
3. Replace the Log File Logical Name in the script with the name of the physical log file name. Add single quotes around the logical file name. This is referred to as Log File Logical Name in the script.
View the file name by right-clicking on the database and select Properties. The file page displays the location and name of the log file. The log file's logical name is found here.
NOTE: This is stored in the same directory as your database file. Log files have an extension of .LDF. But, if necessary, the MDF and LDF may be stored for security and drive space reasons.
4. Click Parse.
View the results in the lower pane and correct any syntax errors.
5. If there are no errors, click Execute to run the SQL script. View the results in the lower pane.
Re-Index
To re-index the database:
1. Copy this script into the query window:
USE DatabaseName
Declare @TableName varchar(255)
Declare TableCursor CURSOR FOR
Select TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE @Command varchar (255)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
NOTE: After pasting the script into the query window you may need to delete the single quotes in the re-index above BASE TABLE and/or Reindexing and then add them back into the script.
2. Replace the DatabaseName with the name of your database.
NOTE: Use the database name shown when you expand the left-hand Databases folder. Do not use the logical name.
3. Click Parse.
View the results in the lower pane and correct any syntax errors.
4. If there are no errors, click Execute to run the SQL script. View the results in the lower pane.
SQL 2008 and SQL 2012
Shrink
To shrink the database and transaction log:
1. From the SQL Server Studio, set the Microsoft Recovery Model to Simple to truncate the transaction log.
2. Click OK.
3. Set the Microsoft Recovery Model to Full.
4. Copy this script into the Enterprise Manager or Management Studio query window:
USE DatabaseName
GO
DBCC SHRINKFILE (N'Log_Name' , 1)
GO
5. Replace the DatabaseName with the your database's name.
NOTE: Use the name of the database shown when you expand the left-hand Databases folder. Do not use the logical name.
View the file's name by right-clicking on the database and select Properties. The file page displays the location and name of both the database and log file. The database file extension is .MDF.
6. Replace the 'Log_Name' in the script with the name of the physical log file name. Only what is in the quotes for the logical file name.
NOTE: This is stored in the same directory as your database file. Log files have an extension of .LDF.
7. Click Parse next to the Execute button.
View the results in the lower pane and correct any syntax errors.
8. If there are no errors, click Execute to run the SQL script. View the results in the lower pane.
9. Backup your database. Changing the Recovery Model from Full to Simple breaks the chain of backups and you need a clean backup to run.
Re-Index
To re–index the database:
1. Copy this script into the query window:
USE DatabaseName
Declare @TableName varchar(255)
Declare TableCursor CURSOR FOR
Select TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE @Command varchar (255)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
NOTE: After pasting the script into the query window you may need to delete the single quotes in the reindex above, BASE TABLE, and/or Reindexing and then add them back into the script.
2. Replace the DatabaseName with the your database's name.
NOTE: Use the name of the database shown when you expand the left-hand Databases folder. Do not use the logical name.
3. Click Parse next to the Execute button.
View the results in the lower pane and correct any syntax errors.
4. If there are no errors, click Execute to run the SQL script. View the results in the lower pane.
Source: https://nav.cartegraph.com/help/8_4/Content/Basic/Administrator/DatabaseManagement/SQLShrink_Task.htm
0 Response to "How to Shrink a Sql Database"
Post a Comment