Solved

Database maintanence plan

Posted on 2006-11-21
7
281 Views
Last Modified: 2006-11-22
I have a database maintenance plan that reorganizes data & index pages, changes free space per page percentage to 10%, removes unused space from database files and shrinks database when it goes beyond 50mb. (db is 5gb - not sure if this should change to 6gb rather than 50mb?)

When this runs from time to time (3:00AM) the websites using the database throw errors until this completes - upwards of 2 hours.

Is there any other way to run this? Break it up at all? I would rather not turn it off.
0
Comment
Question by:just1coder
7 Comments
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
ID: 17987327
>>Is there any other way to run this? Break it up at all? I would rather not turn it off.
You should schedule it to run once or twice a week and it should be run on sunday or any offpeak day when user traffic is less.

>>db is 5gb - not sure if this should change to 6gb rather than 50mb?)
you should change it to 6 GB


0
 
LVL 2

Author Comment

by:just1coder
ID: 17987344
Will that effect performance of the database at all not having it run nightly?

will change it to 6...
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17987412
Will that effect performance of the database at all not having it run nightly?
No i don't think so!

will change it to 6.
Yes
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 2

Author Comment

by:just1coder
ID: 17987446
What does the database shrinking do? Aside from shrinking it of course... by why is it important?
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17987493
>>What does the database shrinking do? Aside from shrinking it of course... by why is it important?
Shrinking Database is not a Good Practice
This is from Books online
Microsoft® SQL Server™ can shrink:

All data and log files for a specific database. Execute DBCC SHRINKDATABASE.
One data or log file at a time for a specific database. Execute DBCC SHRINKFILE.
DBCC SHRINKDATABASE shrinks data files on a per-file basis. However, DBCC SHRINKDATABASE shrinks log files as if all the log files existed in one contiguous log pool.

Assume a database named mydb with two data files and two log files. Both data and log files are 10 MB in size. The first data file contains 6 MB of data.

For each file, SQL Server calculates a target size, which is the size to which the file is to be shrunk. When DBCC SHRINKDATABASE is specified with target_percent, SQL Server calculates target size to be the target_percent amount of space free in the file after shrinking. For example, if you specify a target_percent of 25 for shrinking mydb. SQL Server calculates the target size for this file to be 8 MB (6 MB of data plus 2 MB of free space). Therefore, SQL Server moves any data from the last 2 MB of the data file to any free space in the first 8 MB of the data file and then shrinks the file.

Assume the first data file of mydb contains 7 MB of data. Specifying target_percent of 30 allows this data file to be shrunk to the desired free percentage of 30. However, specifying a target_percent of 40 does not shrink the data file because SQL Server will not shrink a file to a size smaller than the data currently occupies. You can also think of this issue another way: 40 percent desired free space + 70 percent full data file (7 MB out of 10 MB) is greater than 100 percent. Because the desired percentage free plus the current percentage that the data file occupies is over 100 percent (by 10 percent), any target_size greater than 30 will not shrink the data file.

For log files, SQL Server uses target_percent to calculate the target size for the entire log; therefore, target_percent is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. DBCC SHRINKDATABASE attempts to shrink each physical log file to its target size immediately. If no part of the logical log resides in the virtual logs beyond the log file's target size, the file is successfully truncated and DBCC SHRINKDATABASE completes with no messages. However, if part of the logical log resides in the virtual logs beyond the target size, SQL Server frees as much space as possible and then issues an informational message. The message tells you what actions you need to perform to move the logical log out of the virtual logs at the end of the file. After you perform the actions, you can then reissue the DBCC SHRINKDATABASE command to free the remaining space. For more information about shrinking transaction logs, see Shrinking the Transaction Log.

Because a log file can only be shrunk to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file, even if it is not being used. For example, a database with a log file of 1 GB can have the log file shrunk to only 128 MB. For more information about truncation, see Truncating the Transaction Log. For more information about determining virtual log file sizes, see Virtual Log Files.

The target size for data and log files as calculated by DBCC SHRINKDATABASE can never be smaller than the minimum size of a file. The minimum size of a file is the size specified when the file was originally created, or the last explicit size set with a file size changing operation such as ALTER DATABASE with the MODIFY FILE option or DBCC SHRINKFILE. For example, if all the data and log files of mydb were specified to be 10 MB at the time CREATE DATABASE was executed, the minimum size of each file is 10 MB. DBCC SHRINKDATABASE cannot shrink any of the files smaller than 10 MB. If one of the files is explicitly grown to a size of 20 MB by using ALTER DATABASE with the MODIFY FILE option, the new minimum size of the file is 20 MB. To shrink a file to a size smaller than its minimum size, use DBCC SHRINKFILE and specify the new size. Executing DBCC SHRINKFILE changes the minimum file size to the new size specified.

When using data files, DBCC SHRINKDATABASE has the NOTRUNCATE and TRUNCATEONLY options. Both options are ignored if specified for log files. DBCC SHRINKDATABASE with neither option is equivalent to a DBCC SHRINKDATABASE with the NOTRUNCATE option followed by a DBCC SHRINKDATABASE with the TRUNCATEONLY option.

The NOTRUNCATE option, with or without specifying target_percent, performs the actual data movement operations of DBCC SHRINKDATABASE including the movement of allocated pages from the end of a file to unallocated pages in the front of the file. However, the free space at the end of the file is not returned to the operating system and the physical size of the file does not change. Therefore, data files appear not to shrink when the NOTRUNCATE option is specified. For example, assume you are using the mydb database again. mydb has two data files and two log files. The second data file and second log file are both 10 MB in size. When DBCC SHRINKDATABASE mydb NOTRUNCATE is executed, Microsoft SQL Server moves the data from the later pages to the front pages of the data file. However, the file still remains 10 MB in size.

The TRUNCATEONLY option reclaims all free space at the end of the file to the operating system. However, TRUNCATEONLY does not perform any page movement inside the file or files. The specified file is shrunk only to the last allocated extent. target_percent is ignored if specified with the TRUNCATEONLY option.

The database cannot be made smaller than the size of the model database.

The database being shrunk does not have to be in single user mode; other users can be working in the database when it is shrunk. This includes system databases.

0
 
LVL 4

Expert Comment

by:Clothahump
ID: 17988283
Two hours to do all that on a 5 gb database?  Something seems out of whack.

I would change the maintenance plan to run once a week at your best off-time (probably 0200 Sunday morning or something like that).  Do the reorganization and cleanup, but take out the shrinking.  Also see if you can modify your website to announce that the database is down for maintenance.  If you can keep the users from changing the database while your reorganization is going on, you are much, much better off.

Set up a second plan that runs once every 3 or 4 months and let it do the shrinking.  That's about as often as you need to do that.
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17992542
try to create:
-one plan for reindex (or use sql agent job based on the DBCC INDEXDEFRAG- see more BOL and http://weblogs.sqlteam.com/tarad/archive/2004/06/22/1657.aspx)
- another plan for shrinking
-etc.
and see result
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now