1 of 6 in Topic
Next >>
| Topic : SQL Server 2005/2008 Profiler |
|
|
Posted in Community :
Wipro Technologies |
Infosys Technologies |
Satyam Computer Services Ltd |
5 more ...
|
||
|
Activity:
2 comments
1292 views
last activity : 07 06 2010 20:18:04 +0000
|
||
|
|
Database Shrinking in SQL Server How do I reclaim space in SQL Server?
Shrinking Data Files (*.MDF / *.NDF)
If you haven't enabled the auto-shrink setting, there may be times when your database has been cleared of significant amounts of data, and you'd like to reclaim the space on disk that the partially empty MDF file is taking up. To do this, you can use the DBCC SHRINKDATABASE command. See this MSDN entry for the technical documentation on the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands:
DBCC SHRINKDATABASE
DBCC SHRINKFILE
(A lot of people assume that if you delete a lot of rows or drop a table, that the database size will shrink automatically, but this is not the case. In fact, it is rare that you would want to do this; see Tibor's article for more information.)
And see these articles for more information on shrinking SQL Server databases:
SQL Server Architecture: Shrinking Datab...
Creating and Maintaining Databases: Shri...
Shrinking Log Files (*.LDF)
Another problem you might be having is that the transaction log is filling up too quickly, or staying far too large.
See KB #110139 to understand why the transaction log might fill up.
See KB #317375 for information about full or unexpectedly growing log files.
See KB #873235 for information about preventing unexpected log file growth.
Note that the transaction log is expected to shrink and grow depending on what kind of work you're doing in your database, so the size it expands to might be normal. In this case, shrinking it—while recovering disk space—might actually hurt performance... because the log might have to expand again the next time such a transaction runs, but the database is first going to have to expand the file to make room.
For information on how and why the log file grows and empties, based on your recovery model and backup strategy, see the following article:
SQL Server 2000 Backup and Restore
Depending on your recovery model, you might be able to simply run this command to manually truncate the log:
This empties the log file but does not reduce its size. You will then want to use DBCC SHRINKFILE to reduce the size of the individual LDF file(s). See this MSDN article for more information on shrinking a transaction log:
SQL Server Architecture: Shrinking the T...
For official Knowledge Base information on shrinking the log, see KB #256650 (SQL Server 7.0 / MSDE 1.0) and KB #272318 (SQL Server 2000 / MSDE 2000).
Moving Data / Log Files
If you find that your data and log files are outgrowing their disk(s), see KB #224071 for information about altering file locations using sp_detach_db / sp_attach_db. You might solve your problem simply by separating the data files from the log files and splitting them between drives; this will often yield better performance as well, since the two resources aren't contending for disk i/o.
Shrinking Data Files (*.MDF / *.NDF)
If you haven't enabled the auto-shrink setting, there may be times when your database has been cleared of significant amounts of data, and you'd like to reclaim the space on disk that the partially empty MDF file is taking up. To do this, you can use the DBCC SHRINKDATABASE command. See this MSDN entry for the technical documentation on the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands:
DBCC SHRINKDATABASE
DBCC SHRINKFILE
(A lot of people assume that if you delete a lot of rows or drop a table, that the database size will shrink automatically, but this is not the case. In fact, it is rare that you would want to do this; see Tibor's article for more information.)
And see these articles for more information on shrinking SQL Server databases:
SQL Server Architecture: Shrinking Datab...
Creating and Maintaining Databases: Shri...
Shrinking Log Files (*.LDF)
Another problem you might be having is that the transaction log is filling up too quickly, or staying far too large.
See KB #110139 to understand why the transaction log might fill up.
See KB #317375 for information about full or unexpectedly growing log files.
See KB #873235 for information about preventing unexpected log file growth.
Note that the transaction log is expected to shrink and grow depending on what kind of work you're doing in your database, so the size it expands to might be normal. In this case, shrinking it—while recovering disk space—might actually hurt performance... because the log might have to expand again the next time such a transaction runs, but the database is first going to have to expand the file to make room.
For information on how and why the log file grows and empties, based on your recovery model and backup strategy, see the following article:
SQL Server 2000 Backup and Restore
Depending on your recovery model, you might be able to simply run this command to manually truncate the log:
| BACKUP LOG <database name> WITH TRUNCATE_ONLY |
This empties the log file but does not reduce its size. You will then want to use DBCC SHRINKFILE to reduce the size of the individual LDF file(s). See this MSDN article for more information on shrinking a transaction log:
SQL Server Architecture: Shrinking the T...
For official Knowledge Base information on shrinking the log, see KB #256650 (SQL Server 7.0 / MSDE 1.0) and KB #272318 (SQL Server 2000 / MSDE 2000).
Moving Data / Log Files
If you find that your data and log files are outgrowing their disk(s), see KB #224071 for information about altering file locations using sp_detach_db / sp_attach_db. You might solve your problem simply by separating the data files from the log files and splitting them between drives; this will often yield better performance as well, since the two resources aren't contending for disk i/o.
2 comments on "Database Shrinking in SQL Server"
Sort by:
Most Recent
Top Rated
Commented by
RAJEEV KUMAR, Software Developer, NETPROPHETS CYBERWORKS PVT. LTD.
| 03 26 2010 14:36:21 +0000
Report Abuse
Not Rated
Commented by
Samir Nigam, Sr. Software Engineer , SRM TECHSOL Pvt. Ltd.
| 07 26 2008 02:43:50 +0000
Report Abuse
Rating : +1
Found the article
"Database Shrinking in SQL Server"
interesting ?
Share with your connections and communities
Viewers also viewed
|
|
What is SQL Mail in SQL Server and do we use it to send a mail. |
|
|
13 referals
2 answers,
140 views
|
|
|
Industrial production shrinks by 2% as compared to the previous years 8% despite a stimulus... |
|
|
0 referals
16 comments,
685 views
|
|
|
Trend in the database industry is rapid DBMS versioning. It seems like we just start to get a... |
|
|
2 referals
0 comments,
260 views
|
|
|
|
|
|
|
Recent Knowledge (14)
|
|
Facebook will have market value of $234 billion by 2015, up from its current valuation of $85... |
|
|
872 referals
22 comments,
490 views
|
|
|
Military Strength: Comapre India and Pakistan Manpower and Ground Forces India has the second... |
|
|
49 referals
9 comments,
67492 views
|
|
|
Free Style Marketing How many times have you been to a business conference that guided the world... |
|
|
72 referals
4 comments,
723 views
|
|
|
|
|
Sponsored Jobs
More From Author
Its Too Gud.... |
Too good man.....nice to read.... |
मुझे तो मर ही जाना होता है,पर मैं अमर रहता हूँ। कोई समझ पाए या नहीं मेरे प्रेम की कीमत,पर होती वो मेरी जान के बराबर है। मैं एक छोटा सा पतंगा ये जानते हुए कि कभी भी कहीं से एक लौ के वार से मैं अपनी साँसें खो दूँगा। मैं चल पड़ता हूँ पागलों की तरह... |