August 20, 2011 at 5:06 pm
I have a MSSQL 2008 R2 web database online.
I have just run some TSQL that took it from an ave of 600 MB to 3200 MB in database size.
A lot of delete and replace type code.
How do I reduce the the database to normal size ??
I know that SHRINK is a no no while online..
Also I know that one must go from multi to single user.
So what do I do, if I do anything at all ??
3200 MB is too large...
August 20, 2011 at 5:31 pm
DBCC SHRINKFILE is preferable, because you have more control over it.
You should only perform a shrink operation if you have a lot of space that you need to free up?
Shrinking data files should be avoided if possible as it causes fragmentation.
If you notice the size start to grow than you have shrunk the database too much.
You can get blocking by a transaction that is running under a row versioning-based isolation level when you are performing a DBCC SHRINKFILE and DBCC SHRINKDATABASE operation.
You can terminate the shrink operation and any completed work will be saved.
With the exception of the log file I would perform the Shrink Operation during the maintenace.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2011 at 7:01 pm
What the code to use
DBCC SHRINKFILE
When its a database, or is it a log file ??
My Database name is : DBWebProject
So do I do this:
DBCC SHRINKFILE (DBWebProject,64);
August 20, 2011 at 7:12 pm
You have the syntax correct but you want to use the logica name of the datafile and logfile.
The syntax is the same for the Log File as the Data File.
Check the logical filenames by right clicking on the Database in SSMS and select properties. Then Click on the Files Tab. You want the logical file name for each file group (Data and Log Files).
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 20, 2011 at 7:18 pm
Do you mean
C:\..\DBMyWebProject.mdf
August 20, 2011 at 7:21 pm
Digs (8/20/2011)
Do you meanC:\..\DBMyWebProject.mdf
Check the logical filenames by right clicking on the Database in SSMS and select properties. Then Click on the Files Tab. You want the logical file name for each file group (Data and Log Files).
It is probably DBWebProject_Data and DBWebProject_Data_Log but you need to verify.
DBCC SHRINKFILE (DBWebProject_Data,64);
Before you Truncate the Log you need to perform a backup of the Log File.
BACKUP LOG DBWebProject TO DISK = 'C:\Backup\DBWebProject.trn'
DBCC SHRINKFILE (DBWebProject_Log,1);
There are some arguments that you can use.
Refer to the following articles for some conditional considerations.
http://msdn.microsoft.com/en-us/library/ms189493.aspx
Don't use the NO_LOG or TRUNCATE_ONLY to manually truncate the transaction log.
NO_LOG and TRUNCATE_ONLY is deprecated and it will break you log chain and you will not be able to perform a Point in Time recovery.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 21, 2011 at 3:30 pm
I believe this database is in full recovery mode. If you are not able to shrink the database log files then you can try changing the recovery mode to simple and then try to shrink the log file. Then change the recovery mode back to full and take a full backup.
I don't think backup log with truncate only will work in 2008...
August 21, 2011 at 3:45 pm
BuntyBoy (8/21/2011)
I believe this database is in full recovery mode. If you are not able to shrink the database log files then you can try changing the recovery mode to simple and then try to shrink the log file. Then change the recovery mode back to full and take a full backup.I don't think backup log with truncate only will work in 2008...
TRUNCATEONLY was deprecated in SQL Server 2008 you would not use it unless you were not concerned about loss of data. Setting to the Simple Recovery would be an alternative and should only be used unless you did not have an alternative.
If you switch to a simple recory model SQL Server is going to truncate the log on checkpoint and you break the log chain and you will not be able to perform a point in time recovery. You would need to perform a complete backup immediately a pray that you do not have loss of data.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 21, 2011 at 3:51 pm
August 21, 2011 at 3:57 pm
I'm sure that you will get more responses and I expect that someone may provide an alternative solution to this issue. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply