August 30, 2010 at 12:55 am
I was checking the space remaining in drive using
exec GetFileSpaceStats 1
EXEC master..xp_fixeddrives
but it show me an error that two databases are offline.
So how can i shrink the database?
Thanks a lot.
August 30, 2010 at 1:06 am
Kindly provide more information.
1) What is this procedure (GetFileSpaceStats) will return.
2) WHen u r using xp_fixeddrives, why r u using GetFileSpace Stats
3) Try to connect to SSMS and chek if you find any of the dbs in offiline mode.
August 30, 2010 at 1:16 am
chetanr.jain (8/30/2010)
Kindly provide more information.1) What is this procedure (GetFileSpaceStats) will return.
2) WHen u r using xp_fixeddrives, why r u using GetFileSpace Stats
3) Try to connect to SSMS and chek if you find any of the dbs in offiline mode.
1) It will return the information about database,primary file name,log file name,file location (in my case i have to look for E drive only, there are also F,X,Y,Z drive.) space used and remaining.
If i run this procedure then i will know for which database i have to run the query.
I can also know how much space i can use of total free space etc.
2) We can't get all the information (Databse name, file and their location )using xp_fixeddrives.
3) Northwind and Pubs are database that are offline.
I am using below script for shrinking database.
DECLARE @StartingFileSize INT,
@EndingFileSize INT,
@Filename VARCHAR(30),
@FileID INT, --Get FileID from sysfiles in database. (select * from sysfiles)
@MBToShrink INT --This is the amount that the file will be shrunk in MB. Adjust accordingly.
SELECT @FileID = 1, @MBToShrink = 30000 --20GB
SELECT @StartingFileSize = size / 128,
@FileName = name
FROM sysfiles
WHERE fileid = @FileID
SET @EndingFileSize = @StartingFileSize - @MBToShrink
PRINT DB_NAME()
PRINT @StartingFileSize
PRINT @EndingFileSize
PRINT @FileName
WHILE @EndingFileSize < @StartingFileSize
BEGIN
SET @StartingFileSize = @StartingFileSize - 1000
DBCC SHRINKFILE ( @FileName , @StartingFileSize )
END
August 30, 2010 at 3:01 am
Hi, the first thing we would need to investigate is why are the dbs being stated as offline. Are you able to connect through SSMS as mentioned above? Is there anything else like probably a reindex or something running on them which is making them to be reported as offline?
August 30, 2010 at 4:16 am
The database which are OFFLINE cannot be shrunk. Alter your query to exclude those offline files or bring the databases online for your query to work fine.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 30, 2010 at 2:29 pm
Why are you shrinking databases? This should not be an automated process, or something that is done on a regular basis - even if not automated.
Databases will grow - that is what they are designed to do. Continually shrinking and growing the data files will cause file level fragmentation, index fragmentation and performance issues.
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
August 31, 2010 at 1:55 am
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
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
August 31, 2010 at 2:19 am
Jeffrey Williams-493691 (8/30/2010)
Why are you shrinking databases? This should not be an automated process, or something that is done on a regular basis - even if not automated.Databases will grow - that is what they are designed to do. Continually shrinking and growing the data files will cause file level fragmentation, index fragmentation and performance issues.
Yes it is automated process.
But when it goes above 90% it will pop up the message,then we have to shrink it.
August 31, 2010 at 2:48 am
Better to have Log backup ( instead of Shrink) when you get an alert for usage of more then 90%.
August 31, 2010 at 8:05 pm
EasyBoy (8/31/2010)
Jeffrey Williams-493691 (8/30/2010)
Why are you shrinking databases? This should not be an automated process, or something that is done on a regular basis - even if not automated.Databases will grow - that is what they are designed to do. Continually shrinking and growing the data files will cause file level fragmentation, index fragmentation and performance issues.
Yes it is automated process.
But when it goes above 90% it will pop up the message,then we have to shrink it.
I guess you are not paying attention - it is not recommended that you schedule shrink operations on database files. If you shrink the data file, you are causing performance issues for your system. First, the shrink will cause your indexes to become highly fragmented. Second, the shrink will cause file fragmentation because the data file is just going to grow again.
And finally, you need to keep space available in the data file for normal operations. When you perform a reindex operation, you want enough space available in the data file to accomodate the largest index/table in your system. If you don't have that, the data file will grow. You also want to have several months worth of space available - and monitor it, so you don't run into an autogrowth which is an expensive operation and will cause performance issues.
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
September 1, 2010 at 2:08 am
Jeferrey
Thanks for such a great explanation. Greatly appreicate your help
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply