| Author |
 Topic  |
|
|
WillG
Starting Member
United Kingdom
3 Posts |
 Posted - 08/15/2012 : 08:35:10
|
Hi
We had a problem where one of our servers crashed and our database was restored from a .bak file.
Since the restore the Database size has been increasing at a much higher rate than it was before the restore.
I have looked at the size of database using: sp_spaceused and the result was:
Reserved: 13260512 KB
Data: 3621616 KB
Index_size: 571096 KB
Unused: 9067800 KB
As you can see most of the size of the database is from unused space.
The indexes are set to rebuild every week and the free space per page percentage is set to 10%.
Also every night as a part of the Maintenance plans the option to 'Remove unused space from database files' is run with the options, Shrink Database when it grows beyond 50MB and set to 10% space to remain after Shrink.
How can i get rid of the unused space to bring the database size down as we are rapidly running our of disk space due to the backups etc.
Any help greatly appreciated.
|
|
|
komkrit
Yak Posting Veteran
Thailand
60 Posts |
Posted - 08/15/2012 : 21:59:15
|
Shrink database and reorganize file
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
 |
|
|
komkrit
Yak Posting Veteran
Thailand
60 Posts |
Posted - 08/16/2012 : 00:39:39
|
On Maintenance Plan task "Shrink Database Task", there is an option as following, choose option 2 to release unused space.
1. Retain freed space in database files
2. Return freed space to operating system
Let us know if it done and does not reduce in database size.
Or try following command and compare the result.
DBCC SHRINKDATABASE(N'YourDatabaseName', 10 )
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
|
WillG
Starting Member
United Kingdom
3 Posts |
Posted - 08/16/2012 : 03:48:24
|
Hi Komkrit
Thanks for your reply. It in SQL 2000 (should have added that sorry) so the Shrink options are just:
Remove unused space from database files - Which is ticked with the following sub options:
Shrink database when it grown beyond: 50MB
Amount of free space to remain after shrink: 10%
I cant try the DBCC SHRINKDATABASE(N'YourDatabaseName', 10 ) until later on this evening when the server is not being used but i will try that and let you know the result. Thanks.
|
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1773 Posts |
Posted - 08/16/2012 : 15:34:28
|
It is important to understand why it is growing. Is temporary data being written then deleted? This implies that the space is required. In those circumstances , you could be creating more of a problem.
On the other hand , if in analysing you see that the unused space is never used - for example - it was a one-off delete, then issuing a SHRINKDATABASE is good value
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
WillG
Starting Member
United Kingdom
3 Posts |
Posted - 08/17/2012 : 05:01:42
|
Hi
Komkrit i tried what you suggested running DBCC SHRINKDATABASE(N'YourDatabaseName', 10 ) however this had no effect on the unused space.
Jack - Thanks for your reply, how would i go about analysing if the space is getting used or not? Are then any specific commands to run? (Sorry i am new to SQL administration, kind of got lumbered with when other staff left).
Cheers
G
|
|
|
|
jackv
Flowing Fount of Yak Knowledge
United Kingdom
1773 Posts |
Posted - 08/17/2012 : 09:18:07
|
A few different options 1) you could monitor the DMLs executed against the db - particuarly focusing on INSERT and DELETE statements , which are most likely linked to increased | decreased usage. Also , for any ETL jobs that might have BULK jobs. Typical places you coul look include : SQL Server Agent and SSIS
2) You could SHRINK down the db and see how much it grows
Jack Vamvas
--------------------
http://www.sqlserver-dba.com |
|
|
|
komkrit
Yak Posting Veteran
Thailand
60 Posts |
Posted - 08/18/2012 : 00:53:22
|
To G,
Thanks for information.
In my case, I have ever seen a case of database unused space could not be shrinked.
It is from a database have number of inconsistence allocation unit.
Check by running this command(be awared, it takes long time, let it run until completely)
------------
USE <yourdatabasename>
DBCC CHECKDB WITH TABLOCK
------------
If any error found(red text), disconnect all sessions and put database to single-user mode.
Try this command
------------------
DBCC CHECKDB (<yourdatabasename>, REPAIR_ALLOW_DATA_LOSS)
------------------
Have a nice day
Komkrit
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
|
komkrit
Yak Posting Veteran
Thailand
60 Posts |
Posted - 08/18/2012 : 01:04:26
|
To G,
Refer to msdn website --> http://msdn.microsoft.com/en-us/library/ms190488.aspx
It just be cleary that we have to run this command sequentially to
- re-organize page
- and release unused data
DBCC SHRINKDATABASE (<yourdatabasename>, NOTRUNCATE)
DBCC SHRINKDATABASE (<yourdatabasename>, TRUNCATEONLY)
Good Luck
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Komkrit Yensirikul
Currently be a DBA in worldwide hotel reservation. Experienced in top leading banking environment. |
|
|
| |
Topic |
|
Database Size Issues
