July 8, 2011 at 9:22 am
On SQL Server 2008 (version 10.0.1600), I have a database (set up by an engineer in my company, without talking to a dba) that receives proxy log transactions. Running the command
"SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;"
yields the result: 19906.093750 MB available space for the log file.
The command: "select * from sys.database_files" tells me that the database size is "5882904" and the log size is "2791232" - presumably these are record counts? The disk space consumed are 47,063,232 (.mdf) and 22,329,856 (.ldf). Running DBCC Shrinkfile appears to have no effect on the logfile at all, telling me that
Dbld = 6
Field = 2
CurrentSize = 2791232
MinimumSize = 13056
UsedPages = 2791232
EstimatedPages = 13056
I would think that based on the available space I would get better shrinking than virtually none. The Used Pages in particular I find disturbing, given the smaller Estimated Pages. Yes, I ran a backup just before executing the SHRINKFILE. Obviously, there's more to be done here...I am not certain where to begin nor how to ensure I don't skip something.
(database is hosted on a virtual server running out of disk space, which is why i was originally invited to the party)
Any and all suggestions / ideas/ obvious things I've overlooked are welcome. yes, I know to setup regularly scheduled backups (frequency? database currently collects close to a million transactions per day and growing.) .mdf is kept stable by deleting all records over 90 days old. Database is used as a 'one-off' production for providing source data for reports. We plan to use it to build archives of the proxy logs, but I'm not ready to ask anything about that process yet... And, of course, on a $0 budget for acquiring new tools.
TIA
Steve
July 8, 2011 at 11:39 am
Try putting the database into SIMPLE mode, then back into FULL mode. Then do a full backup and a transaction log backup.
See if your DBCC shrinkfile will work properly.
If that still fails, detach the database, then rename the .LDF file.
Then reattached the .MDF file, but remove the .LDF file from the process. Force SQL Server to create a new .LDF file.
July 8, 2011 at 11:53 am
JamesMorrison (7/8/2011)
If that still fails, detach the database, then rename the .LDF file.Then reattached the .MDF file, but remove the .LDF file from the process. Force SQL Server to create a new .LDF file.
No. Please no.
Don't ever delete a database's log file. SQL cannot always recreate it and if it can't you'll be left with a database that refuses to attach.
Also no need to switch to simple recovery. Take 2 log backup (I can explain the technical reason behind taking 2 if you like) and then try the shrink again.
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
July 8, 2011 at 11:59 am
That said, are you sure you should be shrinking at all?
Unless the log grew very large as a product of some unusual operation, the size that it is is the size it needs to be for regular operation. If you shrink it, it'll just grow back to that size. Regrowing a log file takes time & slows the DB down and causes log fragmentation that will slow backups, db recovery and a few other things down
Check that if the DB is in full or bulk-logged recovery that you have regular log backup (not full or diff backups. Log backups). If point-in-time recovery is not required consider switching to simple recovery.
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
July 8, 2011 at 12:41 pm
GilaMonster (7/8/2011)
JamesMorrison (7/8/2011)
If that still fails, detach the database, then rename the .LDF file.Then reattached the .MDF file, but remove the .LDF file from the process. Force SQL Server to create a new .LDF file.
No. Please no.
Don't ever delete a database's log file. SQL cannot always recreate it and if it can't you'll be left with a database that refuses to attach.
Please note, I did not say to "delete" the log file. I told him to rename it.
I typically just add something like "_SAVE" to the end of the .LDF file name.
The purpose of that is so you can rename it back to the original name and re-use the .LDF if you need to go back to it.
I have done this many times without any problem. If you are having an issue, it is likely the exception, not the norm.
It is a well documented process that is provided for by SQL Server.
And if he has a full backup already standing by, there is no risk involved. Just restore from the .BAK.
He is only trying to get the .LDF smaller for space reasons. Althought if it is a virtual server, they really need to assign it more resources from the SAN. It really depends on the situation.
July 8, 2011 at 1:03 pm
GilaMonster (7/8/2011)
Unless the log grew very large as a product of some unusual operation, the size that it is is the size it needs to be for regular operation. If you shrink it, it'll just grow back to that size. Regrowing a log file takes time & slows the DB down and causes log fragmentation that will slow backups, db recovery and a few other things down
If the database already has the VLF (virtual log file) issue, then that would be reason enough to shrink the log file and then manually grow it back to the size it needs to be. Growing it manually in chunks of 8,000 MB is the most efficient increment to minimize the number of VLFs.
To the OP, run DBCC LOGINFO on your FULL databases that have a large .LDF (anything over 8 GB).
If that returns a number of rows measured in the thousands, that you might have a VLF performance issue.
Once done properly, your row count from DBCC LOGINFO should only be a few hundred records.
On our large production databases, I set the log file to autogrow by 8,000 MB when it needs to.
This is documented by Microsoft to be the most efficient for performance.
The default for SQL Server is whatever your Model database is. And the default for the Model data is 10% of log file size, starting at 1 MB.
When I got to my current company and reviewed the production databases, some of the .LDF files were 270+ GB and had 21,000 VLFs.
After fixing those issues (shrink and manually grow by 8,000 MB steps) the .LDF really only needed to be about 160 GB.
So it is a false assumption that the current size of the log file is what it needs to be. The inefficient VLF management issuse can explode the .LDF to a much larger size than it actually needs to be. Even though Microsoft claims to have fixed this with 2005 SP3 and 2008 SP1, that is not completely true. They made the management better, but it is still an issue.
July 8, 2011 at 1:07 pm
JamesMorrison (7/8/2011)
GilaMonster (7/8/2011)
Unless the log grew very large as a product of some unusual operation, the size that it is is the size it needs to be for regular operation. If you shrink it, it'll just grow back to that size. Regrowing a log file takes time & slows the DB down and causes log fragmentation that will slow backups, db recovery and a few other things downIf the database already has the VLF (virtual log file) issue, then that would be reason enough to shrink the log file and then manually grow it back to the size it needs to be. Growing it manually in chunks of 8,000 MB is the most efficient increment to minimize the number of VLFs.
Never said anything contrary that, so I don't know why you're bringing that up.
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
July 8, 2011 at 1:10 pm
JamesMorrison (7/8/2011)
I have done this many times without any problem. If you are having an issue, it is likely the exception, not the norm.It is a well documented process that is provided for by SQL Server.
That it's documented doesn't make it a good idea. It's an option for lost log files (drive failures, etc). It only works if the DB was cleanly shut down beforehand. That the command failing is an exception is not a point. It can and does fail and, if the log has actually been deleted is nasty to resolve.
There is virtually no situation where discarding the log is a recommended solution.
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
July 8, 2011 at 1:18 pm
GilaMonster (7/8/2011)
Never said anything contrary that, so I don't know why you're bringing that up.
I brought it up because that is a perfectly good reason for needing to shink the .LDF file.
The VLF issues can explode your log size to much larger than it needs to be during a reindexing.
Fixing the VLF issue can lead to a much smaller .LDF and that is the problem the OP is having.
In one of our production databases the .LDF was 270 GB before fixing the VLFs (21,000 rows)
After fixing the VLFs (277 rows) the .LDF only needed 160 GB at it's max.
Everyone should take a look at this because it is a common problem that databases can develop over time, especially if the original database was setup by someone that just accepted the Model database defaults.
July 8, 2011 at 1:20 pm
GilaMonster (7/8/2011)
JamesMorrison (7/8/2011)
I have done this many times without any problem. If you are having an issue, it is likely the exception, not the norm.It is a well documented process that is provided for by SQL Server.
That it's documented doesn't make it a good idea. It's an option for lost log files (drive failures, etc). It only works if the DB was cleanly shut down beforehand. That the command failing is an exception is not a point. It can and does fail and, if the log has actually been deleted is nasty to resolve.
There is virtually no situation where discarding the log is a recommended solution.
There are some very valid reasons for getting rid of the large log file and creating a new one. It depends on the situation.
Merely because you have not come across the need, that is no reason for you to be making false statements in such absolute terms.
Nobody suggested to delete the .LDF as part of the process.
Read what I wrote.
Rename the .LDF so it is available if needed.
I think you are just looking to argue for some reason, not really help the person fix the issue he is having.
July 8, 2011 at 1:32 pm
GilaMonster (7/8/2011)
That said, are you sure you should be shrinking at all?Unless the log grew very large as a product of some unusual operation, the size that it is is the size it needs to be for regular operation. If you shrink it, it'll just grow back to that size.
Just assuming that the log file needs to always by the maximum size that it has ever grown, is a false assumption.
There are numerous ways to lower the space that your .LDF needs to be while still maintaining FULL recovery mode.
The OP is having a space issue on a virtual server and appears to have an issue getting more resources.
He could look at the re-indexing strategy. Does every table really need to be re-indexed every time? No. That could lower the .LDF space requirements.
Fixing any possible VLF issues could also dramatically shrink the .LDF overall size.
July 8, 2011 at 4:10 pm
Gail and James,
Thank you for the debate! I was wondering about taking a second backup on the log file...I think i remember that being mentioned before somewhen on these forums (fora?). The problem with the the logfile growth was a lack of backups being taken for over 4 months of growth - Engineers know how to create, not necessarily the rules behind good design, especially when they have no training in databases and are using indirect install tools with embedded databases (speculating - I wasn't told how things were built, I was just given the space problem to solve).
At this point in time, we have a 'SAN' drive attached to give me room to work with, and we currently have 40 GB free on the C: drive (~25%), so I have a margin of error to work with.
I assume 2008 defaults to a non-simple mode during installation, and that defaults were used. I found language talking about commands to use in previous versions (7? 2000?), but the set command was apparently deprecated in 2005 and unavailable in 2008, so I don't have a correct syntax for changing the recovery mode.
Will post on Monday what my results were, after I get to work on this puppy again Monday morning.
Steve (now an occasional DBA, not even part time...:( )
July 9, 2011 at 3:19 am
steve smith-401573 (7/8/2011)
GThe problem with the the logfile growth was a lack of backups being taken for over 4 months of growth -
In that case you do want a once-off shrink. You can fix the VLFs now if you feel comfortable, or just fix the space problem now and the VLFs later
I assume 2008 defaults to a non-simple mode during installation, and that defaults were used. I found language talking about commands to use in previous versions (7? 2000?), but the set command was apparently deprecated in 2005 and unavailable in 2008, so I don't have a correct syntax for changing the recovery mode.
Yes. Default is full recovery. Are you happy with not having point-in-time recovery? Happy that any disaster means restoring the full backup and losing anything since. If so, just switch to simple recovery and remain there (and you don't need to worry about more log backups, that's for when you want to stay in full recovery)
ALTER DATABASE <db name> SET RECOVERY SIMPLE
That's the 2005/2008 version (I can't remember what SQL 2000 was)
Maybe also take a read through this - Managing Transaction Logs[/url]
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 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply