Shrink Database Devices | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Shrink Database Devices

Dear ALL: Good evening, i am facing problem on the SQL SERVER 6.5 I initially created a database (dhotel_xx) with database devices dhotel_xx_dat (800MB)and dhotel_xx_log (400MB) . But after that, i found out that the size for the database devices is too large,is that any way to recude the database devices size ? what is the diffrent between shrinkDB and ShrinkDatabase ? thank you

Yes DBCC SHRINKDB is available and syntaxt is
DBCC SHRINKDB (database_name [, new_size [, ‘MASTEROVERRIDE’]]) |
SHRINKDB (database_name [, new_size [, ‘MASTEROVERRIDE’]])
When specified with only the database_name parameter, DBCC SHRINKDB returns the minimum size to which this database can shrink, and it lists all objects and indexes that are preventing you from shrinking it further. You cannot shrink the database to a size smaller than the one returned; use caution and drop or move the database objects until the returned size is acceptable.
When new_size is specified, DBCC SHRINKDB shrinks the size of the specified database to the value, as specified in 2K pages by the new_size parameter. DBCC SHRINKDB may shrink both the data and log portions of the database. To change the size of only the data or the log, shrink the entire database first and then use the ALTER DATABASE statement to increase the size of the data or log portion of the database.
To shrink a user database, the database must be set to single user mode. Use the sp_dboption system stored procedure to set this database option. After setting the database to single-user mode, it is recommended that you dump both the master database and the database you are shrinking, prior to using DBCC SHRINKDB.
To shrink the master or tempdb databases you must start the server in single-user mode (use the sqlservr command-line executable with the -m parameter). Important The database cannot be shrunk beyond either the size of the model database or to a size that is not a valid increment of allocation units. That is, new_size must be equal to or greater than the minimum size as returned by DBCC SHRINKDB plus any number of 512-byte increments (each of which is 256 2K pages). For example, if DBCC SHRINKDB returned a message that the ‘Database can be shrunk to 5376 pages’, the database can be shrunk to 5376 (10.5 MB), 5632 (11 MB), 5888 (11.5 MB), and so on. For more information, see the "Shrink a Database" example, later in this section.
After successfully shrinking a database to the desired size, back up both the master database and the database that has been shrunk. DBCC SHRINKDB is fully logged and recoverable except when used on the master database. The MASTEROVERRIDE clause is required when decreasing the size of the master database. However, use caution if you need to shrink the size of the master database, because recovery could fail if the system fails while shrinking the master database. Back up the master database prior to shrinking it. You must be the system administrator or database owner to execute this statement.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
the result return Current size of database Size database can be shrunk to
———————— ——————————
878080 878080 DBCC SHRINKDB (dhotel_sucasa, 878080)
after i run the command , the Log devices size is decrease but the data devices size still remain the same, and i went to the folder that store the database devices and the database devices size does not change ?
DBCC SHRINKDATABASE
Shrinks the size of the data files in the specified database. DBCC SHRINKDB
Available SQL Server 6.x, Removed from SQL Server 2000; no longer supported or available (BOL).
Can you try this:
dbcc shrinkdatabase(database_name)
or
dbcc shrinkdatabase(database_name, 10) 10 is the target percentage you can set. This will shrink the datafile too. -Johnson

DBCC SHRINKDATABASE is used from SQL 2000 and in SQL 6.5 you must use SHRINKDB.
Try to shrink the database using enterprise manager and select data device files. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |