February 21, 2010 at 7:29 pm
hi guys, i created a Database from script with 10 GBS the data files (4) and 2 GBs the log files, my database is empty right now, is there any way to resize the datafiles(i.e example from 10 GBs to 2 GBs) And the log file from (2 GBs to 800 mb)without droping and recreating the DB?
February 21, 2010 at 7:53 pm
DBCC SHRINKFILE is what you are after.
The following will shrinkfile 'datafile1' to 1000 MB
e.g. DBCC SHRINkFILE ('datafile1', 1000)
February 21, 2010 at 8:05 pm
There are maintenance Task to Shrink the Database or DBCC SHRINK DATABASE Statement.
There are also maintenance Task and corresponding DBCC REORG, DBCC REINDEX, and a Task to update Statistics.
When you Shrink the Database using the Maintenance Task you want to specify an Amount of Free Space for growth. You also have the option to Release the freed space to the Operating System.
You can also modify the Size using the ALTER DATABASE Statement and specifying the size.
With Databases that have very large tables, you will see the transaction log get huge. So if you have limited Disk storage, take that into consideration. I know that scenario does not apply to this situation.
Correct me if I'm wrong but DBCC SHRINKFILE Has been deprecated.
I would recommend Reindexing the Database and updating Statistics as well.
The is also a DBCC SHINKFILE Statement (see BOL). To Shrink the Transaction Log Back it up and then Shrink it to the desired size.
I could have stated this better but I'm sure that some of the Big Dog's (No pun intended) :-)will jump in and provide their input. I look forward to their comments. I'm getting tired...
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/
February 21, 2010 at 9:14 pm
Just an FYI, when I saw this post there were no replies. I was working on my reply concurrently as the other forum member.
I was not trying to upstage anyone.
I went to a SQL Server 2008 for experienced Oracle DBA's Freebie almost two years ago.
When I mentioned the DBCC SHRINFILE, the Instructor said that the DBCC SHRINKFILE had been deprecated...
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/
February 21, 2010 at 10:25 pm
Just don't ever setup a maintenance task to shrink files... you'll forget about it and have some pretty nasty performance problems because of it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2010 at 10:50 pm
Jeff,
I should have been more clear and I agree with you. 🙂
I never setup a maintenance task to shrink a Data or Log File. I would monitor and shrink on a routine basis.
I have setup maintenance task to re-index, etc and created Jobs but I disabled them because I typically make a backup and run some DBCC Commands prior to an operation that could potentially corrupt the Database.
Then I monitor very closely so that in that something goes wrong, I can react to it.
I have to watch out for people disconnecting Network Cables :w00t:, etc when I'm in the middle of a re-index, Shrink, etc.
Regards
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/
February 22, 2010 at 1:42 pm
thank you guys.
February 22, 2010 at 2:51 pm
dbcc shrinkfile has not been deprecated.
Not quite sure from your post but if your db has more than one log file get rid of the second one using dbcc shrinkfile with emptyfile option and alter database remove file.
---------------------------------------------------------------------
February 22, 2010 at 3:05 pm
Welsh Corgi (2/21/2010)
Jeff,I should have been more clear and I agree with you. 🙂
I never setup a maintenance task to shrink a Data or Log File. I would monitor and shrink on a routine basis.
I have setup maintenance task to re-index, etc and created Jobs but I disabled them because I typically make a backup and run some DBCC Commands prior to an operation that could potentially corrupt the Database.
Then I monitor very closely so that in that something goes wrong, I can react to it.
I have to watch out for people disconnecting Network Cables :w00t:, etc when I'm in the middle of a re-index, Shrink, etc.
Regards
It's not just about setting up a shrink in a maintenance plan. It is also about performing a shrink on a routine basis. You do not want to do that, because it is going to cause performance issues in the long run. Besides, if you are shrinking your database on a routine basis - then wouldn' it make more sense to leave the database at the larger size instead of shrinking it, and letting it grow again?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 22, 2010 at 4:27 pm
.
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/
February 22, 2010 at 6:00 pm
...
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/
February 22, 2010 at 7:19 pm
I quoted your statement, and I questioned your statement about shrinking databases on a routine basis. If you do not shrink your databases on a routine basis - then great, it is my misunderstanding of what you stated.
However, if you look back at your statement - that is exactly what you stated. It is the practice of routinely (scheduled or not) that leads to problems. It is not something that you should do routinely (again, your words - not mine).
There are times when you would shrink a data file or log file - but doing so on a regular basis (routinely) is not recommended.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 22, 2010 at 9:16 pm
You are taking what I said out of context.
Please do not distort what I'm saying or reinvent the conversation.
Be positive!
Thank you.
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/
February 22, 2010 at 9:30 pm
...
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/
February 22, 2010 at 9:37 pm
...
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 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply