Shrink log file and db after hard db activities
-
I have a store procs that performs inserting, updating and deleting, increasing the log file.
What is the best strategy to shrink db end/or log file using t-sql ?
thank
-
If your DB is very transaction oriented, Backup your log regularly. Make sure your DB is running in full recovery mode.
If you are not concerned about backing up your trans log, change your recovery model to simple. You can only rely on full backups in this mode. In Simple mode - your log will be truncated on checkpoint, which will keep your log file in check.
-
the problem is that the store proc will be shipped to several server that they are managed by others admins and belong to differnt companies, so I thought to shrink files and then db just to reduce the size.
any help will be very appreciated.
-
As a general practice you should not run shrink. The DB needs space to operate in and will likely just grow again. Also, shrink introduces fragmentation when it moves data around. See SQL Server 2005 BOL for shrink where I've documented this recommendation.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005 -
OK,
but when log file increase about 4 - 5 GB every time the store proc run (especially tempdb), the space on harddisk is not infinite.
How can I do?
thank
-
The log file is increased by 4-5 Gb, or the data file? I think you mean data file for tempdb.
What does this SP do?
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005 -
the sp move datafrom a database to another including +/- 500 tables and...too much records.
SQLServer 2000 has the autoshrink option; I have never used, but if Microsoft provides this tool means that shrinking db is sometimes useful during no or low activieties.
In my case I think shrink db is needed to avoid HD get full and during moving process start a rollback for low space.
Any contribuition to discussion is requested.....
thank
-
Take a look at my signature - I'm responsible for all the Storage Engine in MS and I'm telling you that autoshrink is discouraged. Shrinking is only useful in a very limited number of scenarios and you haven't given enough info to tell what your scenario is yet.
Can you answer the questions I asked? I'll restate:
1) which database are you concerned about - tempdb or a user database?
2) is it the log files or data files that are growing in size?
3) do they grow by the same amount every time the SP is run, or once the growth has occured, subsequent executions of the SP do not cause any more growth?
4) what recovery mode do you use?
5) how big is the database? and the HD on which its hosted?
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005 -
Three DBs are involved in transferring process:
source db, temp db and destination db. Tempdb is used to make insert and update because of constraints between tables.
Insert and update are under a transaction. It is a long transaction (there isn't another solution otherwise constraints between tables will be broken in case of failure)
After the process tempdb log file inceases as well destination db one.
Every process runs, incresing occur (depending on data moved).
Recovery mode and HD size are variable. As said at the beginning I do not manage the SQL Servers, so I cannot answer.
.....waiting and carry on this interesting discussion.
-
So it seems like the destination db data growth can't be helped, as you're simply inserting a bunch of data into it. Its log file growth could be limited by inserting the data using a bulk load command and having the DB in bulk-logged recovery mode. If you're in full recovery mode, you can manage the log file size by taking log backups. See BOL for details on all of these topics.
I would leave tempdb data files alone - if you shrink them then they will just grow again the next time you run the data transfer. Same goes for the log files. Because you have a long-running transaction, the log cannot be truncated while that transaction is active. Even though tempdb uses the minimally-logged recovery mode, this restriction still applies to allow for rollback. If you shrink the transaction log after this transaction has finished, it will just grow again the next time it is run. You should find that it subsequent runs of your transfer process will only cause the log to grow if they caused more log to be written than the previous transfer processes.
If you can't afford the disk space of having these files remain large between runs of the transfer process then I recommend getting more HD space rather than taking the double perf hit (once to shrink and once to grow) of doing the shrinks every time the transfer process runs.
Hope all that made sense.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply