asked on Reducing the SQL Server database and logs smallest possible without data loss ?
Hi All,
I'm running the below database in older physical box:
and planning to migrate it to different box using offline backup method (cold backup). Hence I need to shrink the SQL server files to reduce the manual copy time.
Without impacting or configuring the database size from the application using the SQL server, how can I shrink the SQL .MDF and .LDF file size ? I need some steps and guidance in this.
Is there any impact on the application when I shrink those databases files to the smallest possible ?
Thanks in advance.
I'm running the below database in older physical box:
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
and planning to migrate it to different box using offline backup method (cold backup). Hence I need to shrink the SQL server files to reduce the manual copy time.
Without impacting or configuring the database size from the application using the SQL server, how can I shrink the SQL .MDF and .LDF file size ? I need some steps and guidance in this.
Is there any impact on the application when I shrink those databases files to the smallest possible ?
Thanks in advance.
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005Windows Server 2008SSRS
Last Comment
Albert Widjaja
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Guy,
What settings should I select for the DB files shrink (.MDF) below ?
![Database]()
What settings should I select for the Log files shrink (.LDF) below ?
![Logs]()
What settings should I select for the DB files shrink (.MDF) below ?
What settings should I select for the Log files shrink (.LDF) below ?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Cool,
I have just did select File Type: Logs and then selecting Release unused space and it went so quick, I can now see significant disk space released.
Can i do:
File Type: Data and then selecting Release unused space again for all SQL server database.
I have just did select File Type: Logs and then selecting Release unused space and it went so quick, I can now see significant disk space released.
Can i do:
File Type: Data and then selecting Release unused space again for all SQL server database.
you cannot do that "for all databases" unless you start scripting the commands (using dbcc shrinkfile)
ASKER
Ah yes, but if I can do it manually one by one then it is not a problem right ?
I need to shrink both DB & logs to the smallest possible.
I need to shrink both DB & logs to the smallest possible.
no problem to do it one by one (in the gui)
ASKER
Thanks !
ASKER
Hi Guy,
When I shrink the databse from GUI, I got this:
When I shrink the databse from GUI, I got this:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Shrink failed for Database 'CSR-DB'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Shrink+Database&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Lock request time out period exceeded.
Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5343&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
This bascially means you still have users on the db, maybe "yourself"
this is sometimes tricky using the GUI, when you have some tables open in edit more or so, or simply an open transaction on the db.
you need to make sure everybody having some long/locking transcations gets logged off
this is sometimes tricky using the GUI, when you have some tables open in edit more or so, or simply an open transaction on the db.
you need to make sure everybody having some long/locking transcations gets logged off
ASKER
Already did, the application services are all stopped.
I cannot even detach it and cannot Turn offline the database ?
I cannot even detach it and cannot Turn offline the database ?
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop all active database connections failed for Server 'PRODDB01'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.5058.0+((SQL11_PCU_Main).140514-1820+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+all+active+database+connections+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Only user processes can be killed. (Microsoft SQL Server, Error: 6107)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5343&EvtSrc=MSSQLServer&EvtID=6107&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
as I said, it may be "yourself".
in the GUI, if you expanded the database, or have an open sql command sheet open to the db (use <database>, it's already that.
you can check in the active sessions if it's really you.
make sure that your session is linked to another db, and then just right-click on the database in the GUI to start the operations
other than that, I would see replication, or sql agent jobs (backups?) running?
in the GUI, if you expanded the database, or have an open sql command sheet open to the db (use <database>, it's already that.
you can check in the active sessions if it's really you.
make sure that your session is linked to another db, and then just right-click on the database in the GUI to start the operations
other than that, I would see replication, or sql agent jobs (backups?) running?
ASKER
Yes, you are awesome :-)
After I close the SSMS and then re open without opening the script window, I can perform the Database Shrink succesfully.
After I close the SSMS and then re open without opening the script window, I can perform the Database Shrink succesfully.
I prefer to use the GUI, is it using:
Right click at the DB Tasks > Shrink > Database
Right click at the DB Tasks > Shrink > Files (I assume this is the log files) ?
Is there any specific order to do it ?
DB first or log first ?