May 19, 2010 at 10:49 pm
My little test db has exploded to 700 MB, log file 30MB
How does one shrink it ???
Code or manually??
Steps please, thanks 🙂
UPDATE: All sorted..
What do you guys do with SQL server and websites, do you have a JOB that runs and shrinks DB every 24 hours or something ???
May 19, 2010 at 11:27 pm
dbcc shrinkfile
You can use this command to shrink the individual files (log or data) to a size that suits your needs.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2010 at 4:23 am
Digs (5/19/2010)
What do you guys do with SQL server and websites, do you have a JOB that runs and shrinks DB every 24 hours or something ???
Ouch, no! Shrink is a bad thing to do regularly. It should be used for emergencies only. If you find yourself needing to shrink often then you aren't managing the database properly.
You should either be doing log backups regularly or if that's not required then choose SIMPLE recovery - but only once you've understood the implications and know how you intend to backup and recover the database.
May 20, 2010 at 9:59 am
700 MB is really a small database. 30 MB is tiny for a log file as well.
There really is no reason to shrink this database unless you are using it as a design database. I would not shrink the database on a regular basis if it is in production. The database is probably that size due to it needing to be that size.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2010 at 12:16 pm
So what does one do to manage a database size.
Back ups sure..
Doesnt a SQL server need maintenance after records have been deleted ?
May 20, 2010 at 12:19 pm
Digs (5/20/2010)
So what does one do to manage a database size.Back ups sure..
Doesnt a SQL server need maintenance after records have been deleted ?
The maintenance to perform after deleting records (if a mass quantity) is to ensure indexes and statistics are up to date. I would leave the database (size) as is due to the small size of it. You will likely need to be reusing that space at some future point.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2010 at 12:45 pm
Digs (5/20/2010)
Doesnt a SQL server need maintenance after records have been deleted ?
No, not usually, because data tends to grow faster than rows get deleted and the space freed by deleted rows and backed-up log files will get re-used anyway. It's best to pre-allocate as much space as you think you will need for at least several months ahead and then just monitor it and add more space if required in future. Only shrink the database if you think you aren't going to need the space again (very unlikely unless your data is static or is actually shrinking).
However, I didn't notice the sizes you specified when you posted originally. Your database is tiny. You probably don't need to worry much about space management but your log file is almost certainly too small already. You need to avoid having the log file grow and shrink a lot because those are very expensive and disruptive operations and they cause fragmentation.
May 31, 2010 at 3:10 am
You can Use the following Query to Shrink the Log File.
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
For More Info Please Read : http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
May 31, 2010 at 3:31 am
Note that TRUNCATE_ONLY is deprecated. It only works in 2000/2005 and not in 2008 and later.
Also before using this you should be aware that it leaves you vulnerable to data loss until you do the next full backup. That is mentioned in the blog article given in the link here but just thought I'd mention it in case someone is tempted just to follow the advice without reading the information given. The bottom line is that if you choose the right recovery model and manage your backups properly then you won't have to do this. I would not recommend using the BACKUP TRUNCATE_ONLY option.
May 31, 2010 at 3:40 am
smratisharma (5/31/2010)
You can Use the following Query to Shrink the Log File.USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
For More Info Please Read : http://blog.sqlauthority.com/2006/12/30/sql-server-shrinking-truncate-log-file-log-full/
That is appalling advice to be tossing around. Truncating a log is not recommended if the DB is in full or bulk-logged recovery (and is not necessary at all if the DB is in simple recovery) and shrinking the log down as small as possible is just going to result in it regrowing next time data is added to the DB, resulting in log fragmentation (lots of VLFs) and possibly external file-system fragmentation.
Take a look through this article - http://www.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply