March 7, 2016 at 11:19 am
Hi i want to shrink my database, This is a critical server and i am not a sql admin. Experts help me.
When i run the query i get the below result.
Database Filetype Name physical name size used available date
SQLDB ROW SQLDB C:\PF.. 153680 57326 96354 2016-03-07
Can i run the below command, if i run the command will i face any issues with the application or anything related to DB. I have a monitoring and reporting application and i have set the logs to be kept for 6 months to generate reports . By doing so will i loose any logs.
DBCC SHRINKFILE(SQLDB, 50000)
March 7, 2016 at 12:51 pm
risingflight143 (3/7/2016)
Hi i want to shrink my database, This is a critical server and i am not a sql admin. Experts help me.
If it's a critical server and you are not a sql admin, then you should not touch it until you know what you are doing. (Sorry to sound harsh, but if it's truly critical to the organisation, then truly they can afford to hire an expert or send you on training)
When i run the query i get the below result.
Which query?
Can i run the below command, if i run the command will i face any issues with the application or anything related to DB.
Yes you can, and yes you will (probably).
Why do you want to shrink the database? Did you recently clear out a lot of data and are you sure that it will never need that much space again? Because if it does, the file will simply grow back, cuasing delays and slow response while it is growing, and causing extra fragmentation.
I have a monitoring and reporting application and i have set the logs to be kept for 6 months to generate reports . By doing so will i loose any logs.
The database log files are not used for monitoring or reporting.
If you are responsible for this critical database (what we here call the "accidental DBA"), then the very first thing you need to do, well before even thinking about freeing disk space, is to understand backup and recovery, and the role of the database's data file and log file in that; as well as recovery models.
A great explanation of backup and restore strategies can be found here: https://www.simple-talk.com/sql/backup-and-recovery/sql-server-2014-backup-basics/%5B/url%5D.
After that, a very good more advanced explanation of the transaction log can be found here: http://www.sqlservercentral.com/stairway/73776/%5B/url%5D.
March 7, 2016 at 7:17 pm
I am running out of disk space so i need to cleanup
March 7, 2016 at 9:23 pm
Use caution as you proceed here. If you don't know the impact of shrinking a database, then you shouldn't be doing it. As an example, when you shrink a data file, it has a nasty tendency to fragment your indexes to 99%. You're going to have some database maintenance to do.
If this database is critical to your organization, then look for other ways to free up space. Talk to the SAN admin to see if you can get more space allocated. If that's not an option, look at other databases on the same instance. Look at how the files are allocated to different drives and see if you have files you can move around.
Also, if you're responsible for the database, then you should really do some reading and studying to get up to where you need to be to maintain the databases. If this is a new area of responsibility for you, I'd start with getting a handle on the backups. Hiring a consultant to help with your immediate need will get the problem solved. It'll also buy you the time to learn what you need to learn. If you're an accidental DBA and also the only one, start learning.
Whatever you do, do yourself a favor and take a full backup before you start - just in case you need it.
March 9, 2016 at 1:57 am
risingflight143 (3/7/2016)
I am running out of disk space so i need to cleanup
Then look for old files you can delete or move, and ask about getting more drive space.
Shrinking DBs is not the answer to low drive space. Databases grow. Shrink is not some magic compression, all it does is move free space from inside the database files to outside. The next thing that will happen as the DB continues to be used will be the DB growing again, leaving you worse off than you were before.
Oh, and 'critical DB' with no SQL admins = disaster waiting to happen. Strongly recommend you speak to your bosses about either getting SQL training or getting someone in that does know DBs.
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
March 9, 2016 at 2:14 am
prettsons (3/7/2016)
Hi Newbie,Compress the backup folder to get more free space.
Surely a "recovery expert" wouldn't have the backups on the same drive as the database files - or have I misunderstood your advice?
John
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply