The issue came to light by the fact the previous DBA had set-up an email notification to an operator on a job failure. Anyway I started to investigate the issue and found that a maintenence job had failed to create a backup file throwing the infamous "Operating system error 112(error not found)" error. Now in my experience this error has, when investigated further led to a drive being out of space. Low and behold this was indeed the case!
Now the server had two databases which where offline one called Surfcontrol_webfilter (an internet access control product) and one called solarwinds (a network / server monitoring product) which in total where about 40GB in size. I spoke to the IT Operations guys who owned these products and both explained these where the repositories for old installations of the products which we did indeed have backups on tape of the last SQL Server backup and that they where no longer needed.
To reclaim some space and ensure the availability of the ONLINE databases I dropped the two unused offline databases and checked the drives free space using;
EXEC master.dbo.xp_fixeddrives;
To my initial surprise the drive still only had 20MB of free space? Then the penny dropped and a quick check, see the below link;
http://msdn.microsoft.com/en-us/library/ms178613.aspx
confirmed that;
"Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer."
So I logged onto the server and deleted the database files in question, another run of;
EXEC master.dbo.xp_fixeddrives;
confirmed that the space had been reclaimed and I now had 40GB of free space on the server.
OK not something that I learned as such, just something I had overlooked and needed confirmation of!
Chris
No comments:
Post a Comment